Hi Team,

I have row group created here on Due Month & Sales Region & added total after Due Month. When a user clicks on particular amount I would like to send the Sales Region value to the new report as Parameter. So Instead of Total text , I would like to have all the sales regions concatenated and sent to the new report.

Can you please help me by writing an expression to get the report parameter? I have added the picture of how I want the values to be concatenated , instead of Total . Is this not possible in SSRS? Need some help please

Thanks in A

Hi Swathi,

According to your description, you want to pass all Sale Region values to the sub report when you click on the total Amount ($18,263,182). Right?

In Reporting Service, we cannot pass the sale region value at aggregate level to the sub report. And if we do this, only the first value will be passed to the sub report. In this scenario, when you click on the total amount, you need to pass month field value to the sub report and query all sale regions based on month value. We have tested in our local environment. Please refer to steps below:

1. Right-click the sale and Sum(sale), then select Text Box Properties and go to Action tab.
2. Select Go to report and add parameter like below (sale pass month and saleregion, Sum(sale) pass month):
   
3. Go to the next report (Report5). Create two datasets to query the month and saleregion parameter. The month parameter available values from month dataset. The saleregion parameter available and default values from saleregion dataset. Ps: saleregion must select Always refresh in advanced tab.
   main dataset query: select * from table_8  where month = @month and saleregion in (@saleregion)
   month dataset query: select distinct month  from table_8
   saleregion dataset query: select saleregion from table_8 where month = @month

The result is like below:

If you have any question, please feel free to ask.

Regards,
Shrek Li

Instead of Displaying the Text Total, Is there a way to Concatenate all the above values and show like , "East,West,Public Region,Central" & then may be I can use this field for sending to the next report?

Hi SG,

Passing the filed to the next report that is a feature of "Go to report. As I mentioned above, saleregion file pass 2 parameters and Sum(sale) pass 1 parameter. In Reporting Service, it will only pass the first value if the field has several values.

Regards,
Shrek Li