Help with Report Studio query join - Cognos 8

[Chicken Head]

I am trying to create a Report Studio report which includes two Expenditure queries and then merge these results together in to a single list. The numeric amounts are from the same fact table but they both have different filter conditions. The resulting query should contain the Reference, Unit description and the 2 total amounts from the separate lists side by side.

I have attempted to construct this as a query join but the results I seem to get nonsensical totals, possibly the total multiplied numerous times overââ,¬Â¦. I have attempted to change the Aggregate options on the amount columns but this does not seem to help.

I have attached a screen shot of the lists I am attempting to merge.

www.bigheadbob.pwp.blueyonder.co.uk/Lists.JPG

[MFGF]

Hi,

What are you using as the join condition(s) between the two queries?  From the looks of things, you should be using 'Property/Unit Reference' and 'Expend_Desc' and 'Date/Month/Year' as your join conditions, using 1..1 --- 0..n as the cardinality.

If you do this, I see no reason it shouldn't work fine.

Regards,

MF.

[Chicken Head]

Thanks for that I was missing the join on the Expend_desc.

Many Thanks, have a nice weekend.

Mark

[Chicken Head]

On a similar area regarding joins, is it possible to have a date join based on within a number of days of each other?

For example two queries with the initial join on a unique reference but then create a join on the date that are within 30 days of each other?

[MFGF]

Absolutely!

You just need to get a bit clever with your join.

Where you are defining the join, press the "add link" button and select the date in each query to begin with.  Then press the "convert to expression" button at the bottom, and set the expression to be something along the lines of

abs(_days_between([Query2].[Date from first query],[Query3].[Date from second query])) <= 30

The _days_between function figures out the difference between the dates as a number of days, and the abs function makes sure the answer is positive.

Have a nice weekend too!

MF.