Report Join Issues

[gpollock]

Hi Everyone, I'm having issues with a join in a report.

The goal for the report is to show item class penetration--what percentage of sales go to a product class, both within the store and overall.  Example: store manager for store abc sees that towels account for 3% of the store's total sales, but company-wide, towels are 4% of *all* stores' total sales, so the manager knows to do better.

I have Query 1-Store Sales.  The level of detail is Store Number and Product Class.  I pull and sum net sales.  I'll add a data item that is percentage(net sales for Store Number) to show class penetration within the store. This query runs very quickly (10 seconds).

I then have Query 2--Retail Sales.  The level of detail is just Product Class.  I pull and sum net sales.  I'll add a data item that is percentage(net sales) to show class penetration overall.  This query runs pretty quickly (15 seconds).

Now to show these on the same report, I'll use a join of the queries.  I join by Product Class (actually use the code for performance), and set the cardinality so Q1 has one and only one Q2, but Q2 has one or more Q1.  I am keeping the default options.

I can't even get results from this new query (Q3).  If I just pull Store Number, Product Class, and net sales from both Q1/Q2, the query will run for 5+ minutes without returning any results.  Right now I'm filtering Q1 on a single store, but my end goal is to keep it open and burst the report by store.

My first question is what in the joins do I need to do to make this work performance-wise?  Is there a specific filter type or join method I need to use?  There are roughly 100 stores, and maybe 50 Product Classes.

Second, is there a better way to get all these values in Q1?  The only reason I use Q2 is to compare a store's metrics with overall retail metrics, and thought joining two queries would be better for performance than making cognos calculate Total() measures, but even if the report is run to filter only a few stores, I still want each store to be compared to the overall values.

Thanks for all your help!

[Lynn]

Hi Everyone, I'm having issues with a join in a report.

The goal for the report is to show item class penetration--what percentage of sales go to a product class, both within the store and overall.  Example: store manager for store abc sees that towels account for 3% of the store's total sales, but company-wide, towels are 4% of *all* stores' total sales, so the manager knows to do better.

I have Query 1-Store Sales.  The level of detail is Store Number and Product Class.  I pull and sum net sales.  I'll add a data item that is percentage(net sales for Store Number) to show class penetration within the store. This query runs very quickly (10 seconds).

I then have Query 2--Retail Sales.  The level of detail is just Product Class.  I pull and sum net sales.  I'll add a data item that is percentage(net sales) to show class penetration overall.  This query runs pretty quickly (15 seconds).

Now to show these on the same report, I'll use a join of the queries.  I join by Product Class (actually use the code for performance), and set the cardinality so Q1 has one and only one Q2, but Q2 has one or more Q1.  I am keeping the default options.

I can't even get results from this new query (Q3).  If I just pull Store Number, Product Class, and net sales from both Q1/Q2, the query will run for 5+ minutes without returning any results.  Right now I'm filtering Q1 on a single store, but my end goal is to keep it open and burst the report by store.

My first question is what in the joins do I need to do to make this work performance-wise?  Is there a specific filter type or join method I need to use?  There are roughly 100 stores, and maybe 50 Product Classes.

Second, is there a better way to get all these values in Q1?  The only reason I use Q2 is to compare a store's metrics with overall retail metrics, and thought joining two queries would be better for performance than making cognos calculate Total() measures, but even if the report is run to filter only a few stores, I still want each store to be compared to the overall values.

Thanks for all your help!

Is this a relational data source? I'm not sure you need two queries to do this. There is a "for" clause you can use in a calculation to control aggregation for the scope that you need.

This gives you the total sales by product class and store:

Code Select Expand


total ( [Sales] for [Product Class], [Store Number] )


This gives you the total sales by product class across all stores:

Code Select Expand


total ( [Sales] for [Product Class] )


[gpollock]

Hi All,

I wanted to give an update, as I worked on this through the weekend.  Strangely, the biggest performance benefit I received was by pulling in fewer data items in Q1 and Q2, and doing the processing in the joined query.  I would have thought that using calculations in the smaller queries would be better.  I also couldn't make the summary rows work well if I had calculations in the smaller queries--still can't explain why.

So I use Q1 as only a few measures directly for product class and store, and then Q2 as the same measures, but only at the product class level.  Then I joined the queries as previously-described, and created any derived measures I could.  As before, percentages and other calculations ran faster if the join query did the work rather than Q1/Q2--I'm baffled.  Now the report runs in about 20 minutes, which is enough of a victory for me to not have to revisit this.

Lynn, the reason why I'm not using your approach is I want to consider non-store summary to include all stores, even if they're filtered out of the reports.  If you're running for stores a,b, and c, I still want stores d and e to be included in the overall retail metrics.