September 21, 2009 at 7:16 am
Hi, 😀
I have an SSRS report to show the order count per YEAR - Quarter.
But, the total order counts is not the same in SSAS cube browser and in SSRS report.
Explanation:
I have a simple time dimension: [Year - Quarter - Month - Date], and want to count orders. ( source is a SSAS CUBE)
1) IF in the SSRS DataSet Query Designer I drop the [YEAR]
On the level [Year] (2009) I get : [2009] 16256 (this is correct)
2) IF in the SSRS DataSet Query Designer I drop the [Year - Quarter] (2009, Q1, Q2, Q3) I get:
[Y2009] [Q1] : 6993 (is correct)
[Y2009] [Q2] : 7558 (is correct)
[Y2009] [Q3] : 1782 (is correct)
But, 6993+7558+1782=16333 and not 16256,
and in my report the auto generated total field shows also: 16333
but this is not correct, it should be 16256.
If I Check in SSAS cube browser,I get correct values:
Y2009 Q1 6993
Y2009 Q2 7558
Y2009 Q3 1782
Total 16256
Grand Total 16256
This is when order lines have more then one delivery dates.( I group on the deliver dates)
So, getting data via cube browser is correct, but via SSRS is not correct.
Regards,
October 7, 2009 at 8:04 pm
I'm just guessing that there is something in your count measure in SSAS that isn't just a straight count. If it was, then I would assume that adding the 3 quarters together would give you the correct value. If you want to show your generated total instead of letting Reporting Services add up the values as it is now, I'd create another dataset that uses the Year and gets the value you want. Put that in your report where you'd normally put the sum() formula to get the grand total.
It's not that SSRS is incorrect, but that it is doing what you asked it to (ie add up 3 quarters worth of data and give you a total).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply