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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS Page Header from Group Data 2 61
Estimating my database size 7 51
Fill Second ComboBox based on First ComboBox Using and SQL 2 34
SQL query 45 41
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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