filter teradata ssrs-2008-r2 reportbuilder report-builder2.0

Filter / Report Builder 2008 R2

I have an unusual situation for a report. I only want to display rows for several accounts (170, 171, 200, 380) if the Cost Center is either A480 or A485. All other accounts should be displayed for all cost centers. If one of the above accounts appears on any other cost center, then it should NOT be displayed in the report.

I thought I may need an expression like “='A480' or 'A485' for the Value field (using IN) but I’m not sure what the Expression field should look like. I'm doing this all within Report Builder.

My report returns data for a number of accounts for a number of cost centers. For example: Account Numbers: 123 / 125 / 130 / 140 / 145 / 170 / 171 / 200 / 380

What I would like the report to do is only display accounts 170 / 171 / 200 / 380 for Cost Centers A480 and A485.

Sample Results:

Cost Center A400: 123 / 125 / 130 / 140 / 145 
Cost Center A440: 123 / 125 / 130 / 140 / 145 
Cost Center A480: 123 / 125 / 130 / 140 / 145 / 170 / 171 / 200 / 380
Cost Center A485: 123 / 125 / 130 / 140 / 145 / 170 / 171 / 200 / 380
Cost Center A500: 123 / 125 / 130 / 140 / 145

Accounts 170 / 171 / 200 / 380 should only be displayed if the Cost Center is A480 or A485. The report has a prompt for Cost Center. Some of the other Cost Centers will have results for accounts 170 / 171 / 200 / 380 but the user doesn't want to see these accounts in the any Cost Center other than A480 or A485.

Thanks for your help........

Solution

For more complex scenarios than a simple filter like the one you describe, you need to implement an expression that covers all cases involving some nested IIFS.

For example:

=IIF(
    Fields!CostCenter.Value = "A480" or Fields!CostCenter.Value = "A485", 
    1, 
    IIF(
        Fields!Account.Value = "170" or Fields!Account.Value = "171" or Fields!Account.Value = "200" or Fields!Account.Value = "300", 
        0, 
        1))

The first IIF covers the special cases of A480 and A485, allowing true for each row with any account number. The second IIF (for all other cost centers) returns a 0 for the excluded account numbers. Now you just have to setup a simple filter on the report (either on the table or the dataset) that only keeps rows that this expression returns 1 for.