Lesson learned about PerformancePoint and SSRS

Today I have learned a few things about PerformancePoint web part, SSRS and report parameters. First thing first, the requirement was to build a SSRS report with SSAS as the data source and the report should be displayed on a Dashboard page.

I started with creating the report using ReportBuilder 3.0. I defined the data source and decided to use Query Designer to generate the MDX query. So I selected some fields and defined the parameters. In addition, I also let Query Designer to generate the parameters automatically which generates hidden datasets

Saved the report, created the performance point report using Dashboard Designer and deployed to SharePoint. I also added a query string url filter web part to read a querystring and pass it to my PerformancePoint webpart. Great, all done!!! But….when I opened the page, I had to wait 2-3 minutes just to see SSRS loading image 😦 .

Long story short, did some research and found some comments about removing the generated hidden datasets if I don’t need them. So I went to Report builder, removed the parameter datasets (which can be found in the Datasets folder, right click and select ‘Show hidden Datasets’) and saved the report. It did improve the rendering time of the report (by a lot!!!).

So lessons learned for today were:

  • Generated queries/parameters are great but need to use them with caution
  • Try to manually create the MDX query (if you can)