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.
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.
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.
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.