SYMPTOM:
A user builds a Report Services document with the following specification:
However, when the document is executed, empty space appears between the bottom of the detail header and the first visible row of the detail section. If an additional text field is added to the detail section (here, a static text marker '<<'), it is apparent that rows are being rendered into the detail section output with empty values.
The use of this feature can generate some maintenance difficulties if there is a high number of objects or if this number increases or the complexity of the layout increases. | The use of this feature can generate some maintenance difficulties if there is a high number of objects or if this number increases or the complexity of the layout increases. |
| |
NOTE: In this case, the dataset reports do not share attributes in common. If the data set reports were constructed so that the secondary dataset adds one or more attributes to the attributes in the primary dataset, then the document display would not have empty rows as displayed above. Instead, rows from the detail section would be repeated.
CAUSE:
This behavior is the result of the compound join algorithm used in Report Services documents to join multiple dataset reports.
Every Report Services document with multiple dataset reports first joins all of the dataset results into one master table, called the "virtual dataset" or "master dataset." The master dataset includes the union of all the attributes in the dataset reports, as well as one column for each metric in the document. This join takes place even in dashboard-style documents, in which the individual datasets are used only as distinct grid or graph objects.
In the compound join, it is possible for some rows to have empty values. For additional details on compound join behavior, consult the following Strategy Knowledge Base technical note:
KB15813 (KB5700-75x-2484): Best practices for joining multiple datasets in a Report Services document in Strategy 9.x
During the formatting phase, three scenarios should be considered:
In the above document, the master dataset has 12 rows -- one for every month. Four of those rows include category data; the other eight rows have a null value for Category and the Revenue metric used in that dataset. Because objects from the month-level dataset are not used in the detail section, the rows from the master dataset with no Category data display as empty rows.
ACTION:
Two approaches may be used to resolve this issue.
1. Allow the detail section to shrink.
If the "Height can shrink" property is set to True, then only one blank row will be displayed, no matter how many blank rows actually exist in the dataset. In the following document, observe that the empty space between the line marking the bottom of the detail header and the first data row is much smaller than in the initial document output.
| |
| |
2. Move the attribute(s) in the detail section to the grouping axis.
As noted above, grouping attributes disregard empty rows when calculating what document sections will be needed. Thus, in the example document, if Category is made a grouping attribute, there will be only four Category sections, one for each attribute element. For each of the Category sections, the detail section will have only one row; if the Category header and footer are empty, then the rows will display sequentially as desired.
In this example, objects that were in the detail header and footer have been moved to the document header and footer so that they will display only once.
Observe in this result that there is no space between the bottom of the header section and the Category rows. Thus, this approach should be used if the single empty row in solution #1 is not acceptable.
Original document design
Revised document design, grouping by Category
Revised document output
