Monday, July 10, 2006 3:30 PM
We would like very much to define and manage our KPIs through AS2005's KPI repository.
We would like those KPIs to be visualised with some sort of sweet looking gagues, like the ones available from Dundas.
However, I can't see any reference to using this product to connect to KPIs that have been centrally defined. Instead, it looks to me like you have to do all the " If actual > budget then 1" sort of logic all over again, within the gague - i.e. at the presentation layer, which seems fundamentally flawed to me.
Am I missing something? Has anyone used any of these sort of visualisation tools to connect direct to AS2005 KPIs?
Tuesday, July 11, 2006 2:34 AMModerator
You could use Excel 2007 (Beta 2) to connect directly to AS 2005 KPI's:
Each KPI component can be added to the PivotTable Values area by checking the checkbox just like any other field. Letâ€™s take a look at an example, specifically, and example of adding Value, Goal, Status, and Trend to a report on our Products and Product Categories. Here is what the report looks like when I add those four components.
As you can see, Value and Goal are presented as numbers. Status and Trend, on the other hand, are nice graphical representations â€“ they can be used to get a very quick visual overview of your business as it is easy to pick out outliers etc. As I mentioned, Status and Trend are normalized values between -1.0 and 1.0. Since these sorts of numerical values are not very interesting to show in a report, we have worked with the SQL Server Analysis Services 2005 team to develop a set of images to represent the Status and Trend for any KPI. The images to be used are defined in the Analysis Services model, so everyone that looks at the Status or Trend in Excel sees the same graphic.
Tuesday, July 11, 2006 8:13 AM
Thank you Deepak. ( What a fantastic blog, btw).I'm sure that Excel Pivot tables will form part of this solution.
However, we also have a requirement for a very graphically rich dashboard type visualiser. The Dundas gagues and charts (there must be others too - I'm not a Dundas shill!) have a 'wow' factor that Excel pivot tables can't come close to.
Basically, I just want to point one of the visualisers at a KPI in an AS cube, and have it pick up the value / goal / status / trend, and display itself accordingly. Isn't this what everybody would want? Why would anyone want to redo the Value / Goal / Status / Trend maths again in the presentation layer?!?!!?!?
Tuesday, July 11, 2006 5:57 PMModerator
That's all too logical, alas - I'm not real familiar with the detailed architecture of the Dundas products, but you might pose this question to Dundas support.
Teo Lachev's blog has an example of a simple KPI custom report item:
One of the most exciting (but not well known and documented) extensibility areas of SSRS 2005 is custom report items (CRI). As its name suggests, custom report items allow you to implement your own reporting controls when the standard SSRS report items (textbox, chart, table, matrix, etc.) are not enough. For example, the attached screenshot shows a report that uses a CRI I wrote to show a KPI value graphically.
Wednesday, July 12, 2006 8:04 AM
"..but you might pose this question to Dundas support..."
I've done exactly that. One of their guys has got back to me, asking me to explain the requirement in more detail, so if anything interesting comes out of this (or at any rate, a definitive answer either way), then I'll post it.