dynamic time on columns with reporting services

Question

dynamic time on columns with reporting services

Richard Polichetti

Old Hand

Points: 325
More actions
May 17, 2005 at 1:42 pm

#110140

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 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?
Richard

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

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

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

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.

HTH,

Steve.

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

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,

Richard

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

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.

Steve.

Richard Polichetti Old Hand Points: 325 More actions · Answer 4

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,

Richard