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).
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.
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.
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
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
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.
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.
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)
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.
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!
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.
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.
