Supercharging Your Tabular Views: AG Grid Powered by Sisense Compose SDK
Supercharging Your Tabular Views: AG Grid Powered by Sisense Compose SDK How to Build a Tabular View with Sisense Compose SDK and AG Grid This guide walks through how to use Sisense Compose SDK's query hooks to power an AG Grid table component in React. We'll break down the steps, explain the code, and highlight the key functionalities of AG Grid. Introduction Tables are essential in BI applications for summarizing large datasets and providing users with a simple way to navigate, filter, and analyze data. AG Grid is an ideal solution for tabular views, offering robust capabilities like sorting, filtering, grouping, and data exporting. When combined with the Sisense Compose SDK, you can power real-time, data-driven applications that provide users with seamless, customizable data interactions. In this example, we are using a sample eCommerce dataset, available through the Sisense Compose SDK free trial, to demonstrate how powerful this combination can be. Users can explore product sales by country, age range, and time period in a fully interactive grid. AG Grid's advanced tabular capabilities include features like multi-level row grouping, custom value formatting, pivot mode for creating complex data hierarchies, and the ability to export data to formats like CSV. These features, when integrated with Sisense's query hooks and real-time data, enable developers to create highly dynamic dashboards where users can manipulate large datasets with ease. This combination of Sisense Compose SDK and AG Grid empowers developers to create rich, interactive data experiences, allowing users to filter and manipulate data at granular levels, all while leveraging real-time querying powered by Sisense. Step-by-Step Breakdown of the Code Setting Up the Project packages used: npm install ag-grid-react ag-grid-community @mui/material @sisense/sdk-ui @sisense/sdk-data Register AG Grid Modules AG Grid uses modules to enable functionality like client-side row models, and sorting and filtering. We register the modules to be used within AG Grid: import { AgGridReact } from "ag-grid-react"; import "ag-grid-community/styles/ag-grid.css"; import "ag-grid-community/styles/ag-theme-alpine.css"; These imports ensure that AG Grid is properly styled and functional. Setting Up the Query with Sisense SDK Sisense Compose SDK’s `useExecuteQuery` is used to fetch data from your data sources. The query can include dimensions and measures which AG Grid will render. const queryProps = useMemo(() => ({ dataSource: DM.DataSource, dimensions: [DM.Country.Country, DM.Commerce.AgeRange, DM.Commerce.Date.Years], measures: [ measureFactory.sum(DM.Commerce.Revenue, "Total Revenue"), measureFactory.sum(DM.Commerce.Quantity, "Total Quantity"), ], }), []); Here, `useExecuteQuery` executes the query based on the defined data source (`DM.DataSource`), dimensions (e.g., country, age range), and measures (e.g., revenue, quantity). Fetching and Displaying Data We leverage React's `useEffect` hook to update the state of `rowData` once data is fetched. This ensures AG Grid displays up-to-date information. const { data, isLoading, isError } = useExecuteQuery(queryProps); useEffect(() => { if (!isLoading && !isError && data) { const rows = data.rows.map((row) => ({ country: row[0]?.text || "N/A", ageRange: row[1]?.text || "N/A", year: row[2]?.text || "N/A", revenue: row[3]?.data || 0, quantity: row[4]?.data || 0, })); setRowData(rows); } }, [data, isLoading, isError]); This block processes the raw data returned from the query and formats it for use in the AG Grid. Column Definitions AG Grid requires column definitions that define how each field should be displayed. const columnDefs = useMemo(() => [ { field: "country", headerName: "Country" }, { field: "ageRange", headerName: "Age Range" }, { field: "year", headerName: "Year" }, { field: "revenue", headerName: "Total Revenue", valueFormatter: (params) => abbreviateNumber(params.value), // Helper function for number formatting }, { field: "quantity", headerName: "Total Quantity", valueFormatter: (params) => abbreviateNumber(params.value), }, ], []); We define five columns: country, age range, year, revenue, and quantity. The `valueFormatter` function ensures that numbers are displayed in an abbreviated format (e.g., "1.2K" for thousands). AG Grid Configuration The grid configuration includes `defaultColDef` for common properties across all columns (like filtering and sorting), and `animateRows` for smoother transitions. const defaultColDef = useMemo(() => ({ flex: 1, minWidth: 100, sortable: true, filter: true, resizable: true, }), []); Here, all columns are set to be sortable, filterable, and resizable by default. Exporting Data AG Grid’s API allows exporting table data to CSV. We use a button to trigger the export functionality: const onBtnExport = useCallback(() => { gridRef.current.api.exportDataAsCsv(); }, []); Rendering the Grid Finally, we render the AG Grid component, passing the `rowData`, `columnDefs`, and `defaultColDef` as props: <AgGridReact ref={gridRef} rowData={rowData} columnDefs={columnDefs} defaultColDef={defaultColDef} animateRows={true} /> This sets up the AG Grid to dynamically render data retrieved via Sisense Compose SDK. Value of Tabular Views Tabular views are crucial for presenting structured data, providing an easy way to explore, filter, and analyze datasets. AG Grid’s built-in features like sorting, filtering, and exporting make it a perfect fit for visualizing data-rich tables in BI environments. Features of AG Grid - Sorting and Filtering: Users can sort and filter data by column. - Grouping: Group rows by common fields. - Customization: Full flexibility in column definitions and row configurations. - Exporting: Allows exporting table data to CSV format. - Performance: Handles large datasets efficiently. Using Sisense Compose SDK to Power Components Sisense Compose SDK is an API-first approach to data querying, which powers the `useExecuteQuery` hook in this component. By combining it with AG Grid, you can easily visualize dynamic, real-time data in table format. Here is a functional example in Github https://github.com/sisensers/ag-grid-compose-sdk.gitSetting Date Filter Members to Specific Days of the Week in Sisense Programmatically
Sisense natively supports a wide range of date filter functionalities and formats. However, certain scenarios may call for unusual custom programmatic forms of date filtering, such as filtering to specific days of the week within a defined date range. For example, focusing a dashboard view on only Wednesdays in a given period, or on weekend dates, can yield valuable insights in time-sensitive analyses. This cannot be directly configured with standard Sisense UI filters when only one date dimension is present in the data source. In the previous article, the concept of date calculations was demonstrated by leveraging JavaScript date objects, modifying filter parameters programmatically via dashboard or widget scripting, and converting those date objects into formats Sisense expects. (For reference, see: Using Native Javascript Date Calculations To Modify Sisense Date Filters) The same principle can be applied to "Day of the Week" based filtering. Sisense allows defining a custom set of date members as a filter. By dynamically constructing a list of dates that fall on certain days of the week within a chosen date range, it is straightforward to pass these custom members into Sisense filters, setting them as members of a date member filter and creating a selective date filter. In the snippet below, JavaScript generates a list of dates falling on specified weekdays—such as Saturdays and Sundays—within a given start and end date range. It then formats them into the string format Sisense expects for filter members. These can be applied within the filter object just as demonstrated in the previously linked article on programmatic date filter modification.Ensuring Accurate PDF Export Headers When Programmatically Modifying Dashboard Filters in Sisense
When working with Sisense dashboards, programmatically modifying filters upon dashboard load via dashboard scripts for a specific user or circumstance is possible. However, an issue can sometimes occur when exporting these dashboards to PDF immediately after such modifications: the filter header displayed at the top of the exported PDF displaying the current dashboard filter state may not accurately reflect the current state of the dashboard filters. This article explains the cause of this issue and presents a solution to ensure that PDF exports display the correct filter information in the header.Using Native Javascript Date Calculations To Modify Sisense Date Filters
Sisense natively supports various types of date filter functionalities. However, there are instances where a dynamically updating date filter is desired based on dynamically changing conditions such as the current date. Such a filter may not precisely align with the filters provided in the Sisense UI. One approach to achieve this custom behavior of a dynamically updating filter is through the use of dashboard or widget scripting.Leveraging Sisense Notebooks with Compose SDK
Discover how to enhance your analytics integration with Sisense Notebooks and the Compose SDK. This article explores a code-first approach to creating custom datasets and visualizations using SQL, empowering data teams to leverage their existing skills. Learn how to seamlessly embed tailored insights into customer-facing applications, streamlining workflows for both analysts and developers. Unlock the potential of your data with Sisense, delivering impactful, personalized analytics that meets your organization’s unique needs.Using the InternalHttp Function Within Scripts and Plugins
The InternalHttp function is a Sisense function within the Sisense internal Prism object. The prism object and the InternalHttp function is present on all Sisense pages, and can be used in scripts and plugins, including when embedded with various forms of Sisense embedding. It facilitates custom additional API requests to the Sisense server by applying the same request headers used for internal Sisense requests to handle details of API requests such as authentication, CORS, and CSRF.Demonstrating ComposeSDK Styling Of Sisense Dashboard Widgets
Demonstrating ComposeSDK Styling Of Existing Sisense Dashboard Widgets When developing ComposeSDK embedded applications, there are three principal techniques for embedding or converting an existing Sisense widget. Discussing each method, and some of the benefits associated with them: Direct Rendering existing Sisense Dashboard Widgets with the DashboardWidget Function: The expedited approach involves rendering an existing Sisense widget directly using the DashboardWidget function. While this method ensures swift integration, it does not allow all data options of the widget to be edited within ComposeSDK or defined as variables, one of the features of native ComposeSDK widgets. Nevertheless, customization of other parameters, including styling, remains feasible based on the props used. Mandatory parameters include the dashboard and widget ID, which allow the rendering of an existing Sisense widget. Widget Recreation using ComposeSDK to Create Native ComposeSDK widgets An alternative method, detailed in this article, entails recreating the widget as a native ComposeSDK widget by employing an existing Sisense widget as a template for a new not directly linked ComposeSDK widget. Though potentially more time-intensive, this approach yields a fully customizable native ComposeSDK widget. It stands independent of any specific widget or dashboard on the Sisense server, enabling complete independence to changes made to the original dashboard or widget, or complete deletion of the widget used as the model. Loading Widget Metadata with the useGetWidgetModel function Leveraging the useGetWidgetModel ComposeSDK function provides a middle option, detailed in this article. It allows automating the return of widget metadata from an existing Sisense widget, facilitating dynamic modifications within ComposeSDK. This method balances somewhat the autonomy of entirely recreating a widget as a native ComposeSDK widget and rendering a widget as a Dashboard Widget. In this article, we will demonstrate and discuss the DashboardWidget rendering feature, a powerful capability within ComposeSDK that allows the embedding and rendering of existing widgets. The focus will be on exploring the large number of styling options provided by this feature. Among the properties of a DashboardWidget component, the styleOptions parameter determines the styling. This parameter accepts a DashboardWidgetStyleOptions object, which includes a large number of parameters documented in detail below and in this documentation page. To demonstrate a practical implementation of using these styling options, the following code example showcases a Compose DashboardWidget utilizing all documented parameters within the DashboardWidgetStyleOptions object. import { DashboardWidget } from "@sisense/sdk-ui"; export function ComposeSDKChart(props) { let styleOptions = { "backgroundColor": "lightblue", "border": true, "borderColor": "green", "cornerRadius": "Large", "shadow": "Dark", "spaceAround": "Large", "header": { "hidden": false, "titleAlignment": "Center", "backgroundColor": "lightblue", "titleTextColor": "blue" }, "height": 200, "width": 200 }; return (<DashboardWidget widgetOid={'65ab8958857ff900335db870'} dashboardOid={'65ab8948857ff900335db86e'} styleOptions={styleOptions} title={"My Chart"} />) } The final result in this example is the widget below, showing the effect of this styling in this specific case: Discussing each style option individually, including the type of parameter accepted by each style setting. backgroundColor: Type: string - Specifically a string containing an HTML color Description: This option sets the background color of the widget. In the example, the background color is set to "lightblue," a preset standard HTML color code that is equal to #ADD8E6 as a hex color code. border: Type: boolean Description: This option determines whether the widget container has a border or not. In the example, the border is enabled with the value set to true, adding a defined boundary to the widget that can be styled. borderColor: Type: string - Specifically a string containing an HTML color, Description: Specifies the color of the widget container's border. cornerRadius: Type: "Large" | "Medium" | "Small" - Three specific options set as specific strings Description: Defines the corner radius of the widget container, similar to the CSS property of the same name, allowing for customization of the widget container's shape. header: Type: object- Contains various parameters that control the styling of the header and title. Description: A style object to customize the widget header. It includes options like background color, divider line toggle, divider line color, header visibility toggle, title alignment, and title text color. header.backgroundColor: Type: string - Specifically a string containing an HTML color Description: Sets the background color of the widget header. header.dividerLine: Type: boolean Description: Controls the visibility of the divider line between the widget header and the chart. header.dividerLineColor Type: string - Specifically a string containing an HTML color Description: Specifies the color of the divider line, if visible. header.hidden Type: boolean Description: Toggles the visibility of the header and title. header.titleAlignment Type: "Left" | "Center" - Two specific options set as specific strings Description: Alignment of the title within the header. The example centers the title using the value "Center. header.titleTextColor Type: string- Specifically a string containing an HTML color Description: Specifies the text color of the header title. height: Type: number - Number in pixels Description: Sets the total height of the widget in pixels. If not explicitly set will use the height of the container. width: Type: number - Number in pixels Description: Sets the total width of the widget in pixels. If not explicitly set will use the width of the container. shadow: Type: "Medium" | "Light" | "Dark" - Three specific options set as specific strings Description: Defines the shadow level of the widget container, similar to the CSS style of the same name. Effects styling only when spaceAround is defined. spaceAround: Type: "Large" | "Medium" | "Small" - Three specific options set as specific strings Description: Specifies the space between the widget container edge and the chart. With these styling options, one can modify the visual styling of a ComposeSDK DashboardWidget, enabling a large amount of style customization for an otherwise unchanged existing dashboard widget selected from an existing dashboard on the Sisense server. While ComposeSDK DashboardWidgets may not offer the extensive modification capabilities available to native ComposeSDK widgets, such as the ability to completely modify all of widget data options, they still provide a large degree of extensive visual customization through the use of styling options. Share your experience in the comments!Converting an Existing Sisense Widget to a Dynamic ComposeSDK Widget Component
Using either the widget Sisense API's, or the Sisense widget JS API object, it is possible to determine all components and parameters of a widget required to create an equivalent dynamic ComposeSDK widget component. This approach allows the extraction of all components and parameters of a widget, enabling the creation of an equivalent dynamic ComposeSDK widget component without directly referencing or relying on a specific widget or dashboard ID. The metadata of an existing widget contains all the information needed to create a dynamic version of an existing Sisense widget. It is also possible to use an existing widget ID and dashboard ID to render an existing widget in ComposeSDK, but this does not take full advantage of the capabilities of ComposeSDK to generate new widgets directly.
