Time across columns and Reporting Serivces

  • 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 reporting 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?


  • This was removed by the editor as SPAM

  • If you want to display variable number of columns in the report I suggest you use a matrix element.
    To utilise it for this you need to flatten the result of your query. The query can be changed into something like this:
    { [Utility Dimension].[Value].[Current] } on columns,
    { CrossJoin( {[TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6]},
     {A_list_of_measures }) } on Rows
    from ACD_Calls
    This will effectively return a set with columns for your date, a column with measure name and a column with the actual value for the cross-section of the measure and the date. Utility dimension in this case is an auxiliary dimension with just one member [Current].
    If you don't want to create a Utility dimension you can use an existing dimension's (that isn't included in the CrossJoin) [All] member instead.
    Hope this helps
  • Thank you, Vadim, for your comments. I would like to try your suggestion but I don't know much about Utility Dimensions. Could you please provide a reference for me to lookup or some other information?

    Thanks again,


  • A utility dimension is just a regular dimension like any other. This dimension will have only one regular member. You can create a dimension like this from a table with just one entry (create a table called dimUtility with columns Key and Value , then insert a row with Key=1 and Value='Current' into it and build a regular dimension from it). This is just a design technique for when you need to perform the same calculation(s) on a set of measures. In your case there are no extra calculations to be performed, the sole purpose of it here is to allow retrieval of value for each tuple (Date,Measure) from a set returned by the CrossJoin. You end up with a result set containing date in one column, measure name in another and a column for the actual value for that measure on that date.

    You can read more about utility dimensions in a book called MDX Solutions


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

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