Greetings, fellow data analysts!
As you well know, you can use Business Intelligence applications to view and analyze numbers in great detail. These numbers tell you who, what, when, where, and how much – but never why. This answer requires language – spoken or written – right alongside your data. That is why DeltaMaster offers rich functionality for adding comments. Users can enter comments in each report and, in the case of pivot tables, even on individual values. The following pages will provide an in-depth look at cell comments. One lesser known feature, for example, is that you can aggregate comments on subordinate levels as a summary. Instead of searching for comments, cell by cell, in a granular cost center budget or complex material master, DeltaMaster automatically pulls them together in a variety of ways. In this edition of clicks!, we will show how you can use cell comments to enhance your planning and reporting applications.
Best regards,
Your Bissantz & Company team
In planning, simply entering and submitting numbers often does not suffice. The stories behind these numbers are much more interesting – the underlying conditions and reasons, developments and expectations, consequences and demands. That’s why comments are so important: You can enter and evaluate them along with the respective values. In DeltaMaster, you can add comments for every single value in a pivot table and save them centrally to share with other users. DeltaMaster also offers the option to aggregate cell comments across multiple levels. What’s more, you can even use cell comments beyond planning, for example, to enhance your reporting applications as well. On the following pages, we will show how you can work with cell comments, present various options for aggregating comments, and explain what preparations need to be made ahead of time.
Showing cell comments
If a cell in a pivot table contains a comment, DeltaMaster will designate it with a small red triangle in the upper-right corner of the table cell – a similar look and feel as Microsoft Excel. If you mouse over this symbol, DeltaMaster will display the cell comment as a tooltip. When you export pivot tables to Excel or HTML emails, the comments are included. They are not displayed in other export formats (e.g. Word, PowerPoint, PDF) because they lack suitable possibilities to add comments at the present time. If desired, you can copy cell comments to report comments (context menu of the pivot table or I want to menu) to include them in the export.
Alternatively, DeltaMaster can also display the text directly in the cells of the pivot table. This option is intended for special applications that rely heavily on commentary. This option then displays the text comments in the entire table instead of the commented values. If necessary, you can hide cell comments column by column, using the Column Properties in the context menu (see DeltaMaster deltas! 5.5.7, feature #14).
If you want to display both comments and values, create a copy of the measure and add it to the table next to the actual one.
You can define if DeltaMaster should show flags only or show text in cells in the Table Properties on the Cell Comments tab.
Writing a cell comment
To add a new Cell Comment or edit an existing one, open the respective function in the context menu of the cells. This function is supported in Viewer, Pivotizer, Analyzer, and Miner modes as well as using the WebClient. A license for planning functionality is required. If you do not see this entry in the context menu, you will have to first prep the application for data entry. We will explain what this entails later on.
The upper part of the editing dialog box shows the cell that the comment is referencing.
If you have activated an aggregated display for comments and the subordinate levels contain comments, DeltaMaster will display them in the lower section of the dialog box. Although you cannot edit the other comments here, you can copy them to a clipboard.
If you wish, DeltaMaster can automatically enter the Cell address and/or the respective Value in the input field as Default values for new Cell Comments (see screenshot above). You can regulate this behavior in the Options (Extras menu) on the Cell Comments tab. The Cell address at cockpit transfers the captions of the members that lie on the axes of the pivot table. The Cell address at cube includes all dimensions including the selected members in the View.
To delete a comment, remove the text in the input field. This will delete the comment text in the database. The database entry will remain with an empty text field.
DeltaMaster saves all comments to a central database so that authorized users can immediately access the same comments. In doing so, it includes their exact “coordinates”, meaning the measure and dimension members that the comment is referencing (i.e. based on the Axis Definition, View, and Cell Comment Context as explained below).
Aggregating cell comments
One clever function in DeltaMaster is the aggregation of texts. If someone has entered comments for a child member deeper in the hierarchy, users can automatically see it on higher levels as well. That means you can review all comments on the highest level without having to check all of the hierarchy’s branches. This also ensures that you do not oversee comments when the respective members are hidden, for example, due to filter criteria or because the lowest hierarchy levels are collapsed.
You can set the Aggregation mode under Options (Extras menu) on the Cell Comments tab.
We will use an example from our reference application “Chair” to clarify these modes. We have created cell comments for revenues on various levels of the customer dimension – specifically for the members “All Customers”, “South”, “Tunbridge Wells”, and “Eurotunnel Corp.”. The area “South 1” contains no comments. The following screenshot shows a pivot table configured to display the texts directly in the cells. This way, you can view all comments that are available for a cell at a glance.
- Not aggregated displays comments (or the red triangles that reference them) where they were written.
The screenshot on your right shows this starting scenario. Four cells contain comments while one does not.
- Aggregated displays all comments of all child members in the parent member.
In other words, it displays the comments for that particular member as well as all subordinate members in the hierarchy. This also includes members that are not currently displayed in the table. If comments are available for other customers in the area “South 1”, DeltaMaster would display them here – and in all higher levels as well.
- If you decide to aggregate up to a certain level, DeltaMaster will display another field to the right of the drop-down list. Here you enter on how many levels to the top DeltaMaster should display the comment. This means that the comments are only passed on to a certain number of levels and not to the highest level. The value is a moving gap, meaning “x” levels to the top and not the “x-th” level from the top.
This option is very useful when you are working for example with complex cost center budgets or granular sales structures. It accounts for the fact that the relevance of individual postings decreases as the overall context grows, while the number of individual postings and comments increases. A comment on an individual cost area may be relevant if you are observing cost areas, cost centers or cost center groups, but not business divisions, companies, or the entire group. The cost center, in turn, is relevant for the cost center group and business division, possibly even the entire company, but not the group.
The example on your right is aggregated to 2 dimension levels, which is also the default setting. DeltaMaster will now display the comment for “Eurotunnel Corp.” on the two next higher levels but not for the region “South” and the top member “All customers”. The same applies to the comment for “Tunbridge Wells”, which you can see for “South 1” and “South” but not any higher.
- If you want to aggregate conditionally, DeltaMaster will only display the comments from lower levels when no comment is available for that particular cell itself.
This is the case with “South 1” in the example on your right. Since no comments are available for this member, DeltaMaster lists the comments for its child members. All other members have their own comments, which are displayed accordingly.
The aggregation mode applies to all pivot tables and FlexReports in the application.
Prep work
Before you can work with cell comments, you must first complete the following steps:
- Ensure that the application is connected to the relational model (Model menu).
- Create tables for cell comments (or ask an IT expert for assistance). See the following section for more information.
- Activate planning functions (Extras menu, Options, Data Entry tab).
- Activate the cell comments in the application (Extras menu, Options, Cell Comments tab).
These steps also apply to pure reporting applications if you want to allow users to enter comments on specific values (and not values themselves). The settings on the Data Input tab of both the Report Properties and Table Properties differentiate it from a planning application. These settings define if and where you can enter values. Cell comments are independent from these settings.
Configuring a database for cell comments
Although creating tables for cell comments is not very difficult, it is usually a job for a database administrator because it requires special permissions. In DeltaMaster, Miner mode is required.
Connecting to the relational model (Model menu) is necessary so that you can administer the cell comments. It is also a prerequisite for displaying the two control elements to configure cell comments (i.e. the link in the Model Browser on the System tab and the Cell Comments Tab in the Options)
In the Model Browser, the System tab contains a link to create/update tables for cell comments. This link opens a dialog box where you can select the fact tables to create or update cell comment tables. If desired, you can also create additional fields so that DeltaMaster can save when a comment was saved by which user (see DeltaMaster deltas! 5.6.0, feature #17). For technical reasons, you will also have to enter the field length. The default value is 1,000 characters, which is slightly more than the length of the past two paragraphs.
If you close the configuration with OK, DeltaMaster will immediately run the SQL commands on the respective database. This is usually the relational model of the application (unless you have specified another one). This means that DeltaMaster saves comment tables and fact tables in the same database. If you want to save the comments in another database, you will have to specify this in the Options on the Cell Comments tab (see below). Instead of running the SQL commands immediately, you can also generate them as script so that you can modify them individually or run them in another database. DeltaMaster either copies the Script to the clipboard or saves it as a file.
You can use the wizard to create new or update existing comment tables. The updating function is additive, which means that the generated scripts do not delete anything (e.g. tables, rows, or columns). If you no longer need certain fields after you make changes to the model, an administrator should delete them manually or rebuild the table from scratch using only the necessary fields.
A special note for database administrators: Each selected fact table uses one comment table with the name “<fact table>_TEXT”. If DeltaMaster cannot find the respective table in the relational model, it will create the table by generating the CREATE command as a Script; otherwise it will provide instructtions to add columns conditionally. The field length is only relevant when creating a table or new columns and has no effect on existing columns. In order to enter cell comments, users will require write permissions for the cell comment table (UPDATE and INSERT).
DeltaMaster takes the Cell comment context (or Dimension context for cell comment tables) into account when you create or update comment tables. You can edit this context in the Options of the Cell Comments tab. It regulates which dimensions and hierarchies DeltaMaster should include for administrating cell comments. Please read DeltaMaster deltas! 5.6.4, feature #5 for more information.
If you want to enter cell comments for user-defined measures, please note that DeltaMaster does not include any fields for them when generating data structures – and for good reason: Applications tend to accumulate a large number of user-defined measures – for example, filter values that are created by dragging and dropping values in an analytic method. It wouldn’t make sense, to automatically generate comment fields for them. If you want to save comments to a specific user-defined measure, you will have to create a column manually in the comment table. Afterwards, you enter this column in the Measure Properties on the System tab as a Comment Column.
Calculated members, such as “?Budget” also require additional prep work. In the Editor for calculated members, you will need to enter a Comment Key, which is the name or the ID that DeltaMaster uses to access the cell comment table. If the members of the dimension are numerical, enter a unique number (e.g. -1) for the Comment Key. Otherwise, please enter text.
If the tables are saved on the same server in a different Database, please enter its name in the Options on the Cell Comments tab.
Once you have set up the database in this manner, select the option to get cell comments from relational database to activate the full comment functionality.
Questions? Comments?
Just contact your Bissantz team for more information.
