Microsoft report - how to have two tables with grouping that should shown different records from the same set based on exclusive condition

Question

Maybe the question title is a little confusing, so I'll try to clarify things In a report I receive a dataset with a datatable which contain records to be shown. I cannot change that (it's not under my control).

The records have a field, RetirementDate, which can be filled or null. Based on this, the records must be shown on two distinct tables on the same report.

Both reports have the same grouping (two groups, on Department an Category, and Category is included inside department).

So reports should looks like this

Table 1 - Non retired (RetirementDate is null)
  Department 1
    Categ 1
      Details ....
    ....
    Categ n
      Details ....

  Department 2
    Categ 1
      Details ....
    ....
    Categ n
      Details ....

Table 2 - Retired (RetirementDate is not null)
  Department 1
    Categ 1
      Details ....
    ....
    Categ n
      Details ....

  Department 2
    Categ 1
      Details ....
    ....
    Categ n
      Details ....

What I was tried was to set Filter for table 1 Trim(RetiredDate&"")="" and for table 2 Trim(RetiredDate&"")!=""

But that didn't worked. Table 1 wasn't even shown (even if 90% of records have RetiredDate=null), it just show grand total band, and most records were shown in table 2. Obvious I missunderstand how Filter on table works

I managed to temporary sort of workaround by setting condition above on Hidden prop on details band of each table, and it kinda worked (except it shows me Department Header / categ header for all dept / categs combinations, no matter they have or no details records - which, obvious, is not good).

Can anyone tell how can I accomplish what I needed? I thought Filter on table should work, but obvious it didn't the way I thought of.

I'm using SSRS 2005, and actually the report is used in offline mode, where the dataset is "pushed" in report before opening by client app and report is loaded in web-based viewer. That's why I cannot change what data I get.

Thank you

Jamie F Jamie F · Accepted Answer · 2011-10-19T22:32:14

You have the right approach. Try using the IsNothing function. In 2008, you can apply a filter to a table. I think there is similar functionality in 2005. If it is like 2008, where you can set multiple formulas (versus the one formula in visibility) then I usually set both formulas to return a string and compare those. So the first formula would be

=IIF(IsNothing(Fields!RetiredDate.value), "Show", "Hide")

Then the second would be ="Show" and I would require that they match.

To debug something like this, you can add the formulas (temporarily) as fields in your table. This will let you see if things are evaluating as you'd expect.

Microsoft report - how to have two tables with grouping that should shown different records from the same set based on exclusive condition

2 Answers