Merged Demensions - How to find Non Matching Items

building reports webintelligence
1

I’m trying to create a slow moving inventory rerport. One query has products with inventory >0 and another with products that have had sales in the last year. I want a report that has the products with no sales. Sounds so easy. Thanks.

Moksnb1 (BOB member since 2008-03-26)

2

Merging dimensions will not do this for you (unlike in Full Client). You’ll need to use either a subquery or a combined (Minus) query.
Check out http://help.sap.com/ to get the Documentation on these topics.

pablolee :uk: (BOB member since 2008-07-29)

3

Not strictly true…depends on scenario

Here’s an example using the eFashion universe

Create Query 1 to list all colors - there should be 209
Create Query 2 to list all colors with sales - there should be 203

So I want a list of colors with no sales,

Create a block with the merged dimension for color and the sales
Create a block filter where the sales are null and your list should show the the 6 colors without sales
Color_query.wid (52.0 KB)

jemstar :ireland: (BOB member since 2006-03-30)

4

Thanks for the help. I was hoping for an easier solution, but I created a subquery and it has been running for about 2 hours. I am going to try and attached the sql maybe you can see something that’s causing a problem. Thanks!
Inventory_no_sales_6_months.xls (20.0 KB)

Moksnb1 (BOB member since 2008-03-26)

5

We don’t now your tables. What’s your query plan showing?

Also note that some DBs respond very badly to NOT IN type queries.

Damocles :uk: (BOB member since 2006-10-05)

6

I don’t know what a query plan is:?: Anyway now I’m scared to run it anymore. I better check with the DBA’s. If I try and use a Combined minus query, don’t I need to have all the same objects in both queries? If so, I can’t use this type of query because some objects are not in the 2nd query (or sub query).

Moksnb1 (BOB member since 2008-03-26)

7

Your DBAs will know what a query plan is.

Have you considered Jemstar’s worked example?

Damocles :uk: (BOB member since 2006-10-05)

8

Well I tried Jemstar’s approach and it did work (thanks Jemstar!). But… It seems that one always has to have the filtering object in the report for this to work. This seems to be a limitation of this approach. I’m going to check with my DBA’s to see if the “Query Plan” is not allowing me to run the sub query. Thanks!

Moksnb1 (BOB member since 2008-03-26)