PerformancePoint Blog

I love PowerPivot. And I really, really love the visualization in the management dashboard that allows one to see which reports are active across time. I’ve thought to myself many a time, “Self, I sure like that visualization. I wish I could show my OWN data inside that thing, however”.

So I decided to take a crack at making my own. Keep in mind I’m a BI guy…NOT a coder. However, I found that most of the elements I needed were already out on the internets: I just needed to track what I needed down, and wire everything together.

So, here’s a screenshot of my baby in a dashboard.

For hosting reasons I won’t get into, you can click ON the second screenshot to launch a working version of the app. Note that it’ll take a few seconds for the bubbles to paint based on how happy my server is. If prompted to login, try corp\runreport as the username and pass@word1 as the password.

Joy.

So, the theme of this post is “if I can do it, you can do it”. I’ll walk through how you can build one of these, and give you my sample project at the end, too.

Scenario:

I have make-believe data for 10 fictional ad agency clients. The data includes metrics around how many online ads are viewed by surfers (impressions), how many of those ads get clicked (clicks), and how much my customer is paying per click (CPC) .  I want to display Clicks on the Y-axis of my chart, CPC on the X-axis, and change the size of the bubble based on how many impressions that client is getting. The slider will control which week I’m showing the values for.

Technology Overview:

• Silverlight (Don’t freak, BI person! I promise you too can do this!)
• SQL Server Analysis Services
• SQL Reporting Services (SQL 2008 R2)

Like I said, I’m a BI guy, not a Silverlight expert. I don’t WANT to be a Silverlight expert. I used Visifire’s Silverlight chart control to do most of the heavy lifting. There is a free version and a pay version. Go check their site out and read the licensing agreement to understand what you’re agreeing to.

All of my data is in SSAS. I found that binding to a SQL data source is much easier than to SSAS, but such is life.  Using SSRS 2008 R2, I created a report which grabs the data I want out of a cube, and then renders the report in ATOM format. I then coax the Silverlight control to consume the ATOM/OData feed.  So,  yes,  you need R2 to do this, or you need to figure out another way to do your data binding.

Here’s how the application works:

• An SSRS report is used to return information from SSAS.
• When the Silverlight app needs data to work with, it uses URL Access to cause the report to run and return results as an ATOM feed.
• The Silverlight app stores this data in memory, and using LINQ, grabs the rows necessary to show for the week we’re dealing with.
• As a user moves the slider, the Silverlight app uses another LINQ query to pull the correct rows from memory, and binds them to the chart.

The Report

Pretty basic, folks! You’ll build an SSRS report which pulls data from either SSAS or SQL, and then stuffs the data into a Tablix:

The rendered report:

The report’s query:

The report in design view:

I created the Tablix using the Matrix wizard. My metrics went into the Values list inside the wizard, Year_Week into the Row groups list, and Client_Name into the Column Groups list.

After that, I removed:

• The three “totals” columns on the left-hand side of the matrix
• The “totals” row at the bottom of the matrix
• The  top-most “grouping” row (which displays the values for your column group)

I removed the top-most “grouping row” because I wanted my resultset to look tabular – It needed to have one and only one row presenting field names. If I hadn’t removed it, I would have had two field name rows, and lord only knows what would have happened when my app tried to consume it!

I finished off by modifying the labels for my metrics. Rather than just displaying static text like “Impressions”, “Clicks”, or  “CPC”, I needed to mash in the Client name we were displaying the metrics for. I used a basic expression to do this. Take a look at the screenshot above and you’ll see exactly how I did it.

The Visifire Chart:

If you navigate to the chart designer page on www.visifire.com, you can create your bubble chart. On the Data page, make sure you switch the Render As drop-down box to Bubble, and then experiment to your heart’s delight until you have something you like. I suggest you play with:

• Chart Page / Theme
• Chart Page / View3D
• Chart Page / AnimationEnabled
• Chart Page / ColorSet
• Title Page / Text
• AxisY Page /  Title
• AxisX Page / Title

When you’re all done, click the View XAML button, and copy that stuff into notepad – it’s the design of your chart! You can also use the Chart XAML that is part of the sample project which does some other things like forcing min/max values on axes, formatting axes values, etc.

The Silverlight Project:

I used Visual Studio 2010 and Expression 4 to create the Silverlight application. I also installed the Silverlight Toolkit. If you don’t have any or all of these tools, jump out to Tim Heuer’s blog and learn how to download/install them. He’s a real live Silverlight guru, and his walkthroughs are awesome.

Now that you have your tools, make sure you download and install the Visifire chart control!

Project Walkthrough

Let’s walk through the project, shall we?

BubbleDataRow.cs

First, we need a class which will represent a row of data. You’ll find that in BubbleDataRow.cs. Note the class named DataRow. You need to create a property for each field the SSRS report returns.

In addition, go ahead and create a “Tooltip” property which will be used when you hover over a bubble in the chart.

For reasons unknown, the report returned 3 textbox fields which contained the name of the client associated with the row of data. To save time, I kept them all rather than trying to rename and/or eliminate any. You can probably do better, but I didn’t bother.

Finally, you’ll see I circled a few things of interest – references you’ll need to add, etc.

