Dynamic Time on columns with reporting services

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

Are you parameterizing textbox values, then expecting the report to be filtered by what is displaying in the boxes? I'm having a hard time understanding what you're trying to do from your description. It may be easier to start from scratch with paramterization within the MDX. Here's an example in one of my articles:

http://databasejournal.com/features/...le.php/3504651

You'll see that the params in this report are done at different "layers," with a hierarchical date picklist thrown in to show how to make picklists cube-based, something I have found to offer vast possibilities. You can, of course, base everything in the report, but might want to consider these options, depending upon your environment / comfort in the dual layers. The TopCount() / BottomCount() params are not what you asked for, but still serve as good examples of params that are effectively placed in the report layer - and to show some of the "stringing" involved in parameterizing MDX.

Another setup with a geographical paradigm appears in this article:

http://www.databasejournal.com/featu...le.php/3385521

I prefer matrix reports for OLAP reporting, as you can see in my Reporting Services series (virtually all the articles focus on OLAP reports, due to the vacuum in OLAP reporting documentation, as well as the fact that I specialize more in OLAP / Analysis Services than relational, which, to many, is far more straightforward.)

Hope this helps. See my other series' at Database Journal for more on this and other related subjects. The index is here:

http://www.databasejournal.com/article.php/1459531

Bill

Thank you Mr. Pearson, I have learned a lot from your articles.
The problem I was trying to describe was that Reporting Serives will automatically derive column names from the mdx query in the "query string" box. An mdx query with time across the top (columns) is problematic because when a dynamic query is built and the dates change, reporting serices changes the column names to reflect the new date. When it does this, the "=Fields!TimeByMinute_All_TimeByMinute_2005_May_2. Value" expression used in the table will be invalid (as May_2 might change to June_2, etc.)
So my question was how to have time change across the top and shield the table layout from Reporting Services changing the column name as differnt dates are used.
I'm so sorry about the long winded question but I think that's the best I can do.
Thank you,
Richard

Sorry re: the delay, but I'm still having to assume a lot from your description - which almost always means that I miss part of the requirement. A further request: Could you simply post the MDX query, so that I can see how you are specifying your axes, as well as to help me gain an understanding of what you are referring to as "dynamic?" (I see lots of definitions for the term these days ...)

Do I understand correctly that this is using a single cube data source, and a Table data region in Reporting Services?

I'll try to get back with some guidance once I understand the dataset you are retrieving.

Thanks!

Bill

Duhhh! Not enough coffee this AM, apparently! I just looked at the initial entry and saw your MDX syntax!

First thing to keep in mind when reporting from a cube via MDX in RS: Measures need to be in the "COLUMNS" specification. Don't worry about the apparent need to put the time dim members there, as a logical extension of the fact that they are destined to be column headers in the report: A rowset is returned and you can define them in Axis(1) - ROWS - and above, and still make them physical column headings.

Not putting measures into the ON COLUMNS Axis (AXIS(0))can mean bizarre results. Try putting them there, and we'll pick up at that point ...

HTH,

Bill

Ok That's what I thought and what others have been saying: for RS, put measures in columns. So I went back to my boss and expained the situtation and the alternatives. But so I am clear, when you said "in Axis(1) - ROWS - and above, and still make them physical column headings.
" that's still saying the same thing right, put measures in columns?

Thanks for everything,
Richard

Yes. What I was trying (sorry if it seemed garbled) to say was "Measures in columns," and not to worry re: time in the ROWS specification of the MDX, because the way the dataset is returned, time dim members can be used as column headings in your report regardless.

Let us know if this helps, or if it only gets you past this issue and into another one on the way to the realization of your objectives. :)

Thanks.

Bill

Yes it does. Help. I'm taking that advise and moving forward, so this can be closed.
More questions to follow on other topics though. Until then, have a nice day.
Richard