Creating a calculated column in a PixelPerfect report

You can create a calculated column in a PixelPerfect report by creating a formula that uses data from available fields. Note that formulas are calculated during report generation. If this takes too long, we recommend moving the value calculations into the QlikView script.

Create new PixelPerfect report

Do the following:

1. Select Reports in the Qlik NPrinting main menu, and then click Create report.
2. Enter a Title for the report. PixelPerfect report with a calculated column.
3. Select PixelPerfect from the Type drop-down list.
4. Select an app from the App drop-down list.
5. Click Create to create the report.
6. Click Edit template to open the Template Editor.
7. Right-click the Levels node, and select Add levels.
8. Select the Connection to the QlikView document that contains the object you want.
9. Select CH184.

Add calculated field

In this example, you will create the cost column by calculating the difference between total sales and gross profit. You can use the formula editor to create more complex formulas.

Do the following:

1. Right-click the CH184_Level, and then select Add Calculated Field from the drop-down list.
2. Enter Cost as the name in the Property Grid.

3. Click the browse button at the extreme right of the Expression field.

   The Expressioneditor appears.

Enter the expression

Do the following:

1. Click Fields in the left column.

   The list of available fields appears in the center column.

2. Double-click Total Sales to insert it into the expression.
3. Click on the - symbol, or type it.
4. Double-click Gross Profit to insert it into the expression.
5. The final expression is [Total Sales] - [Gross Profit]

6. Click OK.

   The calculated column, Cost, appears at the top of CH184_Level list of fields.

Create a table with CH184_Level

Do the following:

1. Right-click the Detail bar.
2. Select Insert Detail Report.

3. Select CH184_Level.

   The result is the DetailReport1 - CH184_Level bar.

Create table cells

Do the following:

1. Press the Ctrl key and select the following fields in order:
   • ProductName
   • Total Sales
   • Gross Profit
   • Cost
2. Left-click the selection
3. Drag and drop the selection into the DetailReport - CH184_Level - Detail1 band.
4. Drag and drop the lower dashed line upward to the Detail1 bar, to eliminate the white space.

Format the CH184_Level cell

After clicking the desired cell, do the following:

1. Click the smart tag, which is the small arrow above the upper right corner of the cell.
2. Click the browse button at the extreme right of the Format String field.
3. Select Currency.
4. Click $0.00 from the list.
5. Click OK.

Preview and save

Do the following:

1. Click Preview.
2. Check the report, close it, and then edit the template again if necessary.
3. Click Save and Close to save the template and close the Template Editor.