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  Development Tools  Reporting Services Development  Iif/Switch

Author  Topic  AnaC Starting Member 1 Post Posted - 2006-04-11 : 13:01:29 Hi! My report has a multi-value parameter used to select the elements to put in the "group by" clause of this query: = "select " & Switch(Parameters!GroupBy.Value(0) = 0, "column1 ", Parameters!GroupBy.Value(0) = 1, "column2 ", Parameters!GroupBy.Value(0) = 2, "column3 ", Parameters!GroupBy.Value(0) = 3, "column4 ") & Iif(Parameters!GroupBy.Count = 2 OR Parameters!GroupBy.Count = 3 OR Parameters!GroupBy.Count = 4, Switch(Parameters!GroupBy.Value(1) = 1, ", column2 ", Parameters!GroupBy.Value(1) = 2, ", column3 ", Parameters!GroupBy.Value(1) = 3, ", column4 "), " ") & Iif(Parameters!GroupBy.Count = 3 OR Parameters!GroupBy.Count = 4, Switch(Parameters!GroupBy.Value(2) = 2, ", column3 ", Parameters!GroupBy.Value(2) = 3, ", column4 "), " ") & Iif(Parameters!GroupBy.Count = 4, ", column4 ", " ") & ...(other columns that don't depend on the multi-value parameter, and that use aggregate functions, so aren't in the "group by" clause) ...(from and where) ...(group by like switchs in the "select") The idea is that when the user select only one option to group by (and he has to select at least one), I just check the Parameters!GroupBy.Value(0) (obviously!). If he selects 2, 3 or 4, I have to check the Parameters!GroupBy.Value(1), if he selects 3 or 4, I have to check the Parameters!GroupBy.Value(2), and if he selects 4, I check the Parameters!GroupBy.Value(3). When I select 4 of the multi-value parameter options, it works fine, but when I select 2 or 1, it says: "An error occurred during local report processing. An error has occurred during report processing. Cannot set the command text for data set ... Error during processing of the CommandText expression of dataset ..." And I don't have any idea why! Could anybody help me? Thank you!