Group to appear Even if Null

[Memento]

Goal:
I need a calculation to occur even if no data is returned. I'd also like the group by deapartment to appear even if no data was returned.

Problem:
I have an overhead report that does hard-coded calculations for several departments. Sometimes, a department will not have any activity for a given month. I still need the calculation to occur even if there is no activity for that month. There's really only two or three administrative departments where this will occur. Since they have no activity, the group by department will not appear.

Ideas:
Create a table/view with all the departments, and group on departments. That way, all the departments will show even if no data is returned.

The user said he only wants the two or three departments to show up if no data is present. The rest should not show up. Any ideas?

 

[MrBillSC]

You could track the 3 departments in formulas and then if any of the departments did not have any records, they could be reported in the report footer.
Example:

//Formula for Dept1
If {table.deptName} = "WhatEverDeptName1" then 1 else 0

//Formula for Dept2
If {table.deptName} = "WhatEverDeptName2" then 1 else 0

//Formula for Dept3
If {table.deptName} = "WhatEverDeptName3" then 1 else 0

Then create 3 subsections in the report footer (one for each of the 3 departments) with static information indicating that they had no data. Conditionally suppress each subsection as follows:

// Suppress report footer a if Sum of @Dept1 > 1
Sum({Dept1}) > 0

Suppress sections b and c in similar way. If you do this you won't need a table of dept names.

If you want to use the view or table of dept names, you would use a left outer join from the dept view to the calculation data. Then conditionally suppress the dept group header, detail, and footer sections if the detail table values were Null and NOT one of the 3 depts you always want to report.

MrBill