SSRS parameter filtering out union part of the query

Question

In my SSRS Report, I have a column named 'Producer Name' and a parameter field call 'ProducerName'. It's currently returning all my producer name rows but in my sql query I union another row call 'Producer Name' + 'Totals'. My SSRS report won't return any of the producer name that has 'total' added on. How do I go about fixing this?

What its currently returning:

Producer Name       Count
    Prod A            15
    Prod A             6
    Prod A             2
    Prod B             4

What it's suppose to return:

    Producer Name         count
        Prod A             15
        Prod A              6
        Prod A              2
        Prod A Totals      23
        Prod B              4
        Prod B Totals       4

I'm thinking it's because my ProducerName parameter won't recognize the word "totals" that I added onto the rows when I union the two statement. any input would be appreciated.

Ian Preston Ian Preston · Accepted Answer · 2013-03-21T16:18:05

A better solution would be to remove the UNION from the query entirely and just handle the Producer Name grouping and group totals at the report level.

What you're describing is basic SSRS functionality, so as you may as well leverage the functionality provided by the tool.

SSRS parameter filtering out union part of the query

1 Answers