Referencing a Cell with Absolute Parent Group Reference

You can also use the value of the parent group to compare summary cells. To refer to a summary cell in another header group, use the absolute value of the header group, as shown below:

$cellName['grpName:absolute_value']

e.g., $sales['state:NJ']
      $sales['yr:"2002"'] - specify numeric values in quotes

Consider a formula table based on the 'All Sales' query. In this example, you will find the relative sales for each year compared to the fixed year 2002. Follow the steps below:

1. Create a new report, and add a table with three rows and three columns.

2. Add the following script to the report's onLoad Handler to store the results of the 'All Sales' query:

var q = runQuery('All Sales'); 

3. Select cell[1,0] in the table. Right-click on the cell and select 'Format' from the context menu. This opens the 'Format' panel at the bottom. The second toList argument groups the returned dates by year.

a. Select the Data tab. In the 'Binding' panel, select the 'Formula' option, and enter 'toList(q['Order Date'],'date=year')' as the formula.

b. In the 'Cell' panel of the Data tab, enter 'yr' for the 'Cell Name' of cell[1,0].

c. In the 'Expansion' panel of the Data tab, select 'Expand Cell' to set cell[1,0] to expand 'Vertical'.

4. Select cell[1,1] in the table.

a. Select the Data tab. In the 'Binding' panel, select the 'Formula' option, and enter the following formula:

sum(q['Total@=year(field["Order Date"]):$yr']) 

In words, this says “for each year in column 'yr', find the 'Order Dates' falling within that year, and sum the 'Totals' for those order dates.” Effectively, this calculates the total revenue generated for a given fiscal year.

b. In the 'Cell' panel of the Data tab, enter 'tot' for the 'Cell Name' of cell[1,1].

5. Select cell[1,2]. In the 'Binding' panel of the Data tab, select the 'Formula' option, and enter the following formula.

$tot - $tot['yr:"2002"'] 

This formula uses absolute cell referencing to calculate the difference between the total revenue (computed in the column named 'tot') of the current year and the year 2002.The table should appear as shown below:

toList(q['Order Date'], 'date=year')	sum(q['Total@= year(field["Order Date"]):$yr'])	$tot - $tot['yr:"2002"']

6. Preview the table.

Referencing a Cell with Parent Group Reference as an Expression

The referenced group can also be specified with a JavaScript expression. The JavaScript expression should be prefixed to the expression string with an '=', as shown below:

$company['state:=iif($type == "T", $state, $province)'] 

The syntax of the iif function used above is CALC.iif(logical_test, value_if_true, value_if_false).

When To Use InetSoft's API and Scripting Language to Render Dashboards

InetSoft's API and scripting language offer powerful tools for creating and rendering dashboards that can significantly enhance data visualization and analysis capabilities. Understanding when and how to use these tools is crucial for maximizing their benefits. Here's an in-depth exploration of scenarios where using InetSoft's API and scripting language to render dashboards is particularly advantageous:

1. Custom Data Integration and Transformation

When dealing with complex data integration and transformation requirements, InetSoft's API and scripting language become essential. Many organizations work with diverse data sources, including databases, spreadsheets, cloud services, and more. The API allows for seamless integration of these sources, enabling real-time data retrieval and updates.

• Scenario: A company wants to merge data from its CRM system, ERP system, and third-party marketing tools into a single dashboard.
• Solution: Using InetSoft's API, developers can write scripts to connect to each data source, extract the necessary data, and transform it into a unified format. The scripting language can handle data cleansing, normalization, and complex transformations, ensuring the dashboard displays accurate and comprehensive information.

2. Dynamic and Interactive Dashboards

Creating dynamic and interactive dashboards that respond to user input or changes in data can greatly enhance user experience and engagement. InetSoft's scripting language allows for the customization of dashboard components and interactions.

• Scenario: An e-commerce business wants to create a sales performance dashboard that allows users to filter data by region, product category, and time period.
• Solution: Developers can use the scripting language to add interactive elements such as dropdown menus, sliders, and date pickers. The API can then dynamically query the underlying data based on user selections, updating the dashboard in real-time without requiring a full page refresh.

3. Automated Reporting and Notifications

For organizations that need to generate regular reports or send notifications based on specific data conditions, InetSoft's scripting language offers automation capabilities that can save time and reduce manual effort.

• Scenario: A financial services firm needs to generate daily reports on portfolio performance and send alerts if certain thresholds are met.
• Solution: Scripts can be written to schedule report generation and define alert conditions. The API can trigger these scripts at specified intervals, automatically rendering the necessary dashboards and sending out email notifications or alerts through other communication channels.

4. Advanced Data Visualization and Custom Charts

When standard chart types and visualizations are insufficient for representing complex data relationships, InetSoft's scripting language provides the flexibility to create custom visualizations.

• Scenario: A healthcare organization needs to visualize patient data trends using specialized charts like Sankey diagrams or custom heat maps.
• Solution: Developers can leverage the scripting language to define custom chart types and visualizations that are not available out-of-the-box. The API facilitates the integration of these custom charts into dashboards, ensuring they are rendered accurately with the underlying data.

5. Embedding Dashboards in Other Applications

Integrating dashboards into existing applications or web portals can provide users with seamless access to critical data insights without switching contexts. InetSoft's API is particularly useful for embedding dashboards within other software environments.

• Scenario: A project management tool wants to embed resource utilization dashboards within its interface to provide real-time insights to project managers.
• Solution: Using the API, developers can embed InetSoft dashboards into the project management tool's UI, ensuring the dashboards are fully integrated and interactive. The API allows for passing parameters and data between the host application and the embedded dashboard, maintaining a cohesive user experience.

6. Personalized Dashboards and User-Specific Views

In scenarios where different users require personalized views of the same underlying data, InetSoft's scripting language can tailor dashboards to individual needs.

• Scenario: A sales organization needs each salesperson to see a personalized dashboard showing their own sales metrics and targets.
• Solution: Scripts can be used to customize the dashboard view based on the logged-in user's role or ID. The API can fetch and display data specific to each user, ensuring confidentiality and relevance.

7. Real-Time Data Monitoring and Streaming

For applications that require real-time data monitoring and streaming, such as operational dashboards in manufacturing or network monitoring systems, InetSoft's API and scripting capabilities are indispensable.

• Scenario: A manufacturing company needs to monitor production line metrics in real-time and display alerts for any anomalies.
• Solution: The API can connect to real-time data streams from sensors and machinery. Scripts can process this data on-the-fly, updating the dashboard with the latest metrics and triggering alerts when predefined conditions are met.

Previous: Report Scripting - Group Numbering

Next: Formula Table Functions