Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Reporting Services and Server Aggregates

with 6 comments

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.

Written by Chris Webb

April 25, 2006 at 8:31 pm

Posted in Reporting Services

6 Responses

Subscribe to comments with RSS.

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

    Greg

    April 26, 2006 at 2:19 am

  2. You are absolutely right about that. This makes SSRS next to useless for OLAP reporting IMHO.

    peter

    April 26, 2006 at 8:16 am

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

    Greg

    April 26, 2006 at 8:44 am

  4. 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 ;)

    peter

    April 26, 2006 at 9:46 am

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

    Dan Meyers

    May 31, 2006 at 10:08 pm

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

    Greg

    March 29, 2007 at 10:55 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,859 other followers

%d bloggers like this: