Boon Blog

Project “Crescent” Demo: Introduction to Highlighting

Sean Boon — Fri, 05 Aug 2011 07:46:34 GMT

With the release of SQL Server “Denali” CTP3, we are introducing Project “Crescent”. Project Crescent is Reporting Services’ new reporting tool targeted at data consumers to visually explore their data and answer ad-hoc questions with ease. There is lots to cover with Project Crescent and over the next months you are going to see a significant amount of content related to it via the usual channels. In fact, if you haven’t taken the opportunity yet, I’d highly recommend the following links:

SQL Server Reporting Services Team Blog Announces Project Crescent Availability
Project Crescent Overview and Wiki
Project Crescent: Demos Galore (session from TechEd North America 2011)

Also, today, we released a lengthy 14 minute video titled Project Crescent: Getting Started and Getting the Most from Project Crescent. It covers much of the functionality available in CTP3.

In addition to these resources I’m planning on starting a video series accompanied by a set of PowerPivot workbooks. These videos will be based on real data, all of which I plan to make available for everyone to download. I certainly view these as works in progress, and as I look at topics to cover, I’ll definitely be making changes to the workbooks and republishing them. And if you are watching these videos, and you questions or have an idea about how to improve the workbooks with examples, please do feel free to contact me here on the blog. I’d like this to be as much of a community effort as possible.

This first video demonstrates a feature in Project Crescent that is known as highlighting. If you want to read a great article on this topic, I highly recommend Stephen Few’s article Coordinated Highlighting in Context. I’m going to demonstrate the Crescent functionality over a set of maybe two or three videos, and this first one covers the interactivity associated with column charts and column charts that have series and a legend. The data being used for this demonstration is from the CARS (Car Allowance Rebate System) program that ran here in the United States during 2009. You may have heard of this program by another name, the “Cash for Clunkers” program. At any rate, being a government program, the data is all freely available for download on the site and I have turned it into a very basic PowerPivot workbook that you can download here if you like. There are quite a few interesting stories to tell about this data, and I hope to show you some of them. Over time as I create more videos and demos covering additional topics, I plan to update the workbook and continue to make it available. Below is the first video on this topic.

Project Crescent Demo: Introduction to Highlighting

To summarize the video, everything in a Crescent report is interactive (except maybe the textboxes : ) ). People can simply click on a column in a chart and use that as means to highlight data throughout the report. The other thing you hopefully noticed is that as a report author, I didn’t have to configure this feature at all, it just works. There are no property pages that open up and require you to be an expert in how the system works underneath to make it all happen. Much of it happens due to the relationships that are already defined in the model, but as someone who is creating the report or interacting with the data, you don’t even need to be aware of that. And highlighting isn’t the only thing that happens on the canvas. Charts can also filter and we’ll look at that feature in a future video.

Map Gallery on Codeplex for your SSRS 2008 R2 Reports

Sean Boon — Wed, 29 Sep 2010 16:03:47 GMT

If you are using maps in SSRS 2008 R2, you might be aware that the Map Wizard only provides U.S. based maps. However, it is totally possible to add more maps to the gallery. Head on over to http://mapgallery.codeplex.com/ where a community project has started up to provide maps for other locations. The site also includes links to directions for how you can create files that will load into the Map Gallery for Reporting Services.

Maps: You’re Missing the Point(s)!

Sean Boon — Mon, 03 May 2010 16:41:13 GMT

While the map in SSRS 2008 R2 supports different data sources for spatial data (ESRI shape files, SQL Server spatial, and included maps in the Map Gallery), sometimes that may not be enough for your needs. Another way you can get spatial data into the map is to add it to the map directly in the report authoring environment (both BIDS and Report Builder 3.0).

In this scenario, we’ll look at an example where you might have a list of stores with just a store name field and you’d like to show their sales and profitability. The dataset looks like this.

Once you have the dataset, simply add a map to the report by right-clicking –> Insert Map. This will place a blank map on the report.

Next, select a map and add a tile layer. This will place a Bing! maps tile layer background on the map. In our case, we are going to assume that our stores are all in the state of Washington so we need to zoom the map into the Washington state area and we can do that by just panning and zooming the map in the designer. After zooming and panning the map will look something like this.

Now that we have the state, we can add embedded points into the map. For our case, we are going to assume that our three stores are in Seattle, Tacoma, and Olympia. To add the points, first add a point layer to the map using the fly-out.

Now there is a point layer on the map. Next, select the drop-down on the point layer and choose the option to “Add Point” as shown below. Add the point over the Seattle city area.

Once the point is on the map, you can select it and bring up its property page. Give the point a name, and you can optionally choose to open up a lot of other properties for the point using the checkbox override. For now, we’ll leave this box in its unchecked state.

Repeat this procedure to add Store 2 and Store 3 in Tacoma and Olympia.

Once the map has all three points added, select the Point Layer in the fly-out and choose the option to bring up the layer wizard. This will bring up the layer wizard for the point layer.

Select the Bubble Map option and hit “Next”. On the next page where the wizard asks you to select an analytical dataset, select the dataset you created based on the query at the beginning of this post, and then select “Next”.

On the next page you will be asked to bind the analytical data to the name property we created on the embedded points. Here’s a screenshot of what that should look like.

On the next page of the wizard, we’ll choose the options for setting the bubble size and bubble color. In our case, we want to make the bubble sizes dependent on sales and the bubble color to be dependent on profit margin. Below is what the wizard should look like once it’s filled in.

Click “Finish” to leave the wizard and return to the report design surface.

Before we run the report, there’s one last bit of cleanup we need to do. Select the Point Layer and bring up the Point Color rule property page. We want to remove the point colors showing up in the legend on the right. Select the Legend tab and from the “Show in Legend” drop-down, de-select Legend1 as shown below.

Exit the property page and then return to the report. Go ahead and run it now and you should see the following:

That’s it. Of course, you could go in and add a Legend Title, or even change the Point color rule properties to create as many color buckets as you like to customize the report further.

del.icio.us Tags: SSRS,maps,R2,SQL Server Reporting Services 2008 R2

SSRS 2008 R2 Data Visualization Examples

Sean Boon — Thu, 18 Mar 2010 02:20:57 GMT

Today, the Reporting Services team gave a series of presentations at an all day “airlift” event. Here are the reports and database backup for the demo I gave. In future posts, I will walk through building this report. Thank you for attending the event today!

An Introduction to Data Bars in SQL Server Reporting Services 2008 R2

Sean Boon — Mon, 23 Nov 2009 21:30:42 GMT

Data Bars are a new feature in SQL Server Reporting Services 2008 R2 introduced with the November CTP. Data Bars are useful for building what are often referred to as “trellis” and “table lense” displays that help report consumers indentify possible correlations between multiple values, or in the case of a single column, to simply get an idea of the distribution of small and large values, and the differences between them.

I’ve taken the NFL report from a previous post and added a few columns to it to show what is possible using the new data bar feature in SQL Server Reporting Services.

The first column to the right of the sparkline displays the team’s turn over ratio. One more column to the right displays the team’s points per game versus the league average. The final column displays the team’s yards per game statistic.

When I first look at this table, which is sorted by total wins, I see a couple of things. First, the Denver Broncos gained the most yards, but turned the ball over a lot (which probably explains why they missed the playoffs last year and maybe why the Bears are having problems this year :) ). Second, the teams that score less points than the league average also tend to lose more games than average. That’s a bit obvious, but notice that the Steelers actually scored less than the league average, but had the third most wins. Without the aid of the data bars, I suspect it would take much longer to see these insights.

To build this report, you will need to download the NFL database here. Also download the NFL report with the win-loss sparklines as this serves as the starting point for this exercise. In part 1, we’ll build a data bar to track points per team, and in future posts we’ll handle building out the turn-over ratio and points per game versus average columns that go a little more in depth with what you can do with data bars.

To insert a data bar in your report, you can simply pick the tablix cell you want to place the data bar in and go to the ribbon and Insert —> Data Bar. That provides you with the following data bar picker page.

We built support in for a few different data bar types; the standard data bar, the stacked data bar, the stacked 100% data bar, and we also added the column versions of each of these. In this case, let’s pick the standard data bar.

Once the data bar is added to the report, we need to give it a value. For this first example, let’s use the “Score” field from the dataset.

If we run this report, it should look like this.

Select the data bar, right click and bring up the horizontal axis properties property page. Let’s spend a few minutes here to go over these options.

At the top of this page, you will see that the “Align axes in:” option is set to “Talbix3”. This setting works in conjunction with the Axis Min and Axis Max settings direclty below it. For cases where the Axis Minimum or Maximum are set to “Auto” the group maximum or minimum will be used to define the axis range for the data bars. In the sample below, I’ve copied the data bar to a new column to the right and simply changed the minimum setting to Auto. Notice the distinction between the two columns. The column on the right almost implies that the Cincinnati Bengals scored very few points in the 2008 season, when they actually scored 204 points. Personally, I prefer the view on the left where the axis minimum is set at zero.

The final report based on this introduction to data bars can be found here.

In future posts, we’ll dig into some additional formatting options for the data bars and how to handle scenarios where you have both positive and negative values (as shown in the first report in this post).

SQL Server Reporting Services 2008 R2 Map Tip: Address Geocoding with SQL Server 2008 Spatial

Sean Boon — Wed, 18 Nov 2009 21:42:37 GMT

This post is just a pointer to a great blog post on Spatial Ed’s blog that addresses (pun intended) one of the most common questions I get regarding the map in Reporting Services which is, “In my database, all of my store locations, customers, etc. are just addresses. Can you map that?”

The answer to this question is that this data is no different than the other attributes that should be managed using traditional data warehousing techniques. You likely have a customer dimension table with an address, and you should already have in place a set of processes for managing the attributes of your customer through the lifecycle of your data warehouse. Geocoding of addresses should be integrated into this process and you can use the techniques in Ed’s post to accomplish this. Then, when your report runs, all of the geo-coding will be done, you won’t need to make a million web service calls to find out how many customers are within 100 miles of your store.

SQL Server 2008 R2 Map Tips: How To Import Shapefiles Into SQL Server and Aggregate Spatial Data.

Sean Boon — Tue, 17 Nov 2009 23:33:29 GMT

In the August CTP of SQL Server 2008 R2, we introduced the map report item in Reporting Services. As you might already be aware, the map in SSRS supports the spatial data types and functions that are available in SQL Server. In this post, we’ll look at what you can aggregate spatial data for your reporting needs.

The Scenario

In this scenario, we are going to build a map of the United States broken into regions (West, Central, and East).

Step 1: Getting the Map Data

To start with, we need to find a set of maps to support this exercise. The subject of finding maps is one of the most common questions we receive when people are using the map report item. In this case, all of the maps we need can be found on the http:///www.census.gov web site.

The state files are located at :http://www.census.gov/cgi-bin/geo/shapefiles/national-files
The county files are located at: http://www.census.gov/geo/www/cob/co2000.html

For those of you who are already working with the map report item in SSRS, at this point you might be asking yourself “Why not just use the included maps in the map gallery for this effort?”. We certainly could do that, however, there’s another wrinkle to this scenario that would make this approach difficult and we’ll discuss that later in this post.

Step 2: Converting Shapefiles into SQL Server Spatial Data

Now that we have the shape files for states and counties, how do we get them into SQL Server spatial? To do this, we will use a tool from http://www.sharpgis.net/page/Shape2SQL.aspx called “Shape2SQL”. All you need to do here is point the tool at the shapefiles you downloaded previously, and now your data is stored as spatial data types in SQL Server.

Step 3: Creating the Regions

Now that we have the US states and counties in our database as SQL spatial columns, we need to address how to create the regions. Below is the image of the map that shows the regions we need to create.

I suspect that this will be a very common requirement when building map reports. You’ll have a set of shapefile data based on political units, but your company or organization has units of analysis that are made up of these units. These units of analysis, in our case regions, we’ll change over time. Stores move between regions, regions get realigned, etc. Do you want to have to create new shapefiles every time this happens? I doubt it, plus if you have dimensional tables in your data warehouse that track the composition of these regions, wouldn’t it be great to just rely on those definitions that are already defined. We have a couple of options we can rely on here.

Step 3: (Option 1) Aggregate Polygons in SSRS

One of the features that we added in this release is support for the UNION() aggregate on spatial data types directly in Reporting Services.

Let’s look at how we would do this. Add a dataset to your report using the following query based on the database this article links to. This is simply a join between a table that defines our regions and the data that stores the spatial data for our states. I’m taking the liberty of removing the non continental states from the query, but that’s just for the sake of the appearance in the map :).

SELECT *

FROM dbo.Regions r, test s

WHERE r.statefips= s.statefp
and stateFIPS < 60 and State <> 'Alaska'

ORDER by statefips desc

Now, add a map to the report. You could use the wizard here, but to demonstrate this particular feature I would just insert a blank map.

On the map, choose the option to add a Polygon Layer.

Next, open up the Layer Data property page by right-clicking on the Polygon Layer. On the General page, set the spatial field to an expression below.

=UNION(Fields!geom.value)

The Union() aggregate now supports SQL spatial data types! Great, but we need to group it by something.

To do the grouping, open up the property grid and under the data node, open up the Group Property Page.

Add a group based on the [Region] field.

Now, when you run the report, you should have three distinct regions (West, Central, and East).

I would like to thank Robert Bruckner, who has a great blog over at http://blogs.msdn.com/robertbruckner/, who championed and built the prototype of this very cool feature.

Step 3: (Option 2) Aggregate Polygons in SQL Server

Support for the UNION() aggregate in SSRS is a great feature, but you will notice that if you are aggregating a lot of polygons at report run-time, that your report is going to take awhile to run. This aggregate is not cheap. If you are looking for a best practice in this area, I would recommend that you look at aggregating this data in SQL Server and storing the results of those aggregations in your database, just as if you were building aggregate tables in a traditional data warehouse.

While there is an STUnion() function in SQL Server spatial, it operates on two spatial objects, so you can’t group multiple items and use a traditional GROUP BY query in SQL. Thankfully, the SQL Spatial team has built a function library that can be added to SQL Server that will enable you to use a standard GROUP BY clause in your queries! You can download this function library at:

http://sqlspatialtools.codeplex.com/

Follow the instructions for enabling the functions on your SQL Server, and when you’re done, you should be able to write a query like this:

SELECT

MAX(Region), dbo.GeographyUnionAggregate(s.geom.Reduce(2)) as geo2

FROM dbo.Regions r, USStates s

WHERE r.statefips= s.statefp

and s.statefp < 60

GROUP BY r.region

This query is a join against the table that defines our regions and the table that contains the imported shape data that is now stored in SQL spatial.

The reduce function that is included above is designed to work around a current issue in the product in which the way the globe is projected in SQL Server 2008 spatial.

Here is what the query result looks like.

You can include this query as part of your SSIS packages in which you do your ETL operations. Then, in your report, you can you can write a simple SELECT statement against this new table that returns the aggregated shapes in your query. You can then add a layer to your map based on this dataset. This would give you the best performance at report run-time.

Downloads

I’ve added the database backup and report that shows the RDL UNION() aggregate to the location below.

SSRS 2008 R2 Samples

Building Win-Loss Sparklines in SQL Server Reporting Services 2008 R2

Sean Boon — Mon, 16 Nov 2009 19:21:56 GMT

With the November CTP of SQL Server Reporting Services 2008 R2, we are introducing a new visualization in SSRS, sparklines. Sparklines were developed by Edward Tufte and are described as “small, intense, simple data words”. In this post we’ll look at how you can add a “win-loss” sparkline to your reports.

For this example, we’ll take a look at the 2008 NFL season schedule by team and draw a sparkline to depict each team’s performance over the year. When we’re done, it will look like the image below. This image shows each team’s result by week where the up ticks are wins and the down ticks are losses. The dark blue ticks are home games and the light blue ticks are away games.

Step 1: The Dataset

The dataset that we need contains the scores of all the games from the 2008 NFL season. I’ve placed a backup of the database for this exercise up at SSRS 2008 R2 Samples.

SELECT Home as Team, HomeScore as Score, [Week], 'H' as Home, Away as Opponent, AwayScore as OpponentScore, (SELECT Team from TeamOffense where teamoffense.TeamAbb=scores.Home) as TeamName
FROM dbo.scores

UNION ALL

SELECT Away as Team, AwayScore as Score, [Week], 'A' as Home, Home as Opponent, HomeScore as OpponentScore,(SELECT Team from TeamOffense where teamoffense.TeamAbb=scores.Away) as TeamName
FROM dbo.scores

Step 2: Add Matrix

Inside the report body, add a matrix to your report and place the TeamName field on row groups. This should give you the look below.

Step 3: Add Sparkline

To add a sparkline to your report, you can right click on a non-detail cell of a tablix or use the ribbon entry point.

We have added several sparkline types to Report Builder 3.0, but the concept for all of them is the same. They are effectively small charts that are stripped down to just the visual for the series. There are no legends, no axis titles, no data point labels, etc. It’s just the line, bar, or column. Of course, if you want to add any of these features that are turned off because you are targeting a small multiple scenario (subject of a future blog post :) ), you can simply select the sparkline and choose the option to convert it to full chart. Sparklines are persisted in our RDL format as full charts.

For this example, we are going to use the column chart. Select that first option. Your report should now look like the image below.

Step 4: Specify the Series Value and Category Grouping

In our scenario, we want to use the sparkline to chart each team’s result over the 17 weeks of the NFL season. To start with, let’s just give the series a value which we will go in and edit. Select the bar series and you should see our new data visualization panel. This is new in the November CTP as well.

Click on the “+” symbol and add the [Score] field to the Values area.

Next, we need to define the Category Group for the sparkline. In our case, each bar represents a week so, we’ll pick the [Week] field. This will remove the details group. Your data visualization panel should now look like the image below.

Now things are going to get a little bit more advanced. The purpose of our sparkline is to show whether or not each team won or lost for each week of the season. We need to modify our Series value expression in order to achieve this goal.

With the data visualization panel still open, select the series and bring up it’s properties.

Change the Value to the following expression:

=IIF(IsNothing(SUM(Fields!Score.Value)), 0, IIF(SUM(Fields!Score.Value) >SUM(Fields!OpponentScore.Value), 1,-1))

There is a lot going on in this expression, and we’ll get to all of it before this post is over, but for the time being, what we should take away from this expression is that it’s simply doing a check of the current team’s score and comparing it to the opponent’s score, and assigning a 1 for a win and a –1 for a loss. These are the values that are going to be charted on the sparkline.

Let’s go ahead and run the report to see how things are looking to this point.

Step 4: Axis Synchronization

If you count all of the little up and down ticks on our sparkline, the avid NFL fan will notice something wrong with this picture. Namely, each team has 16 “sparks”, but the NFL season is actually comprised of 17 weeks in which every team gets one week off. As a result, if we were trying to ask ourselves, which teams won on Week 6, the sparkline above would not be helpful, because some of the teams would have had their bye week already. What we want to see is a gap in the axis to reflect the bye weeks.

For those of you who are not NFL fans, here’s another scenario that is similar. Suppose you have 10 sales people, and only a few of them worked for the entire year, but you have a few that worked during the holiday season. When you display the sparklines to show their monthly sales, you want all of the monthly sales bars to be the same size in width and be aligned to the appropriate month.

How do we solve this problem? One way would be to use an OUTER JOIN query, but we realized we shouldn’t necessarily require report authors to do such work in order to get the visual they need. So, along with the addition of sparklines, we added another feature in SSRS 2008 called Group Domain Scope that surfaces itself in sparklines as Axis alignment. The domain scope feature is actually very useful in non-sparkline scenarios as well. I will cover this particular feature in a future blog post, but for folks who want to start using it now, you can think of it as a feature that fills in the missing values for each instance in a group based on the total unique instances in the group.

Back to the problem at hand. To insert all of our bye weeks on the sparkline, we will use the axis synchronization feature. You can do this by selecting the sparkline and bringing up the Horizontal Axis properties.

Check the check box for “Align Axes in:” and set it to the tablix.

Run the report. Now you should see the following:

Notice now that each team has a gap in their sparkline that corresponds to their bye week. If you look back at the value expression for the series, this is where the IsNothing() check on the team’s score comes into play. If the team doesn’t have an entry for the week, and they don’t have an entry in our database when they are on their bye week, we can check for that using IsNothing() in conjuction with the Axis Synchronization feature and the result is we chart a value of 0 for those instances.

Step 5: Conditional Formatting the Sparkline

If you look back at our final sparkline at the top of the post, you will notice that some bars are light blue and some bars are darker blue. Why and how do we do this? The why is simple: I’d like to be able to show a distinction between home and away games.

The “how” part is actually relatively straightforward. Open up the Series property page and go to the fill tab.

Open up the Color expression editor and insert the following expression:

=IIF(Fields!Home.Value= "H", "Blue","LightBlue")

Run the report. Now we have the effect we are looking for.

Step 6: Let’s Add Some Tooltips

One last requirement before we are done. I’d like to see the opponent for each week in my sparkline. For this case, we can add a tooltip to our sparkline, so that user’s can hover over each point and see who the opponent was.

Select the series and open up it’s Series Data property page. There you will find a tooltip property. Set it to the [Opponent] Field. Now, run the report.

It’s hard to see where the mouse is pointed in this capture, but it’s on Week 5 of the Arizona Cardinals schedule. Looks like they beat the Buffalo Bills that week.

Step 7: Add the Axis Line

This last one is a bit hidden, but to add the horizontal axis line we will need to go to the property grid. Select the Sparkline and in the property grid, and open up the Chart Areas section.

Select the Category Axes in the above editor and then in the window that opens up, choose the option to mark the axis visible.

That’s it. Your report should be all done now.

Download the Solution

I’ve placed the supporting database backup and the reports up on my SkyDrive account. You can download them here.

SSRS 2008 R2 Samples

Working with XY Scatter Charts in Reporting Services 2008

Sean Boon — Wed, 03 Jun 2009 08:55:34 GMT

Recently, I’ve been fielding a few questions regarding XY Scatter Charts in Reporting Services 2008 (see MSDN Forum Question). We have definitely noticed that working with XY Scatter charts is a bit difficult, and we do appreciate the feedback and will work to improve them in a future release. The remainder of this post will detail how you can successfully build these charts in SSRS 2008.

Option 1: Use a Unique Identifier on Rows.

This example relies on using the Category Grouping feature of the chart, but requires a unique identifier in your result set for each row. Star with this example query:

SELECT 1 as X, 1 as Y, 100 as RowNumber
UNION ALL
SELECT 2 as X, 3 as Y, 200 as RowNumber
UNION ALL
SELECT 2 as X, 7 as Y, 300 as RowNumber
UNION ALL
SELECT 3 as X, 10 as Y, 400 as RowNumber

Next, insert an XY Scatter chart into the report. Place the RowNumber on Category Groups, and add “Y” to the Data Fields area. At this point the chart will look like the image below.

Finally, right click on the [Sum(Y)] token in the data fields area and bring up the property page for the series. Here, change the Category field value to the X field.

Now, run the report, and your chart should look like the one below.

Option 2: Use a Detail Group as the Category Group

Instead of returning a unique identifier on your records, another option is to rely on a detail group for the category group for the chart. From a UI perspective, this one is very difficult to discover.

First, let’s start with the same SQL query provided above, and insert a new XY Scatter chart into the report.

Again, place the Y field in the data fields area of the chart flange, but this time place the X field as the Category Group (it actually doesn’t matter which field you put here as you will see in a moment).

Next, right-click on the Category Group in the chart flange and bring up its property page. It should look like this:

With the page open, select the single group (shown outlined in red) and press the “Delete” button. This will empty the group box. Hit “OK” when done. Believe it or not, you have just created a detail group for the chart.

Once the detail group is created, there is an issue where the chart flange is no longer available. To edit the series, select the series in the plot area and bring up its property page. On this page, set the Category Field equal to [X].

Run the report. The XY scatter chart should display correctly.

I agree that both of these solutions are effectively work-arounds for a feature that should be much easier to use, and this is something we will work to address in a future release of Reporting Services.

Enabling Drilldown on (SSRS) Reporting Services 2008 Charts

Sean Boon — Mon, 09 Feb 2009 03:25:10 GMT

In this post I'm going to spend some time covering a subject that comes up quite a bit on our forums, which is how to enable drill-down on a chart in Reporting Services. In this example, I'm using the Analysis Services 2008 AdventureWorks cube.

First, create a connection to the Analysis Services database, and in the report, create a dataset based on the following query:

SELECT NON EMPTY
{ [Measures].[Internet Sales Amount] } ON COLUMNS,

{[Customer].[Customer Geography].[Country].members,[Customer].[Customer Geography].[State-Province],[Customer].[Customer Geography].[City]} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

This returns a dataset that looks like the following:

The dataset is similar in structure as a query you could author against a relational database using the GROUP BY clause.

The next step involves inserting a chart into the report. In this example, I'm using a column chart. Once the column chart is inserted into the report, add the country field to the category groups and then add the Internet Sales field to the data fields section of the chart.

Another piece we'll need in this sample is a set of parameters to track where we are in the hierarchy as we drill. Create three parameters in the report named pCountry, pState, and pCity and set their visibility to hidden, allow null values and no default values supplied.

Next, return to the category groups on the chart. As we drill on the bars, the category grouping on the chart should change from country, state and city, and the dataset should be filtered to the correct level as well. To set up the category grouping on the chart to use the following expression.

=IIF(isNothing(parameters!pCountry.Value),
Fields!Country.Value, IIF(isNothing(parameters!pState.Value)=True,Fields!State_Province.Value,Fields!City.Value))

Use a similar expression for the label text option which is right above where you define the category groups.

To set up the filtering, select the filter option on the category group and add a filter and set it's expression to the following:

=IIF(IsNothing(Parameters!pCountry.Value), IsNothing(Fields!State_Province.Value),
IIF(IsNothing(Parameters!pState.Value), (IsNothing(Fields!State_Province)=False AndAlso Fields!Country.Value = Parameters!pCountry.Value),
(IsNothing(Fields!City.Value)= False AndAlso Fields!State_Province.Value=Parameters!pState.Value)))

Set the operator to "=" and set the value to "=True" (no quotes required). By setting up the filter expression in this way, we are effectively doing a check on our level parameter, and depending on it's value, we are passing a different expression along to the filter operation. In the simplest example, if the country parameter is set to Nothing, we simply pass the IsNothing(Fields!State_Province.Value) expression to the filter. All records where that expression evaluates to True will not be displayed.

Finally, we need to set up the action on the chart series so that when the user clicks on a column, we redisplay the report. To do this, select the series and set up the page as shown below.

In this example, we are specifying our existing report as the "go to report" and are passing the current state of our parameters to the new instance of the report that will execute when the user selects one of the bars on the chart.

In closing, this is just one option for implementing a drilldown on a chart. Another option here, that I would recommend for larger data sets would be to actually set the query to an expression based on similar logic. If you have other alternatives for implementing this type of a scenario, add a link in the comments section or contact me via the blog. I'm interested to hear how others implement this scenario.

I've uploaded this file to my SkyDrive account. You can get it here.

del.icio.us Tags: SSRS,data visualization,charts

Attending SQL PASS Community Summit This Week

Sean Boon — Wed, 19 Nov 2008 06:20:30 GMT

This is just a quick note to let people know that I'm attending the SQL PASS Community Summit this week. Here's a summary of where you can find me if you have some questions regarding data visualization or SQL Server Reporting Services, or if you'd just like to stop by and say "hello". Should be a great conference!

Wednesday 11 AM - 1 PM (PST) - Ask the Experts
Wednesday 1:30 PM - 4 PM - Microsoft Product Pavilion
Friday 4PM - 5:30 PM - Report Authoring with SQL Server 2008 (Breakout Session)

SSRS and Microsoft Chart Control How To: Change the Width of Bars and Columns

Sean Boon — Fri, 31 Oct 2008 05:14:32 GMT

Every once and I while I get asked this question, and it fits nicely with a series I've been planning to blog on what you can do with custom attributes in charts in SQL Server Reporting Services 2008. The question is, "How do I change the width of the bars and columns on the charts?".

To change the width of bars or columns on a chart, you need to access the custom attributes for the charts. In Report Builder 2.0, you do this by going to the ribbon under the View area and check the "Properties" checkbox. This will enable the property grid that people who use the BI Development Studio are familiar with. Next, select the series you are interested in on the chart, either via the chart flange or by selecting the bar or column on the chart. Look over to the property grid and look for a section called "General". Under the "General" heading there is an area for custom attributes. Expand the custom attributes node and you will see the following.

Right there under custom attributes you can change the relative size of the point widths. Anything less than 1 will create space between the bars and columns. Anything greater than 1 will cause the bars or columns to overlap.

Custom attributes also work on the ASP.Net and Winforms chart that we just released.

Here's a code snippet that'll do the same thing if you are using the stand-alone controls.

VB ' Set the Series PointWidth. Chart1.Series(0)("PointWidth") = "1.2"

// Set the Series PointWidth.

Chart1.Series[0]["PointWidth"] = "1.2";

I'll be posting more on custom attributes very soon.

Silverlight Charts Are Now Available with the Silverlight Toolkit!

Sean Boon — Tue, 28 Oct 2008 23:56:41 GMT

Today, at PDC, the release of the Silverlight toolkit was made available. Included in this release, is a preview version of a charting control that was produced as a collaborative effort between the Silverlight controls and SQL Server Reporting Services team.

This initial release includes support for the following chart types:

Bar
Line
Pie
Column
Scatter

In addition to these series types, the chart supports a few types of animations, but it also can update based on changes in the underlying data if the chart is attached to an ObservableCollection-based data source. The chart also supports the templating capabilities present in Silverlight.

That's it for now. Let us know what you think of the Silverlight charts over on the discussion area in codeplex. We're eager to get your feedback!

Microsoft Chart Controls for .NET Framework 3.5 Released!

Sean Boon — Sat, 25 Oct 2008 04:21:42 GMT

