Applying filters to two series independently in SSRS 2008 R2

Posted on 2014-10-09
Last Modified: 2014-10-23
I'm trying to create a rate chart in SSRS 2008 R2. It actually combines data from 2 source queries:
1) Time-series RATE data, which is shown in green while the machine is running
2) Event DOWNTIME data, which are the intervals shown in red and yellow at full height.
Sample Rate Chart
My first attempt was to use a range chart in SSRS, but the problem is you can't overlay a line/column/area chart on top of a range chart.

My second attempt was to use an area chart and line chart together, which is possible in SSRS.
Let me explain the dataset a bit: RATE and DOWNTIME data come from two different queries, as the timestamps do not align well to join them cleanly. I changed the output of both queries so that they match and combined them, adding an additional field called SourceType ("DT" vs "Rate") to be able to differentiate them within one dataset.

So far, I've been able to visualize the DOWNTIME event data (with rate inflated to the max for the set - 1400) to an area chart, but in order to do so, I had to introduce a Category Group to filter on the DT data only. Now I'm trying to add a line series on the same chart, tied to the RATE data, but because of the Category Group, I get this:
Area   Line chart
Here is the Design view for a little better context:
Design View
My question is, how can I have the Rate line series use the filter "SourceType = Rate" instead of the filter applied to the Category Group as shown?

Question by:jdallen75
  • 3
  • 2
LVL 37

Expert Comment

ID: 40372407
First of all, thanks for this challenging question!

As you've discovered it's not possible to apply a filter to just one chart series.  However, there's a workaround!  The trick is that the fields you use in the Values box should be filtered in advance.  This can be done by adding two calculated fields to your dataset using a formula like this:

=IIF(Fields!CurrencyAlternateKey.Value = "USD", Fields!EndOfDayRate.Value, 0)

Open in new window

The expression above would be used to create a Rate_USD field.  Putting Rate_USD in the Values box now creates a line that contains the "filtered" USD data.  A second field called Rate_EUR and its line in the chart could be created in a similar fashion.

Doing the above means you don't need the filter on the category group as the data shown is already filtered.

I hope you understand what I mean?  In your case you might need to implement it a little differently, depending on what your dataset looks like.  It's a bit difficult to say without seeing the query and the data...

Author Comment

ID: 40373047
Hi ValentinoV,

I appreciate both the feedback and the idea, however it doesn't work because of the way I had to manipulate the data to get the area chart to work properly. First, I've attached a sample dataset in Excel to put some better context around it.

The records in green are the Rate data, which is the basis for the line series (which works now). The records in yellow are the original DT data, which as you can see, the start/end times don't join nicely with those of the Rate data. Their rates have been updated to the max of the Rate set (to get full bar height). The trick I then had to pull out of a hat was to add the records in orange; they duplicate the yellow records, except that the orange EndTimes correspond to the yellow StartTimes (and orange StartTime doesn't matter). What this accomplishes is that it gives the downtime event "width" for the area chart (see the Area Line chart in the original post).

As you can see in the "reference-only" range chart, this worked for the DT events because you attach the StartTime *and* EndTime to each range segment. In line/column/area charts, you are only attaching *one* time, in this case the EndTime. So to get the area chart to work properly, I had to have a series consisting of effectively both StartTime and EndTimes for each event, and could not include zeroes for the Rate data, hence the category filter.

So in the calculated fields (bottom two in the design view), rather than set the irrelevant values to zero, I need to actually remove them (I think I tried NULL instead, but that leaves weird gaps in the series). I also removed the filter from the category group. That is why the area chart is thrown out of whack - I've added black markers to the Downtime series to highlight this impact.
Modified DesignResulting Output with calculated fields
Thanks for the post, and if you have any other ideas, I'd love to hear them. For now, I believe my only option is to create a series of one-minute buckets of data, and populate each with the appropriate DT and Rate data. Then I can use a line/area chart to achieve the result.
LVL 37

Expert Comment

ID: 40386386
Hi, just wanted to let you know that I've given this some more thought but I can't come up with another solution as what you've proposed.  Have you been able to get it working since you posted your previous comment?  Would be great if you got that to work!

Accepted Solution

jdallen75 earned 0 total points
ID: 40386476
Unfortunately not. I had to merge the two datasets into one-minute buckets so I could properly access the right fields for both series. Thanks for the check-in though.


Author Closing Comment

ID: 40398867
No solution found - a technical limitation of SSRS. Had to merge the datasets as in the previous comment.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question