Master / Detail Report

Marek Koenig specializes in Business Intelligence, SharePoint and Custom Development.

Marek Koenig

I’ve always had a difficult time creating a good master / detail report. The biggest problem has been remembering the solution that came out of the trials and tribulations I went through the time before. My train of thought goes through the same varied iterations every time.

I always start off with a single report and two separate tables. This idea works but I can’t dynamically change the parameter of the detail. Then I try using a sub-report, also works but–yet again–I can’t send parameters using a click. Every time, same steps, same frustration.

Then it dawns on me, why not use two separate reports on a single SharePoint page? With that layout a user can click on a part of a chart and it will filter the detail report through a URL query string. Snazzy.

The solution is pretty simple to implement and will allow you to build a quick, interactive dashboard. You may ask yourself: “Wasn’t the last post about this too?”

Yes and no. The last solution did show off a master / detail solution but it was using two different pieces of technology, this one is just reporting services.

The first step is putting together the master report; in this case it will be a pie chart that summarizes sales for the last month by product category.

To create the interactivity between the two reports, add an action to the chart series to go to a URL:

=”{Base Url}/MasterDetail.aspx?Category=” & Fields!ProductCategoryID.Value

As you can see, I’m pointing to a web part page that I’ve created and passing in a parameter for my detail report. I save the report to SharePoint and create a new detail report that shows the sales broken down by quantity and item name. After adding a report parameter for the item category I was done.

Now on the SharePoint site, I need to create a new page, MasterDetail.aspx and add a few web parts to it. I add two Reporting Services web parts and a single Query String URL Filter web part. I configure the Filter web part like so:

Connect the Filter web part to the detail report and the category parameter:

And now you can start click on the chart and watch the detail refresh on the side.

subscribe by emailSubscribe to be emailed about new SharePoint posts.

About marek koenig
Microsoft consultant specializing in BI, SharePoint and custom development.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: