02-22-2023 12:01 PM
I am working on a dashboard that will be used by many different teams and users. In order to save on runtime and the number of insights in the dashboard I would like to create a dynamic table that allows the users to select from whatever fields and metrics they desire (dynamic groupby and measures).
In order to create a more streamlined experience I would like to have some pre defined groups of dimensions and/or measures they can easily select and view. Is there a way to go about this in Incorta? I am almost looking for a way to create presentation variables with lists of desired columns to be populated into a dynamic table.
Example: Sales Filter -> returns table grouped by client, location, sales dollars, sales units
Finance Filter -> returns table grouped by category, vendor, costs
04-05-2023 08:46 AM
A few options for you depending on requirements.
1) The listing table has a dynamic group by(dimensions) and the aggregate table has dynamic dimensions and measures in the settings. Please see screenshot below and link to article:
For more flexibility across visualizations there is an option 2 as follows:
Step 1) Get the descriptive values for the dynamic fields which can be business user friendly into a table, this can be via a flat file or simply with some SQL as shown below in this scenario where I want to be able to dynamically switch between 'OrderQuantity' and 'Revenue'. However you choose to do this is fine.
Step 2) On new or existing dashboard create a Presentation Variable in this case for these dynamic measures. In my case I am naming the variable 'dynamicfield' and defining the field with the field from the table in step 1 above as you can see in the following image:
Step 3) On any visualization for the dynamic field you need a formula column with a conditional statement that will capture the value from the table in step 1, in our scenario either 'Revenue' or 'OrderQuantity' and swap that with the fully qualified field name as show in the image below either 'Online_Store.SalesWeather.Revenue' or 'Online_Store.SalesWeather.OrderQty'. So, if the variable 'dynamicfield' = 'Revenue' then it will render the dynamic field with 'Online_Store.SalesWeather.Revenue' and vice versa.
Additionally if you want to have the field name render to the end user be dynamic as well you simply add $$dynamicfield or '$$' before what you choose to name the variable itself and itll swap it as the end users make changes.
We can now interact with it as shown below. Notice the Field Revenue is our dynamic field.
If I change it to 'OrderQuantity':