Employee Performance (formula_table2.srt)

This example is an Employee Performance report which displays the sales made by sales employees to clients in different states, and breaks down performance by fiscal year and product category.

 

Walkthrough

This report uses dynamic listing in both the horizontal and vertical directions. It also demonstrates how to manipulate date fields in formula tables and how to implement hybrid column or row headers.

For this example, you will use a query called 'Employee Sales'. This query is embedded (i.e., stored locally) in the sample 'formula_table2.srt' template. Before you can use this query, you must make it accessible within the global scope. To import the query into the shared query registry, follow the steps below:

1. Open the 'formula_table2.srt' report in Style Studio. You can find this report template in the StyleIntelligence\examples\docExamples\script directory.

2. Expand the Style Studio Asset panel.

3. Expand the 'Report' > 'Local Query' node on the tree.

4. Expand the 'Data Source' node on the tree.

5. Drag the 'Employee Sales' query from the 'Local Query' node to the 'Orders' node.

 

This exports the query to the global query registry, where it will be accessible to other reports. (See Creating a Local Query in Data Modeling for more details.)

6. Close the 'formula_table2.srt' report. (Do not save it.)

Now that the 'Employee Sales' query is available in the shared registry, follow with the steps below to create the desired 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 line:

var q = runQuery('Employee Sales');

5. Expand the Style Studio Toolbox panel. (Select 'Toolbox Pane' from the Style Studio Window menu.)

6. From the Toolbox panel, drag a Table component into the report.

7. Right-click the table, and select 'Table' > 'Convert to Formula Table' from the context menu.

8. Resize the Table to have 4 rows and 4 columns. (See Inserting Rows and Columns into a Table for details.)

 

9. Fill the table with the vertical listing of sales employees, grouped by state. To do this, start with the top level of grouping (Employee) followed by the sub-group (State). Follow the steps below:

a. Expand the Style Studio Properties panel. (Select 'Properties Pane' from the Style Studio Window menu.)

b. Select cell[2,0] (third row, first column) and select the Data tab in the Properties panel.

c. In the 'Binding' region of the Data tab, choose the 'Formula' option, and enter the following formula:

toList(q['Employee']);

d. Check the 'Expand Cell' box and select the 'Vertical' radio button.

e. Set the 'Cell Name' to 'emp'.

f. Select cell[3,0] (fourth row, first column), and add the following formula:

toList(q['State']);

The formula toList(q['State']) will list all states, regardless of employee. If you prefer to list only those states within which the given employee has sales, change the formula to the following:

toList(q['State@Employee:$emp']);

g. Check the 'Expand Cell' box and select the 'Vertical' radio button.

h. Set the 'Cell Name' to 'st', and select 'emp' as the 'Row Group'. This creates the grouping hierarchy between 'emp' and 'State'.

9. Fill the table with the horizontal listing of years, a horizontal listing of product categories, and an empty cell between them. To do this, follow the steps below:

a. Select cell[1,1] (second row, second column), and add the following formula:

toList(q['OrderDate'], 'date=year');

b. Check the 'Expand Cell' box and select the 'Horizontal' radio button.

c. Set the 'Cell Name' to be “yr”.

d. Select cell[1,3] (second row, fourth column), and add the following formula:

toList(q['Category']);

e. Check the 'Expand Cell' box and select the 'Horizontal' radio button.

f. Set the 'Cell Name' to be “cat”.

You have now configured the headers, and you can add the summary formulas. These formulas return summaries of numeric data fields for a given entity in the vertical and horizontal group/sub-group, e.g., sum of the quantity purchased for a given employee, in a given year, or sum of the quantity purchased in a given state, for a given employee, in a given year. etc.

First add formulas to the lowest level of grouping, and then work your way up.

7. Select cell[3,1] (fourth row, second column), and add the following formula:

sum(q['Quantity@=year(OrderDate):$yr;Employee:$emp;State:$st']);

8. Set the 'Cell Name' to be “salesEmpYearState”.

Note the difference in the syntax of the two formulas. The formula for cell[3,1] uses a function “year(OrderDate)” to extract the year of a given order date. (See the Formula Tables section of the Report Scripting for more details).

9. Select cell[3,3] (fourth row, fourth column), and add the following formula:

sum(q['Quantity@Employee:$emp;State:$st;Category:$cat']);

10. Set the 'Cell Name' to be “salesEmpCatState”.

You should define the formulas at the lowest grouping level, and use the cell reference to perform summaries at the higher levels of grouping.

11. Add the summary formulas at the Employee level (the higher level of grouping). Follow these steps:

a. Select cell[2,1] (third row, second column), and add the following formula:

sum($salesEmpYearState);

b. Select cell[2,3] (third row, fourth column), and add the following formula:

sum($salesEmpCatState);

3. Now add column title cells which span their entire datasets:

a. Click to select cell[0,1] (first row, second column), and type the text “Year”.

b. Select the Option tab in the Properties panel, and enable 'Merge Expanded Cells'.

c. Click to select cell[0,3] (first row, fourth column), and type the text “Category”.

d. Select the Option tab in the Properties panel, and enable 'Merge Expanded Cells'.

 

5. Resize the columns, modify the fonts, assign borders, etc., so that the report displays nicely.

 

6. Preview the report to view the output. The final result can be seen in the report named 'formula_table2.srt'.

<< Sales Revenue Ledger (formula_table1.srt) © 1996-2013 InetSoft Technology Corporation (v11.4) 8 Viewing a Report >>