Reporting Rows – Basics
Reporting Rows allows reports to be designed where each row has their own unique list of codes or ranges for a given Row Description. Rows in the Report may are given Line Numbers or Names that can be used in Formulas to calculate value in other parts of the Report. The video shows a classic Profit and Loss style Report where each row is made up of different Account Code criteria. There is a Expanded section that auto creates Rows based on a Account Code range and also various Totals such as Gross Profit. In addition a Icon is created on the Report to allow a Drill down to the transactions on any given Row.
The video does not quite explain defining Expand and Collapse sections well. These require two Rows to define the start Row and End Row for a Expand and Collapse region.
Report showing Expand and Collapse Icon and region
Download Samples (The use the Sharperlight Sample Accounting Database and Datamodel)
PL2.rprAdvanced Formulas (Requires version 3.1.40 or higher)
Normally formulas will Total a range based on the Line Names or will be simply add or subtract Line Names. It is however possible to add conditions and even reference the results of other Output columns. Please note that if a Reporting Row design has formulas that reference the results of other Output names then it’s important to ensure that the Query Builder outputs actually do have Outputs with those names otherwise the formula’s will not work correctly.
Use the Lookup button next to the Formula for assistance on creating advanced Formulas
Formulas that Reference Other Columns Values
To reference another Output columns result by it’s Name in Reporting Rows one can use the function
RowLogicTotalRange(“<outputName>”,”<formula>”)
The output name is the name on the output field and the formula is the ranges or names of rows you wish to get the result for
For examples below
EXP-GP-RowLogicTotalRange(“Budget”,”EXP-GP”)
Or
AA+BB+CC+DD-RowLogicTotalRange(“Adjustments”,”AA+BB+CC+DD”)
Or
AA+BB+CC+DD-RowLogicTotalRange(“AdjustA”,”AA+BB+CC+DD”)-RowLogicTotalRange(“AdjustB”,”AA+BB+CC+DD”)
Change the Formula based on the Output Column Name
The function RowLogicOutputName() will allow you to know what the current Output Name is. By using the Output Name with a IIF or Switch statement you can change out the formulas depending on the Output name
Swith( RowLogicOutputName()
,”Actual”, SALES-EXPENSE
,”AdjustedSalest”, (SALES-ADJUSTMENT)*0.80
,”Sales”, SALES
, (SALES-ADJUSTMENT)*0.75
)
or
IIF( RowLogicOutputName()=”MyOutputName”, SALES-EXPENSE, SALES-EXPANSE+ADJUSTMENT)
Published: 3/08/2015 2:43 PM