phiReport

This tutorial introduces the following features:

• Multi-Value Association
• Excel Formula Fields
• BY-EXP/GROUP-BY 
• Clear Duplicated Data (CDD)
• Repeat Detail (RD)

The Invoice List Detail report uses Data View INV.VIEW which contains a Multi-Value group/association defined under "LineItem".

When importing existing dictionary items from your live files to a Data View, phiReport looks at the association definition to dynamically create the MV Groups.  

On D and I/V type dictionaries phiReport looks at the MV flag on attribute 6 and uses the data in attribute 7 as the MV Group name.

On A and S type dictionaries, phiReport looks at attribute 4 for the "C" and "D" flag.  A Controlling/Dependent structure would have a dictionary with C and a semi-colon list of attributes and each dependent attributes would have a D with a list of controlling attributes.

ex:

CODE<1> = "A"

CODE<2> = 5

CODE<4> = "C;6;7"

DESC<1> = "A"

DESC<2> = 6

DESC<4> = "D;5"

PRICE<1> = "A"

PRICE<2> = 7

PRICE<4> = "D;5;10"

When importing the above structure, phiReport creates an MV Group called CODE_GRP and assigns fields CODE, DESC and PRICE to that group.  phiReport only looks at the first controlling attribute. In the above example, field PRICE will only belong to the CODE_GRP group because it is the first one defined in <4> "D;5;10"

A "C" by itself creates an MV Group with a single fields.

The particularities in this report are fields EXTPRICE, INV.TOTAL and TAXAMT.  Those fields are "virtual" fields, similar to a translate or A/F type correlatives.  These fields each define an Excel formula as shown here:

   

Formula = {QTY}*{PRICE} translates to multiply the value in field QTY by the value in field PRICE

Formula = {EXTPRICE}+{FRT}+{TAXAMT} translates to Add values in field EXTPRICE to value in field FRT to value in field TAXAMT

Formula = {EXTPRICE}*0.08 translates to multiply values in field EXTPRICE by 0.08

Because these fields are Excel formula virtual fields, they are rendered by Excel.  The preview tab will not calculate the formula, instead it will apply any correlatives and/or conversions defined in the dictionary items.

The next points of interest in the report are the Totals and the Grp (Group) check-boxes.  The Total flags turn on the column total feature as seen in the Product List report.  The Grp flag turns on the Group feature equivalent to a BREAK-ON or GROUP-BY instruction.

The Excel group selector allows you to expand and collapse the report details.  In Excel click the [2] to collapse the detail.  The image below shows the collapsed result. 

As you can see, collapsing the detail in Excel also hides all the important information i.e. PO#, DATE, CUSTOMER and NAME.  In order to repeat that data on the total line, we need to modify the Data View to force data repeat on total lines.

Back in the Data View Manage, select the four fields to repeat (PO#, DATE, CUSTOMER and NAME) and check "Repeat Detail" and enter INV.NO in the provided textbox.  This instructs phiReport to repeat the data when Grp (BREAK-ON/GROUP-BY) is turned on for the INV.NO field.

One last thing we want to do for this report is to not repeat the detail info when in expended mode.

This is done from the Data View Manager.  Select the main field (INV.NO) and check "Clear Dup Data".  In the provided textbox, enter the names of the fields to clear when data is repeated in INV.NO.  List all fields separated by a comma.

Rerun your report to get the designed result.