CODEWITHSUNDEEP
t-sqlssrs-2008ssrs-grouping
dp3
dp3

Reputation: 1677

Conditional Group in SSRS Tablix or DB View

I've created a report that is supposed to use a tablix to group data exactly as seen below:

Region  |  State  |  Customer  |  CustomerKey | Status
North   | NY      |  Bob       |  111         | VIP
        |         |  Mary      |  112         | VIP
        | MA      |  Bob       |  111         | Regular
        |         |  Tim       |  113         | Regular
East    | MD      |  Greg      |  114         | VIP
        | VA      |  Bob       |  111         | VIP
West    | CA      |  Greg      |  114         | Regular
        |         |  Mary      |  112         | VIP
        |         |  Sean      |  115         | Regular
        | WA      |  Sean      |  115         | VIP

This is relatively easy because I am using a view with all the appropriate fields and relations to make grouping in the tablix simple. I only run into a problem with my VIP Customers. In the view, customers who have VIP status also have duplicate records of them with Regular status. For example, the data is stored as follows:

Region  |  State  |  Customer  |  CustomerKey | Status
North   | NY      |  Bob       |  111         | VIP
North   | NY      |  Bob       |  111         | Regular
North   | NY      |  Mary      |  112         | VIP
North   | NY      |  Mary      |  112         | Regular
North   | MA      |  Bob       |  111         | Regular
North   | MA      |  Tim       |  113         | Regular
East    | MD      |  Greg      |  114         | VIP
East    | MD      |  Greg      |  114         | Regular
East    | VA      |  Bob       |  111         | VIP
East    | VA      |  Bob       |  111         | Regular
West    | CA      |  Greg      |  114         | Regular
West    | CA      |  Mary      |  112         | VIP
West    | CA      |  Mary      |  112         | Regular
West    | CA      |  Sean      |  115         | Regular
West    | WA      |  Sean      |  115         | VIP
West    | WA      |  Sean      |  115         | Regular

My SSRS report is therefore displaying the data as follows:

Region  |  State  |  Customer  |  CustomerKey | Status
North   | NY      |  Bob       |  111         | VIP
        |         |            |              | Regular
        |         |  Mary      |  112         | VIP
        |         |            |              | Regular
        | MA      |  Bob       |  111         | Regular
        |         |  Tim       |  113         | Regular
East    | MD      |  Greg      |  114         | VIP
        |         |            |              | Regular
        | VA      |  Bob       |  111         | VIP
        |         |            |              | Regular
West    | CA      |  Greg      |  114         | Regular
        |         |  Mary      |  112         | VIP
        |         |            |              | Regular
        |         |  Sean      |  115         | Regular
        | WA      |  Sean      |  115         | VIP
        |         |            |              | Regular

If a user has a Status of VIP, I don't care about their record with their Status of Regular (for that particular Region/State). Is there anyway I can hide these conditionally? Thanks in advance

Upvotes: 1

Views: 646

Answers (1)

Ian Preston
Ian Preston

Reputation: 39606

Personally I'd look at solving this at a database query level in the first instance.

That said, one way at the report level would be to set up Groups based on Region, State and Customer, then in the Status column you could have an expression like:

=IIf(CountRows("CustomerGroup") = 2, "VIP", "Regular")

It's a bit clunky, but should work as it seems each customer can either have one or two rows at the Region/State level - if two they must be VIP, and since you've set a a group you're getting distinct values for Customers within each Region/State combo.

Upvotes: 1

Related Questions