How to make SSRS reports in a PerformancePoint dashboard “pop out” or open in a new window.

I’ve had this request from multiple clients.  The scenario is usually that they have a PerformancePoint dashboard that contains a mix of PerformancePoint Analytic Chart reports and Reporting Services reports.  The Reporting Services reports are usually just gauges or some kind of chart, but they want the ability to click on them and to show the underlying detail data.  The problem is usually that the detail data they want to see is far larger than the space allotted in the dashboard for the original SSRS gauge or chart…….so we need the detail data to open in a new window.

Unfortunately there isn’t really an option for “Open report in new window” in the settings (but that sure would be nice if anyone from Microsoft ever reads this).  So to make this happen, we’re going to need to write a little Javascript.

Assumptions for this walkthrough:

·         SSRS is in SharePoint integrated mode (although you can do this in Native mode as well)

·         SSRS is either 2008 or above.

The short version of this post is that you basically need to add the javascript shown below to the “Action” property of whatever it is your user is going to click on in the report. 

="javascript:void(window.open('http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl', '', 'width=1000, height=800, top=0, left=0, resizable=yes'))"

Where:

Yellow = The URL to your report.

Green = The dimensions of the new window.

So the first thing we need to know is the URL to the report that we want to come up in our new window.  To get the URL we’re just going to browse to and open the report in SharePoint, and then copy it from the browser address bar….however you’ve probably got some stuff at the end that you don’t need.  For instance my report is called BlogExampleReport and the URL is:

http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl&Source=http%3A%2F%2Fmastvmbase%2Fsites%2FTavisBIDemo%2FSSRS%2FForms%2Fcurrent%2Easpx&DefaultItemOpen=1

We want to remove everything after reportname.rdl so it looks like this:

http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl

Now that we have our report URL we just add it to the Javascript as shown highlighted in yellow above.  Now we need to add our javascript to our report that the user will click on in the dashboard.  In my case, this report is a gauge.  So we’ll need to

1.      Right click on the Gauge in design mode, and select “Properties”

2.      Select “Action” from the Properties window.

3.      Select “Go to URL” as the action.

4.      Click the “Fx” button next to the “Select URL” field, and copy in our Javascript.

Click on the picture below to see each of the open screens.

clip_image002[4]

That should do it.  Just click OK on all the open windows and then deploy the report to test it.  NOTE: The report must be deployed in order for the Javascript to work.  It will not work in the preview window of your report designer. 

Now let’s say we want to pass a parameter from our original report to our report that opens in the new window.  We can also do this through the URL by adjusting our Javascript like so:

="javascript:void(window.open('http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl&rp:p_ReportParameter1=ParameterValue', '', 'width=1000, height=800, top=0, left=0, resizable=yes'))"

Where:

Yellow = The URL to your report.

Purple = The parameter and value you’re passing in.

Green = The dimensions of the new window.

In the above example we’re passing a value of “ParameterValue” into our parameter named “p_ReportParameter1”.

It’s worth noting that you can also control several aspects of your report viewer toolbar by passing parameters in via the url.  There is an excellent post about doing so here:

http://blogs.msdn.com/b/prash/archive/2009/01/21/passing-url-report-parameters-to-reports-in-sharepoint-document-library-in-ssrs-2008.aspx

So I think that about wraps it up.  While it’s a little messy, and kind of a pain to manage through environments….the functionality is possible.  If you have several reports that need to be moved between dev/uat/prod, I highly suggest leveraging shared data sets to store your base report URL ei: “http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/” .  Doing so will allow you to change it in one place, instead of having to do so in each individual report when you deploy to a new environment.  Until next time, hope this helps.

How to Auto-Generate a Time Dimension

