• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

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.value=”Vendor”, 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?
0
kalittaair
Asked:
kalittaair
  • 4
  • 2
1 Solution
 
Alpesh PatelAssistant ConsultantCommented:
Should be same DataType of both Vendor and State filed.
0
 
kalittaairAuthor Commented:
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.
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,

can't really think of anything you're doing wrong.
try this...i'm not sure it would work but I don't have access to ssrs now, but will test it and work furthr when I go home tonight.


=iif(Parameters!GroupBy.Value =”Vendor”,  Fields(Fields!Vendor.Value).Value, Fields(Fields!State.Value).Value)

Also if you can,  please post your query, or output from the SP.

Thanks
Jason



0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kalittaairAuthor Commented:
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?

0
 
Jason Yousef, MSSr. BI DeveloperCommented:
just an example, not the actual data to get an idea,and also to try to build my test table.

Thanks
0
 
kalittaairAuthor Commented:
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!
0
 
kalittaairAuthor Commented:
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.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now