Getting a cross join of all the dimension attributes in a list report

[Dineshpullagura]

This is the scenario,

I have a case which has the following dimensions, Case Identifier,Case Owner, Case Status, Case Creation Date, Case Resolution Date. I am trying to create a list which displays all the dimension attributes like

Case Identifier | Case Status | Case Owner | Case Creation Date | Case Resolution Date

I have my fact table which links all these dimension keys..

My goal is to show all these attributes for each and every case, but when i am trying to add all these to a list in a report, i am getting a cartesian join of all the dimension attributes.

Please suggest what could be the problem.

Thank you for your time.

[Robl]

It sounds like you need to either;

Add in some dummy data for 0 values in the database.
Use a cube.
Create a query subject for the full structure, another for the data, outer join them in the report.

It depends on whether this is a one-off problem or an ongoing requirement, and just how much data you're talking about.

[Dineshpullagura]

HI,

I am building this report using cube as the data source. The data is just around 10000 rows.

[MFGF]

I have my fact table which links all these dimension keys..

Having a fact table implies you're reporting off a data warehouse?

I am building this report using cube as the data source. The data is just around 10000 rows.

Here you say you're reporting off a cube. Which is true? Data Warehouse database or cube?

You need to bring in at least one measure to be able to avoid the issues you are experiencing. I'd also humbly suggest that a cube is not an ideal choice of data source for reports like this (if you are using a cube, that is). Cubes are not optimised to allow efficient list-style reporting - this kind of report would be much better served from a relational source.

MF.

[Dineshpullagura]

I apologize for the confusion. My data source for report is a cube built on top of a star schema using cognos dynamic cube designer. I am trying to replicate a report built on relational tables using cube.

[Robl]

Oh, it's quite simple then.

Just bring in a measure, something like 'case count' and then supress zeros and missing values.

[Dineshpullagura]

I tried bringing in a measure, but i am getting missed values. Is there a way to suppress the missed values.

And as per my report requirement i need to show only those attributes without any measure.


Thank you.   

[Dineshpullagura]

Thanks a ton for the help. I am using filter expression to suppress the missed values.