Default value reporting services if no data

Question

I have a filter on my report that is a multivalue list for UnitNumber.

The report is set up to show each unit's details on a separate page.

If I select unit #3 and unit #4 and unit #4 doesn't have any data, is it possible to show the data for unit #3 and then on the next page say "Unit #4 has no activity"?

Answer 1

Assuming your existing dataset only holds activity values (so if Unit #4 has no activity, it will not be included in your existing dataset), the answers are:

1. No, not with your existing dataset.

2. Yes, if you amend your existing dataset to include an outer join from a table listing units - like so:

   select u.unit_no, a.unit_no activity_unit, ... [other activity fields]

   from units u left join activities a on u.unit_no = a.unit_no

   where u.unit_no in (@unit_no)

   and include a group heading level expression, dependant on whether activity_unit is nothing, to display your "Unit has no activity" message.

Answer 2

Different way of doing this would also require you to change your datasets, but to have your top level group be in a list page breaking on the group and have each list call a subreport for its unit number, the subreport can ofcourse have the no data available inside it. So you will end up with a page for unit #4 which calls a subreport of no data.

Just a different take on the same idea.