Establishing Matches Within Merged Dimensions

[former_member201488]

Here’s a little trick I use for establishing matches in merged dimensions. I’m using the Island Resorts Universe to demonstrate.

Let’s say I have the Sales Data for 2006 to establish which customers bought holidays in 2006, and I want to compare this to reservations in 2007 to see which of these customers made reservations for the following year as well.

We all know the easy way to do this is via a Combined (intersection) query:

..which gives us the following (correct) output:

..but if we want to show this in the context of our Sales 2006 data, what can we do?

I have my combined query already (as above), and I've added a query returning just the 2006 Sales customers (essentially this is the "2006 Sales" half of the combined query). For full disclosure, I’ve also added a query to return the 2007 reservations data (which is the other half of the combined query) so you can check the results – which for all three of these is:

…but really the 2007 data isn’t necessary – all we need is the combined query and the 2006 Sales Data. I'm merging the dimensions in the report.

So, I want to show all of my 2006 Customers and highlight those who have reserved in 2007.

The first thing you might think of doing is something like this:

=If [Sales].[Customer]=[Reservations].[Customer] Then 1 Else 0

..which doesn't quite hit the spot. Try it yourself.

Next, maybe:

=Match(ReportFilter([Sales].[Customer]);"*"+[Reservations].[Customer]+"*")

..which also doesn't quite cut the mustard.

So what can we do?

Here’s my solution, which like most things I try to do, is low maintenance and involves no universe work in the background.

Create another query containing just the Customer object, and in the query filter section, use the same object but set it to return values from another query. The query to return values from is our first query – the combined one – which is the intersection between 2006 Sales and 2007 Reservations:

I’m calling this query [Filter Query]. Then back in the report, we need to create a couple of objects. Firstly, this one which I will call [SQL]:

=DataProviderSQL([Filter Query])

This object returns the SQL statement from the query, which is this:

As you can see, it contains a list of our Customers from the Combined Query. We can now use this as a basis to match against our results from the 2006 Sales Customers query. We do this using our second object:

=Match([SQL];"*"+[Sales].[Customer]+"*")

When we apply this object to our 2006 Sales Customers, we can see that those who have reserved in 2007 have a “1” next to them, indicating a true result for the match function:

It’s not hard to turn this into an alerter instead:

One caveat: This mechanism is limited by the number of results you can return using query on query, which I believe is set by default to 1000.

Like many things in Business Objects, there's often more than one way to accomplish something. This was my way of performing matches with merged dimensions, there may well be other easier ways....let me know if there are!

Either way, I hope you find this useful!