Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos Analytics Platform => Cognos Analytics => Reporting => Topic started by: asmfloyd on 12 Dec 2019 04:03:48 PM

Title: Making a Cross tab report from a left join query
Post by: asmfloyd on 12 Dec 2019 04:03:48 PM
Hello,

  I have a Share dimension having 5 rows with codes as  X1,X2,X3,X4,X5. I also have a Portfolio dimension which uses the values of share dimension.
My  fact table "FACT" does not have rows for "X2" of share dimension. But I still need to display X2 column in my report.

   Hence I created a query "Q1" with columns from Fact and Share dimension. Then another query "Q2" with Share dimension columns. I then made a new query "JOINQRY" by doing a left outer join between Share dim and Portfolio fact.

   I created a Cross tab report from the JOINQRY, with row dimension as Portfolio name; Column dimension as Shares and measure as Fact. Now if the measure is null,I get a blank row (As it is left outer and right table does not have value). How to remove the null row? If I say fact is null, it removes the column (X2) itself.

  An alternative method is using a List report and code manually for each columns as X1,X2,... which is not a great idea. I want to check whether there is any option to use the Crosstab eliminating the blank row.


Thanks in advance,
Title: Re: Making a Cross tab report from a left join query
Post by: Andrei I on 13 Dec 2019 07:51:57 AM
What is datasource? Relational package, cube, DMR, anything else?
Title: Re: Making a Cross tab report from a left join query
Post by: asmfloyd on 13 Dec 2019 09:56:24 AM
Its a relational one Oracle.
   But the queries and joins I am creating inside Query Studio only; using the existing objects in the package.
Title: Re: Making a Cross tab report from a left join query
Post by: Andrei I on 13 Dec 2019 12:53:02 PM
Quote from: asmfloyd on 12 Dec 2019 04:03:48 PM
  I have a Share dimension having 5 rows with codes as  X1,X2,X3,X4,X5. I also have a Portfolio dimension which uses the values of share dimension.
My  fact table "FACT" does not have rows for "X2" of share dimension. But I still need to display X2 column in my report.
   Hence I created a query "Q1" with columns from Fact and Share dimension. Then another query "Q2" with Share dimension columns. I then made a new query "JOINQRY" by doing a left outer join between Share dim and Portfolio fact.
   I created a Cross tab report from the JOINQRY, with row dimension as Portfolio name; Column dimension as Shares and measure as Fact. Now if the measure is null,I get a blank row (As it is left outer and right table does not have value). How to remove the null row? If I say fact is null, it removes the column (X2) itself.
You are probably not explaining your joins correctly
Q1: Fact inner join Share dimension ( is this a Portfolio fact? Where Portfolio name is coming from?)
Q2: Share dimension only
JOINQRY: Q2 left join Q1

Title: Re: Making a Cross tab report from a left join query
Post by: asmfloyd on 13 Dec 2019 01:42:35 PM
Hi Andrei,
  I am sorry for the confusion.

   Q1: Yes it is a Portfolio Fact. It is joined with Share and Portfolio dims. So Portfolio name is also in there.
      So Query1 has sharename,Portfolioname and Fact measure.

   Q2: Share dimension only.

  JOINQRY: Q2 LEFT JOIN Q1.
      
      So for the row X2 from Q2 query, there is no matching row in Q1.

Thanks much for looking.
Title: Re: Making a Cross tab report from a left join query
Post by: Andrei I on 13 Dec 2019 03:38:41 PM
I did some tests. Suppress Zeros By Rows Only did the trick in my test. Did you try it?
Title: Re: Making a Cross tab report from a left join query
Post by: asmfloyd on 13 Dec 2019 03:51:10 PM
Wow!! Thanks much Andrei. You made my day!!.Ddn't know that there was something like that. (:
Text only | Text with Images