CODEWITHSUNDEEP
sqlcognos
Kevin BI
Kevin BI

Reputation: 11

Combine Columns with similar measures in Crosstab in Cognos/SQL

So I have a crosstab that measures the amount of hours spent at a single facility. Our rows seperate the type of task for the hours worked, and the columns seperate the facility where the hours were worked.

The problem I have is that in our database we have 2 levels of facilities. Each Level 1 Facility has ten Level 2 "child" systems below it. What we want is to be able to roll all of the Level 2 facility columns' data into their respective Level 1 facility parent.

I've included an example below. The first t̶a̶b̶l̶e̶ crosstab is the one I have, and I want to get to the second t̶a̶b̶l̶e̶ crosstab.

So in our system the "parent" and "child" facilities are connected through a field called OBJ_PARENT. Each of the "children" have the name of its "parent" inside OBJ_PARENT.

I will give the SQL I have written up upon request.

EDIT: I've provided my current SQL below

select EVT_WORKORDER, EVT_DEPARTMENT, EVT_WOSTATUS, EVT_WOTYPE, EVT_FACILITY, OBJ_FACILITY, OBJ_PMD, OBJ_PARENT, BOO_HOURS, BOO_DATE, BOO_ENTERED, BOO_ACTIVITY, BOO_PERSON,  ACT_ACTIVITY, ACT_TASK, ACT_WORKORDER, PRV_CODE, PRV_PROPERTY, PRV_VALUE,
sum(case when boo_person <> 'UNPAID' then boo_hours else 0 end ) "Paid Hours",
sum( boo_hours ) "All Hours"
FROM R5EVENTS
JOIN R5OBJECTS ON EVT_FACILITY = OBJ_FACILITY
JOIN R5BOOKEDHOURS ON BOO_EVENT = EVT_WORKORDER
join R5ACTIVITIES on EVT_WORKORDER = ACT_WORKORDER
JOIN r5propertyvalues ON ( ACT_TASK || '#0' = PRV_CODE)
where prv_property = 'CORESRV'
 and ACT_ACTIVITY= BOO_ACTIVITY
AND EVT_DEPARTMENT = 'PK-MAINT'
AND EVT_WOTYPE IN ('JOB', 'PPM')
AND EVT_WOSTATUS IN ('R', 'FC', 'C', 'H', 'FI', 'CI', 'AP', 'IP', 'DF')
and OBJ_PMD in (#PROMPTMANY ('PMD')#)
and (OBJ_FACILITY in (#PROMPTMANY ('Park')#)
        OR
        OBJ_PARENT in ( SELECT OBJ_FACILITY from R5OBJECTS where OBJ_FACILITY in (#PROMPTMANY ('Park')#)))
group by EVT_WORKORDER, EVT_DEPARTMENT, EVT_WOSTATUS, EVT_WOTYPE, EVT_FACILITY, OBJ_FACILITY, OBJ_PMD, OBJ_PARENT, BOO_HOURS, BOO_DATE, BOO_ENTERED, BOO_ACTIVITY, BOO_PERSON,  ACT_ACTIVITY, ACT_TASK, ACT_WORKORDER, PRV_CODE, PRV_PROPERTY, PRV_VALUE

BOO stands for "Booked"

Upvotes: 1

Views: 1226

Answers (1)

toddsonofodin
toddsonofodin

Reputation: 513

The proper way would be:

  • Create a true hierarchy table in the database
  • Have all of the children (which, in this case with the way your data is stored, also includes what you refer to as Level 1) relate to a parent
  • Model in FM so that the fact table joins to the dimension table on the child
  • Expose the parent object from the hierarchy table
  • Bring the parent object into the top edge of the crosstab

If you want to do it quick and dirty, use some sort of string function to chop off the "child" part of the facility.

Upvotes: 2

Related Questions