Solved

Applying filters to two series independently in SSRS 2008 R2

Posted on 2014-10-09
5
712 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
How to increase the row limit in Jasper Server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now