Advanced Matrix Reporting Techniques

David Leibowitz wrote a great article on some of the more advanced issues when creating Martix reports. View the article here.

The following six points are discussed in the article:

1. Dynamic dataset creation – Rather than provide a simple select statement or reference to a stored procedure, you’ll be able to dynamically modify the dataset source code at runtime.
2. Query-based dynamic grouping – My last article showed you how to dynamically change grouping levels on your report output (the layout tab). But those were based upon a static list of values in the Parameters collection. This time, we’ll use a query to illustrate that you can provide dynamic grouping based upon a dynamically changing dataset.
3. Dynamic column names – Keeping in spirit with the whole dynamic nature of this article, we’ll change the actual column headings depending upon the type of data our user has selected.
4. The cells-in-cells technique – Otherwise known as the "rectangle inside a textbox" technique, this helps us achieve a bit more usability with the somewhat limited framework of the SSRS Matrix control by allowing us to add as many subcolumns within a column as we’d like
5. Custom matrix aggregates – The heart of this article, and probably the very reason you are still reading. Going beyond the standard SUM, we’ll use the powerful inspection expression InScope() to provide nearly limitless calculations.
6. Custom chart coloring – We’ll add some data visualization to the report, but we’ll modify the chart coloring at runtime.

Report Datadriven Subscription with multiple values

The issue I ran into here was that I had created a report running from a cube. I then setup the data driven subscription to read a parameter from a table, generate the reports, and store them in Excel format. My problem was that I had another parameter which had to have multiple values.

This is all good if you can hardcode the multiple values when you create the subscription, but what if the values had to change? As was the case.

Setting parameterized defaults on the report did not work... Don't know why though. When running the report manually it seemed fine, but running with a subscription caused the report to process indefinitely.

The Solution:
You will need to setup a scheduled storedproc or SSIS for this one... Probably at the same time you want your data driven reports to run.
Calculate the proper values for the multi value parameter, then store them in the "Subscriptions" table of the "ReportServer" database. This table holds the definitions for all the subscriptions created.
The values for the parameters will go in the "Parameters" column. It is in xml format so it will look like this:


<ParameterValues>
<ParameterValue>
<Name>Param1</Name>
<Field>Param1Field<Field>
</ParameterValue>
<ParameterValue>
<Name>Param2</Name>
<Value>Param2MultiValue1</Value>
</ParameterValue>
<ParameterValue>
<Name>Param2</Name>
<Value>Param2MultiValue2</Value>
</ParameterValue>
</ParameterValues>

Now just update the table in SSIS, and run the scheduled task (which can be found in SQL Agent) using the "Execute SQL Server Agent Job" task

‘Njoy
Tiaan Otto

SSRS - Report Subscription Error

We got an error when trying to create any type of report subscription.

Error Given:
The EXECUTE permission was denied on the object 'sp_verify_job_identifiers', database 'msdb', schema 'dbo'. The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.

Part of the solution can be found at:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1520081&SiteID=1

This only states that you should redo the databse connection. We tried that and it did not work. What you should do is: Go into your Report Services Config Manager, goto the set the Credential Type to "Windows Credentials" and then use sqlservice as the "Account Name"


Hope this saves some time