How to Create a Calculated Column in a PixelPerfect Report

    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



    Click on Preview



    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.