Query Calculation help

[nimaruici]

Hi,

I'm new to Cognos so apologize if this is way too much of a beginner question (if there are any great resources you can recommend for reporting by using advanced expressions that would be amazing .. been trying to find some).

I run a daily report that shows the open order book of the company based on the snapshot of orders yet to have shipped as of yesterday's end. Most relevant columns are the ones below. Every combination of Order Number & Line is unique.

Order Number |   Order Line   | Planned Delivery Date

The data source also contains a field called "Calendar Date" - this represents the day the snapshot of the order book is taken from and the report is filtered to a dynamic yesterday with this expression _days_between(current_date,[Calendar Date])=1 . I do not include this in the report as a column as there is no point.

I want to add a calculated column/query calc to the report, that returns for every row a past Planned Delivery Date from an older order book "snapshot" to see if there have been any planned date re-dates of existing orders.

So for example if I want to compare it to what we had in the system 2 days ago ... the query calc would return the Planned Delivery Date for every record where the Order Number and Order Lines are the same and the Calendar Date is _add_days(current_date,-2). For situation where order number & line didn't exist 2 days ago I assume the formula would return a blank or something since they are a new order that only exist for _days_between(current_date,[Calendar Date])=1.

Any help is much appreciated... thanks a bunch.

[nimaruici]

I guess the simplest way to reduce this to essence is I have a report that's filtered for Calendar Date _add_days(current_date,-1) ... but I want a single specific column within that report to be filtered for Calendar Date = _add_days(current_date,-2) .. the data item expression of the query calc to ignore the filter expression of the larger report query.

Thanks a lot.

[MFGF]

I guess the simplest way to reduce this to essence is I have a report that's filtered for Calendar Date _add_days(current_date,-1) ... but I want a single specific column within that report to be filtered for Calendar Date = _add_days(current_date,-2) .. the data item expression of the query calc to ignore the filter expression of the larger report query.

Thanks a lot.

Hi,

I'm assuming you are using a relational package to base the report on? As your filter will exclude all rows whose calendar date is not yesterday, you're going to need a second query in the report which has a filter to bring in the rows you need for two days ago. Join the queries on Order Number and Order Line, and populate a third query (the one that will drive the list/crosstab container on your page) with the items you need. You can add your past planned delivery date from the second query to the third query too.

Cheers!

MF.

[nimaruici]

Thank you, I just found the part of the interface where I can join the queries. Looks to be working.

Cheers.