Solved

Applying filters to two series independently in SSRS 2008 R2

Posted on 2014-10-09
5
935 Views
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?

Thanks
Jeff
0
Comment
Question by:jdallen75
[X]
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
5 Comments
 
LVL 37

Expert Comment

by:ValentinoV
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...
0
 

Author Comment

by:jdallen75
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.
Sample-Output.xlsx
0
 
LVL 37

Expert Comment

by:ValentinoV
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!
0
 

Accepted Solution

by:
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.

Jeff
0
 

Author Closing Comment

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

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 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