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

    Add-Calculated-Field.png

     

    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

    Enter-the-Expression.png
    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"

    Insert-the-Detail-Report-Band-for--CH341_Level-.png
    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

    Embed-Headings-in-Template.png
    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

    Format--CH341_Level--Cell.png
    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

    Preview.png


    Click on Preview

    Result

    Result.png

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

    Edit the Expression

    Edit-the-Expression.png

    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

    Final-Result.png

     

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