Hello,

I have a situation where I have to calculated Net Revenue for 3 locations aggregated based on product type. Location are FL,AZ,MN. Product type are A,B,C,D,E.Years are 2014,2015,2016.

Now, Report has multivalue Product Type parameters. Product type drop down is the only parameter. When user select A from the parameter list, then I need to display column chart with three bar, one for each location for each year. 2014 will have three bar,2015 will have 3 bar and 2016 will have three bar once data is available.  If user selects A as parameter, then for location FL and AZ, we need to hide the data for parameter A and display data for MN only for 2014 for parameter A.  When user select A and B multivalue from drop down, then the aggregate value should suppress revenue for A and display B's revenue only for FL and AZ but total revenue for MN (A+B).Similar If user select A,B and C from parameter drop down, the FL and AZ should display Revenue (B+C) and exclude revenue from  A and displayall Revenue (A+B+C) for MN.So on.. Basically I need to exclude revenue from Product A for FL and AZ for all different type of parameter combination for 2014. I need to display revenues for all production for 2015 and 2016.

Please Let me know if I can that in ssrs report side.

Hello 1234alex! 

There are many ways to accomplish what you're looking to do. If I understand you correctly, you're looking to display a bar chart that includes data for each year and when a user clicks the interactive chart the data will display details for what was clicked.

SSRS uses "Drill through" and "Drill Down" options. You could have a child report that contains the detail that is jumped to when a user clicks. This is Drill Through.  Including child data on the parent report is called Drill Down.

Additionally, if you're having problems with multi-value parameters you should research the split and join functions. Also the IN clause for TSQL.

Hello,

I am not trying to create drill down or drill through report. I want one standard report as mentioned above.

To use a single report, you'd want to create it with nested datasets or with subreports, though subreports can cause higher CPU and resource consumption so it is best to limit their use.

You can use the visible option on each element you want to display and toggle them on or off.

There are a lot of great tutorials on creating drill down reports both on youtube and on MSDN.  If you search BING for dd207042 the first link should be what you're looking for. Unfortunately I don't have the verification needed to post links or images in the forums; however, choosing my post as an answer will help me gain points to get verified. :)

If you found THIS POST helpful, please click the Select as Answer link. :)

ff519554 for drill through (I think drill through is a better solution. It's much easier and allows the user to run the detail report separately)

• Edited by Daniel MSFT 7 hours 24 minutes ago corrected msdn search code for drilldown

To use a single report, you'd want to create it with nested datasets or with subreports, though subreports can cause higher CPU and resource consumption so it is best to limit their use.

You can use the visible option on each element you want to display and toggle them on or off.

There are a lot of great tutorials on creating drill down reports both on youtube and on MSDN.  If you search BING for dd207042 the first link should be what you're looking for. Unfortunately I don't have the verification needed to post links or images in the forums; however, choosing my post as an answer will help me gain points to get verified. :)

If you found THIS POST helpful, please click the Select as Answer link. :)

ff519554 for drill through (I think drill through is a better solution. It's much easier and allows the user to run the detail report separately)

• Edited by Daniel MSFT Friday, May 29, 2015 11:41 PM corrected msdn search code for drilldown

Hi 1234alex,

According to your description, you want to always exclude the records for Product A, FL and AZ in 2014. Right?

In Reporting Services, if you have already retrieved all records from data source, the aggregation or filter condition will apply on whole dataset. For your requirement, the most effective way is to exclude the result set in your source query. There's no specific way to ignore records on SSRS report side.

Regards,