If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Why is report studio generating two underlying queries?

Started by Mkrbns, 02 Oct 2013 12:47:03 PM

Previous topic - Next topic

Mkrbns

I have a package containing a 3 query subject relationship, call them Table A, B, and C:

Table A has one or more of B.  B can only have one A.
Table A has zero to many of C.  C can only have one A.
Tables B and C have a many to many relationship, hence they both relate to A.

When I pull columns from all three query subjects into a report I would expect data values from all three to repeat on the report.  However, I am getting blank values from Table B and alternating blank values for Table C.  How can I get all rows to contain data?

When I look at Tools> Show Generated SQL/MDX I see that Congos is creating two queries.  One with Tables A and B.  And one with Tables A and C.  I would expect one query that deals with all three tables at once.  I really don't want to have to model out a combined table in framework manager as I have other needs for these three distinct query subjects.

blom0344

With B and C considered facts, then converging on table A would mean a multifact scenario. With a determinant on table A Cognos should be able to provide a stitch query (look that one up) You do not give any information about the origin of the measures you are using, if any ..  Are you sure the outer join is needed?

Mkrbns

Cognos is definitely providing a stitched query (thank you for that pointer).  Since both tables B and C are on the many side of the relationship to Table A I can understand why Cognos considers them facts.  The relationship of A to C is definitely a zero to many.  But for kicks I changed it to 1 to many.  It only marginally improves my result set.  I only get data from B in the first row, then a bunch of blank rows for all the remaining rows related to data from Table C. Is there a way to force the data from Table B to repeat for each occurrence of data in Table C? 

blom0344

How does Cognos perform the stitch ?  Can you post the generated SQL to give a better chance to analyze this?