I have been working with both SharePoint 2010 and SQL Server 2008 R2 Report Builder 3.0 quite a bit in the past month and I thought I would take the time to share a few things with you that I learned that were not readily apparent with Report Builder 3.0. You see I come from an infrastructure background and have very little experience with development save a little admin scripting with Windows Scripting Host, WMI, and ADSI. So if you are a “dev” guy you can stop reading right now!
If you have not checked out Report Builder 3.0, YOU NEED TOO! A lot of new capabilities were added and it is “fairly” easy to use.
Download Report Builder 3.0 Here.
As I said, I have been working with SharePoint and one of the compelling features in Report Builder 3.0 is it can use SharePoint lists to create a data set.
When you couple this with the reporting services integration between SQL Server 2008 R2 and SharePoint 2010 you have yet another new BI package to expose powerful reports to your customers.
Report Builder 3.0 has wizard driven processes to quickly create charts, matrices, etc. It is very similar to constructing a pivot table in excel, but in the example below I am using a SharePoint list.
Selecting the dataset (you can also create a new one).
Arranging the fields for the matrix. In my matrix I wanted to see attendance numbers for training sessions that were conducted this quarter by company and session type. You can see how I dropped the fields into the boxes below.
I wanted to be able to expand and contract my matrix in a stepped manner
Pick one of the default themes…
And click finish. You have a matrix that pulls directly from a SharePoint list that expands or contracts to show more detail. In this screen shot I have given the report a name and expanded the two delivery models to show the partner types below. I still need to make some changes and spruce it up.
You will notice first that the columns prior to the total column are numbered “7, 8, and 9”. This if you recall was the “month” field from the SharePoint list wich is happens to be a integer value of the month a particular training event occurred. I will fix this formatting in a moment.
By clicking the “month” placeholder, I can change the Value to the “Preferred Date” field.
I then Change the Number format to “date” and select the date formatting that I prefer.
Now when I run the report the month columns are formatted correctly. If I had built the Matrix with “Preferred Date” as the column group, it would have created a column fr each entry rather than aggregate them by month. There may be another way to do this, but it threw me for a loop for quite a while.
So if we were to extend this report to include indicators and percentage metrics, it would be nicer. Let’s see what we can do?
Below you can see I have added an indicator that turns green at the value of 600, and yellow between 500 and 599. I have also placed an expression in the “percentage” column that divides the “Delivery Model” attendance total by the goal of 700. You can see though that unlike the indicators, this expression is dividing the “total” row against 700 rather than dividing each delivery model. Let’s examine why this is and find a fix.
If I examine the expression it seems reasonable. …doesn’t it. All I did to get this formula was to click “datasets”, and then “Dataset1”, and finally selected the Sum of the attendance. Sounds right; how can it be wrong.
Watch yourself! In an effort to save typing syntax make certain you are getting only what you need! Notice the formula states “dataset1”. This implies ALL of dataset1, not just the portion within the row on your matrix. Okay so now you may think you have to use some fancy IIF function to differentiate between different values within the delivery model, but this is not the case. To have the percentages show correctly, simply remove the “dataset1” portion of the formula so it reads as below:
The report will now run like a champ and all percentages will calculate correctly!
I hope this helps some of the novices out there with getting started with Report Builder 3.0. Keep in mind, with the integration pieces I mentioned above these reports can be hosted on SharePoint, thus extending the value of what your customer has already purchased with SQL Server 2008 R2 and SharePoint 2010.
Thanks and regards,
Minor point Woody but it spoils an otherwise excellent post. Four of the images do not display in the post because they are pointing towards your computers c: drive rather than the blog website. End resolt is that the images do not display.
More of this - great post.
My question regards grouping. I can hide columns of data from the data source but is there a way to hide the column in a grouped field? I can dynamically remove groupings but the physical column unfortunately stays on the screen. Any ideas on how to do this?