Kasper de Jonge PowerPivot Blog

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

I had to create a report this week where the client wanted the values of this year next to the values of last year. To compare year total until the current month they want a subtotal field at the current month.

To achieve this i created a row between the Total and the Total of the month where i want to show the Total until that month.

As expression I use the RunningValue function:

=RunningValue(Fields!Reseller_Sales_Amount.Value, Sum, Nothing)

This creates the sum per month of the current month. Now all you have to do is make the row invisible on all months except the current. I used a second dataset to determine the last month we have data from.

This results in the following result where the subtotal field will automatically move up and down depending on the data.

I was creating some reports that were linked together by drilldown on organisation level. The entry report is different per user since not all users have access to the organisation wide report.  But because the organisation exists of a ragged hierarchy the drilldown links have to be determined dynamically.  This means the entry report for department level only users will have to load the report dynamically .

The only option is to use multiple subreports and determine the visibility in an expression, I decided to use a Countdistinct on the organisation level in the expression. One mayor drawback of this construction is that when a reporting control is invisible it doesn’t mean it wont be executed. This means both of the reports will be loaded on report execution, not good for performance. I decided i really wanted to control the subreport execution so i created a parameter in the subreport and add this to expression that loads the report (and the other drilldown report) with 1 or Nothing as value. When a parameter isn’t passed (same as Nothing) to the subreport the report won’t load thus no performance penalty.

I really don’t like these constructions but i don’t see another way. I wish you could use an expression to fill the report name to load, would make sense wouldn’t it ?

Sometime ago i found the excellent blog of Valentino Vrankenwhen searching for Chart Optimization Tips. He has posted a new blog about Pie Chart Techniques in which he covers all kind of Pie chart issues in SSRS and how to solve them. Great post and must read when you want to work with Pie Charts:

http://blog.hoegaerden.be/2009/10/25/pie-chart-techniques/

I was working with a client where i had some difficult security questions, that i had to solve without having to resort to advanced MDX query’s. The client has to be able to create reports without MDX knowledge. Take the following example from the AdventureWorks cube:

I want to give users a report that show all sales by state province of the country they are in, e.g. you are a sales person belonging to postal code 91801 and you want a report showing all the state-province’s of the United States since that is the country you belong to.
As seen in the dimension below:

But by default users are forbidden to see the sales of another state province when their postal code isn’t in it. When you place security on a country by using a role, autoexists take care of the security for the rest of the levels resulting in only the parents of the selected postal codes will be shown to the user with the role defined.

This will result in not being able to show the parent levels your postal code does not belong to, as seen below while browsing the report using the role:

When you create a report based on this dimension you get the following report (don’t mind the formatting)

The key to solving this problem is using two dimensions with the same levels. One with the security enabled by using a role and one dimension (you can just copy the original dimension) without security, you could remove the postal code level in this dimension to make sure the security is intact.

When you create your report you use levels from both dimensions, and this is where autoexists is your friend. Since you want to show only city’s from a sales persons own country you use the the country level from the secured dimension (only his own country is returned by the secured dimension) and the state province level from the security-less-dimension, autoexists takes care of showing only those state provinces of the country the sales person belongs to.

Resulting in the following desired report:

All in all reasonably simple when you understand autoexists.

SQLCat has a great blog post about installing SSRS with Sharepoint integration:

This technical note describes how we designed and implemented a business intelligence solution that utilized a server farm containing Microsoft® Office SharePoint® Server 2007, Microsoft Office PerformancePoint® Server 2007 Monitoring Server, and Microsoft SQL Server® 2008 Reporting Services in SharePoint mode, all running on Windows Server® 2008 R2 and with all servers and applications configured for Kerberos authentication. In this technical note, we discuss the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

wish we had this sooner, implementing this is NOT easy. See also my colleague Marc Valk excellent blog post about Sharepoint and SSRS integration over multiple servers (not the default all on one box) with the famous double hop issues and how to fix them. Read it here:

http://www.marcvalk.net/2009/04/sharepoint-and-ssrs-integration/

I have developed a report where i have to use data from different datasets, to get these on one report i have to use multiple tablix data regions
(one dataset per tablix). The report must appear as one tablix in stead of multiple. The tablix data regions are placed below each other with the same columns.

And that’s where the layout problems start, I made sure the columns from the tablix data regions have the same width and are aligned by using the alignment grids.
While using preview mode it looks great the columns are aligned perfect, but after publishing the report to the reporting server the layout is all fumbled up. The columns are no longer aligned below each other.

After some extensive debugging and playing around, I found the solution: it appears reporting services renders the preview different from the browser, the solution from my problem was to set the cangrow property of the row to false (the default setting is true).

I understand this is the solution to my specific problem but it can give you a hint to your solution, there are several properties that can alter the layout of your report.