Time across columns and Reporting Serivces

Question

Post reply

Time across columns and Reporting Serivces

Richard Polichetti

Old Hand

Points: 345
More actions
May 17, 2005 at 1:38 pm

#110882

Hi all!
If I have some mdx I'm using in reporting services like this:
select
{ [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?
Richard
May 20, 2005 at 8:00 am

This was removed by the editor as SPAM

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

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

Vadim Govorovski SSC Enthusiast Points: 150 More actions · Answer 1

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:

select

{ [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

Richard Polichetti Old Hand Points: 345 More actions · Answer 2

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,

Richard

Vadim Govorovski SSC Enthusiast Points: 150 More actions · Answer 3

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

http://www.amazon.co.uk/exec/obidos/ASIN/0471400467/qid=1117029832/sr=8-1/ref=sr_8_xs_ap_i1_xgl/026-5739942-3359605