This is just a quick post to help spread the word that the Microsoft Chart Controls for .NET Framework 3.5 are now publicly available. A big congrats to the team!

Here are some useful links

Downloads

1. Microsoft Chart Controls for Microsoft .NET Framework 3.5 – This installs the ASP.NET and Windows Forms Controls. The assemblies will be installed in the GAC as well as in the “%Program File%\Microsoft Chart Controls\Assemblies” folder. If you build an application using the controls, your setup and deployment should add MSChart.exe installer as a pre-requisite.

2. Microsoft Chart Controls for Microsoft .NET Framework 3.5 Language Pack – This installs the language pack for the Chart Controls. It is available in 23 .NET Framework languages.

3. Microsoft Chart Controls Add-on for Microsoft Visual Studio 2008 – This installs the IntelliSense file in English for the controls and also adds the controls to the toolbox for ASP.NET and Windows Forms.

Where do we find more information?

4. ASP.NET Samples

5. Documentation

Forum: Windows Forms and ASP.NET Chart Controls It will be live shortly.

How To: Build Sparkline Reports in SQL Server Reporting Services

Sean Boon — Fri, 10 Oct 2008 08:30:00 GMT

This is my first post in what I hope is a long series of how to's related to data visualization. For my first post I'm going to demonstrate how you can add sparklines to your SSRS reports.

Below is a screenshot of what a sparkline may look like in SSRS.

Sparklines are effectively miniature versions of charts used to display trends. In the example above, I've used an area chart, but line charts and column charts are also often used as sparklines and in many cases people will add additional visuals such as conditionally formatting the color of data points.

To implement a sparkline in Reporting Services, simply insert a chart into the body of the report. You probably don't want to start by embedding the chart in the table or matrix right away because you will want to modify the properties of the chart and selection of chart elements works best when the chart is large enough that you can actually select its contents. .

You will also need a dataset. In the example above, I have a query that is retrieving data from the AdventureWorksDW sample database that you can download from Codeplex. The query is:

SELECT T.CalendarYear,
    T.CalendarQuarter,
    T.MonthNumberOfYear,
    SUM(S.ExtendedAmount)as Sales ,
    COALESCE(SUM(Q.SalesAmountQuota )/ COUNT(SalesAmountQuota),
    SUM(Q.SalesAmountQuota )/ COUNT(SalesAmountQuota)) as Quota ,
    SUM(S.ExtendedAmount) / (SUM(Q.SalesAmountQuota )/ COUNT(SalesAmountQuota)) as Within,
    E.FirstName + ' ' + E.LastName AS Employee,
    E.EmployeeKey,
    70 as Target

FROM

FactResellerSales as S

LEFT OUTER JOIN dbo.dimTime T ON S.orderdatekey = TimeKey

JOIN dbo.DimEmployee E
ON S.EmployeeKey = E.EmployeeKey

LEFT OUTER JOIN dbo.FactSalesQuota Q on
S.EmployeeKey= Q.EmployeeKey AND
T.CalendarYear = Q.CalendarYear AND
T.CalendarQuarter = Q.CalendarQuarter

WHERE S.EmployeeKey in
(SELECT TOP 10 EmployeeKey from FactResellerSales GROUP BY factResellerSales.EmployeeKey ORDER BY SUM(ExtendedAmount) DESC)

GROUP BY T.CalendarYear ,T.CalendarQuarter,T.MonthNumberOfYear, E.FirstName, E.LastName, E.EmployeeKey

ORDER By Employee, T.CalendarYear ASC, T.CalendarQuarter ASC, T.MonthNumberOfYear

Once the chart is in the body of the report, add the Sales to the Data Fields on the chart and then place the Calendar and Month fields in the category groups.

To finish the look of the sparkline all you need to do is the following:

Delete the chart title
Delete the chart legend
Right click on both the category and value axis and hide them
Select each axis title and right click and choose the option to hide them

To enable the gradient fill on the area chart do the following:

Select the series on the chart and bring up it's properties
On the fill property page, choose fill style = "Gradient"
For this case, the first color is "Cornflower Blue" and the secondary color is set to "White".

Once you have the look of the sparkline, you can just drag the sparkline into the table or matrix in the appropriate place. The table or matrix will do the job of restricting the rows of the dataset so that each sparkline only shows the data for each row (in this case, each salesperson).

If you have any suggestions for topics on data visualization for Reporting Services, please feel free to leave them in comments.