Documentation Campaign Campaign Classic v7 Documentation

Also applies to v8

Create a table

Last update: September 10, 2024

Applies to:
Campaign Classic v7

You can add a table to a report to display data. This can be a pivot table created based on cube measurements, a list with group, or a table containing a breakdown of values.

Create a list with group

A List with group type table lets you group data in the table and produce statistics on it. For instance, you can create totals and sub-totals for the data. Each group has its own header, detail and footer line.

CAUTION

The Page activity containing the table must be preceded by a Query or Script activity to collect the data to be analyzed in the report. For more on these activities, refer to Collect data to analyze and Script activity.

Operating principle

It may happen that you need to analyze several data categories at once. A list with group lets you combine data and create statistics on various groups of data within the same table. To do this, you can create a group in the table.

In the following example, the group shows all the campaigns in the database, the deliveries, and the number of messages sent per delivery and per campaign.

It lets you list the campaigns (Label (Campaign), the list of deliveries (Label ) linked to the campaign, and lets you count the number of messages sent per delivery (Processed), before adding them up for each campaign (Sum(@processed) ).

Implementation steps

A full implementation example is provided here: Use case: Create a report with a group list.

Please note the following steps to create a ‘List with group’ type table:

Go to the report chart and place a Query activity. Refer to Collect data to analyze.
Fill in the source table and select the fields of the table which the statistics will concern.
Place a Page activity in the chart. For more on this, refer to Static elements.
Insert a List with group type table into the page.
Specify the data path, or the table selected as a data source in the query.

This step is mandatory if you want to recover the fields in the source table later and insert them into the cells of the table.
Creating the table and its content.
Display the finalized report in the Preview tab. You can then publish the report and export it into a different format if necessary. For more on this, refer to Export a report.

Add lines and columns

By default, a List with group type table includes a header, a detail line, and a footer line.

The group itself includes header, detail and footer lines.

Header line: this line lets you give a title to the columns of the table.
Detail line: this line contains statistical values.
Footer line: this line lets you display the total values.

Lines and columns can be added to suit your needs.

The group can be placed on any line of the table and includes its own header, detail and footer lines.

Line and column: to add or delete a line or a column, go to an existing line or column and use the right-click menu.

The nature of the line you add depends on the location of the cursor. For example, to add a header line, place your cursors on a header, then click Add > A line above/below.

The width of the columns can be modified via the Column format item.

Group: to add a group, go to a line and select the matching item in the drop-down menu.

Define cell content

To edit a cell of the table and define its content and format, go to the cell and use the right-click menu.

Use the Expression menu entry to select the values to display.

To insert the values to be analyzed directly into the table, select them among the available fields.

The list of available fields coincides with the content of the query before the table in the report construction chart.
Enter a label for a cell, the header one for example.

To do this, use the same process as for inserting a field into the database, but don’t select an expression. Enter the label in the Label field. It will be displayed as is.
Calculating an aggregate (an average, a sum, etc.) and displaying it in the cell.

To do this, use the Aggregates menu entry and select the desired campaign.

Define cell format

To define the cell format, the Cell format… menu lets you access all formatting options available for the selected cell.

These options enable you to personalize the final rendering of the report and make it easier to read information.

Use the Carriage return field when exporting data to Excel: select the Yes value to force the carriage return. This value will be kept when exporting. For more on this, refer to Export a report.

The Cell format window lets you access the following tab:

The Value tab
The Borders tab
The Click tab
The Extra tab

The Value tab lets you change the font and the various value attributes or to define a format based on their nature.

The format changes data display: for example, the Number, Monetary and Percentage formats allow you to align the figures on the right and display decimal points.

Example of how to configure a currency format: you can specify the currency which the values are expressed in, choose whether or not to separate thousands, and show negative values in red. The position of the currency symbol depends on the language of the operator defined in their profile.

Configuration example for dates: you can choose whether or not to display the time.

The Borders tab lets you add borders to the lines and columns in the table. Adding borders to the cells may lead to performance issues when exporting large reports into Excel.

If necessary, you can define borders in the table template (Administration > Configuration > Form rendering ).

In this case, you’ll have the following syntax:

In the Web tab:

 .tabular td {
 border: solid 1px #000000;
 }

In the Excel tab:

 <style name="odd" fillColor="#fdfdfd">
  <border>
   <borderTop value="solid 0.05pt #000000" />
   <borderBottom value="solid 0.05pt #000000" />
   <borderLeft value="solid 0.05pt #000000" />
   <borderRight value="solid 0.05pt #000000" />
  </border>
 </style>

 <style name="even" fillColor="#f7f8fa">
  <border>
   <borderTop value="solid 0.05pt #000000" />
   <borderBottom value="solid 0.05pt #000000" />
   <borderLeft value="solid 0.05pt #000000" />
   <borderRight value="solid 0.05pt #000000" />
  </border>
 </style>

The Click tab lets you define an action when the user clicks the content of a cell or of the table.

In the example below, clicking the value in the cell lets you display the second page of the report: it will contain information on the delivery in the cell.

The Extra tab lets you link a visual to your data, such as a colored mark or a value bar. The colored mark is used when the table is shown as a legend in a chart. For more on this, refer to the implementation example: Step 5 - Create the second page

Use case: Create a report with a group list

In this example, we are going to create a two-page report: the first page will contain the list and the total deliveries per campaign, as well as the number of messages sent. Delivery names will be clickable links and will enable you to go on to the second page of the report to view the breakdown of deliveries per email domain for the selected delivery with a table and a chart. On the second page, the table will serve as a legend for the chart.

Step 1 - Create a report

Create a new report that concerns the campaign schema, Campaigns (nms).

Click Save to create the report.

Go to the chart and add the first components to be used for designing the report content: a first query and a first page.

Step 2 - Create the first query

The first query lets you collect deliveries linked to each campaign. The goal is to display a report on the various deliveries of the Adobe Campaign database linked to each campaign.

Double-click the first query to edit it, then apply the following steps to configure it:

Start by changing the schema on which the query’s source is applied: select the Deliveries (nms) schema.
Click the Edit query link and display the advanced fields.
Select the following fields:
- the delivery label,
- the primary key of the delivery,
- the campaign label,
- the indicator of processed deliveries,
- the foreign key of the Campaign link,
- the error rate indicator.
Link an alias to each field: this is recommended to facilitate the selection of data from the table that will be added to the first page of the report.

For this example, we’ll use the following aliases:
- Label: @label
- Primary key: @deliveryId
- Label (Campaign): @label1
- Processed: @processed
- Foreign key of the ‘Campaign’ (‘id’ field) link: @operationId
- Error rate: @errorRatio
Click the Next button twice to get to the Data filtering step.

Add a filtering condition to collect only the deliveries linked to a campaign.

The syntax of this filter is as follows: “Foreign key of the ‘Campaigns’ link greater than 0”.
Click Finish to save these conditions, then click Ok to close the query editor.

Step 3: Create the first page

In this step, we are going to configure the first page of the report. To configure it, apply the following steps:

Open the Page activity and enter its title, for instance Deliveries in this case.
Insert a list with group via the toolbar and enter its label, for instance: List of deliveries per campaign.
Click the Table data XPath… link and select the delivery link, i.e. [query/delivery].
Click the Data tab and change layout of the table: add three columns on the right.
Add a group.

This group will enable you to group campaigns and the deliveries linked to them.
In the group window, reference the Foreign key of the ‘Campaign’ link and close the window.
Edit the first cell of the group header and insert the Label field of the campaigns as an expression.
Edit the second cell of the details line and select the deliveries Label.
Edit the format of this cell and open the Click tab. Configure the adequate options so that when the users clicks the name of a delivery, it opens in the same window.

To do this, select a Next page type action and select In the same window as an open option.
In the lower section of the window, click Add and specify the /vars/selectedDelivery path and the @deliveryId expression that matches the alias of the primary key of the delivery, as defined in the query created previously. This formula lets you access the selected delivery.
Edit the second cell of the footer line of the group and enter Total per campaign as a label.
Edit the third cell of the header line of the group and enter Number of messages sent as a label.

This information coincides with the column title.
Edit the third cell of the detail line and select the processed message indicator as an expression.
Edit the third cell of the footer line of the group, select the processed delivery indicator and apply the Sum aggregate to it.
Edit the fourth cell of the detail line and select the error delivery error rate as an expression.
Select this cell to display a value bar representing the delivery error rate.

To do this, access the cell format, then go to the More tab. Select the Value bar entry in the drop-down list and select the Hide the cell value option.

You may now view a rendering of the report. Click the Preview tab and select the Global option: this shows the list of all deliveries in the Adobe Campaign database that are linked to a campaign.

We recommend using the Preview tab to make sure the data in your table is properly selected and configured. Once this is done, you can go on to formatting your table.
Apply the Bold style to the cells that show the total per campaign and the total number of messages processed.
Click the 1st cell of the group header line, the one that displays the campaign name, and select Edit > Merge to right.

Merging the first two cells of the group header line will re-align the campaign title and the list of deliveries linked to it.

CAUTION
We recommend waiting until your report is built before merging cells since merging is irreversible.

Step 4 - Create the second query

We want to add a second query and a second page to display the detail of a delivery when the user of the report clicks on it. Before adding the query, edit the page you have created and enable the outgoing transition so that it can be linked to the query.

Add a new query after the Page activity and edit its schema: select the Recipient delivery logs schema.
Edit the query and define the output columns. To display the number of deliveries per email domain, you need to:
- calculate the sum of primary keys to count the number of delivery logs:
- collect recipient email domains and group information on this field: to do this, select the Group option in the domain name column.
Link the following aliases to the fields:
- count(primary key): @count
- Email domain (Recipient): @domain
Click the Next button twice: this takes you to the Data filtering step.

Add a filtering condition to collect only the information linked to the selected delivery.

The syntax is as follows: Foreign key of the ‘Delivery’ link equals the value of the setting $([vars/selectedDelivery])
Close the query configuration window and add a page to the chart, just after the second query.

Step 5 - Create the second page

Edit the page and enter its label: Email domains.
Uncheck the Enable output transitions option: this is the last page of the report and will not be followed by another activity.
Add a new list with a group using the right-click menu and call it Email domains per recipient.
Click the Table data XPath… and select the Recipient delivery logs link.
In the Data tab, adapt the table as follows:
- Add two columns on the right hand side.
- In the first cell of the detail line, add the rowNum()-1 expression to count the number of lines. Then alter the format of the cell: in the Extra tab, select Color tab and click Ok.
  
  This configuration will enable you to use the table as a caption for the chart.
- In the second cell of the detail line, add the Email domain(Recipient) expression.
- In the third cell of the detail line, add the count(primary key) expression.
Add a pie chart to the page using the right-click menu and assign the Email domains label to it. For more information, refer to Chart types and variants.
Click the Variants link and deselect the Display label and the Display caption options.
Check that no value sorting is configured. For more on this, refer to this section.
In the Data tab, change the data source: select Context data from the drop-down list.
Then click Advanced settings and select the link to the recipient delivery logs.
In the Chart type section, select the Email domain variable.
Then add the calculation to be carried out: select the sum as an operator.
Click the Detail button to select the field which the count will concern, then close the configuration window.
Save the report.

Your page is now configured.

Step 6 - View the report

To view the result of this configuration, click the Preview tab and select the Global option.

The first page of your report details the list of all deliveries included in the database.

If you click the link of one of these deliveries, it shows the chart showing the breakdown of email domains for this delivery. You are now on the second page of the report and can return to the previous page by clicking the appropriate button.

Create a breakdown or pivot table

This type of table lets you display statistics calculated on the data in the database.

The configuration of these types of reports is similar to the one used for the descriptive analysis assistant. For more on this, refer to this page.

For more on creating a pivot table, refer to this section.

recommendation-more-help