I'm using a crosstab report on a data cube in Report Studio. I'm using the following expression in a data item to show the top 3 suppliers according to Sales YTD.
topCount ([Supplier Name],3,tuple([YTD],[Sales]))
I have to columns: QTD and YTD. Using the above expression, the report shows the top 3 suppliers according to YTD sales and then shows the QTD sales in the first column and YTD sales in the second column. This part works fine.
When I use Automatic Summary, the total sales calculated for the top 3 for YTD is correct. However, the total sales calculated for QTD is the total of the top 3 according to QTD sales. I want it to be the total of top 3 suppliers according to YTD sales.
Example. The summary for QTD should show 100 because the total QTD sales for the top 3 suppliers according to YTD sales is 100. But the summary shows 120.
Supplier Name.......QTD Sales........YTD Sales
S-34................................50.................300
S-56................................30.................200
S-48.................................20................100
Summary........................120................600
To help explain the problem, below shows the top 3 suppliers according to QTD sales. You can see that Supplier S-16 was not in the top 3 when using YTD sales. The top 3 total by QTD sales shows the total to be 120.
Supplier Name.......QTD Sales........YTD Sales
S-34.................................50.................300
S-16.................................40...................80
S-56.................................30..................200
Summary........................120..................580
How can I show the QTD total of the top 3 suppliers by YTD sales of 100 instead of 120?
What do you get if you drag a quert calculation below the supplier row headings and code an expression
aggregate(currentMeasure within set [your suppliers level])
Does this provide correct summary totals?
MF.