Recently I was contacted by Peter Koller from Norway, asking me about some bizarre behaviour he’d seen with calculated members disappearing from query resultsets in Reporting Services. I had a suspicion about why it was happening and came up with a workaround, but asked him to post it as a bug which he duly did:
Now much as I’m tempted, I’m not going to go off on another rant about the fundamental flaws in the way support for Analysis Services is implemented in Reporting Services. I’m going to seize on the glimmer of hope contained in the following sentence:
For a future release and maybe service pack, we are considering adding an explicit switch that allows treating server aggregate rows as "detail rows".
What, let Reporting Services actually display the results of your MDX query without adulteration? Sounds like a dangerously sane idea! I’d like to propose some community action (I’m currently in France so I must have become infected with Gallic militancy): can anyone who agrees with me that this feature should be in the next service pack leave a comment at the above link? Hopefully if a few comments get posted then it’ll help persuade the RS team to do something.
I posted another workaround. It\’s a little kludgy but at least you don\’t have to use OleDB and lose the nice new MDX parameters functionality:
http://lab.msdn.microsoft.com/ProductFeedback/ViewWorkaround.aspx?FeedbackID=FDBK48790#2
Just to be clear, the reason that row is dropping out when displayed in the detail row of a report table is because of the [Customer].[State-Province].[All] member being in the tuple, not because of the calculated measure. I believe all the other Country total rows were dropping out, too.
You are absolutely right about that. This makes SSRS next to useless for OLAP reporting IMHO.
My opinion on the issue is that the extra switch is a must-have. But I also feel strongly that you will not always want to turn it on… for instance, when you\’re working with a matrix and you want a subtotal for the entire matrix, it\’s very handy just to just turn on subtotal and have it automagically figure out the right row in your dataset to use as the aggregate for the subtotal.
That might be so, but a lot of reporting is done as static tables (especially in finance) where the content is tightly controlled. I hardly use matrixes at all. So yes, the switch is a must-have 😉
Ditto. This is a must have. I also agree with you that there are way too many "little" bugs in RS, especially regarding AS. I will post a comment as well.
You folks may be happy to know that SP2 is more flexible with regard to how subtotal rows are handled in SSRS:
http://www.artisconsulting.com/Blogs/tabid/94/EntryID/1/Default.aspx