Problem with the data or me?

[JackHefner]

I have a project I'm working on and it seemed like it would be simple, but it's got me stumped and the guy I work with say's "You can't get there from here", so I want to prove him wrong, but dang...he may be right.
Here's the challenge.
I have a list that is has phone numbers listed in this manner:

Employee type number
Joe Work xxx-xxxx
Home xxx-xxxx
Mobile xxx-xxxx

What I want to do is flatten out the list
Employee Home Work Mobile
Joe xxx-xxxx xxx-xxxx xxx-xxxx


Can this be done? It seems like each line is a record, but that really does not make any sense to me from a database perspective. I'm really new to cognos, so it may be par for the course, and can't find a way to store a variable, or get it to work with IF/Then/Else statements.

Any help would be greatly appreciated.

 

[blom0344]

In database terms this is often described as denormalizing/flattening the data-set.
One way in which this can be achieved is creating a crosstab on a normalized set. Unfortunately, I have yet to find out how we can build a crosstab where you can use a non-fact in the body (as Cognos only accepts data-items there that have a aggregate against them)

So, you're fellow-worker is partially wrong, cause obviously this can be very easily achieved in the database itself. The alternative is a special query subject in the framework that does much the same thing.

If the database is beyond your reach, then a combination of CASE and max/min operators should still be a possible approach.
(It would help if the number of type's is a limited one , though)

Ties Blom

 

[sln007]

Jack,

Create 3 queries:
Name, Home_PH, Work_PH, Mobile_PH
Where each populates the Name column, and one of the 3 Phone Number types.
Union the 3 queries together.
Group by Name, Home_PH, Work_PH, Mobile_PH

Joe, XXX-XXXX, 'spaces', 'spaces'
Joe, 'spaces', XXX-XXXX, 'spaces'
Joe, 'spaces', 'spaces', XXX-XXXX

These will be unioned into another query
That query will be the report source.



SLN
State of Ohio, MIS

 

[blom0344]

The union you mention will always return 3 rows.
Why do you expect Cognos to generate 1 row out of 3 distinctly different rows? And what's with the 'spaces' ?

You need to solve this in the database and if you are working with SQL server than use the pivot/unpivot capabilities



Ties Blom

 

[TyClabbers]

In report studio create three queries. Each query has the columns employee, type and number. The first query has a filter on type = 'Work', the second type = 'Work' and the third type = 'Mobile'. Then create a join (query 4) between query 1 and 2 linking on employee. Then create a join between queries 3 and 4 again with a link on employee. The last query will then list one row with the three telephone numbers. Of course, if an employee has more then one mobilephones this will lead to more then one row.

 

[sln007]

BLOM,

The final query, while containing 3 rows each, will be reduced to 1, when the report studio report groups on all 4 columns. It will reduce the result to 1 row by NAME, TYPE1, TYPE2, TYPE3.

Auto-Aggregation might do that anyway.

It will essentially be:
SELECT NAME,
TYPE1, TYPE2, TYPE3,
PHONE1, PHONE2, PHONE3
FROM
(SELECT NAME,
'WORK' as TYPE1, PHONE as PHONE1,
'HOME' as TYPE2, ' ' as PHONE2,
'CELL' as TYPE3, ' ' as PHONE3
FROM TABLE_NAME
UNION
SELECT NAME,
'WORK' as TYPE1, ' ' as PHONE1,
'HOME' as TYPE2, PHONE as PHONE2,
'CELL' as TYPE3, ' ' as PHONE3
FROM TABLE_NAME
UNION
SELECT NAME,
'WORK' as TYPE1, ' ' as PHONE1,
'HOME' as TYPE2, ' ' as PHONE2,
'CELL' as TYPE3, PHONE as PHONE3
FROM TABLE_NAME)
GROUP BY NAME,
TYPE1, TYPE2, TYPE3,
PHONE1, PHONE2, PHONE3
ORDER BY NAME

Then just 'CUT' the TYPEx columns from your Report Studio report.

SLN