Adatis BI Blogs

Analysis Services KPIs and Reporting Services 2008 R2

There was a comment on my last blog post by Peter Eb asking about whether Reporting Services 2008 R2 knows how to render SSAS KPIs automatically. The short answer is that it doesn't unfortunately, but there are only a few steps needed to get it working. For this blog post I've built a simple report that uses the Adventure Works DW 2008 R2 Analysis Services database as a data source. The first step is to build a data set that contains a query that references the Month attribute and the Channel Revenue KPI: I'm interested in reporting on the status and trend part of the KPI, so I've added the month and value parts of the KPI, plus blank columns for the status and the trend: For the status and the trend columns, the next step is then to drag on (or right click and insert) an indicator in each of the cells. This is when the manual steps come in: Right click on the indicator to open the properties window; Set the Value of the indicator to be the Status part of the Analysis Services KPI; Change the States Measurement Unit to Numeric; Configure the indicator Start and End properties to be -1, 0 and 1. SSAS KPIs can have an unlimited number of status values, so it may be necessary to add more states here if SSAS returns more than 3 values for the Status. This changes that are needed to the indicator properties window is shown below: Applying the same method to the Trend part of the KPI produces the following simple report: This post wasn't really supposed to compare SSRS Vs PerformancePoint Services, but I've found out that PerformancePoint Services does pick up the SSAS KPI meta data (slight mismatch on the colours only), saving you having to set up the indicator yourself: It's a shame that the KPIs aren't picked up automatically by SSRS, but then again the above process isn't too difficult to master.

Bullet Charts and Sparklines in Reporting Services 2005

Here at Adatis we're big fans of Reporting Services.  Yep, it has it's quirks and frustrations (don't talk to me about excel export!) but doesn't deserve all the criticism it gets in our opinion.  You can create some great looking reports with not a lot of extra effort over the defaults (though how many times have you seen a "slate" style report called "Report1" go into production!) We're also fans of what some might call the more "trendy" visualisation techniques such as sparklines and bullet charts and with Microsoft acquiring Dundas technology for use in SQL 2008, SSRS has some much improved functionality in this area.  I'll be taking a look at some of these new features in a future post. Whilst SSRS 2005 does have it's limitations, with a little effort you can still create some very effective information visualisations.  The dashboard below is built using completely standard functionality Adding sparklines and in-table bar charts is very simple (and pretty clever IMO).  This MSDN Article covers how to do it in detail but in essence you simply add a line or bar chart into a cell in your data table. Whilst the bullet chart (in the Sales Channel Performance region of the dashboard) may be simplistic compared to those available in SSRS 2008 or MicroCharts from XLCubed (from whom I nicked the styling for our dashboard) it still provides a very clear visualisation of the data.  This required a little more trickery in the underlying query but is also just a chart in a table cell - this time a 100% stacked bar.  I've explained how this works here

PPS Monitoring - Missing Parameters using a Reporting Services report in SharePoint integrated mode

If you're running Monitoring with SP1 applied and working with a Reporting Services report in your dashboard that comes from a report server in SharePoint integrated mode, you may experience an issue where your parameters don't appear in the Report Parameters section. There's a hotfix available for this issue upon request from MS support:

Tracking PerformancePoint Planning Submissions Using Reporting Services

The standard operational reports that come with PerformancePoint will allow you to report on a variety of PerformancePoint admin related activities, such as cycles, assignments, forms, jobs and associations. I find that the assignments report is particularly useful - after all, finding out who has/hasn't submitted is an important part of any data-gathering exercise. Whilst it is useful, I do find that the assignments report is the one that admin users want changed, especially when a model site exists containing many cycles and assignments. Extra Functionality With a large PPS Planning implementation you can easily end up with many assignment instances, cycles and users. I've been involved in such an implementation recently, and, due to the large number of assignments, the admin user requested a bit more filtering capability than the out of the box assignments report provides. Also, the existing assignments report tells will tell you that user A has submitted their assignment, but it won't go into any detail about what the submission actually contained. E.g. did the user submit all their entities? For some users it is quite key to know what other users have been submitting - for one thing it makes navigation easier if as an approver you know exactly which department/entity to pick in the assignment filters. Examples By knowing which tables to use, you can write an SSRS report that provides the additional functionality mentioned above. The starting point is to get the base report query right. In my case, as I'm in a separate auditing database, the query goes inside a custom stored procedure, and is as follows: SELECT A.AssignmentId, C.CycleInstanceName, AD.AssignmentDefName, A.AssignmentName, U.UserId, U.UserName, ENT.Name AS EntityName, CUST.Name As CustomerName, CASE WHEN A.Status = 'partial' OR A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1 ELSE 0 END AS Draft_Submitted, CASE WHEN A.Status = 'Approved' OR A.Status = 'Submitted' THEN 1 ELSE 0 END As Final_Submitted, CASE WHEN A.Status = 'Approved' THEN 1 ELSE 0 END AS Approved, Approve.UserName As Approver FROM dbo.Assignments A LEFT OUTER JOIN dbo.[MG_Planning_MeasureGroup_default_partition] Fact ON A.AssignmentID = Fact.AssignmentID LEFT OUTER JOIN dbo.AssignmentDefinitions AD ON AD.AssignmentDefID = A.AssignmentDefID LEFT OUTER JOIN dbo.CycleInstances C ON C.CycleInstanceID = A.CycleInstanceID LEFT OUTER JOIN dbo.D_Entity ENT ON ENT.MemberId = Fact.Entity_MemberId LEFT OUTER JOIN dbo.D_Customer CUST ON CUST.MemberId = Fact.[Customer_MemberId] LEFT OUTER JOIN dbo.BizUsers U ON U.UserID = A.ContributorUserId LEFT OUTER JOIN dbo.ApproverList AL ON AL.AssignmentID = A.AssignmentId LEFT OUTER JOIN dbo.BizUsers Approve ON Approve.UserID = AL.ApproverUserID You can figure out most of the tables to use by looking at a view called AssignmentsView within the application database. One thing that I have taken into account is assignment definitions. If you have large number of users completing an assignment, then the chances are that you will have set up an assignment definition that points at a business role or a submission hierarchy. You ideally want to be able to filter on the assignment definition to return all assignment instances that belong to that assignment definition. Therefore, in my case I have three filters for the report, but you could easily add more: The final view is a report that shows the status of the assignments returned by the filter, but also, when expanded, shows the the entities and customers that the contributor has submitted: The above is just a taster of what can be achieved. A couple of ways that it can be extended include: Integrating with Sacha's data auditing idea to provide detailed history on what values the contributor has changed; Including comments, annotations and deadlines.

Reporting Services 2008 - Connection Problems

Having recently rebuilt a server entirely with "2008" technology I have a small problem with reporting services.  When trying to look at the Reports, I received the error: "The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel." I found, that even though we are not trying to look at the secure site, the certificate causes issues.  For development and testing purposes I found it easiest to remove the certificate.  To do this perform the following steps: 1. Open Reporting Services Configuration Manager 2. Click Web Service URL and click "Advanced" 3. Remove the SSL certificates, both for IPv4 and IPv6 and click ok, and then Apply.  This will remove the SSL certificates, you should now be able to use Reporting Services 2008. 

Oooh - See the pretty report ;)

Users love pretty things! Especially upper management who have minions to actually make sense of the numbers behind the reports for them ;)  Out of the box Reporting Services reports look anything but pretty! Here's few tips to make your bosses happy :) Use a background image as a border for charts. The image behind the chart below is a simple png file of only 8kb in size.  To make this work you need to use a rectangle object with the background image set to your image and the chart dropped in the rectangle Use custom palettes for your chart series.  To do this set up a new table with two columns, one that relates to the key's in your data source and one with the hex codes of the colours you want to use.  Then in your recordset join the id to your data.  Once you are returning the hex value for each row to your RS dataset you can use this to define the series colour: 1) Right click on chart -> Properties 2) Select the data tab then edit the value 3) Select the appearance tab then the series style button 4) Select the fill tab then use an expression to 5) Set the color property to your dataset field 6) Click OK, OK, OK, OK (phew!) I've uploaded a sample project to show how this is done.  Use black borders and lines sparingly (silver is always a good one). Put RS in Sharepoint Integration mode - the toolbars and filters are much nicer and auto-hide (sort of!).  Make sure you consider the limitations of this mode first though. Avoid using 3d perspectives in your charts - this is the easiest way to make your charts fugly (IMO)!

ProClarity and Reporting Services reports in a PPS Monitoring Dashboard

So I was going to do a post on displaying a reporting services report in a PPSM Dashboard but then realised then that the PPS team had already done that: And then I was halfway through a post on how a Proclarity view works in PPSM when the PPS Team posted one on just that! Hey guys - leave some for us!! ;) Just so you haven't completely wasted your time reading this, here's a couple of additional points: Multi-select filters don't work with multi-value parameters in RS. It only passes the first item selected through to RS. There is no concept of cascading filters in PPSM v1 - The dev team know this is much requested feature. So far we've got around this by using a multi-select tree filter and the promise of a fix in the next version!! Your don't need to define anything particularly in your Proclarity to be able to connect a PPSM filter.  Just connect a filter that contains valid members from any dimension in your cube.  I know Alyson has mentioned this in her post but quite a neat feature I reckon If you have a slicer on the background - this will still display within your PAS view as shown below: Remember to consider RS/Proclarity security and kerberos related implications depending on your PPSM security config and environment (Another post coming here i think!) Using these two report types lets you get around a few of the "still to come" features of the built-in analytic chart and grids such as: Customise chart colors - (PC/RS) Use a secondary Y axis on a chart (PC) Use SQL stored procedures as a datasource (RS) Lastly Nick B has just done a great post on how parameters work with a web page report. This opens up pretty much endless possibilities...

Reporting Services features not supported in Sharepoint Integration Mode

The latest version of SQL Books Online details the features of Reporting Services that are available in Sharepoint Integration mode and, more importantly, the features that aren't!!  Be careful if you plan to migrate as there are some surprising omissions: URL addressing is different in SharePoint integrated mode. SharePoint URLs are used to reference reports, report models, shared data sources, and resources. The report server folder hierarchy is not used. (This basically means no querystring parameters!!!) Reporting Services custom security extensions cannot be deployed or used on the report server. The report server includes a special-purpose security extension that is used whenever you configure a report server to run in SharePoint integrated mode. This security extension is an internal component, and it is required for integrated operations. Report Manager or Management Studio cannot be used to manage a report server instance that is configured for SharePoint integration. Data-driven subscriptions are not available. This applies to all editions. The rs.exe command line utility is not supported. The utility does not support the SOAP endpoint used for programmatic access to a report server that runs in SharePoint integrated mode. Linked reports are not supported. My Reports is not supported. Job management features that allow you to stop a long-running report process are not supported. Batching methods will not be supported. I haven't tested it yet but I have a feeling this might mean that the PerformancePoint Planning operational reports may not work in this mode The online (!) Books Online article is here