Kasper de Jonge BI Blog

Last weekend i was at the dutch SQLZaterdag (SQLSaturday) where I attendend a session from Hans Geurtsen who showed us the new features of Reporting services 2008 R2. Among others he showed us the new Group Domain Scope property to align columns, i just had to find out how this works. I knew of a excelent blog post by Sean Boon where he used the Domain scope property to align the axis of a sparkleline.

Ok what is the problem, lets say we have a few tablixes with sales per month, not every year has sales for all months, this would result in the following report:

What we would like to do is align the months so each month will be shown at each tablix, i designed the report to place the Tablix within a list.

I named the parent list “Maintablix”, the Group Domain Scope needs to have a scope to sync the axes against. You cannot just add tablixes and sync them, you need to have one parent tablix to sync your child tablixes against. When you create a report like this you can add the parent tablix name to the DomainScope property to your column properties of the child tablix: 

this will result in the following report, as you can see the columns are now aligned:

This is a pretty nice feature that could prove very handy, but what I really would like to see to sync axis over multiple tablixes, i couldn’t get that to work and i don’t think that is possible.

I’ve been using SSAS in combination with Reporting Services for a few years now but i have always found it very cumbersome to deal with YTD and other time intelligence functions in combination with SSRS. A few days ago i read this whitepaper: Translating Cognos PowerPlay Transformer Relative Date Functionality into SQL Server Analysis Services. In this paper they use the “Add business intelligence” wizard to add time intelligent members to the time dimension, I’ve seen the button but never tested it out.

This whitepaper made me do some testing. To do so i’ve created a cube based on the adventureworks database. I’ve created 2 dimension, and used the sales amount in the fact table.

In the date dimension I’ve created a hierarchy:

Now for the magic bit:

We run the Business Intelligence wizard to add a time intelligence functions

We can pick multiple calculations to be created

You have to make sure you pick the date hierarchy so the date calculation can determine the relationship between the different levels.

Last step is picking the measure we want to use the functions with

Now we can click finish, the time intelligent functions will be implemented. This is done by adding a calculated member to the time dimension as we can see here:

This will add a attribute with 2 members to my date dimension, one which shows the measure value for the current month selected and member that shows the year to date to the month selected:

we now can use this member in a query, in for example a SSRS report:

As you can see we now have the sales amount of the selected month from the selected month and the sales amount of the current month year to date. Putting this in a tablix makes it into the following report:

This is great stuff, makes YTD very easy to implement !

I don’t know how i could have missed this great wizard for all this time. I’ll use it for sure very soon at the next project.

I recently had a question concerning reporting services, they wanted to compare sales of a month to the average sales per month like in the report i reproduced in the report below:

This is not easy in reporting services 2008 and before. To solve this problem we need to divide the total sales by the number of months, which you can obtain by using the CountRows function. But then you have a problem with the country’s. It would be solvable, but using a lot of tricks.

Suddenly i remembered a blog post from Robert Bruckner which mentions the new features of SQL Server 2008 R2:

Aggregates of Aggregates
This enables report authors to nest RDL aggregate expressions inside other RDL aggregate expressions with unlimited nesting levels.  For example, the expression =Avg(Sum(Sales, “Month”), “Year”) would compute the average total monthly sales

This could solve my problem very easy using the new expression:

=avg(Sum(Fields!Internet_Sales_Amount.Value),”Calendar_Year”)

This will give us the avg of the total sum of sales per month per year. We then compare it to each other and determine the percentage.

Using the aggregates of aggregates functions could make my life a lot easier in my daily work, just like the Lookup function i talked about before.

This screencast shows how you can import data from a reporting services report published to SharePoint into PowerPivot:

I was being triggered by my own remark from my previous blogpost Solving a business problem with Powerpivot CTP3 using DAX Timefunctions where i stated:

I wish i could use this in Reporting services on top of cubes, doing these same things in MDX wil most definitely give us the feared too many rows error.

I decided this had to be possible, when a PowerPivot app is published to SharePoint the data is loaded into SSAS. So this blogpost will describe how we can use reporting services to show data we created in PowerPivot.

First we have to save the App to SharePoint, thanks to Dave Wickert we know manual uploading to SharePoint is the best way to go, resulting in a published PowerPivot App:

Next we start Report builder 3.0 to make a connection to the SSAS integrated server.

