Grand Total not the same in SSRS and SSAS

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


    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.


  • 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 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply