cross tab output improper

[Nandini.t]

Hi all,


As earlier i posted that if i select the particular year in prompt i should get output for previous 5 years along with the slected year.
As am getting the output but bit improper.

--1--- if i dont have data in any row r column i should get 0 but that particular year column and row is skipping  in o/p.
--2--- displaying blank instead of '0'.

example of how my o/p should be:

             2005  | 2006  | 2007 | 2008 | 2009 | 2010
---------------------------------------------------------------
X         |   45    |  0       |  0      |   0     |  0     |  0
---------------------------------------------------------------
Y         |   0       |  0       |  0      |   0     |  0     |  0
---------------------------------------------------------------
Z         |   0       |  0       |  0      |   0     |  0     |  0


example of o/p how iam getting:         

             2005  | 2006  | 2007 | 2008 | 2009 | 2010
      --------------------------------------------------------------
X          |   45   |           |          |          |          | 


pl help me out.....

Many thanx for any assistance..

[pricter]

For displaying blank as zero go to your measure property data format and in blank values place 0.

For the first quenstion you should use an outer join in order to "create" the extra values.

[Nandini.t]

hi pricter.
Thanx for ur reply. but please i request you to eloborate about the solution. Since iam finding difficult to trace it..

Many thanks
Nandini

[Nandini.t]

pl any assistance ??

[CognosPaul]

Is your source relational or dimensional? It may be that the query simply isn't returning Y and Z because they don't have any data for those years.

[Nandini.t]

its relational..

my client needs it to be '0' instead of blank..

[CognosPaul]

So Y and Z are not being returned at all, correct?

[HalfBloodPrince]

goto query explorer then keep query one as same
. Add one more query Query 2.
then add column for which u want blank values i think product in ur case.
than add a join condition. Add query two first then  query 1.
then double click on  join relationship
and set cardinality as  0..1 to 0..n. on product

then drag product from query 2 all other columns from query 2 to new query that is query 3.

now change or cross tab query to 3 or add new cross tab .

[Nandini.t]

hi prince,

'then add column for which u want blank values i  think product in ur case'

How can this be done.
I didnt get u. can u pl eloborate.

[HalfBloodPrince]

Column  whose value is  skipped

[CognosPaul]

I think a slightly easier way may to create a new query that returns all of those values which are not returned in the original query, and union that with the original one.

[Nandini.t]

sorry prince it seems to be same as before...

[Nandini.t]

yes !  Y and Z are not being returned at all....

[Nandini.t]

any ideas pl share..... 

Many thanks in advance!!!!

[CognosPaul]

Create a new query.

You haven't described what field Y and Z are, so I'm calling it Countries.

Bring in Countries, a data item that has year(current_date), and a data item that is 0

Now union that query with your original one (I'm assuming that the original query also only has Country, Year and Measure). The output of that query should look something like:


Country | Year | Measure
--------+------+------------
  X     | 2005 | 45
  X     | 2006 | 0
  X     | 2007 | 0
  X     | 2008 | 0
  X     | 2009 | 0
  X     | 2010 | 0
  X     | 2012 | 0
  Y     | 2012 | 0
  Z     | 2012 | 0


When you put the fields into your crosstab, you should see Y and Z as expected.

[Nandini.t]

Hi Paul,
The following are what i have in my query

--1---   x , y ,z here are product codes.. i have a filter on product codes to get only x,y,z.

--2---   I have Incident Year

--3--   Total  Amount

--4--  few more necessary data items.

so, as you specified 'and a data item that is 0'
I dindt get which data item to be made 0.
pl guide me...

[HalfBloodPrince]

To display zero where values are missing set format of your measure to Number and in Missing Value character property put 0.

[CognosPaul]

I meant the expression should be 0. My advice is to create a union between the two queries - the first one which does not have y and z, and another one which returns all of the products.

The reason you don't see y and z in the crosstab is because there is no data for those products. The rows aren't being returned in the query and Cognos can't include them in the crosstab. The way around is to force them in.

There are two ways around this.

My solution is to create a union between your query and a new one which returns all of the products you want.

HalfBloodPrince's was to create a new query that is a simple list of the products of all the products you want, and to create an outer join from your original query to that list. In the resultant subquery, use the the Product field from the new query and the other fields from your original one. The end result should be almost what you need.