show no subs

Hiding Single Item Subtotals © integra solutions inc of 3 Last Update July 4, 1999

www.islink.com

The information in this document is provided as-is. The information is believed to be correct, but future releases of theBusinessObjects software may render suggestions or strategies provided here useless, inappropriate, or incompatible. Allinformation is copyright © integra solutions, inc, and may not be redistributed in any form or via any media electronic orotherwise without obtaining prior written consent from the author(s).

Hiding Single Item Subtotals

The Problem DefinedOne of the most common analysis features used in BusinessObjects is the Break + Sumcombination. Using a report Break causes data to be broken into different parts, while the Sumfeature then provides a subtotal for each break area as well as a grand total at the bottom of theblock.

In some cases, however, the different data values in a break may only occur one time. Theresulting report shows a single line followed immediately by a subtotal. Since the subtotal line willbe exactly the same as the detailed line, users often ask if that line of subtotal is even necessary.

According to the functionality provided by the Break + Sum combination, the subtotal will bepresent. BusinessObjects does not distinguish between multi-line or single-line items when doinga subtotal. So this document will outline a way to “hide” the subtotal when only a single line ofdetail is present.

First, The ReportThe following report was built with the standard Island Resorts Marketing universe.

Note that for the country France there is only one line of detail, while for the US there are twolines. This data will be used to demonstrate the solution. After adding a Break on the country anda Sub on the Revenue, the report looks like this:

As expected, there is a subtotal for each country, followed by a grand total at the end of thereport. But the subtotal for France is not really required.


www.islink.com


Learning to CountThe first step to hide single item subtotals is to know if there is, in fact, a single item in the break.With the report as structured, there will always be a single country in the break. There may,however, be more than one Resort in each country. So count them using the following variable:

The variable is called ShowFooter since we will use the result of this calculation to determine ifthe footer will be shown or not. The formula:

= If Count(<Resort>) <> 1 Then 1 Else 0

… makes the following decision…

If there is more than one resort, then set a flag to one, otherwise set the flag to zero

By setting up this boolean value, we can choose to display footer information or not, based on thecount of the number of Resorts. Since the footer in the previous example showed both thecountry name and the subtotal value, two additional variables are required.

TotalCountry: If <ShowFooter> = 1 Then <Country>TotalRevenue: If <ShowFooter> = 1 Then Sum(<Revenue>)

In each case the variables make use of the ShowFooter variable to determine if additionalinformation should be displayed or not. Displaying the report with the new calculations in thefooter area results in the following.


www.islink.com


Finishing the JobIn the final report, the ShowFooter variable does not need to be displayed. And if there is nosubtotal information, then the borders around the subtotal cells should not be displayed either.(Note: there is no way to remove the cell, but hiding the border would seem to make the cells goaway.)

Dynamic formatting based on the value of a variable should immediately bring to mind the Alerterfeature. With an appropriate Alerter, and a variable to work with, formatting can be dynamicallyaltered. This feature will even provide a way to remove the cell borders.

In the final example, you can clearly see that there is a space where a subtotal entry could go, butusing the variables outlined in the previous pages it has been removed. The final step is to createan Alerter.

By creating an alerter based on the ShowFooter variable and checking for a zero value (no footerinformation is displayed) then the report can be set up to hide the borders. In this case, the borderwas set to White, as that was the background color. It did not seem to work when the border wassimply turned off. (For full details, examine the Alerter as defined in the downloadable file thataccompanies this document.)

ConclusionWith careful variable construction and an Alerter based on that variable, subtotals for single itemscan disappear! They are not really gone, but the combination of no value displayed on the hiddenborders on the cells makes them appear to have been removed.

show no subs

Documents