Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Analysis Server and Reporting Services (2008)  SSRS 2008 : Ignore filter for single chart series

Author  Topic  SHardy Starting Member 35 Posts Posted - 2011-09-23 : 11:47:53 Hi, I am new to SSRS, and I am currently in the process of building some "real life" samples that I can present as a "proof of concept" so as to sell the idea of using SSRS. I am building my report in SSRS 2008 R2 BIDS. It is using a single shared dataset, and currently just has a single line chart. I have added two paremeter selections, which are cascading: 1) Sales Region 2) Salesperson I have report setup so that upon the initial preview it will show the chart for the total company. I can then select a Sales Region only to report the total for that region, or I can select both a Sales Region and then a Salesperson from that region to chart that individual's performance. The chart is quite simple. The x axis is time based, reporting the last 2 years by month. Whilst the y axis is simply some KPI %. I currently have two lines on the chart. The 1st is just the monthly KPI %, whilst the 2nd is a 6 month moving average. What I would like to do (or rather "need" to do) is to be able to add another line or two to give the following results... When filtering to show a region total, I also want to display the total company 6 month moving average. When filtering to show an individual salesperson, I want to display the region's 6 month moving average. When no filter is applied, no additional series should be shown. As I understand, I will add to add two new series to the chart, and apply expressions to their "hidden" properties. I am ok with that. However, my problem is, if we take the 1st example... When I have selected a region filter (currently this filter is applied against the dataset), the current two lines on the chart are then based on that filtered data so as to represent the chosen region. However, I then need to add a 3rd series which needs to ignore that chosen filter, and instead report for the complete dataset. For the 2nd example, it would need to ignore the Salesperson filter, but still apply the Region filter. Is there any possible way for this to be achieved? I understand that if it is, then it may need substantial changes to the report setup to implement. But I am open to any suggestions that may help me to achieve this. Many Thanks, Simon