3

We are in process of converting cognos reports to ssrs reports . i have a query which i got it from the cognos report and it is written in pervasive sql i am not sure about the language. I am able to interpret most of the query but i am stuck with one command XSUM. It has a syntax as

XSUM(col21 at col3 col7 col5 col5 for col3 col7) as col22 where col21,col3,col5, and col7 are columns from the table.

Can any one help me in understanding what the command XSUM does and what is the equivalent of it in T-sql please.

Thanks.

lch
  • 193
  • 1
  • 10
  • 20

3 Answers3

3

The X stands for extended.

Your report is probably doing some arithmetic on the application outside of the database that is generating these xsums and xmins.

Mez
  • 4,666
  • 4
  • 29
  • 57
  • hi, thanks for reply.we are not performing any operations outside the database as of i know. I have three sub queries and the data is coming from tables. How to know whether there is a application outside database which is performing these arithmetic operations.Thanks. – lch Apr 06 '12 at 15:48
  • 1
    Well if you execute the query, and it returns one thing, and in the report you see that the data is being manipulated, you know that something is being calculated at the report level. I would only use the old reports to get an idea of what is required, and re-build them in the report builder of reporting services. – Mez Apr 07 '12 at 15:53
  • Where did you get this query from? If you capture the query submitted to the database (i.e. using SQL Profiler), you won't see any XSUM's in there. You only see them in the report side. If you are intending to migrate reports you need to understand this. @Mez gives good advice: Don't try and do a technical report conversion. You should probably understand and rebuild the reports. – Nick.Mc Sep 01 '15 at 06:24
0

The XSUM is just as Stephen has highlighted. It means calculations are being performed within the report itself. Is your report doing any roll ups or aggs across queries or subtotals in the crosstab itself or are you joining subqueries within a report. Once you have subqueries and tie them together, the arithmetic has to be performed on the application servers. Think of it as a two tier approach -> gather the data from the database that meets the criteria of the filters -> then aggregate the data on the application server before firing off to the UI.

Robert Larson
  • 151
  • 7
0

You may want to looking at the native sql and cognos sql probably one of them may not have the Xsum.

Apart from this, will there be a semantic layer in SSRS where you have to create something similar to a package. If thats the case , would be better to recreate the report in SSRS.

Thanks PR

priya ravi
  • 1
  • 1