Making a Cross tab report from a left join query

[asmfloyd]

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,

[Andrei I]

What is datasource? Relational package, cube, DMR, anything else?

[asmfloyd]

Its a relational one Oracle.
   But the queries and joins I am creating inside Query Studio only; using the existing objects in the package.

[Andrei I]

  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

[asmfloyd]

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.

[Andrei I]

I did some tests. Suppress Zeros By Rows Only did the trick in my test. Did you try it?

[asmfloyd]

Wow!! Thanks much Andrei. You made my day!!.Ddn't know that there was something like that. (: