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.
