kalittaair
asked on
Need to dynamically group by
Experts:
Using SSRS 2008.
I have a dataset containing 2 columns of data, Vendor and State. The same vendor can be in multiple states and states can of course contain multiple vendors. I want to group the report by either vendor or state, depending on which the user selects.
I set up a visible parameter called GrouBy. The valid choices for this parameter are "Vendor" or "State". The default is "Vendor".
I have a 2 column table displaying Vendor and State. I created a parent group by right clicking and add group>parent group. In the expression for the parent group I have the following:
=IIF(Parameters!GroupBy.va lue=”Vendo r”, Fields!Vendor.Value, Fields!State.Value)
When I preview the report I get the error: "The Group expression used in grouping 'Group1' returned a data type that is not valid'
Any idea what I'm doing wrong?
Using SSRS 2008.
I have a dataset containing 2 columns of data, Vendor and State. The same vendor can be in multiple states and states can of course contain multiple vendors. I want to group the report by either vendor or state, depending on which the user selects.
I set up a visible parameter called GrouBy. The valid choices for this parameter are "Vendor" or "State". The default is "Vendor".
I have a 2 column table displaying Vendor and State. I created a parent group by right clicking and add group>parent group. In the expression for the parent group I have the following:
=IIF(Parameters!GroupBy.va
When I preview the report I get the error: "The Group expression used in grouping 'Group1' returned a data type that is not valid'
Any idea what I'm doing wrong?
Should be same DataType of both Vendor and State filed.
ASKER
What should be? Do I somehow set the DataType of the group expression? How? Vendor and State are both Feilds being returned from my stored proc.
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jason - Thanks for the suggestion. I tried it and get the same error.
You want me to post my results from my SP? Basically the list of vendors and locations?
You want me to post my results from my SP? Basically the list of vendors and locations?
just an example, not the actual data to get an idea,and also to try to build my test table.
Thanks
Thanks
ASKER
OK, A good example might be:
Vendor State
Acme Texas
Joe's Florida
Brother's Michigan
New Guys Texas
Acme Ohio
Gordon's Texas
Acme New York
In the example about we have one vendor in multiple states (Acme) and one state with multiple vendors (Texas). I need to be able to let the user chose to group by Vendor or State.
Thanks!
Vendor State
Acme Texas
Joe's Florida
Brother's Michigan
New Guys Texas
Acme Ohio
Gordon's Texas
Acme New York
In the example about we have one vendor in multiple states (Acme) and one state with multiple vendors (Texas). I need to be able to let the user chose to group by Vendor or State.
Thanks!
ASKER
Had a typo! Although I fixed it by corrected my typo, your suggested solution forced me to re-examine everything so I'm accepting this solution. Thanks.