Report Studio - Not getting the correct values form multi-table

[Ruud_B]

Hi,
Using IBM Cognos Reporting (v10.2) on linux. I'm trying to get a SLA report working.

I have Tables with uptime info for Agent Types linux, windows, R2 and R4.

When I create a query for only 1 AgentType the report works for the Agent Type used in the query.
The query looks like this:
   CASE [Agent Type]
   WHEN ( 'windows') THEN (
           ( [Windows Performance].[System Hourly].[System Up Time (Seconds) (Total)] )
   ) ELSE (
           -2
   ) END
Note: the -2 is just to be able to see when it drops through the case statement, normally it will be -1.

I can change the Agent type to the 4 possible values and in the 'THEN'  use the correct table and it works for every Agent Type individually.

However, in my report I tried many options and queries but I do not succeed to get all Agent Types in my report.
I simplified the following query (removed the calculation to percent) to ensure any impact from other features.

The following is the query I use to detect the Agent Type and determine which table I have to use to get the uptime. The tables used are to get the Hourly Uptime for each Agent Type:
   CASE [Agent Type]
   WHEN ( 'windows') THEN (
           ( [Windows Performance].[System Hourly].[System Up Time (Seconds) (Total)] )
   ) WHEN ( 'linux') THEN (
           ( [Linux Extended Metrics].[Linux System Statistics Hourly].[System Uptime (Total)] )
   ) WHEN  ('R2') THEN (
           ( [Agentless Windows Extended Metrics].[HR System Hourly].[TOT_hrSystem_Uptime_Numeric]/100)
   ) WHEN ( 'R4') THEN (
           ( [Agentless Linux Extended Metrics].[System Hourly].[hrSystem_Uptime_Numeric (Total)])
   ) ELSE (
          -2
   ) END
But the result is that only the Linux Agent Type is working and the others are not processed correctly. In fact for the fields with the -2 there is a value in the corresponding table and blink fields indicate there is no data.
(see attached screen capture)

So, apparently there is somehow detected there is a value...only it does not get it.
What am I missing here? Anybody hints?

Thanks

Ruud

[Lynn]

You should look at the SQL that is generated to understand what is happening. Cognos generally wants a model to be a star schema with fact tables surrounded by dimensions. If you have separate fact tables they are queried separately and then stitched together locally on the Cognos side. Without knowing how your model is constructed it is difficult to say what is going on. Which table holds the Agent Type field you are using in the case statement and how is that table joined to the various tables in your model?

An alternative approach might be to create four separate queries to return the appropriate value per agent type and then put the separate result sets together using a union.