Need to dynamically group by

Posted on 2011-04-21
Last Modified: 2012-05-11

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?
Question by:kalittaair
    LVL 21

    Expert Comment

    by:Alpesh Patel
    Should be same DataType of both Vendor and State filed.

    Author Comment

    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.

    LVL 21

    Accepted Solution


    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.



    Author Comment

    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?

    LVL 21

    Expert Comment

    just an example, not the actual data to get an idea,and also to try to build my test table.


    Author Comment

    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.


    Author Closing Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now