Conditional Grouping

[azzazzello]

Greetings,

I have a report that I produce with columns

Date, A, B, C, D, Total, Revenue

I have 3 user - inputed parameters

start_time,
end_time,
groupBy

start_time and date_time are used in the query. GroupBy I intend to use to perform a conditional grouping. It is a multivalue field, which contains select options A,B,and C, which correspond to my columns. My intent is to group data by selected options (or by none, if none is selected).

The way i wanted to do this is to make a group on the detail row, make Total and Revenue be =sum(Total.value) and = sum(Revenue.Value) respectively, and have the following Expression in the Group On section for EACH of the 3 grouping options (A,B,C):

=Iif(join(Parameters!groupBy.Label,",").Contains("A"),Fields!A.value,"")

The problem I am getting, as I try to get just that one conditional grouping to work, is that when I do NOT select A, the darn thing spits out just one row (it works just fine when I actually select A). I don't know what I should put in the false condition to stop the Grouping from taking place. What do I do? Also, if one could suggest a better way to figure out if a certain Label (or Value) has been selected (exists/contains functions to examine array itself?), I would be greatful

 

[xlbo]

In terms of a FALSe condition, have you tried:

Iif(join(Parameters!groupBy.Label,",").Contains("A"),Fields!A.value,system.vbnull)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[azzazzello]

It's telling me vbnull is not part of system. I've tried System.dbnull.value, but that did not work. That damn thing is just grouping by that null, and since it's constant for all rows, I am left with a single row after the group. I need a way to somehow negate the Group On on the false condition.

 

[azzazzello]

Ultimately, what I really want is a dynamically determined group by. I need to give the user an option by which columns to group, and display data accordingly. That's all. Perhaps there is a better approach to accomplish this then what I am trying to do. I would love to hear it.

 

[rfisch]

Try this link...

http://blogs.msdn.com/chrishays/archive/2004/07/15/DynamicGrouping.aspx

Rob