MainPage.xaml

MainPage.xaml is where your “UI design” will go.  In the sample project, you’ll see that MainPage.XAML contains a number of items:

• A StackPanel (used to align & organize UI like an HTML table does in a web page)
• The XAML for the Visifire chart – it’s pasted inside the StackPanel
• A Slider control to change the week we’re viewing
• A Button to fire off the weekly animation
• A TextBlock to display the week we’re working with

Take a look at the “Raw” XAML. You’ll see a snippet like this:

           <vc:Chart.Series>
                <vc:DataSeries RenderAs="Bubble" AxisYType="Primary" >
                    <!--<vc:DataSeries.DataPoints>
                        <vc:DataPoint XValue=".2" ZValue="11284" YValue="351139" />
                        <vc:DataPoint XValue=".5" ZValue="39500" YValue="345254" />
                        <vc:DataPoint XValue=".7" ZValue="25442" YValue="50000" />
                        <vc:DataPoint XValue=".1" ZValue="22000" YValue="274316" />
                        <vc:DataPoint XValue="1.2" ZValue="3000" YValue="207349" />
                       </vc:DataSeries.DataPoints>-->
                        <vc:DataSeries.DataMappings>
                            <vc:DataMapping MemberName="XValue" Path="CPC"/>
                            <vc:DataMapping MemberName="ZValue" Path="Impressions"/>
                            <vc:DataMapping MemberName="YValue" Path="Clicks"/>
                            <vc:DataMapping MemberName="ToolTipText" Path="Tooltip"/>
                        </vc:DataSeries.DataMappings>
                    </vc:DataSeries>
                </vc:Chart.Series>
            </vc:Chart>

Remember those “hard coded” data points in the Visifire chart designer? I’ve commented those out and instead am using Visifire’s ability to do DataMapping. Inside the DataMappings element, we are:

• Mapping the CPC property/field of our DataRow class to the X-axis
• Mapping the Impressions property/field of our DataRow class to the Z-axis, which represents the size of the bubble
• Mapping Clicks to the Y-axis
• Mapping our Tooltip property/field to the ToolTipText of the chart

If you look at the Slider element, you’ll see it has an event handler which fires when someone moves the slider thumb:

ValueChanged=”slider1_ValueChanged”

The button has an event handler, too:

Click=”Button_Click”

MainPage.xaml.cs

Here’s where everything comes together. The code itself is pretty basic, and does not include exception handling – you’ll want to improve on that.  I’m going to give some highlights:

PowerPivotClone_Loaded() executes the SSRS report via URL Access. Here’s “my” URL Access string:

http://foo.com/reportserver?http%3a%2f%2fcsusdemo%2fReport+Library%2fPowerPivotSample2.rdl&rs:Command=Render&rs:Format=ATOM&rc:DataFeed=xAx0x1xCx0

• foo.com is the fictional location of my external website. The real machine name is csusdemo. If you are doing this on an intranet, you’ll probably have the real machine name here.
• http://csusdemo/Report Library/PowerPivotSample2.rdl is the pointer to the report we’re going to run.
• rs:Command=Render&rs:Format=ATOM&rc:DataFeed=xAx0x1xCx0 is where we tell SSRS to render the report as an ATOM data feed.

The easiest way to get this string right is to run your report in Report Manager, click the orange “Export to Data Feed” button, and then save the resulting file to disk. Open it with notepad and examine the contents – you’ll more-or-less see the same string as above except:

• foo.com is replaced with http://machineName/_vti_bin/ReportServer? You’ll want http://foo.com/repoertserver? or http://csusdemo/reportserver? instead.
• Symbols like / : <SPACE> and & have been encoded.

GOTCHA: Make sure you pull the DataFeed value out of the file above…don’t just use mine (rc:DataFeed=xAx0x1xCx0), as you’ll get a different value from SSRS and you need to use it.

OnAllQueryComplete() runs when data from SSRS arrives. In this callback, we load up a variable with all rows from SSRS and then figure out which rows we want to plug into the chart immediately by way of a LINQ query.

GetMoreRows() is executed when the slider value changes. It uses LINQ to grab another set of rows to bind to the chart based on what week we just “slid” to.

Other Stuff

I think you probably have enough to work with now. One note on deployment, however: In order for Silverlight to access services (like that SSRS report) in another domain, you have to do a bit of work by adding policy files in the root folder of the site which hosts the service.

You can read more about this requirement here:

http://timheuer.com/blog/articles/silverlight-get-started-part-3-accessing-data.aspx (search on cross-domain access)

In order for SL to consume your SSRS report, you’ll need to drop the clientpolicy and/or crossdomain files that are in the sample files into the root of the site SSRS runs under.

Here are two threads which explain how to accomplish this:

http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/6df4da9b-01ba-409b-a482-a2ceebbde4fd

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/483b2f31-fd82-4918-ae51-5e3d0070c0c3

I personally run SSRS in MOSS Integrated mode, and I generally have SSRS run under port 80 along WITH MOSS itself. As a result, I dropped my files into the root of site SharePoint -80, or C:\inetpub\wwwroot\wss\VirtualDirectories\80

Good luck, and have fun!

The Sample Code

Here!