How to Create a Calculated Column in a PixelPerfect Report

[Frank_S]

Description: This tutorial illustrates how to create calculated columns in a PixelPerfect report using Calculated Field

Add Calculated Field

After adding CH341 - Sales by Product CH341 - Sales by Product in the Levels node:

1. Right-click on CH341_Level
2. Select Add Calculated Field from the menu
3. Enter AvgPrice as Name
4. Click on the browse button at the extreme right of the Expression field

Refer to How to Create a Simple PixelPerfect Report

Enter the Expression

1. Enter [Sales] / [Quantity] as expression
2. Click on OK

You can double click on a field to insert it in the expression

Insert the Detail Report Band for "CH341_Level"

1. Right click on the Detail bar
2. Select Insert Detail Report
3. Select "CH341_Level". The result is DetailReport1 - "CH341_Level" bar.

Embed Headings in Template

1. Hold down the CTRL key on your keyboard while you select the following fields in the following order:ProductName, Sales, Quantity, AvgPrice
2. Left click and hold on the selection made in Step 1.
3. While holding the selection, drag and drop it into DetailReport - "CH341_Level" -> Detail1 band
4. Drag the Detail1 bar upper border till it coincides with the lower border of the Level border to eliminate the white spaces

Format "CH341_Level" Cell

After clicking desired cell:

1. Click on the small arrow above the upper right corner of the cell
2. Click on the browse button at the extreme right of the Format String field
3. Select Currency
4. Click on $0.00 from the list. When done click on OK

Preview

Click on Preview

Result

See the step below to create a more complex expression and avoid the Infinity issue

Edit the Expression

1. Select AvgPrice expressions
2. Click on the browse button at the extreme right of the Expression field
3. Enter Iif([Quantity] != 0,[Sales] / [Quantity]  ,' - ' ) and click OK

Final Result

On previewing again, you can see that the word Infinity has been replaced with the ' - ' character where the quantity value is 0.