Tagged: SSRS

SSRS 2008: Using the TOP filter in a chart

I found an interesting forum post over at SQLTeam this morning, and decided to have a go at coming up with a solution.

Filters are quite commonly used in Tablix reports to show only the top n rows from a dataset.  This is easily done by using the Top N operator in the Filters tab of the Tablix, and choosing the column in the Expression drop down.

In the forum post however, the original poster was wanting to show the top n values in a chart.  The chart would by default sum up the values in the data field.  The trick would be to get the top n aggregated values, as opposed to taking the top n original values and then doing the aggregation.  To start with, I created the following very simple table and data.

create table EmployeeSales
EmployeeName varchar(20),
SalesAmount money

insert into EmployeeSales
(EmployeeName, SalesAmount)
(‘Jerry’, 9.99),
(‘Jerry’, 30.00),
(‘Jerry’, 55.70),
(‘Jerry’, 4.99),
(‘George’, 3.99),
(‘George’, 1.99),
(‘George’, 1.99),
(‘George’, 1.99),
(‘George’, 15.00),
(‘Kramer’, 17.00),
(‘Kramer’, 152.00),
(‘Kramer’, 99.99),
(‘Kramer’, 75.00),
(‘Elaine’, 9.99),
(‘Elaine’, 15.99),
(‘Elaine’, 3.99),
(‘Newman’, 120.00),
(‘Newman’, 5.99),
(‘Newman’, 5.99),
(‘Newman’, 0.99)

I then created a report in SSRS 2008 with a DataSet called Sales, which contained the following SQL query.

select EmployeeName, SalesAmount
from EmployeeSales

I dropped a Tablix onto the report, and set the source to be the Sales dataset, with two columns for the fields in the dataset.  At this point I also created two Parameters, @TopSalesN and @TopTotalSalesN, both integers with a default value of 5.  I then went to the Filters tab in the Tablix Properties and added a filter with the following values (see picture)…

Expression: [SalesAmount]
Operator: Top N
Value: [@TopSalesN]


Then in the Sorting tab, I sorted the data by [SalesAmount], order Z to A.

Running a preview confirmed this worked as expected and I could change the parameter to show as many values as I needed.

Then I added a standard column chart next to the tablix.  I dropped the SalesAmount column on the data fields section, and EmployeeName onto the category fields section.  There is no filter option on the Series properties for the data, so I tried to add a filter on the Chart Properties.  I found two issues with this.  Firstly, if you try to use an aggregate value in the filter Expression then the report fails to process, as this is not allowed.  If you filter on the raw values, then obviously all you do is remove rows from the raw data and the results are not what you expect.

To produce the correct results, the filtering (and sorting, if required) needs to be added to the Category Group Properties on the Category Field.  I set it up as follows…

Expression: [Sum(SalesAmount)]
Operator: Top N
Value: [@TopTotalSalesN]

This gave me exactly what I was looking for.  The image shows the preview of the report, with the top 10 rows from the raw dataset in the tablix, and the top 3 Sales by Employee in the chart.

The original poster also wanted the chart data to be sorted.  This is easily done by adding a sort on the Category Group Properties, sorting by [Sum(SalesAmount)] Z to A.  The final image shows how this looks.

I can see quite a few situations where using this method of having one “raw” dataset and more than one report item consuming the data in different ways would be very beneficial.  Particularly in cases where you have a data report and you need to show a summary chart relating to the data on the same report.

Another method you can use for “Top N” reports is to pass a TopN parameter into a stored procedure, and use that value in the SQL statement.  For example…

create procedure [dbo].[TopSales]
@TopN tinyint = 20

set nocount on;

select top (@TopN)
from EmployeeSales
order by SalesAmount desc

exec dbo.TopSales 10

Column headings do not repeat in SSRS 2008

I’ve just stumbled upon an odd issue with the Tablix control in SSRS 2008.  I deployed a report on a server a few weeks ago and it is now reporting on enough data to make it span multiple pages.  I wanted the column headings to appear on all pages, not just the first.  However, when I set the RepeatColumnHeaders property on the Tablix to be true, the headings still did not appear on the next page.  After a bit of playing around I enabled Advanced Mode in the Grouping Pane, and had a look at the properties for the top (Static) group in Row Groups.  Under Other, there is a property called RepeatOnNewPage.  Setting this to True made the column headers appear as required.  OK, so that’s fine, but I can’t help but think that this a a bug in SSRS 2008, and should be fixed by MS.  Looking through the RDL file itself, I can’t find anything that would cause the RepeatColumnHeaders property not to behave as expected.

The “Header should remain visible while scrolling” option does not appear to work either; I haven’t yet found a workaround for this.

Formatting Dates in SSRS

In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc.  Therefore I’ve found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me.  Previously I would have used FormatDateTime in the expression, as shown here:

=FormatDateTime(Fields!DateStamp.Value, DateFormat.ShortDate)

Depending on the regional settings, this could return either 12/3/2008 or 3/12/2008.  When designing the report, I want to know exactly what the date will look like, no matter where it is deployed.  So I instead use the Format function to allow me to choose the format I require.  Some examples:

=Format(Fields!DateStamp.Value, "dd-MMM-yy")
returns 03-Dec-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy")
returns 03-Dec-2008

=Format(Fields!DateStamp.Value, "dd-MMMM-yy")
returns 03-December-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy H:mm")
returns 03-Dec-2008 16:44

This method removes a lot of the issues regarding date formatting, and means there are no shocks or questions when it comes to deployment time!

Edit: Setting the Language property of the report to your locale will also ensure that dates in your report parameters will have the correct format.  This is something that is often overlooked.