Greetings, fellow data analysts!
When you are striving to standardize reporting, you will have to address the topic of terminology sooner or later. In other words, things with the same meaning should have the same name. Technical requirements also play a role. Very long labels and terms, for example, consume unnecessary space in reports. In this case, it sometimes helps to use alternative short forms. The sports sections of German newspapers do this regularly. When the day’s soccer matches are in the spotlight, the editors are more generous with space and write out longer names such as “Mönchengladbach” or “Kaiserslautern”. Yet when the standings table is pushed to the sidelines, they usually shorten them to “M’Gladbach” or “K’lautern”. But that is better than using a smaller type size or leaving out data. DeltaMaster offers many sophisticated methods to edit labels and correct default terms from the database so that the report, the person who made it, and the readers don’t suffer. What’s more, it packs that all in a uniform environment.
Your Bissantz & Company team
The labels of rows and columns often determine how dense a table appears. The default setting of DeltaMaster allots each table column the width that it needs to completely display its longest text. As a result, one single long description is all it takes to make a column very wide. Sometimes, that is annoying because the column consumes a large amount of space that you would rather use for additional information. The eyes of readers have to bridge the large gap that emerges between the shorter labels and their respective numbers. Sometimes the table becomes so wide that it no longer fits on the screen without a scrollbar. Many times, an overly precise name doesn’t even make the situation any clearer. In financial controlling, we are satisfied when we can find “land” in a report. The fact that this expands to “land, land rights, and buildings including those on third-party land” in the annual financial statement is irrelevant for most operational and strategic decisions. So what should you do when the names of dimension members, measures, etc. are too long? Or what should you do when the terms that your source systems provide are not suitable for making appealing reports?
DeltaMaster supports many different methods to work around awkward terms. We will focus here on the methods that affect an entire analysis session or application as well as special functions for pivot tables, the most important format for efficient reporting.
Aliases for dimension members
The easiest solution is when short names already exist in the (OLAP) database. Keeping the data neat and clean is worth the effort since different applications and/or report editors will probably use these objects time and time again as part of a self-service BI solution. Some source systems allow you to administer short and long terms simultaneously. In this case, they can be used as member properties in the OLAP database. If your source system does not recognize short terms, you can add them during the data transfer process, for example, with the help of a mapping table. Contact your account manager for more information.
You can then access these member properties as aliases in the Dimension Browser. If you select an Alias from the browser, DeltaMaster will display the respective member property values as the name in the entire analysis session or application. This is the same approach you would use to support multilanguage applications. You can, however, also use it if you have administered short and long terms alongside each other respectively.
Alias sets for measures and other objects
The method described above, however, does not work for measures because you cannot add additional properties for them in most databases. This is also the case in Microsoft SQL Server Analysis Services. As a result, you cannot maintain different terms – either as short and long names or in different languages – for measures in the database. In DeltaMaster, however, you can enter and administer the alternative names in alias sets (please read DeltaMaster clicks! 04/2007 for more information). The names of dimensions, hierarchies, and levels are also part of alias sets. These names are often displayed in reports and can get in the way of a concise presentation. Dimension members are not part of alias sets because you can use aliases in member properties as described above.
You can define which alias set DeltaMaster should use in the analysis session or application in the Options (Extras menu) on the Alias Sets tab.
Editing dimension members, hierarchies, levels, and measures
No matter which terms are used in the database, you can also edit names in DeltaMaster. The following options are valid for the entire analysis session or application.
- From the context menu in the Dimension Browser, select Edit Member and enter an Alias Name.
DeltaMaster will display the optional Description as a tooltip in pivot tables. You can use this field to provide a detailed description that users can access on demand.
- In the Dimension Browser, you can also rename levels from their context menu or hierarchies from the I want to menu.
- You can also shorten measure names by renaming them in the Measure Browser (Model menu in Pivotizer, Analyzer, and Miner modes). DeltaMaster will display the Description as a tooltip in pivot tables.
These editing options are designed for individual corrections that apply to the entire application or session. If you need to rename many options, you should use the database.
Editing labels in pivot tables
Cumbersome names stick out the most in pivot tables. If you design it well, you can place ten or fifteen columns of values next to each other in a data-dense table that is clear for your readers to understand. When you add the labels of the rows, however, there is little room for overly descriptive terms like the ones we described for the balance sheet account for land. Due to the importance of pivot tables, DeltaMaster offers a series of functions specifically for designing this report type. Although the methods described above also apply to pivot tables, you do need to fine tune them a bit to optimize a certain report. This is why you can also edit the label for individual reports as well.
There are generally two different ways you can do this:
- Shorten by renaming: You define shorter terms than the ones that are defined in the database so that the formatting runs automatically.
- Shorten through formatting: You specify how much room you wish to allot for each name, and DeltaMaster adapts the text by truncating or wrapping it.
Individual names in the Axis Definition
In the Axis Definition of the pivot table, you can “overwrite” the terms from the database for the current report – provided that it refers to a Member or Measure Selection or a combination that is defined with the Row or Column Editor. In this case, you can edit the members by hitting the F2 key (or by double clicking on a member selection). For more information, please read DeltaMaster deltas! 5.5.5, features #12 and #13.
Member property as a caption
You can also use aliases from member properties in individual reports (i.e. instead of the entire analysis session or application). Define that DeltaMaster should display the desired property as a caption in the Axis Definition on the Member Properties tab (see DeltaMaster deltas! 5.5.6, feature #11).
Changing the output terms is a good idea when you know how the table or large parts of it will be set up. That is the case in many standard reports such P&L or gross margin calculations as KPI views. Here, the row members (i.e. the KPI view), column members (e.g. cumulations, budget-actual variances, or variances to the previous year), and the nesting with dimensions such as customers, business units, material groups, etc. are all known. In these cases, you can change individual labels.
This approach does not work so well with other tables, for example, customer or material lists which report consumers can filter. In cases like these, it is difficult to say if overly long names can lead to design problems, let alone which names they might be. Nevertheless, you can still shorten through formatting.
Column width in table properties
One easy way to save space in pivot tables is to limit the column width. In the Table Properties on the General tab, you can define the Column Width for Data Columns and as well as the one for Fixed Columns.
- Data columns contain numerical values. The User Defined width (in pixels) is valid for all data columns. It is a minimum value, which means that each column will be at least as wide as the data requires it to be. In other words, DeltaMaster will neither cut off cell elements such as numbers and sparklines nor break the content of data cells. The headlines of data columns react differently: DeltaMaster will automatically break them so that a long heading does not create a wider column.
- Fixed columns are located on the left corner of the table and display the names of row members (e.g. accounts, customers, materials) or measures. The User Defined Width (in pixels) is valid for all fixed columns. It is a maximum value, which means that each column will be no wider than the defined width. If all of the names are shorter, DeltaMaster will automatically adjust the column width respectively to the smallest possible size. If the names are longer, DeltaMaster will automatically cut them or create line breaks if the respective option is activated.
Truncating and wrapping names are compromises: the name requires additional space which will stretch the table vertically. If DeltaMaster cuts off the name, you could lose important parts of it. You can live with these compromises, if the table is not known in advance.
Creating breaks with two spaces
You can define where you wish to break a column heading by entering two spaces in the name of the measure or dimension member. DeltaMaster will then break the text, if it is shown in a column heading. If the text is displayed as a row header, two spaces will not break the text.
You can save valuable space horizontally if you rotate the name 90 degrees and Label columns or rows upright. You can activate these settings in the Table Properties. Here you can also choose how many hierarchies you wish to label upright so that you do not need to rotate all of them.
- DeltaMaster counts columns from the inside out. Rotating the text will make the cells with labels higher, but the names won’t be broken or cut. For more information, please read DeltaMaster deltas! 5.5.9, feature #12.
- DeltaMaster counts rows from the outside in. The row height remains untouched and the labels will be cut if necessary. For more information, please read DeltaMaster deltas! 5.5.3, feature #18.
Since vertical labels are not as easy to read as horizontal ones, you will need to weigh this disadvantage and the advantages of the gained space accordingly.
In this example from our reference application “Chair”, the vertical labeling of the outermost hierarchy works: This label literally ties together the measures that are displayed inside and displays the context well. These labels also contain terms that are common in the respective application so that your readers can quickly recognize them as a whole.
The screenshot on your right shows another case where vertical labels do not hinder readability. The labels for the months are short so that your readers can understand them quickly.
The following section sums up the tips described above as a checklist.
a) Adjusting names in the database: Maintain (additional) labels in the master data
b) Adjusting names for the entire application or analysis session:
- Select aliases for dimension members (Dimension Browser)
- Create alias sets for measures and other objects
- Edit and rename dimension members, hierarchies, levels, and measures (Dimension Browser, Measure Browser)
c) Adjusting names in pivot tables
- Enter individual labels in the Axis Definition (Member Selection, Row, or Column Editor)
- Display aliases from member properties as a label (Axis Definition)
- Specify user-defined column widths (Table Properties)
- Add column breaks in column headings (two spaces in the names of dimension members or measures)
- Create upright labels (Table Properties)
You can use these methods to ensure well-labeled reports in easily automatable report formats; namely pivot tables and analytic methods. You don’t need a FlexReport to do it.
Just contact your Bissantz team for more information.