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 :)
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.
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
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.
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 :)
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!
substring ( cast ( floor ( [ID_MONAT] ), varchar(20), 1, 6 )
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 :)
Great! Glad it worked out.
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.
Quote from: Marvin Falentin on 20 Jun 2017 03:13:08 AM
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.
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 :(
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 :(