Make a Group Section Visible when there is no data for this group

I have a report that groups on departments. The requestor would like all
sections to show whether they have information in them or not - also all
sections need to show if there are is no information, totals, etc.
Thank you,
NormaOn Nov 20, 1:48 pm, NormaD <Nor...@.discussions.microsoft.com> wrote:
> I have a report that groups on departments. The requestor would like all
> sections to show whether they have information in them or not - also all
> sections need to show if there are is no information, totals, etc.
> Thank you,
> Norma
Two ways of doing it from what I see.
1) Do a left join on the Department information with your fact data,
so that a department name is included even if there is no data for it.
Data:
SELECT D.NAME AS DEPARTMENT_NAME, F.AMOUNT
FROM DEPARTMENTS D LEFT JOIN FACT_TABLE F
ON D.DEPARTMENT_KEY = F.DEPARTMENT_KEY
Layout: Drop a Table object, add a Group that groups on the
department name. Put your department header information on that group
row, and all of the facts in the detail row.
The table will still render, but your row counts will be different
because you will be adding a row for each department that has no fact
data.
2) Implement a Subreport that passes the Department info
Data: I would have an outer Report that does a SELECT on all of your
departments by name/id.
Layout: Drop a list box on the report that groups by the Department
name/id. Then in the list, put a subreport, and pass the Department
as a Parameter to the subreport. Put your department header
information here.
Data: In the Subreport, use the @.Department in your WHERE clause to
filter to a single department.
Layout: Drop your fact headers & totals here
Now your row counts in the Subreport will properly show zero rows when
there are no facts for a department.
-- Scott