dynamic time on columns with reporting services

  • Hi all!

    If I have some mdx I'm using in reporting services like this:


    { [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6]   }  on columns,

    {A_list_of_measures } on rows

    from ACD_Calls

    The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.

    I'll post this in Analysis Services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.

    What do you think? Would a matrix be more flexible in this case?


  • I recently had the same issue and went with a matrix.  I also followed quite closely the recommendations in the msdn doc 'Integrating Analysis Services with Reporting Services', where you only ever have the measure on your columns.  For your query it would be simple to swing these around make them work.  One (very compelling) reason to only ever have the measures on columns is if you want to use 'NON EMPTY', as by having the emasures on the cols you will always get a data set returned (even if it is completely empty) whereas the way you have your query currently, if it was to return no time on the columns, the AS server would throw an exception saying that there is/was nothing on the 0 axis, and RS could not (and doe not) handle this other than throwing it to the user.  Having it the other way, if an empty data set is returned then the matrix will display the no rows' message as set in the properties.

    I was using parameters in mine (so, dynamic MDX query) but had a report that basically had n number of weeks on the columns, a calc member for the total on columns, some customers on rows and a single measure in the data section of the matrix.  I've pasted a sample below...

    = "WITH  MEMBER " & Code.ReturnParentMonth(Parameters!pWeek.Value) & ".[Total] as 'SUM({" & Parameters!pYear.Value

    & ".FirstChild.FirstChild.FirstChild:"

    & Parameters!pWeek.Value

    &  "}, [Measures].[Total Revenue])' SELECT NON EMPTY {{[Chrg Cust].[By Class].[Customer Name].MEMBERS} * {{"

    & Parameters!pYear.Value

    & ".FirstChild.FirstChild.FirstChild:" & Parameters!pWeek.Value

    &  "}, " & Code.ReturnParentMonth(Parameters!pWeek.Value) & ".[Total]} }  ON ROWS , {[Measures].[Total Revenue]} ON COLUMNS FROM [Wkly Rev] WHERE "

    & Parameters!pEntity.Value

    If you go down this route, remember that if you want to multi-line your statement, you'll either have to break it into seperate strings yourself (as per the statement above) or use the VB.net string method <string>.Replace(<replace_me>, <with_me&gt.  I've used both and they work as easily as each other for me.



  • Ok Steve- seems like you know what the heck you're doing so it's a pleasure to meet you. Once again- seems like there's no silver bullet- just don't do it. I didn't know dates (especially dynamic dates) were a no-no in columns in mdx-to-RS.

    I'll read the doc you referenced too.

    Thanks again,


  • Hey Richard.  No problems.  After re-reading your question (and my long-winded answer), a more simple answer is that when you have items set as columns in your mdx, these are what the field names become in RS; and if the number of these is dynamic, it is quite difficult to create all of the aliases (which really means creating calculated members fo reach of them with specific names) and then using these in the MDX query.  It really does become a lot easier if you stick to measures in columns for the MDX as then your dynamic number of row members actually come back in the rows with a single column name (eg 'Customers') and then you use this in a matrix and it will create a new column 9or row) as required for each distinct customer name that comes throw in the rows.



  • I see, Steve. Yes, that answer is very good. That could be really nasty especially if I allow the user to pick any date range they want. Ick. So I'll take that advise and put the dates in the rows.

    Thanks again,


Viewing 5 posts - 1 through 4 (of 4 total)

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