We had a
requirement a few months back for a document that would show a table with
different expressions on each row along with some subtotals and expressions
that reference other rows and columns. It needed to be a single table that
could be sent to Excel. It was obviously a requirement that grew out of
existing, legacy reporting solutions using Excel but many corporations are
wedded to Excel and the larger the corporation, the harder it is to find anyone
who believes they have the authority to make changes or do something different.
The solution
was to build a document with a straight table with a single dimension that is
loaded in the loadscript with an inline load like this:
TBL_KPI_TYPE:
LOAD * INLINE [
KPI_NO,
KPI_TYPE
1,
Transportation:
2, Intermodal %
of Miles
3, Sea %
4, LTL %
5, Avg
Pallets/Truck
6,
7, Total Diesel
Cost
8, Diesel
$/Gallon
9, Diesel # of
Gallons
10, Average
distance to Customers
];
The straight
table used KPI_TYPE as the dimension so those text values get listed in the
leftmost column like any other dimension. The chart expression had to be
slightly different for each column -- some columns were fiscal months, some fiscal quarters, one was a year-to-date column -- but they all looked something like this:
Pick(KPI_NO,
' '.
$(v.intermodel_pcent_miles),
$(v.sea_pcent),
$(ltl_pcent),
$(avg_palletspertruck),
' ',
$(total_diesel),
$(diesel_price),
$(diesel_tot_gallons),
$(avg_dist_to_cust)
)
This is
simplified from the actual document.
- Note that the Pick function in the expression uses KPI_NO which is defined in the inline load. The Pick function avoids “if” statements that can affect speed performance for the table as users change their selections.
- The dimension isn’t a real data field. Each line in the expression must use the appropriate field names and set analysis to satisfy the requirement for the row. In the example above, variables are used for each row to help make the overall expression understandable at a glance.
- Because each row is a different expression, the formatting must be done within the expression in the variable using the Num function. Some of those rows are integers, some are in thousands, some are money and some are percentages.
- Because some of the expressions referred to other rows and columns (using the Above and RangeSum functions), I had to turn off the ability to drag and drop the columns. I also had to lock in the selection of all KPI_TYPE values so that the user wouldn’t accidentally make selections on KPI_TYPE. If the expressions were not using functions like Above then allowing the user to make selections on KPI_TYPE would make the chart more flexible.
- Note that some of the KPI_TYPE values can be labels and the corresponding row in the expression is just a blank line. (Make sure the chart properties are not going to hide a row of all blanks.)
- As Aaron mentions in a comment below, something similar can be achieved with a synthetic dimension using ValueList. I think ValueList is better suited to a small number of values but it offers the advantage that a user can't accidentally select one of the values by clicking on the chart; a downside is that it may consume more resources (versus an actual field dimension) when used with a large data model.
★★★
