Sales Revenue Ledger (formula_table1.srt)
The sales revenue ledger is a commonly used report layout. An example is shown below.

Walkthrough
The sales revenue ledger report contains a vertical listing of grouped entities (e.g., State and Customers), and a horizontal multi-layered listing of headers. The headers might correspond directly to a column of data, or to calculated fields. In this example, the table is populated with data from two separate queries 'customers' and 'Order details'.
Follow the steps below to create this formula table:
1. Click the 'New' button in the Style Studio toolbar. This opens the 'New Asset' dialog box.
2. In the left panel, select the 'Report' node. In the right 'Types' panel, select 'Blank Tabular Report', and click 'OK'.
3. Right-click on an empty region of the report and select 'Script' from the context menu. This opens the Script Editor.
4. Select the onLoad tab of the Script Editor, and enter the following lines:
var q1 = runQuery('customers');
var q2 = runQuery('Order details');
This script executes the 'customers' and 'Order details' queries and stores the resulting tabular data in variables q1 and q2, respectively.
5. Expand the Style Studio Toolbox panel. (Select 'Toolbox Pane' from the Style Studio 'Window' menu.)

6. Drag the 'Table' component from the Toolbox panel to the report. This creates a new Table without a binding.
7. Right-click the Table and select 'Convert to Formula Table' from the context menu.
8. Change the Table dimensions to 6 rows and 4 columns. See Inserting Rows and Columns into a Table for details.
In the following steps, you will fill the table with the vertical listing of 'companies' grouped by 'state', starting with the top level of grouping (state), followed by the sub-group (company within each state).
9. Select cell[2,0] (third row, first column) and select the Data tab in the Properties panel.
10. In the 'Binding' region of the Data tab, choose the 'Formula' option, and enter the following formula:
toList(q1['state'])
The “q1['state']” construct extracts all the values from the 'state' column of the query result set 'q1'. The toList function extracts only the unique values from that result set. (See toList in Report Scripting for more information.)
11. Check the 'Expand Cell' box and select the 'Vertical' radio button. This sets the cell to expand (fill) vertically.
12. Set the 'Cell Name' field in the 'Cell' panel to be “st”. Click the 'Apply' button.

Note: To merge two cells: left click on the first cell and drag your mouse over the second cell to select both, then right-click > 'Table' > 'Merge Cells'.
13. Shift-click on both cell[3,0] (fourth row, first column) and cell[4,0] (fifth row, first column) to select both cells.
14. Right-click on the selected region, and choose 'Table' > 'Merge Cells' from the context menu.

15. In the 'Binding' region of the Data tab, choose the 'Formula' option, and enter the following formula for the merged cell:
toList(q1['company_name@state:$st']);
This extracts a unique list of companies for a given state 'st'. The '@state:$st' clause ensures that you obtain only companies within the particular state 'st'. If this clause were omitted, you would obtain a list of all the companies in the result set.
16. Check the 'Expand Cell' box and select the 'Vertical' radio button.
17. Set the 'Cell Name' to 'comp', and select 'st' from the 'Row Group' menu. This creates the grouping hierarchy between 'State' and 'Company'.

18. Merge cell[0,3] (first row, fourth column) and cell[1,3] (second row, fourth column) as described earlier.
See Changing a Table Cell Data Binding for information on setting static text.
19. Add the following headers using static text:
Cell[0,1] = 'Total Quantity'
Cell[0,2] = 'Gross Revenue'
Cell[1,1] = 'Average Price'
Cell[1,2] = 'Discounted Revenue'
Cell[5,0] = 'Total Revenue'
Merged Cell[0,3] = 'Net Revenue'

20. Add the summary formulas. These return summaries of numeric data fields for a given entity in the group/sub-group, e.g., sum of the quantity purchased for a given company. You will add formulas to the lowest level of grouping, and then work your way up.
a. Add the following summary formulas to the following cells:
Cell[3,1]: sum(q2['Quantity@Company:$comp']);
Cell[4,1]: average(q2['Price@Company:$comp']);
Cell[3,2]: sum(q2['Total@Company:$comp']);
Cell[4,2]: sum(q2['=Total*Discount@Company:$comp']);
b. For cell[3,2], set the 'Cell Name' to 'grossRevenue'.
c. For cell[4,2], set the 'Cell Name' to 'discountRevenue'.
21. Calculate the 'Net Revenue' by subtracting the 'grossRevenue' from the 'discountRevenue'.
a. Merge cell[3,3] (fourth row, fourth column) and cell[4,3] (fifth row, fourth column) as described earlier.
b. Add the following formula to the merged cell:
$grossRevenue-$discountRevenue
c. For the merged cell, set the 'Cell Name' to 'netRevenue'. This is the net revenue for every company within a state.
22. Add the following formula to cell[2,3]:
sum($netRevenue);
This is the total net revenue for each state.
23. Add the following formula to cell[5,3]:
sum($netRevenue);
This is the grand total of the net revenue.

Note: Define the formulas at the lowest grouping level, and then use the cell reference to perform summaries at the higher levels of grouping. The same formula, 'sum($netRevenue)', yields different results based on the context of the cell to which it is added.
24. (Optional) To match the example, add appropriate number and currency formats to the formula and static text cells, merge adjacent blank cells, and set the desired cell alignments. Bolding, italics, and larger font size help make the row and column headers stand out.

25. Preview the report to view the output, which should match the figure at the beginning of this section, Sales Revenue Ledger (formula_table1.srt).
| << Advanced Formula Table Walkthrough | © 1996-2013 InetSoft Technology Corporation (v11.5) | Employee Performance (formula_table2.srt) >> |