One thing almost every Data Warehouse has in common is that they have a Time dimension.  There’s almost always a need to view aggregated data by day, month, quarter, year, etc.  In addition to the standards you might also have a fiscal calendar that’s slightly different than the standard calendar.  Typically a decision is made to generate the Time dimension starting at point in time the business feels the data will be relevant to the warehouse, and then populated out into a point in time that’s in the distant future (I usually do about 10 years for starters. 

Now you could spend a lot of time writing the sql script that is going to create and populate your time dimension.  However, you can also use SQL Server Analysis Services (SSAS) to do the bulk of the work for you (even if you don’t actually plan on using SSAS…..or if SSAS is a little intimidating to you). 

This walkthrough will guide you through using one of the built in wizards to generate and populate a Time dimension inside your SQL Server database, with very little effort.  In this walkthrough I’ll be using SQL Server 2012, but the steps are very similar in SQL Server 2008.  So let’s get to it:

1.       First things first, we’ll need to create a new Analysis Services project in SQL Server Data Tools (or BIDS in 2008).  If you’re using 2012, make sure to select the “Multidimensional and Data Mining” project, and not the “Tabular Project”.  Name it whatever you want and click ok.

clip_image002

2.      Once our project is created you should see several folders in your “Solution Explorer” (right hand side of your screen most likely).  Right click on the Dimensions folder as shown below and then left click on “New Dimension…”.

clip_image003

3.      Make sure to select “Generate a time table in the data source” and then click next.

clip_image004

4.      On this screen we get to choose the start and end date ranges for the dates we want to populate our time dimension with.  In addition we get to choose what additional attributes we want to include in our Time dimension such as Quarter, Hafl Year, etc.  Make your selections, and then click next.

clip_image005

5.      If you have Fiscal or reporting calendars that are offset from the standard calendar, you can choose to include additional attributes with those offset values in your calendar.  In this example we’re not going to use any of these, but it’s important you know they’re available.  Click next.

clip_image006

6.      On this screen I’ve renamed the dimension from Time to DimTime just because I’m in the habit of always starting my dimension table names with Dim.  Feel free to rename your dimension whatever you want here.  YOU MUST MAKE SURE TO CHECK “Generate schema now” BEFORE YOU CLICK FINISH!  That’s why I highlighted it in yellow all pretty like.

clip_image007

7.      Now we’ve kicked off a new wizard (by selecting  “generate schema now” in our previous screen and selecting finish) to generate our schema.  Click next on the intro screen.

clip_image008

8.      Now we need to create a data source that points to the database that we want our dimension table created in.  Click new, and walk through the data source creation wizard.  It’s exactly like the data source wizard that you find in SSRS, SSIS, or SSAS.  Once you’ve created your data source, select it in the “Data source” dropdown and click next.

clip_image009

9.      You can tell the wizard to create or not create various constraints on the table when it gets created.  For this example we’re going to leave all options checked (default).  Just make sure the “Populate time table(s)” dropdown has “Populate” selected and then click next. 

clip_image010

10.  This screen contains several options that should all be pretty self-explanatory.  We’re going to leave them all as their default values and click next.

clip_image011

11.  Aww yeaaaah!  The finish screen!  Your screen should look something similar to mine, just review it and as long as nothing looks out of place go ahead and click finish.

clip_image012

12.  The schema generation process might take a few minutes.  I’d guess the wider the range of the start and end dates you picked at the beginning of the wizard, the longer it will take.  Ultimately you want to see “Generation Completed Successfully” down at the bottom left in green.

clip_image014

13.  Now if I open up a query window in SQL Server Management Studio (SSMS) to the same database that I pointed my data source to during the walkthrough, I see that I now have a table named DimTime (though yours will be named whatever you named it…..it will be named “Time” if you didn’t specify anything).  Running a quick select * statement shows me that it is indeed populated. 

clip_image015

 

Well, that was pretty easy….Way more so than writing our own scripts to generate and populate this table.  Well, I hope everyone found this useful.  I also recently enabled comments on the blog (so feel free to leave a comment if this did or didn’t work out for you.)