SSRS – Using Analysis Services Dynamic Management Views to Build Dynamic Reports

Who is online?  0 guests and 0 members
Home  »  Blogs  »  DevinKnight  »  SSRS – Using Analysis Services Dynamic Management Views to Build Dynamic Reports
Anil and trapijgn like this.
 
3.79
/5
Avg: 3.79/5: (1 votes)

Comments (14)

Anil
Anil said:
Nice post Devin, similarly,can't we use for selecting the Multiple Measures as a parameter by user in dropdown list. Thanks, Anil Maharjan
2/2/2012
 · 
 
by
DevinKnight
DevinKnight said:
Sure could. Would just need to modify the source query to accept the multiple measures.
2/2/2012
 · 
 
by
chinni
chinni said:
Hi Devin, Excellent Post.When i am trying to do same thing am getting an error. w hen I add the second dataset with this below query SELECT Measure_Name, Measure_Unique_Name, Default_Format_String FROM $System.mdschema_measures WHERE Cube_Name = 'Adventure Works DW2008R2Cube' ORDER BY Measure_Name It issuing an error: 'This operation is supported on a data reader of this data provider only at query design time.' can u reply asap
2/2/2012
 · 
 
by
DevinKnight
DevinKnight said:
Couple things to check. Are you using SQL 2008 or higher? Also sounds like you may be trying to enter in the query in the query builder. If so the query builder doesn't support this query it has to be put in the expression window.
2/3/2012
 · 
 
by
chinni
chinni said:
Thanks for the Reply. I tried in both sql2008r2 and sql 2008 and I entered the query in the expression.But this doesn't work. Even I try to enter the parameters manually it is issuing the error even for that. One more thing I cant see any column names in the data to put them in tablix . One suggestion why cant u make a video of that then it will be easy to understand. Thanks
2/3/2012
 · 
 
by
chinni
chinni said:
Sry Davin, My Fault.Some syntax errors. I could figure out my error. Every thing seems to working fine. Only thing i cant see is the data when I select the value in paramter, empty data is coming In my matrix tablix, in place of data,what should i place
2/3/2012
 · 
 
by
DevinKnight
DevinKnight said:
Interesting.. I'd probably have to see what you have to figure out why no data is showing.
2/3/2012
 · 
 
by
chinni
chinni said:
Can you please tell me , what should be added to the data in my matrix tablix
2/3/2012
 · 
 
by
chinni
chinni said:
I like to show my screenshots.How can I send to you? Will give an clear picture where i am dng wrong?
2/3/2012
 · 
 
by
DevinKnight
DevinKnight said:
You should add fields from the first dataset to the body of your report. The second dataset just supports the parameter dropdown box.
2/3/2012
 · 
 
by
chinni
chinni said:
I done the exactly the same.In my dataset1 I have ProductCategory,FiscalYear ,SalesAmount. In rows,I added ProductCategory,In colmns, Fiscal year and In data, SalesAmount. When I preview when I select any value from dropdown list,no data showing in my result
2/3/2012
 · 
 
by
DevinKnight
DevinKnight said:
Ok i think i know what's wrong. You need to change dataset1 to accept the parameter that has the dynamic measure name. Do something like this. ="With " +" Member [Measures].[Generic Measure] " +" AS STRTOVALUE("""+Parameters!MeasureName.Value+""") " +"Select " +" [Measures].[Generic Measure] ON Columns " +" ,NON EMPTY ([Date].[Calendar].[Calendar Year] " +" ,[Product].[Product Categories].[Category]) ON Rows " +"From " +" [Adventure Works] "
2/3/2012
 · 
 
by
chinni
chinni said:
This is my Query ="With " +" Member [Measures].[Generic Measure]" +" AS STRTOVALUE("""+Parameters!MeasureName.Value+""") " +"SELECT " +" [Measures].[Generic Measure] ON COLUMNS " +" ,NON EMPTY ([Due Date].[Fiscal Year].[Fiscal Year] " +" , [Dim Product].[English Product Category Name].[English Product Category Name]) ON ROWS " + " FROM " +" [Adventure Works DW2008R2Cube]"
2/3/2012
 · 
 
by
chinni
chinni said:
I dont know where am i doing wrong. There are no errors.But when I select the paramter value I can see the productcategory name and year but no data
2/3/2012
 · 
 
by
DevinKnight
DevinKnight said:
Yeah it's hard to tell. Your query looks good. Feel free to send me screenshots if you'd like. dknight@pragmaticworks.com
2/3/2012
 · 
 
by
chinni
chinni said:
Devin, I mailed you my screenshots. My email id suman.adindla@gmail.com Please have a look
2/3/2012
 · 
 
by
DevinKnight
DevinKnight said:
I replied to your email hope it helped.
2/3/2012
 · 
 
by
Blogs RSS Feed

DevinKnight's latest blog posts

Blogs RSS Feed

Latest community blog posts