Setting dynamic group, rows or chart category/series 1

[olitvinov]

How to set groupping in matrix dynamically, based on parameter?
In matrix we have a group row, group column and value.
I want for example
set row to Sales!Category if parameter1 = 'Category'
else set to Sales!Subcat
set column to Sales!Year if parameter2 = 'Year'
else set to Sales!Region
Set value (of cross reference) to [Sum(Sales!TotalLine)] if parameter3 = 'TotalLine'
else set to Sum9Sales!Qty)

Once I know how to do it, I would set the same for a chart hopefully.

One of the problems is that I can't find functions like in Crystal Reports. I was able to put functions (result of function) in report in Crystal. Where function would say something like:
if(param3='TotalLine')
=Sum(Sales!TotalLine)

Hope you understanding where I'm going with it.

Thanks,
Oleg

 

[RiverGuy]

You use what are called Expressions in SSRS. If you right-click on a cell, and choose "Expression," an editor will pop up prompting you to enter an expression. For your if/then/else syntax, you would do something like the following:

=IIF(Parameters!SomeParameter.Value = 'Some Value', SUM(Fields!SomeField.Value), SUM(Fields!SomeField2.Value))

Same thing with other editors in SSRS. The group, filter, sort editors all allow you to put in Expressions.

For more advanced logic, you can right-click the little square in the upper left corner of your layout tab. Choose Properties and then Code. You can create Visual Basic functions here to be called in your Expressions.