Hide null rows and Sort rows/columns in oac dv workbook

In OAC dv visualization, there is no option to hide NULL rows,  like we have in oac classic analysis.

Here is the workaround, to suppress NULL rows in a   oac dv visualization.

———

Lets say in a visualization, we are looking at two columns – one is measure called ACTUAL and another one is a dimension  acct.  There are some rows where ACTUAL is null for some acct members    and we want to suppress those rows.

The Filter that we can add in the visualization to hide NULL rows should be an attribute – so the question HOW can we create an attribute from ACTUAL measure.

OAC provides a conversion function called ATTRIBUTE which is not documented in oac documentation.

Here is the syntax and explanation:

===============================================================

ATTRIBUTE(measure/numeric_expr [BY dimension] [WHERE condition])

or

ATTRIBUTE(measure/numeric_expr [BY level [, level1, levelN]] [WHERE condition])

This function enables you to treat a measure expression as an attribute column. You can specify the aggregation level to apply to the measure before the result is converted into an attribute.

numeric_expr is any expression that evaluates to a numerical value.

Level is the aggregation level. You can specify more than one level. If you don’t specify a level, aggregation is performed at the grand total level.

‘condition’ is the  filter to apply.  It is optional.

======================================================

Two steps to suppress NULL rows in a DV visualization:

1. Create a new calculation – use one of the syntax shown below:

CASE WHEN ATTRIBUTE(ACTUAL BY acct) is NULL then 'A' else 'B' END

CASE WHEN ATTRIBUTE(ACTUAL BY acct) = ''    then 'A' else 'B' END

2. Add this calculation in the Filter of that visualization’s GRAMMAR panel, and select value ‘A’ for it.

Sort rows or columns in oac (DV, BIP, classic)

If there is a sort column in the query (logical or physical)  then use that in oac.    If it is a pivot table and you want to sort columns  – for example Months are columns (or ACROSS dimension) then add two objects from data query – the label column and its sort column (for example month and end_date columns).  Make sure that sort column in above label column.  Hide sort column or make the font very small  or  make background+font color the same.  Do the same for  rows (or DOWN dimension).     In short,  sort column has to be added in the report and then hidden or use font coloring to hide it.

Note:  Sometimes the sort in the  report (or workbook) stops working.   In that case,  Edit the workbook and move the sort column after label column  and then  move it again   above label column.  This fixes the problem.

Ascending or Descending Sort

In the GRAMMAR panel,   Right-click on sort order column and change the sort from  ‘Earliest to Latest’  or  ‘Latest to Earliest’