Custom Attribute (Select)

Pre-requisite

Info

  • Custom attributes can be used in drag and drop based visuals only.
  • For Query Based visuals, add the formula to your query. To make it reusable, consider exploring Query Expressions in PersivX.

PersivX allows adding new attributes (custom calculations) on data source level as well. New attributes added appear in the attributes list and can be used just like other columns already available in the data source. The colour (Blue) will help visually distinguish the custom attributes from the actual column available directly from the data source (which are colour coded green). alt text



Creating cusom attributes allows reusing same formula across multiple tiles / reports. Any updates in the calculation will be reflected on all visuals using the attribute. This allows sharing formulas across visuals and incorporating updates to formula in a more structured (manageable) way as compared to creating Calculated Fields in each tile.


Important

Think of creating these custom attributes as adding new column in a table in the database - with the difference that the column is not actually added to the database. When this new attribute (column) is used, its values are calculated in real time - as they are not actually stored anywhere.

When used, PersivX will add the custom attribute to the query itself. Hence, just like Calcualted Fields, you can use all the functions / macros available in the data source.

Lets use the same example from Calculated Fields documentation - displaying monthly sales in a visual.


STEP 1: Identify what fields are required.
In our data source, for every line item in order, we have the quantity ordered and price of each quantity. Let's first calculate the line total using the formula lineTotal = orderNumber * priceEach. alt text


STEP 2: Identify which table / object to add the new attribute. This is for organizing attributes only. It does matter where you add the new attribute to, but organizing it properly makes it easy to search attributes - as more and more attributes are added over time.

It makes the most sense to add the lineTotal attribute under the same table orderDetails. Click on + New Attribute under the table name and change the attribute type to SELECT QUERY. alt text

Note

We use the type SELECT QUERY, because we want PersivX to use the formula as-is when fetching the data.


STEP 3: Add the formula / select clause, and give the attribute (column) a name. Description is optional. Do not worry, you can edit this formula, if required, in future.

Note

  • The formula should use the recommended SQL formats, i.e. each column should be used in the format - <TABLE_NAME>.<COLUMN_NAME>
  • Formula can contain columns from different tables as well.
  • Since the provided formula will be used as is in the query, you can use any function / macro provided by the connected data source - for example, you can use DATE functions like EXTRACT(YEAR FROM ) or aggregate functions like SUM / MAX / GROUP_CONCAT or even case statements like IF-ELSE or WHEN-THEN-END.

The formula we want to add is orderNumber * priceEach, which in the recommended SQL format becomes order_details.xlsx.orderNumber * order_details.xlsx.priceEach where order_details.xslx is the table name as shown in PersivX. (.xlsx in the table name comes up because we are working with excel files uploaded to PersivX for this example.)

alt text


STEP 4: Click save, and the attribute immediately becomes available for use (drag-and-drop). Let's add this to our visual.

alt text

Perfect, the values show up, but we want to do a SUM over lineTotal for each month to arrive at the monthly order totals.

To do so, we can update the current lineTotal attribute to orderTotal and use the updated formula SUM(order_details.xlsx.orderNumber * order_details.xlsx.priceEach). But a better practice is to create another attribute called orderTotal. This will allow using lineTotal and/or orderTotal in future - whenever required.


STEP 5: Creating a new attribute for orderTotal. It makes most sense to add orderTotal to the orders.xlsx table. So lets create a new attribute and name it orderTotal.

alt text We could have used the formula SUM(order_details.xlsx.orderNumber * order_details.xlsx.priceEach), but we already have the order_details.xlsx.orderNumber * order_details.xlsx.priceEach calculated in lineTotal.
Hence, we can simplify the formula to SUM(order_details.xlsx.lineTotal) for calculating the orderTotal.

Note

Notice how the custom attributes are also following the recommended SQL formats, i.e. - <TABLE_NAME>.<COLUMN_NAME>. In this case lineTotal is being used as order_details.xlsx.lineTotal where order_details.xlsx is the tableName were we created the attribute lineTotal.


STEP 6: Click save. Next, let's remove the lineTotal attribute from the visual and add orderTotal.

alt text Perfect! now we have the required monthly sales. We can go on and drop more attributes on the visual. PersivX will automically update the queries to build the visual.

As always, you can update the visual type to desired bar / line or any other charts, and customize the look and feel of the column as well.


The newly created attributes lineTotal and orderTotal can now be added to any visual on the report, or even other reports using this data source.