Reports with dynamic MDX queries

[Elena_76]

Hi all, today I have been asked from a client if it is possible to create a Pivot like report using Reporting Services. I answered that it is not possible to have such a report. But the client would like to know if it possible to simulate that functionality this way: to put in the aspx page where there is the report say some drop down lists and other controls the user could use to choose what dimensions / members of them to have in the report and where to put them ( eg rows or columns ) .

I think this necessarily involves to have separate and different queries ( different in their structure and not just in some parameters ) to pass to the report at run time *each time the user does a choice*

For what I know it is not possible to do that, that is to have a report to receive such an unknowed 'at design time' query.

I kindly ask you this:

1. I am right in saying that to simulate a pivot I need to pass each time a new query ? I understood that well?

2. and ( if yes #1) I am right in saying that RS does not support these 'dynamic' queries to be passed at run time ?

Thanks and regards

Elena

[Island1]

When you say "dynamic queries" do you mean queries with ad hoc parameters? This might seem like a crazy question, but I've heard so many definitions for "dynamic" that I'm almost afraid to use it in my articles!

I think I can makes some suggestions if, indeed, you're looking for help with parameterization of MDX Datasets. Take a look at my Reporting Services series, where a large percentage of my writing surrounds OLAP reporting with RS. I began to focus on this "niche" because none of the books to date deal, in any material way (that is, no more than a handful of pages, at best) with reporting from Analysis Services cubes. The stringing, etc., can be a little tricky, but it is most certainly possible - I work within the Analysis Services / Reporting Services combination with clients on a daily basis.

Get back with specific questions and I'll see what I can offer as time is available. Many other professionals read these forums, as well, and many appear willing to contribute. Try to create separate entries for different questions, if you don't mind, so that anything we discuss can be shared with others in the same boat - it makes the searching easier.

Good Luck, and have a great day!

Bill

[tellier]

It is possible using OLAP cubes to have a pivot table in a Reporting Services report. There is an example in the samples that are installed with Reporting Services.

Here are some other Microsoft samples:
Reporting Services 2000 - OLAP Sample Reports
http://www.microsoft.com/downloads/d...DisplayLang=en

As well William E. Pearson III has articles available. For a listing of them visit: http://www.databasejournal.com/article.php/1459531

Cheers,

Toby Tellier