Custom Analysis
Some of the analysis metrics or KPIs can't be derived directly from the columns in the table or aggregate/date functions provided with them. They need to be created by writing some logic in a systematic way.
Sprinkle provides the functionality in Reports to do custom analysis by writing SQL expressions using aggregations, functions, and operators. Below we discuss different types of SQL expressions along with their examples.
Custom columns
Custom columns are SQL expressions to create new columns at the level of the input and use them in your reports/visualizations.
For example, you need to create a KPI in your report to categorize products into High value, Medium value, or Low value based on their price. You can write a SQL expression to create these categories using CASE - WHEN statements.
You can create custom columns by following below steps:
Click on the Custom tab in the Input pane.
Open the Custom Columns section and click on the '+Add Column' button.
In the New Custom Column pop-up, write the SQL expression for the KPI, give it a name, and enter the description for the column(optional).
You can use variables in your expression as well (optional).
Then click on the Validate button to validate the SQL expression.
Once validated you can create it by clicking on the Create button.
Then drag this Custom Column from the Input pane to the Build section of the Visualizations pane or Filters pane to use it as a filter.
Click on the Save/Run button.
Custom metrics
Custom metrics are SQL expressions to create complex aggregated metrics and use them in your report.
For example, you have a Region-wise Sales report, but you need to add a bifurcation of Sales done by Cash and Digital payment modes in the report. You can write SQL expressions to create two more metrics using aggregation of CASE..WHEN statements, in this case- SUM(CASE WHEN payment_mode = 'Cash' THEN sales END) or SUM(CASE WHEN payment_mode = 'Online' THEN sales END).
You can create custom metrics by following below steps:
Click on the Custom tab in the Input pane.
Open the Custom Metrics section and click on the '+Add Metric' button.
In the New Custom Metric pop-up, write the SQL expression for the metric, give it a name, and enter the description for the metric (optional).
You can use variables in your expression as well (optional).
Then click on the Validate button to validate the SQL expression.
Once validated you can create it by clicking on the Create button.
Then drag this Custom Metric from the Input pane to the Build section of the Visualizations pane or Filters pane to use it as a filter.
Click on the Save/Run button.
Custom Filters
Custom filters are SQL expressions to create complex logic that evaluates to a boolean and you can use it to filter your data.
For example, you need to filter data in your report by comparing values of two columns, you can not do that directly using normal filters. You can write an expression to compare the two columns and use it to filter the data.
You can create custom filters by following below steps:
Click on the Custom tab in the Input pane.
Open the Custom Filters section and click on the '+Add Filter' button.
In the New Custom Filter pop-up, write the boolean expression for the filter, give it a name, and enter the description for the filter (optional).
You can use variables in your expression as well (optional).
Then click on the Validate button to validate the SQL expression.
Once validated you can create it by clicking on the Create button.
Then drag this Custom filter from the Table pane to the Filters pane.
Click on the Save/Run button.
Last updated