Multi-Select with an All Values Choice, and Fragments

Here’s a not uncommon scenario. You are designing a dashboard page with a report such as sales by customer. To show sales by customer, you design a report containing the CustomerName column so that facts are grouped by customer. You provide a multi-select prompt to allow the user to select which customer data to view. But you also want to provide the ability to view the data for all customers as a total, without grouping by customer.

For example, consider the following sales data for a group of retailers and suppliers.

Each supplier wants to view the data by customer, but also wants to see the data for all customers as a total.

Mike wants to see data for A and C as separate items, but also has the need to see what sales are for the total of A + C. Ned wants to see data for C, D, and the total of C + D. Oscar wants to see the data for A, B, C, or D as well as the total of all four.

In other words, Mike wants a multi-select prompt for CustomerName that offers him three choices: A, C, and All Customers -- something that looks like this:

This would allow Mike to see a report like this

or this

 

using the same prompt and the same report.

This can be done by creating multiple sources for the CustomerName logical column – one source for individual customers and another source for All Customers – and then combining both sources (unioning all) as separate fragments.  The SQL you need to have the BI server generate should be of the form:

select D0.C1 as C1
,sum(D0.C2) as C2
from (
(select
T3476.CUSTOMERNAME as C1
,T3484.AMOUNT as C2
from …)
union all
(select
'**All Customers' as C1
,T3484.AMOUNT as C2
from …)
)D0
group by D0.C1

To get this union all to happen, you need to use the Fragmentation content feature of the logical table source object. In this case you could create two sources as fragments.

To get the union all to work, you not only need to have checks in the checkboxes, you also need to define different fragmentation content. It does not really matter what the fragmentation content is! In this example, there is a logical column, “Customer Selection”, that is used to define the fragmentation content. This logical column does not even have to be exposed in the presentation layer. Its sole function is to tell the admin tool that these two fragments have different content and therefore need to be combined to get all the values for the CustomerName logical column.

In the business model I generated for this topic, I have a table called “SupplierCustomer” that lists all the Customers doing business with each supplier. This table is included in each logical table source. In the source for the individual customer names, it joins to the Customers table.

Note the logical column to physical column mapping.

In the other logical table source, the only table is the SupplierCustomer table (or, in this case, an alias of it).

The logical column CustomerName maps to the value
‘**All Customers’. (The double asterisks help to sort this value first in the list of customers).

This example also uses an init block to set the value of a SupplierID session variable when the user (i.e. the supplier) logs in. This session variable is also used in the WHERE clause of each LTS.

This technique works reasonably well, but there is one big problem: it fails when a column based on a time series function (Ago or ToDate) is used. For example, this query succeeded.

When logged in as Ned, it produced these results:

However, when the column Month Ago Amount, defined using the Ago function, was added

an error occurred.

[nQSError: 22049] Function AGO may not be used in a query that refers partitioned (fragmented) Logical Table Sources.

I was surprised when I saw this error. When the time series functions were introduced (and sadly, in my opinion, the Time Series Wizard was deleted) they imposed many restrictions in the metadata. In recent releases, some of these restrictions have been eliminated,  but not this one. It is too bad, since fragments in general are a valuable feature and have always been a core feature of OBIEE. So you have two choices: abandon fragments entirely or abandon time series functions and do time series the old fashioned way.  But if you choose the second path, you have to manually perform each step to set up Period Ago comparison measures, since you no longer have the time series wizard to do the steps for you.