Unexpected result of a union query in an active report

[Marvin Falentin]

Hey everyone,

I got two queries with three different columns (date, sales of a, sales of b), the types match and the order is right.

My Problem is the outcome of my Union query in a list is different from what I expected, this is what I was hoping for:

query 1                   

201701             100           75
201702             200           100
201703             75             100

query 2

201701             200           45
201702             300           120
201703             35             120

=> Union query 3

201701             300           110
201702             500           120
201703             110           220

The actual result looks like this:

=> Union query 3

201701             100           75
201701             200           45
201702             200           100
201702             300           120
201703             75             100
201703             35             120

Any idea on how to fix this issue?

Kind regards,

Marvin

Ps: I apologize for the bad mockup :)

[Lynn]

Looks like a good mockup to me!  :D

Try setting the aggregate properties in your union query. Set the date aggregate property to none, and set the two different sales aggregate properties to total.

[Marvin Falentin]

Hey Lynn,

thanks for your advice, I forgot something in my first mockup:

Union query 3

201701             100           75
201701.0          200           45
201702             200           100
201702.0          300           120
201703             75             100
201703.0          35             120

For some reason Cognos adds an ".0" to the query subject values and that's probably why it can't add those together right?

Probably important is when I  display those queries (query 1 and query 2) in a list but not unioned both are being displayed properly.

Kind regards,

Marvin

[Lynn]

What is the datatype for that first column of information? It looks like a year and month but perhaps it is stored as a number rather than text?

You might try casting that field to a varchar(6) in both query1 and query2, then set the aggregation property to none in query3.

[Marvin Falentin]

I casted the numeric solumn to Integer from float, now I'm at

=> Union query 3

201701             100           75
201701             200           45
201702             200           100
201702             300           120
201703             75             100
201703             35             120

this point again so I play around with the aggregates and come back to you, thanks a lot Lynn :)

[Lynn]

What is your database? It seems strange that casting would introduce the comma. I'd make sure the aggregation property is set to none for this field in all queries.

The floor function returns the integer portion of a decimal field which could cast to varchar and then substring to get first 6 positions. Don't know if that will do any better for you.

Goes to show how important data architecture and a date dimension are!

Code Select Expand


substring ( cast ( floor ( [ID_MONAT] ), varchar(20), 1, 6 )


[Marvin Falentin]

I already updated / changed my last answer because I came to the same conclusion, using Integer and your recommendation for aggregates, working perfectly now, thank you very much Lynn :)

[Lynn]

Great! Glad it worked out.

[Marvin Falentin]

So, after our little chat yesterday I was eager to try out the solution and put that part into the report it was designed for (before that I only tested it out in an empty report with the same data sources for performance reasons), now the solution isn't working anymore :(

It always gives me XQE-V5-0005 Errors for every single data item from the DMR and TM1 source as well as RSV-V4-0004 Errors for every single "new data item" e.g. "cast([date]; integer)"

Any idea why it's not working in my active report but on a blank one even though the data sources are the same and I didn't do anything different?

Update: I'm using two different packages for my report (should be possible in Cognos 11 right?) and as for the Expression "cast([date] integer)" I had to use the "available components" [date] because I can't choose from the source itself ([xx].[xx].[xx]), the second package where my DMR / TM1 items are in isn't available.

[Lynn]

So, after our little chat yesterday I was eager to try out the solution and put that part into the report it was designed for (before that I only tested it out in an empty report with the same data sources for performance reasons), now the solution isn't working anymore :(

It always gives me XQE-V5-0005 Errors for every single data item from the DMR and TM1 source as well as RSV-V4-0004 Errors for every single "new data item" e.g. "cast([date]; integer)"

Any idea why it's not working in my active report but on a blank one even though the data sources are the same and I didn't do anything different?

Update: I'm using two different packages for my report (should be possible in Cognos 11 right?) and as for the Expression "cast([date] integer)" I had to use the "available components" [date] because I can't choose from the source itself ([xx].[xx].[xx]), the second package where my DMR / TM1 items are in isn't available.

Sorry I assumed this was a relational package. Not sure how to adapt based on this new information. Dimensional sources have members with MUNs (member unique names) and captions so it is quite a different kettle of fish.

[Marvin Falentin]

My bad really, I forgot to mention I'm using both dimensional and TM1 as a source rather than relational packages.

What's striking me as odd is that I can easily do it on a blank active report but when it comes to the active report that has got another (relational) package as well it throws me that error message..

As far as my understanding of Cognos goes it's supposedly working with a) several packages as sources in one report and b) both relational and dimensional / TM1 packages as sources, right?

This is confusing me really :(

[Marvin Falentin]

So I just checked the blank report, added my relational source as a second package and suddenly it throws me the same error so I guess you just can't use relational and dimensional / TM1 packages together in one report :(