We have a user that would like to have 3 total columns to the right of their crosstab report created. The report shows data by month(January) and by sales person number (2001 and 2010 in this example) The user would like the 3 total columns on the right to be totals for the Extended Sales $ Local, Extended Surcharge Amount, and Total Sales columns. So for the example picture provided the column totals for the 2nd line on the report of 2014-01-07 would be 6184.05 for Extended Sales $ Local, 1360.48 for Extended Surcharge Amount, and 7544.53 for Total Sales. Thank you in advance for any suggestions!
Quote from: gatorfe on 31 Jan 2014 09:07:50 AM
We have a user that would like to have 3 total columns to the right of their crosstab report created. The report shows data by month(January) and by sales person number (2001 and 2010 in this example) The user would like the 3 total columns on the right to be totals for the Extended Sales $ Local, Extended Surcharge Amount, and Total Sales columns. So for the example picture provided the column totals for the 2nd line on the report of 2014-01-07 would be 6184.05 for Extended Sales $ Local, 1360.48 for Extended Surcharge Amount, and 7544.53 for Total Sales. Thank you in advance for any suggestions!
Hi gatorfe,
I hope you have a got a solution by now. Incase you haven't, I have a method of achieving what you want:
Note: Refer to the screenshot I have attached. Assuming, this is what you want, you can try the following:
1) Go to the Crosstab query and from the Toolbox items, drag a Query calculation into the query window.
2) Put a check in the radio button for Set Expression and then under Hierarchy dropdown, select Sales Person Number hierarchy and then click OK.
3) In the following Set Expression window, drag and drop the 1st measure and click OK. This will be the Total of Measure 1 based on Sales Person Number.
4) Perform the above steps to create the remaining two total measures. So now you have 3 total measures created.
5) Go to the Page Explorer.
6) Drag a Crosstab Space just next to the Crosstab Node Member i.e. sales person Number in your case. Give the name as Total by inserting a text.
7) Drag and drop the 3 total measures, one next to the other, just below this new Total column.
Run the report. You will get the desired result.
Cheers :)
Best Regards
Dev