The server is called “Geminibi” by default, you can find your sheet name in the name of your SSAS database, i called my sheet BP followed by Sandbox which is the name of the cube (default) and followed by what seems a generated Guid. I would recommend to create a account to use as credential and not use integrated security and add this to a role in the SSAS database.

UPDATE: I just remember reading a blog post a while back using a published PowerPivot App in SSRS, seems you can use the XLSX path from SharePoin as datasource (is identical to above):

We can now create the report using the measure we created in PowerPivot with DAX, somehow these DAX measures are created as MDX in SSAS, i wish i could see the MDX that was created.

Putting it together in a report (remember to select a date, we used time intelligent functions) we get the same result as in Excel:

We now can build a similar report like our excel sheet, we can even make the year a parameter (which i wanted in PowerPivot):

which we can run, resulting in a report build on a published PowerPivot App:

We now can publish this report to SharePoint to make the report available to other users (i added a new R2 sparkleline chart as well, just because i love m )

As you can see it’s pretty easy to use Published PowerPivot App as a reporting source, you obvious don’t have the same tweaks available as you would have on SSAS (think aggregations etc) and when someone changes the PowerPivot app you are doomed (you could backup and restore the cube in it’s current state on a different server). Who can name me more potential issues ?

After installing PowerPivot on SharePoint 2010 thanks to the excelent guid of VidasM i decided to try and install report services 2008 R2.

To start I downloaded the SQL Server® 2008 R2 November CTP Reporting Services Add-in for Microsoft SharePoint® Technologies 2010 and followed the instructions there:

Step 1: Install a SharePoint technology instance. (Already did this when installing PowerPivot with SharePoint)

Step 2: Install SQL Server 2008 R2 November CTP Reporting Services and specify that the report server use SharePoint Integrated mode.

Step 3: Configure Reporting Services.

Step 4: Download the Reporting Services Add-in by clicking the rsSharePoint.msi link later on this page. To start the installation immediately, click Run.

After installing Reporting services and the add-in your reporting server is ready to be integrated with SharePoint, in SharePoint 2010 we have some new admin screens.

To integrate go to central admin, general application settings:

When you successfully installed the add-in a reporting services icon will be there. Click Reporting Services Integration:

Add the report server web service url (To get the URL, open the Reporting Services Configuration tool, connect to the report server, and click Web Service URL. Click the URL to verify it works. Copy the URL and paste it into Report Server Web Service URL.), select your authentication mode (windows authentication is prefered). Add a username and password of your admin account. Click ok to configure and start the integration.

After the installation you can set the reporting services default.

What is changed in SP2010 is that there isn’t a report library available. You have to add content types to a default library. So go to a site collection, site actions,  View all site content.

Create a Asset library:

Now we have to make sure we can add reports to the library. To do this we have to add content types:

Open the library, click on library tools, library settings, Under Content Types, click Add from existing site content types. In the Select Content Types section, in Select site content types from, click the arrow to select Reporting Services. In the Available Site Content Types list, click Report Builder, Report Data Source and Report and then click Add to move the selected content type to the Content types to add list.

Now we are ready to upload reports and execute them from within our webparts:

More reading about this on BOL:

How to: Configure Report Server Integration in SharePoint Central Administration

How to: Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode)

Thanks to twitter i found out about a great Codeplex project called SCRUBS:

CRUBS your SQL Reporting Services Logs to provide management, auditing & optimization reporting.

SSRS provides robust logging in the Execution Log, but no management metrics in the box. You’d have to develop your own DW, SSIS procedures and metrics for reporting. Not anymore!

Start Scrubbing your SSRS Execution Log & Gain Insight!

Want to know the longest running SSRS reports? Top users? Which reports should be targeted for optimization and tuning? How about IT Governance; to determine who’s reviewing certain types of data, or who isn’t using your platform at all?

SCRUBS for SQL Server 2008 – Community Edition Includes:

• Datawarehouse schema
• SSIS ETLs to scrub the Reporting Services Execution Log
• Reports! Check out our Report Catalog
• Source Code

I’ve looked at it and it is a great project gives you all kinds of insight in your reporting environment. The community version is free and gives some great reports, you can get the full pack for as little as 100 dollars. Download the package here: http://scrubs.codeplex.com/

SSRS got a few very nice new visual controls in the November CTP like the Indicator and databar control. In this blog post we create a visual dashboard with the new SSRS.

