Highlighting changes automatically in updated reports

Greetings, fellow data analysts!

You can often make processes much more efficient by focusing on changes. Loading a data warehouse, for example, is much faster when you concentrate on new and modified records (i.e. delta load). Video films are compressed by first saving a few reference images – instead of them all in their entirety – and then calculating the smaller difference images. To discuss legislative changes more quickly, lawyers and poli­ticians compare the old and new versions and highlight the modified passages. As a general rule, editors of articles, reports, and many other written compositions also mark their changes or track them using a word processing program.

DeltaMaster supports a type of change logic as well: You can use ReportServer to automatically update and distribute reports as well as highlight any changes. That makes it easier for the reader without making it harder for the editor. Sometimes boosting efficiency can be that simple.

Best regards,
Your Bissantz & Company team

There are many different business scenarios where you need to update and extend lists or evaluations on a regular basis. If your sales team is inviting clients and prospects to an in-house exhibition, for example, the sales director will want to have daily updates on the registrations. The registration list will grow larger from day to day. To ensure that the sales director does not have to review it each time from start to finish, you should highlight the new registrations – not to mention, the accomplishments of the hardworking inside sales team.

The screenshot on your right shows an example of this type of report. This email, opened in Microsoft Outlook, is based on a DeltaMaster pivot table that ReportServer sent as an HTML email. The report shows two things: the current status in its entirety as well as what has chan­ged. That is important because changes usually require more attention than things that have stayed the same (and have already received due attention at an earlier time). Best of all, you can set up these applications quickly with DeltaMaster ReportServer as we will explain below. As example, we stick to the event registrations. The steps below, however, are a general description and apply to many different usage scenarios, such as customer service inquiries (i.e. support cases), manufacturing orders, or entries from a CRM system. You can even highlight changes in reports where only the values change and the number of rows and columns remain constant.

Exception Reporting: Changes as exceptions

The key to recognizing changes is so-called Exception Reporting. With this option, you can influence the behavior of Report Server. For each report, you can define a condition that ReportServer evaluates when processing the job. Only if the condition is fulfilled, ReportServer will include the report in the job output. If all reports of a report source (i.e. analysis session or application) are filtered out through conditions, ReportServer will end its work without outputting anything. In other words, it generates no files and sends no emails. It only generates files and sends emails if the defined condition has been fulfilled for at least one report (or at least one report is not linked to a condition). You can use ReportServer in this manner to send event-driven messages. For more information about this function, please read DeltaMaster clicks! 11/2008.

Conditions

You can configure Exception Reporting in Pivotizer mode and higher in the Report Properties (context menu of the reports in the Briefing Book) on the respective tab. Here you opt to include the report in the result of a ReportServer job only, if a condition has been fulfilled.

The condition is defined as one of three things:

• The number of rows in the report: The most important scenario where you would use this condition is to eliminate empty reports. You should only include a report if it has “more than 0 rows”. This option, for example, is used to create detailed lists about variances: If there are no variances, ReportServer should not generate the entire report. This option is also useful if you are preparing personalized briefing books: ReportServer will hold back reports that are filtered by recipients if they are empty following the filter. Instead of receiving an empty report, the recipient receives nothing at all. This option is designed for pivot tables and primarily tabular report types (e.g. SQL Drill Through, Ranking, or PowerSearch).
• An MDX statement: If the MDX expression returns “true”, ReportServer will include the report in the job result. With MDX, you can include criteria that are not reflected in the report’s length or cannot even be taken directly from the report. You can apply the MDX expression to all report types. This is the only option for combination cockpits or other cases where you cannot define a row count as a general rule.
• A comparison whether the current results contain changes compared with the last run: This option is available for all types of reports. Depending on the output format, you can highlight the changed table cells in pivot tables and reports from an SQL Drill-Through.

For the task at hand in our example, the third option is the right one: ReportServer should check if something has changed in the registration list and highlight the changes if necessary. You only need to activate the option in the Report Properties. No changes are necessary in ReportServer.

Updating the report source

How does ReportServer know if something has changed? It calculates the report with “fresh” data and compares the result of this calculation with the one saved in the analysis session (DAS file). If the results are identical, nothing has obviously changed and it will not include the report in the job output. If there are no reports to be included in the job output, ReportServer will not output anything as described above. There is no need to send a message when there is nothing new to report.

Provided that the change recognition is activated for at least one report, ReportServer will recalculate the analysis session selected as the Report Source for each job cycle and save it to the same DAS file. This differs from the normal behavior of ReportServer, which generally does not change the report source. When it comes to changes, however, it will update the report source with each cycle. The DAS file, therefore, must not have write protection. Applications in Repository and DM2GO files are currently not supported, the change management feature is currently reserved for DAS files only.

Formatting

If ReportServer has recognized changes and these changes are to be highlighted, DeltaMaster will color the background of the respective cells yellow depending on the output format. This formatting is supported in Microsoft Office formats (Word, Excel, PowerPoint), PDFs as well as HTML files and mails. In SQL Drill-Through reports, you can output the report content as text (instead of embedding the report as an image in the HTML document). In this case you can use CSS to modify the formatting (see DeltaMaster deltas! 5.6.1, feature #1b).

See and go

If you send the list as an email, do a service to your readers and enter the most important figures in the subject line. In our example that would be the current number of entries (i.e. registrations), how many are new compared to the past report, and how many were removed.

This method of reporting is extremely efficient. No information channel is monitored as intensively as an email inbox – and the subject line is the first thing that readers notice in their email. Oftentimes, however, this channel is not utilized to its full potential. All too often, the subject line only provides a general announcement (e.g. “Registrations for in-house exhibition”) instead of actual information (“In-house Exhibition: 41 Registrations, 5 new, 0 cancelled”). In DeltaMaster clicks! 06/2013, we explained how you can write better subject lines using variables that automatically embed values from reports into the subject line.

To communicate changes in reports, you can use three special variables:

“X” here stands for the ID of the report where you want to insert the length or delta length. You can find the ID in the Report Properties or tooltip that appears when you hold the Alt key while placing your mouse on a report. In order for this to work, the report must be based on a pivot table or SQL Drill-Through.

You can place variables in the Email Subject, Email Text, and Email Attachment. When it sends the message, ReportServer will replace these variables with the respective values.

Questions? Comments?

Just contact your Bissantz team for more information.