I have report with 4 Organizations (Co A, Co B, Co C, and Co D) and I grouped them by Organization Code. When exporting to Excel format, each Organization  has its individual tab with list of employees. and total cost for each Organization - Co A Total Cost, Co B Total Cost, Co C Total Cost, and Co D Total Cost, respectively. The report is working fine until one day, one of those organizations (Co D) has no data (this is correct for not having data because I verified). So now I'm showing 3 tabs (A, B, and C) instead of 4. This is confusing to the manager because it made them question that if I missed including Co D to the report. Is there a way that I can still show the tab that has no total with specified text? I tried the 'Content specified in the No tab data' option under No data contents properties, and it's not working. Still showing 3 tabs instead of 4. I greatly appreciate for assistance.

------------------------------
Martie Lysas
------------------------------

Hi Martie,

do you have any zero suppression activated on your data container? Is there a filter in the query that will remove all organization units with no data? 

And also: habe you checked that property on the data container as well?

------------------------------
Robert Dostal
Team Leader BI
GEMÜ Gebr. Müller Apparatebau GmbH & Co. KG
Ingelfingen
------------------------------

The answer to this question depends on how the underlying data is modelled and how your report is created.

You are not seeing a tab for Co D as the query that drives the pages of your report is not returning any rows for Co D.

There are numerous options available to you...

One that some would suggest is changing the data model to an outer join. But as we don't know how your data is modelled this is a subject that could go across pages

of discussion.

There are numerous ways to achieve what you want in a report. However, all require a bit of an understanding of how reports work beyond the basic drag and drop. And the appropriate technique also depends on how the report you have has been created.

I'll give you one solution that will work for a very basic list report...

If you have created a page break using a basic list you probably have the page set and list based on the same query. If you look at the master detail relationship of the list it will be sectioned.

As this single query is no longer returning a row for Co D, you won't get a page for Co D.

The solution is to write a new query that always returns a row for every page you want, regardless of if there is any fact data for that page. For example just create a query that has your Co's in it and nothing else. Test the query to make sure it contains a row for every page you want.

You then need to create your Page Set around this new query.

If you look at the properties of the page set:

Ensure the query it uses is your new query.

You will now get a page for every Co.

As the page and the list are now based around different queries you will need to update the master detail relationship on the list. You need to filter the list based on the page. Something like:  

On the list, ensure the render page when empty is still set to on:

This ensures you still get a page when the list is empty, which it will be as the list query contains no data for Co D.

And that the list has something for the no data contents:

As I said, there are numerous ways of achieving your requirement. This is just one way. Determining the best way could only come with an understanding of the data and the report.

------------------------------
Marc Reed
------------------------------