Business Data Catalog Filters for SSRS Web Part
A couple of weeks ago a colleague asked me about web part filters in Sharepoint that could be used to drive context for other web parts on a page. In particular, establishing context for SQL Server Reporting Services web parts.
In our system in production today we have a set of custom ASP.NET web parts which get their context established from another custom web part. For example we have a page which allows the user to select a particular portfolio, and a series of web parts on that page get populated with projects from that portfolio for different aspects they are interested in (projects with milestones that have recently been achieved, projects with milestones which have recently been pushed back/up, etc.). It looks something like this (actual project names have been removed, and I’m only showing one of the child web parts on the page).
We happened to have tasks for the current sprint to replace some of these custom web parts with out-of-the-box web parts, so as to reduce the amount of code we’re supporting and utilize more of the features of the Microsoft SQL Server BI stack. So I attempted to replicate this page with OOB web parts like the Business Data Catalog Filter web part for the portfolio selector, and SSRS reports and web parts for the other milestone web parts.
We already had a BDC app definition for the portfolios, and so dropping a BDC Filter web part on the page was easy.
The business also had a new requirement to be able to change the number of days used in the query. So I added a Text Filter web part to the page.
Developing the new tables as SSRS reports was also very easy, and I dropped the SSRS Report Viewer web part on the page for the first report (Milestones Achieved).
Then it was a matter of connecting them up. The BDC Filter web part had a connection option for sending the filter value to another web part – such as the SSRS Report Viewer web part on the page.
When selecting this a dialog appeared asking which parameter from the SSRS report I wanted to hook up to.
The Text Filter web part was pretty similar. This was hooked up, the page was published, and I took it for a spin.
It worked, however, it didn’t quite look and feel the way I had hoped. In our custom web part we had the portfolio selector in the form of a drop down list. Click the drop down arrow, pick a new item, and the page refreshes with the new context. With the BDC Filter web part you are presented with the obtuse checkmark and book icons:
If you click the book icon, you get the familiar (if you’re familiar with Sharepoint and BDC that is) business data item picker dialog where you can search and pick a value.
This is great if you have hundreds or thousands of items you need a person to choose from. This is not so convenient if you only have a small handful of items that the user should be able to pick from a drop down list. In the web part properties there appears to be a way to modify how the list is presented, but I only saw a single value of ‘Search dialog’ in the ‘Display list as’ field.
Perhaps something in the BDC app definition determines what options are possible here. I will need to explore this a little more.
Note that manually typing in a value in the text field for the BDC filter does work fine, but you need to enter an exact match. Not very nice for longer named list items. Another option may be to use something like the Business Data List web part as a filter source. But even then I don’t think you have an option to display items as a drop down list.
The Text Filter web part worked ok as well, but there are no options for controlling the type of text that can be entered. For example, I’d like to restrict it to integer values only. A different option here might be to use a Choice Filter web part and restrict the choices to something concrete like -30, -60, -90, etc.
