Kasper de Jonge PowerPivot Blog

As most of you probably know in SQL Server 2008 SSRS integrated in SharePoint we had a most annoying bug. When you want to add a drilldown action to another report when using SharePoint you had to add the entire URL to the action. As can be seen in this BOL article: http://msdn.microsoft.com/en-us/library/ms159847.aspx

The list of report names includes all reports in the current report server project. If the drillthrough report is on the report server but is not in the project, type the name of the report. The report name can contain a relative path from this report or an absolute path to the report on the server. An example of a relative path is …\DrillthroughReport\Store. An example of an absolute path is \SalesReports\DrillthroughReport\Store.

On a report server configured in native mode, specify a drilthrough report name without the file extension .rdl.

On a report server configured in SharePoint integrated mode, you must specify the report name in an expression and include the file extension .rdl. For example, =”http://site/subsite/Documents/DrillthroughReport.rdl”.

When i read the what is new in Reporting services 2008 R2 i noticed this sentence:

When working with reports on a SharePoint server integrated with reporting services, subscriptions and drill-through links will now work directly with the linked resources in the document library itself.

When i got home I immediately decided to check it out, and it worked So now it you can just add the filename and it works. I noticed something else as well:

They finally embedded the return to previous page in the control as well ! yeahhh.

Two small but great changes who will make my life a little easier

For anyone who works with analysis services and reporting services the SQLCAT team has release a great whitepaper:

Providing strong customer service has helped this customer become the largest commercial bank in Thailand. Always looking for new ways to enhance the services it provides, the bank wanted to study its customer data to help it better determine customer needs, but a reporting system based on manual report development in spreadsheets by using mainframe data (provided in text files) proved cumbersome.  The customer wanted to implement a business intelligence solution on the Microsoft® SQL Server® 2008 BI suite, including SQL Server Analysis Services and SQL Server Reporting Services, to ensure the proposed solution could meet their customer requirements of the Balance Scorecard (a dynamically updated dashboard of Key Performance Indicators (KPIs)), dynamic reports, and dynamic security. The customer has gained a better view into its operations and deeper customer knowledge since deploying its BI solution.This document focuses on the technical concepts and techniques we used to implement reports with SQL Server 2008 Reporting Services, using an Analysis Services cube as the data source.

It has taught me a lot of new things i did not know .. and I work with this stuff almost every day .. it gives also a great example of how SSRS and SSAS are not really aligned together since you have to do a lot of manual work and change the default MDX to custom MDX.

Check it out here.

Great whitepaper on:

Using Kerberos authentication in a SQL Server 2008 Reporting Services service environment provides a mechanism for mutual authentication between client and server before a secure network connection is established. This article describes how to configure and troubleshoot a Reporting Services service environment to use Kerberos authentication with full delegation.

Download the whitepaper here.

Think you might need this more than you want ..

Just a quick post to let you know that you don’t have to look for the SharePoint Add-in in the RTM version of SharePoint and SQL Server 2008 R2. This is now integrated into the SharePoint prereq installation:

Very good from Microsoft to make this step Although i have installed the RTM on my PC I didn’t notice it until someone asked it on twitter, i found the answer on this blogpost:  http://whitepages.sfintelligence.com/?p=98

Was reading through my RSS feeds today and read this excelent post by Robert Bruckner which shows you how you can hide a static column and how you can make sure you get the result you want:

http://blogs.msdn.com/robertbruckner/archive/2010/05/02/report-design-shrinking-hidden-static-columns.aspx

A common question is customer who want their report to be printed automatically in landscape.  You can achieve this really easy by using the report properties and click the landscape picture. This will change the width and height from the report to landscape properties.

The printer will automatically detect it to be landscape and print it accordingly.

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.