I have an SSRS report I'd like to include in a PPS dashboard. I've followed all the steps outlined in several blog posts describing how to do it, but my SSRS report still doesn't function. The parameters aren't enabled, and when I choose the filters, the report is blank. Here's a screen shot:

Are there any gotchas I should be looking out for? For example, does the name of the filter need to match the name of the SSRS parameter, etc.?

Thanks in advance!

Hello, It looks like you haven't connected your performance point filters to your Reporting Services parameters.  You would make that connection in the dashboard designer or using sharepoint connections.  This article describes the process.

http://blogs.msdn.com/b/performancepoint/archive/2010/05/11/how-to-integrate-ssrs-reports-with-dashboard-filters.aspx

Ryan

I actually have done that, following the exact steps in the article you mentioned, yet they are still not functioning properly - which is why I'm wondering if there is some other gotcha I need to be checking for.

I see.  You might try setting the Available Values for the parameters to None in the actual reporting services report.  Now that you mention it, I think that will "solve" it.

Ryan

Setting "Available Values" to NONE in the report would break the report for those who are not using the report via PPS.

But per what someone stated in the article, unchecking the "Show Toolbar" checkbox in report properties seems to have an impact. Now I'm actually getting an SSRS parameter error, which tells me the filter in the dashboard is now getting successfully sent to the SSRS report.

I have NO idea why the Toolbar option would affect parameters/filters, but this seems like a big bug to me.

Okay making some progress, but it looks like due to the nature of my SSRS reports and their parameters, I may need to actually create an entirely different set of reports that are compatible with PPS. For example:

I have created several shared datasets that act as available/default values for parameters. This is the MDX code for two of them: CalendarYearEx and SelectedMonthEx. CalendarYearEx returns the year in " Calendar YYYY" format and SelectedMonthEx returns the month in "Month YYYY" format. This is due to user requirements.

-- CalendarYearEx

[Time].[Year -  Half Year -  Quarter -  Month -  Date].[All].Children

--SelectedMonthEx

Descendants(
  StrToMember(@CalendarYearEx, CONSTRAINED),
  [Time].[Year -  Half Year -  Quarter -  Month -  Date].[Month],
  SELF)

The report I'm working on takes these two parameters: CalendarYearEX and SelectedMonthEx. The available values for each are the shared datasets. AS you can see, the SelectedMonthEx dataset depends on the CalendarYearEx parameter. The list of months depends on what year is selected.

In addition, the default for CalendarYearEx is based on another shared dataset. This dataset returns the current year.

When running my report in PPS, I want the default values in the SSRS report to override the defaults you have to choose in PPS - which looks like that can't be done.

Secondly, since I need to create PPS filters, how would one go about rewriting that SelectMonthEx MDX in order for it to work in PPS? Can you override the PPS defaults it asks for?

Thread has gone dead...closing.

If you need to rewrite the filters you can use the MDX query instead of selection the members.

Just need to be sure that your PPS and SSRS filters/parameters are using the same syntax, that is the big thing.  So if SSRS is expecting unique member name, then pass that, otherwise pass the member value of the text to SSRS.

You shouldn't need to display the SSRS toolbar either, is this Native or Integrated Mode SSRS?

What build of SharePoint are you on...meaning which CU are you on...SP1 CU? (hopefully SP1 and CU 2012 or later).

Forgot to mention that I have a post on doing this setup as well: http://denglishbi.wordpress.com/2010/12/31/using-reporting-services-report-ssrs-with-performancepoint-services-pps/

Dan:

Thanks for the reply!!

Turns out I was able to get my two reports in question to work before you posted your reply. Essentially, everything you mentioned.

I had to name the filters the same name as my parameters in the SSRS report, I'm not displaying the tool bar or the parameters on either report.

For both filters I'm using MDX statements and these are working fine.

The one problem I have is with defaults. A great number of my reports use an MDX result as the default. For example, several reports have a Year and Month parameter which default to the current year and the previous month. In PPS, I don't see how I would be able to replicate this behavior, given that you have to choose a particular value for a default. And apparently defaults in an SSRS report do not carry over to the PPS world.

Is there any way around this limitation?

Thanks!!

To create the PPS filters that you are looking for you could use the Time Intelligence features of PPS.

Here is a post that will walk you through setting that up and using that with the different PPS content.

http://www.bidn.com/blogs/MMilligan/bidn-blog/2603/time-intelligence-filters-in-performancepoint-2010

The other option that you could possibly explore would be to use a named set that is in your SSAS source, but the TI filter is probably what you are looking for and would work best.

I actually found the same article a little while ago...thanks!!

But the solution I need will differ slightly than what is presented in the post.

The TI filters will give you different values for a filter (YTD, same month last year, trailing twelve month, etc.). What I need is for the filter to still just give me year and month/Year but default them to the current year and month. The TI filters give a period of time from how I under stand it. I would still need to be able to choose "Calendar 2012" and "January 2012" if need be.

For example:

F

From what I can tell, this differs from what the TI filters would provide. Although I do see a use for TI filters in some of my other dashboards.

Sure, that makes sense.

Well even if you did setup a filter like that and established a default member, that is pretty much a one time deal because of the caching of parameters that gets done after users select items.  You would need to modify that process if you wanted to change that and people have done that.

So you could create an MDX query to return these year/month values and order it as you need to, but with the caching you would still run into issues. 

You're right...forgot about caching!!

It's not a complete must have...more of a "we had it here, why can't we have it there?" kind of thing. I'll just have to "educate" the masses that they will have to make a couple of extra mouse clicks when opening the report - God forbid ;-)

Thanks for all the help - much appreciated!!