How to get a column from the commandtext chosen by the report user as a pie chart series grouping

Question

Hi there,

Be gentle with me, this is my first post.

I have a report with a commandtext like this:
SELECT "Column1, Column2, Column3 from Notifications WHERE (Received_Date_Time  > @From_Date AND Received_Date_Time  <= @To_Date )"
The @From_Date and @To_Date are chosen by the report user on the report toolbar.
I have a third ReportParameter that is populated by VB.Net with the column names i.e Column1, Column2, Column3 for the report user to chose also.

I need to dynamically get the column name and use this in the pie chart SeriesGrouping.
If tried placing an expression to get the column name chosen by the report user !(=Parameters!Column.Value) which should return "Column2" for example
inside the expression to get the value of that column for the series grouping;
like this.....
=Fields!(=Parameters!Column.Value).Value

however this results in the following error messages from reporting services:

[rsCompilerErrorInExpression] The GroupExpression expression for the chart ‘chart1’ contains an error: [BC30277] Type character '!' does not match declared data type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields'.

[rsCompilerErrorInExpression] The Label expression for the chart ‘chart1’ contains an error: [BC30277] Type character '!' does not match declared data type 'Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields'.

Is what I am trying to do possible and if so how please?

regards,
McCartrey


 

Answer 1

Your expression would require two passes to evaluate, so unfortunately that is not possible.

You may find the Switch function works here. Something like this:

=Switch(Parameters!Column.Value = "Column1", Fields!Column1.Value,  Parameters!Column.Value = "Column2", Fields!Column2.Value,  Parameters!Column.Value = "Column3", Fields!Column3.Value)

Granted, this is not nearly as dynamic and would require maintenance should you add/change columns.

---

This posting is provided "AS IS" with no warranties, and confers no rights.