Creating Formula Tables for Complex Layouts

Formula tables are used to create real-time, spreadsheet-like reports with highly specific or complex layouts. These tables can be used to implement the kind of data grouping and aggregation which is commonly required in accounting and financial applications.

Formula tables bridge the gap between spreadsheet applications (like Excel) and traditional reporting tools. In a spreadsheet, you usually 'fill' a column with data and then define summary formulas that reference different cells.

A formula table is similar. Rather than bind the table to a query as a whole, you extract parts of a query result set and then dynamically fill the header rows and header columns of the table. You can then reference these 'filled-in' cells to perform statistical calculations in formulas.

The next sections discuss some of the most commonly used formulas, and methods for referencing data from query results and table cells.

Extracting Data from a Query

To populate a formula table with data from a query result set, execute the query using the runQuery() method and save the result in a variable.

var q = runQuery('customers');

You can then selectively extract parts of that query result set (see Referencing Query Data) and fill the cells of your formula table.

marketing management dashboard example

Extracting Data from a Report Element

To extract data from another report element (Table/Section) use the element's table or data attributes:

var q = Table1.table['column_name']

Referencing Query Data

Once you have executed the query (see Extracting Data from a Query), you can access specific ranges of the query result set. The data-referencing syntax allows you to also group and filter the results, and to create expressions.

Referencing a Query Column

You can reference all cells of a field (column) in the result set by using the field name:

q['state']; 

The following example illustrates this approach.

Consider the sample 'customers' query. In this example, you will extract all the values under the 'state' column and use them to populate a formula table. Follow the steps below.

1. Create a new report. Add a table with two rows and four columns.

2. Run the 'customers' query in the onLoad script. (See Extracting Data from a Query for more details.) var q = runQuery('customers')

3. Select cell[1,0]. Right-click on the cell and select 'Format' from the context menu. This opens the Format panel at the bottom.

4. Click the Data tab in the 'Format' panel. Select the 'Formula' button in the 'Binding' section, and enter the following formula for cell[1,0]: q['state'] This will extract the entire 'state' column from the 'customers' query to populate the table.

5. In the 'Expansion' region of the Data tab, select 'Expand Cell' and choose 'Vertical'. (This causes the extracted data to fill down vertically.)

6. Preview the report and see how the table is populated with all of the records from the 'state' column.

More Articles About Reporting

Balancing Control with Self-Service - Talking about master data management and some other topics around governance and controls in a world where you need to still enable self-service. InetSoft has their own ways of addressing this. We can support the IT layer, where they have got tight controls on things, but enable self-service for the users. But I am sure that various vendors are going to come out with their own solutions to the problem as well...

Church Administrator Reporting Tools - A Church Administrator can utilize a database reporting tool in various ways to streamline operations, enhance communication, and make informed decisions. Here's a detailed overview of how a Church Administrator can use a database reporting tool: Membership Management: Member Profiles: The database tool can store detailed information about church members, including names, contact details, family connections, and participation in various church activities. Attendance Tracking: The tool can generate reports on attendance for services, classes, events, and special programs. This data helps in understanding member engagement and planning future events...

Create a Chart with an API - Bind a point-type chart to the sample 'All Sales' query, with 'Company' (top 5) on the X-axis, and Sum(Total) on the Y-axis. Add the following script in the onLoad Handler...

Discussing Visual Analysis - Today we are here to talk about visual analysis and specifically working with data. What is visual analysis? The origin of visual analysis goes back to two of the success stories of computers in this world. One of them was databases. Back in the '60s, there were many different approaches to databases. In the '70s, relational algebra was developed, and relational databases emerged as a standard. This gave databases the property that they could support a high level query language. And in particular, they could support parallel and transactional queries, which allowed us to use them for everything. We use them for finance. We use them for medicine, retail, education, on and on like that...

Ensuring An Efficient Machine-Learning Process - By automating time-consuming operations such as data collection, information organization, and trend reporting, machine learning solutions can boost productivity and efficiency. All of those jobs, even with enormous amounts of data, may be completed instantly thanks to machine learning business applications. The machine learning process takes several steps, each of which can benefit greatly from data visualization. Using data visualization techniques, data scientists can focus on the broader picture of the data at each step rather than on scrutinizing values. This allows them to pay attention to model design, model performance, and the meaning of data. Here is how data visualization is useful in each step...

How Could I Embed A Chart Like This In My Web App? - Embedding an InetSoft chart in your web app can be achieved through several methods depending on your specific requirements and the capabilities of the InetSoft platform. One common approach is to use the embedding functionality provided by InetSoft itself. InetSoft may offer options to generate embeddable code snippets or URLs that you can integrate directly into your web app's HTML code. This typically involves accessing the chart or dashboard within the InetSoft platform, selecting the sharing or embedding option, and copying the provided code snippet or URL...

Previous: Data Tables