We start as usually by opening the BIDS and create a new reporting services project (nothing new here). I couldn’t get data while using the report wizard after creating the dataset the wizard seems to lose its data (CTP bug i guess). So I just create a new empty report and creating a shared datasource.

Next up is creating a KPI dashboard that shows whether the sales per sales region are on quota. We start with creating a dataset to get our data, the first thing we notice is the possibility of selecting a shared dataset:

This could be very time saving and efficient, since you can share a dataset to your entire project. I regularly have to copy and paste a dataset from one report to the other.

Ok i created a dataset with sales amount, sales quota and sales region, as you can see you can convert your dataset to a share dataset:

It will now be available in your project for all other reports:

On on to our dashboard, i create a Tablix to contain our values and eventually the indicator:

Running this gives us the percentage of sales compared to the quota. Now i want to include the indicator in there, insert the indicator on the report (check out the new controls available):

select the indicator type you wish:

create a new column in the tablix, paste the indicator on the tablix:

Now all we have to do is set up the indicator:

I use the percentage of quota versus amount to determine the color of the indicator, two things to notice when your value is out of bounds you won’t have an indicator, i feel a greater or smaller then value should be available. The other thing is the States Measurement unit setting, i couldn’t find out what it exactly meant, you can select numeric or percentage there but I couldn’t get it to work. But this is all it takes to create a indicator, very easy!

Now i want to show my dashboard which sales per product group are selling the best, with the possibility to drill down, we start again by creating the dataset and a tablix:

Now i want to use the data bar to show the amount of products sold in comparison the the other products. Insert a new column into the tablix and drag the databar control onto the tablix, you can click the bar to set the values (i had to search for that ..):

as you can see you can even set more bars by category or series, we don’t need that now.

Putting the value in should do it, but of course since we want to see the values in comparison to the rest, the bars have to have all the same size, by default it will be as wide as the value. So we use the Max salesamount value to determine the width. This will result in the following

As you can see, the new reporting services controls make creating a more visible dashboard a lot easier. The one thing that would make the combination SSAS, SSRS as real killer app is the addition of time intelligent function. But i think we are getting there !

Some very cool updates in the new Reporting Services November CTP, read them all here:

http://blogs.msdn.com/sqlrsteamblog/archive/2009/11/09/sql-server-2008-r2-november-ctp-what-s-new-in-reporting-services.aspx

Some changes that have potential:

Shared Data Sets
Datasets can now be shared, stored, processed and cached externally from the report.  Cache refresh plans let you cache reports or shared dataset query results on first use or from a schedule.

Pretty neat feature and could be very elegant

New Data Visualization Report Items
Data Bars - each bar is scaled based on the maximum value of the immediate group (a default which can be adjusted).
Sparklines are data-intense, word-sized graphics.  These visualizations make it possible for report consumers to quickly see things like trends over time at a glance.
Indicators are small icons that are often used to depict the status or trend for a given value.  Setting up indicators based on a number of pre-configured sets is now very easy.
Furthermore, this CTP includes improvements to the Map wizard.

I like em ! the indicator comes as a suprise but a very welcome one !

Aggregates of Aggregates
This enables report authors to nest RDL aggregate expressions inside other RDL aggregate expressions with unlimited nesting levels.  For example, the expression =Avg(Sum(Sales, “Month”), “Year”) would compute the average total monthly sales.

This is very cool ! could be very handy !

Enhanced SharePoint Integration for SharePoint 2007 and SharePoint 2010
This includes support for multiple SharePoint Zones, the SharePoint Universal Logging service, and Report Parts.  We added a new SharePoint List data extension with query designer support.  Furthermore, support for right-to-left text with Arabic and Hebrew was added in the SharePoint user interface.

Dont know what this really means.. what are SharePoint zones.

I’m creating a demo where i want to make a chart that shows me numbers in a month by planned number.

To get the best visual view i want to combine to chart types in one chart:

To do is actually very easy:

Steps for achieving this in 2008

1. Select the chart.
2. drop the required fields in the drop data fileds here section.
3. Select the particular filed on which you want to have line chart on the bar chart.
(Click on one of the required bar on which you want to have line chart.)
4. Right click and click on change chart type.
5. Then select Chart type window will be opened select the line chat which you required.

Found at the SSRS forum