Reporting M-N Metrics by Not Included Tags
The following procedure is applicable to XAE versions 1 and 2. It is advised to upgrade XAE to version 3 for improved handlng of M:N. See Many-to-Many in Logical Data Models for more details.
This article expands upon Reporting M-N Metrics by Tag that describes how to create reports filtered by a single, specific tag in a M:N data model.
In that M:N article, you created a report whose metrics were filtered by the presence of an attribute value (Ticket Tag=customer). The # Tickets w/Customer metric looked like the following:
Select # Tickets WHERE Ticket Tag = customer
This metric is not used in the report directly but is used to specify the filter of the report.
In this article, you create a similar metric to detect tickets that do not contain the Ticket Tag=customer attribute value. This metric is again used to filter the report.
Create a new empty report.
Create the Tickets w/o Customer metric In the Custom Metric Editor which should look like the following:
SELECT CASE WHEN (SELECT# TicketsWHERE Ticket Tag=customer)=1 THEN 0 ELSE # Tickets END
This type of metric is structured to return a value of 0 if the condition of the WHERE clause is met. Otherwise, the value of the # Tickets metric is returned. In this instance, the WHERE test is Ticket Tag=customer.
Create a numeric range filter in your report. This filter should test for values of your Tickets w/o Customer metric is greater than 0:
In your report, verify that the Tickets w/o Customer metric is not included in your report.
Create your report including the metrics to measure the non-customer tickets. Add attributes to the How pane to slice as needed.For reporting on metrics that have non-specific attribute values associated, you must create a metric to be used in the numeric range filter, similar to other M:N reporting. However, in the case of detecting absence of the attribute values, you must apply CASE/ELSE logic to the metric definition, looking only for values where the tag is not present.