Integrating KPI in Reporting Services and drill down reports

I'm interested to implement KPIs from Analysis Services 2005 in Reporting Services 2005 in a graphical way as it is seen inside Analysis Services.

how can I archieve this?

and

what is the best way for drilldowns in reporting services?

Thanks for your help,

Roger

OK, my question is general - thats because I'm new to SSRS... So I did some reading and found out that the Matrix is a good way to display drill-downs.

I'm wondering: Do charts offer a possibility for drilldowns?

and I did not found a place to read anything about KPI and presenting them in Reporting Services in a easy and efficient way like you can do it in SSAS.

Anybody can give me a hint?

Thanks, Roger

|||

One way to do support AS2005 KPI's is to place an image control within your data region (e.g. table) and use separate images to represent each state the KPI could be in. Then use an expression to map each KPI image to the correct state based on your the value of an expression as follows:

=iif(Fields!Product_Gross_Profit_Margin_Value.Value = -1, "gauge_asc0",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < -0.5, "gauge_asc1",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0, "gauge_asc2",

iif(Fields!Product_Gross_Profit_Margin_Value.Value < 0.5, "gauge_asc3",

"gauge_asc4"))))

Here, guage_ascn represents the various states the KPI could be in. While not 100% integrated to "absorb" AS2005 KPI images, should still let you get the job done.

Alternatively, might want to start from a Report Builder report since that tool offers native support for showing AS2005 KPI's. To see how to edit a Report Builder report using the report authoring tool in VS2005 see http://blogs.msdn.com/bimusings/archive/2005/09/23/473379.aspx

|||Thanks! Regards Tom|||

How to include SQL Server 2005 KPI’s in Reporting Services Reports

(The following is originally a description with screendumps, which unfortunately are not supported. Hopefully usefull anyway ...)

There seems to be an issue about including SQL Server 2005 KPI's in Reporting Services Reports (in Visual Studio) - especially the images associated with the generated parameters, e.g. "-1" leads to "red gauge / traffic light" and so on.

However it is possible to make reports in Report Builder including SQL Server 2005 KPI's.

It has been suggested to include a data source and a Report Server Model in the Visual Studio Project. This should make it possible to build a report including KPI’s. However there still seems to be a problem accessing the measures even though the metadata can be viewed. The Report Wizard does not have an option for including graphics for the indicators – only the numbers to generate the images (“1”, ”-1”, ”0” etc.). Therefore the indicators are not included in the dataset and cannot be included in the reports.

This has lead to the suggestion, that there is an issue with the Report Model functionality in Visual Studio.

There is a work around though ...

First you must associate your KPI’s to measure groups in the Visual Studio project …

Establish an Analysis Services data source on your Report Server …

Note: the Connection String must be typed when the data source is established. (See next-next screen dump.)

From the data sources on the reportserver a Report Model can be generated.

(Double-click the data source and click on generate model.)

Based on this model, reports can be build in Report Builder - including SQL Server 2005 KPI's, including images.

Open Report Builder from the Report Manager. The established data model(s) will be shown as possible data sources.

Build the report …

The reports can be exported as a .rdl-file from Report Builder. Click Save to file … not Save as …

If you choose Save as … the report is saved on the server.

Import the .rdl-file in your Visual Studio project …

The layout of the report can be edited in Visual Studio. The Data and Preview panes return an error when clicked.

Save and deploy the report … Right-click the project name / Properties to view where the report is deployed to by default (e.g. http://localhost/reportserver/reports).

The described solution is definitely a work-around – it is not an optimal solution. There are two major downsides with the solution: the reports cannot be previewed in Visual Studio and the dataset cannot be changed in Visual Studio.