Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Applying filters to two series independently in SSRS 2008 R2

Posted on 2014-10-09
5
Medium Priority
?
1,088 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
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach 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.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

916 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