Aggregating a days between result

[ggustafson]

Good afternoon,

I am working with a relational data source, and in particular, I am working with a table that stores our changes in inventory values in the following format
1. new_on_hand - shows the new quantity on hand as of the Date of the record.
2. old_on_hand - shows the prior quantity on hand as of the Date of the record.
3. date - shows the datestamp of the record

There is not an average quantity on hand stored in this table, so I was trying to create a version of it for my query. I created a query with the following data items to try to simulate the average quantity on hand for a given item.

I keep getting an error message from TechData when I try to post. I'm going to try posting this message in smaller parts to see if that helps. I'll follow up this message with one for the data items I've created, and another for the long SQL error I'm getting afterwards.

[ggustafson]

I still can't figure out why the forum keeps giving me Access Denied Error 15 messages. Probably something in the text of my post causing an issue, I'm not sure. I also tried uploading a text file instead, but I'm getting error messages that the upload path is unavailable. Sigh.

Anyways, the short version is I have created several data items on my way to deriving the Average QOH. Basically I've presorted the data by Date, used days between functions and a running-diff function to give me the number of days each inventory value is held, and then multiplied that by the inventory on hand. When I try to total the resulting data item, I'm getting the following SQL error.

UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T4'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T3'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'Movement___Current8'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]'_days_between' is not a recognized built-in function name. (SQLSTATE=42000, SQLERRORCODE=195)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T1'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T0'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]'_days_between' is not a recognized built-in function name. (SQLSTATE=42000, SQLERRORCODE=195)RSV-SRV-0042 Trace back:RSReportService.cpp(724): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(857): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(311): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(914): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestRSRequest.cpp(1705): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()RSQueryMgr.cpp(530): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(602): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(710): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(289): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(278): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(174): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(165): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1153): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1151): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1108): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1084): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(800): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4484): QFException: CCL_THROW: CoordinationPlanner

Before I add the total function, I'm able to run the query and View Tabulated results without an issue, so I'm guessing this might be related to the Date column being an Identifier and not a Fact, so the resulting data item based on it is giving an error when aggregation is thrown into the mix? But I'm still fairly new to Cognos and SQL, so I was hoping to either get some ideas to workaround this, or a different method or suggestion for trying to get the average QOH with the available components, or someone to point me in the right direction if my understanding of this error is incorrect.

Thank you for any feedback you can offer. I appreciate anyone willing to read this far =).

[MFGF]

I still can't figure out why the forum keeps giving me Access Denied Error 15 messages. Probably something in the text of my post causing an issue, I'm not sure. I also tried uploading a text file instead, but I'm getting error messages that the upload path is unavailable. Sigh.

Anyways, the short version is I have created several data items on my way to deriving the Average QOH. Basically I've presorted the data by Date, used days between functions and a running-diff function to give me the number of days each inventory value is held, and then multiplied that by the inventory on hand. When I try to total the resulting data item, I'm getting the following SQL error.

UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T4'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T3'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'Movement___Current8'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]'_days_between' is not a recognized built-in function name. (SQLSTATE=42000, SQLERRORCODE=195)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T1'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Incorrect syntax near 'T0'. (SQLSTATE=42000, SQLERRORCODE=102)UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]'_days_between' is not a recognized built-in function name. (SQLSTATE=42000, SQLERRORCODE=195)RSV-SRV-0042 Trace back:RSReportService.cpp(724): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(857): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(311): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(914): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestRSRequest.cpp(1705): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()RSQueryMgr.cpp(530): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(602): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(710): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(289): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(278): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(174): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(165): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1153): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1151): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1108): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1084): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(800): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4484): QFException: CCL_THROW: CoordinationPlanner

Before I add the total function, I'm able to run the query and View Tabulated results without an issue, so I'm guessing this might be related to the Date column being an Identifier and not a Fact, so the resulting data item based on it is giving an error when aggregation is thrown into the mix? But I'm still fairly new to Cognos and SQL, so I was hoping to either get some ideas to workaround this, or a different method or suggestion for trying to get the average QOH with the available components, or someone to point me in the right direction if my understanding of this error is incorrect.

Thank you for any feedback you can offer. I appreciate anyone willing to read this far =).

Hi,

I assume you are getting these error when you try to summarise a calculated item you defined in your report? The first things to check are the Detail Aggregation and Summary Aggregation property values of this calculated item (in the properties pane). If they are set to Automatic or None, try setting both to Total.

I'm not quite following what you are doing in the report here, but some of the errors seem to imply that your data source is receiving a _days_between() function call in the SQL. This isn't ever going to work as it's a Cognos internal function not a database function. Are you manually coding SQL in your report or is the query engine just getting confused here?

Cheers!

MF.

[ggustafson]

Hi,

I assume you are getting these error when you try to summarise a calculated item you defined in your report? The first things to check are the Detail Aggregation and Summary Aggregation property values of this calculated item (in the properties pane). If they are set to Automatic or None, try setting both to Total.

I'm not quite following what you are doing in the report here, but some of the errors seem to imply that your data source is receiving a _days_between() function call in the SQL. This isn't ever going to work as it's a Cognos internal function not a database function. Are you manually coding SQL in your report or is the query engine just getting confused here?

Cheers!

MF.

No, I'm not manually coding any SQL, so the query engine and I are both getting confused =P.

Hopefully a little more detail helps. I have several different data items in the mix, and I'm not sure which of them to change the aggregate functions on. They are currently all set to the default settings of Automatic for Aggregate Function and Rollup Aggregate Function. They are also set as "Yes" on the Detail property. I'm not sure if that's also related. The description of that field seems to state it's a trigger for whether to use the item for aggregations or not.

1. [Days to Today] - This one uses the days between function to give me the number of days between the datestamp for each record and current_date.
_days_between(current_date,[Date])

2. [365 Days Ago] - I have a detail filter that limits the data to the past 365 days, so this data item just gives me the earliest date for the range (current_date - 365)

3. [Min Date for Item] - This gives me the oldest date for any given item
minimum([Date] for [Item])

4. [Running Difference for Item] - This gives me the # of days between each inventory value change for each item
Case
When [Date]=[Min Date for Item]
Then _days_between([Min Date for Item],[365 Days Ago]
Else abs(running-difference([Days to Today] for [Item]
End

5. [Quantity * # of Days] - This is to multiply the appropriate inventory value by the number of days the value was held.
Case
When [Date]=[Min Date for Item]
Else [Old On Hand]*[Running Difference for Item]
End

I can run the query without issue with all of the above data items in place. Adding the next step is where I begin getting the SQL error. I'm trying to get a total for item#5 above, so that I can then divide the total by 365 to give me the average quantity on hand for each item.
total([Quantity * # of Days] for [Item])

I've tried changing the Aggregate Function and Rollup Aggregate Function properties both to Total for the [Quantity * # of Days] data item but I am still getting the same error.

Thank you for trying to help, I appreciate it. Please let me know if I can help clarify anything else.

[ggustafson]

I just noticed I had a line missing from item # 5 [Quantity * # of Days]. It is missing in the forum post, not in my data item. I tried editing it in, but the forum is giving me another Access Denied Error 15 message. I'm trying a separate post to see if that makes a difference.
The missing line is my Then statement of New On Hand times the Running Difference.

[MFGF]

No, I'm not manually coding any SQL, so the query engine and I are both getting confused =P.

Hopefully a little more detail helps. I have several different data items in the mix, and I'm not sure which of them to change the aggregate functions on. They are currently all set to the default settings of Automatic for Aggregate Function and Rollup Aggregate Function. They are also set as "Yes" on the Detail property. I'm not sure if that's also related. The description of that field seems to state it's a trigger for whether to use the item for aggregations or not.

1. [Days to Today] - This one uses the days between function to give me the number of days between the datestamp for each record and current_date.
_days_between(current_date,[Date])

2. [365 Days Ago] - I have a detail filter that limits the data to the past 365 days, so this data item just gives me the earliest date for the range (current_date - 365)

3. [Min Date for Item] - This gives me the oldest date for any given item
minimum([Date] for [Item])

4. [Running Difference for Item] - This gives me the # of days between each inventory value change for each item
Case
When [Date]=[Min Date for Item]
Then _days_between([Min Date for Item],[365 Days Ago]
Else abs(running-difference([Days to Today] for [Item]
End

5. [Quantity * # of Days] - This is to multiply the appropriate inventory value by the number of days the value was held.
Case
When [Date]=[Min Date for Item]
Else [Old On Hand]*[Running Difference for Item]
End

I can run the query without issue with all of the above data items in place. Adding the next step is where I begin getting the SQL error. I'm trying to get a total for item#5 above, so that I can then divide the total by 365 to give me the average quantity on hand for each item.
total([Quantity * # of Days] for [Item])

I've tried changing the Aggregate Function and Rollup Aggregate Function properties both to Total for the [Quantity * # of Days] data item but I am still getting the same error.

Thank you for trying to help, I appreciate it. Please let me know if I can help clarify anything else.

Hi,

For your #2 calculation (365 days ago) are you actually subtracting an integer (365) from a date (current_date) or are you using the _add_days() function to do this? If you are subtracting an integer from a date you're going to get a very odd result. Use the _add_days() function instead - _add_days(current_date, -365)

I notice you are then using this as one of the arguments in your _days_between function. This might be the root of your issue here. Try fixing this next and see if you manage to get any further.

Oh, and apologies, this is Cognos 10 not Cognos Analytics, so the properties I mentioned before should have been Aggregate Function and Rollup Aggregate Function (not Detail Aggregation and Summary Aggregation). Sorry if this caused you any confusion.

Cheers!

MF.

[ggustafson]

That did it! Thank you very much! I wasn't aware of the issue with combining integer manipulation with a date field. I'll keep that in mind going forwards. Thank you so much for your help!

[MFGF]

That did it! Thank you very much! I wasn't aware of the issue with combining integer manipulation with a date field. I'll keep that in mind going forwards. Thank you so much for your help!

Excellent! Glad that worked. :)

Cheers!

MF.

[MFGF]

Excellent! Glad that worked. :)

Cheers!

MF.

Just for completeness, you could also have used the _add_years() function here, or the _add_months() function. It's worth browsing through the functions in the expression editor to get an idea of what they do and which are available.

Cheers!

MF.

[ggustafson]

I'm encountering another problem, and I'm hoping someone can point me in the right direction.

The below are additional data items I've created.

6. [Total QOH*DOH] - This is to provide the held inventory values multiplied by the # of days the value was held, totaled for each item.
total([Quantity * # of Days] for [Item #])

7. [Average QOH] - This provides the average quantity on hand for each item by dividing the above item by 365 days.
[Total QOH*DOH]/365

8. Total Average QOH per Buyer - This just totals the average QOH for each item for each buyer.
total([Average QOH] for [Buyer])

9. [Shipped Quantities] - This identifies the value changes related to our shipped/sold quantities.
Case
When   [Movement Code]='SH'
Then   -[Movement Quantity]
Else   null
End

10. [Total Shipped per Buyer] - This totals the shipped/sold quantities for each buyer.
total([Shipped Quantities] for [Buyer])

11. [Turns per Buyer] - This is dividing the total quantity shipped/sold for each buyer by the average QOH of inventory for the same buyer.
[Total Shipped per Buyer]/[Total Average QOH per Buyer]

All of the data items are working and reporting correctly when I View Tabular Data, but when I drop Buyer, Total Average QOH per Buyer, Total Shipped per Buyer, and Turns per Buyer in a List report, Total Average QOH per Buyer is reporting a different value, about 3.5 to 4.5 times more than what was shown in Tabular Data. Total Shipped per Buyer is still reporting the same value as shown in Tabular Data.

Any ideas why one of the two data items would change when they are both reporting for the same context of Buyer? I appreciate any ideas/suggestions.

[MFGF]

I'm encountering another problem, and I'm hoping someone can point me in the right direction.

The below are additional data items I've created.

6. [Total QOH*DOH] - This is to provide the held inventory values multiplied by the # of days the value was held, totaled for each item.
total([Quantity * # of Days] for [Item #])

7. [Average QOH] - This provides the average quantity on hand for each item by dividing the above item by 365 days.
[Total QOH*DOH]/365

8. Total Average QOH per Buyer - This just totals the average QOH for each item for each buyer.
total([Average QOH] for [Buyer])

9. [Shipped Quantities] - This identifies the value changes related to our shipped/sold quantities.
Case
When   [Movement Code]='SH'
Then   -[Movement Quantity]
Else   null
End

10. [Total Shipped per Buyer] - This totals the shipped/sold quantities for each buyer.
total([Shipped Quantities] for [Buyer])

11. [Turns per Buyer] - This is dividing the total quantity shipped/sold for each buyer by the average QOH of inventory for the same buyer.
[Total Shipped per Buyer]/[Total Average QOH per Buyer]

All of the data items are working and reporting correctly when I View Tabular Data, but when I drop Buyer, Total Average QOH per Buyer, Total Shipped per Buyer, and Turns per Buyer in a List report, Total Average QOH per Buyer is reporting a different value, about 3.5 to 4.5 times more than what was shown in Tabular Data. Total Shipped per Buyer is still reporting the same value as shown in Tabular Data.

Any ideas why one of the two data items would change when they are both reporting for the same context of Buyer? I appreciate any ideas/suggestions.

Hi,

Is this a report based on a package? It sounds like you may be getting double-counting, which can often be the result of an issue in the underlying model. Just to be clear, your report just uses a single query and that query feeds data into a List? There are no joined queries in the report?

Cheers!

MF.

[ggustafson]

This is based on a package, yes. The query I'm working in is the result of a join, so that's possibly the source of my data issue. Our data only correctly identifies our buyer for each item in one particular warehouse, so I'm querying all items and the matching buyer for that warehouse, and then joining that to the query that has the new-on-hand and old-on-hand data for all warehouses to provide the buyer for each movement.

Query 1: [Buyer per Item] - queries all items along with their appropriate buyer. The only filter on the data is to limit the records to the one warehouse with correct buyer values.
Query 2: [Movement & Current] - A union between two queries; one showing the current on hand for all items and warehouses, same table as the data in Query 1, but without the warehouse filter. The second query shows item movement, with new-on-hand and old-on-hand data for each record, so the full union shows any items that had movement of any kind in the past 365 days, along with the current snapshot of all items and their quantities on hand.

The two queries are then joined using the item_num field with the following relationships/cardinality.

1. Each "Buyer per Item" has one or more "Movement & Current".
2. Each "Movement & Current" has zero or one "Buyer per Item" (outer join). {I've also tried this set as each M&C has one and only one BpI, because the one warehouse with correct buyer data is our distribution center, so it should contain all items, but both settings yield the same result}

I hope this additional information is helpful. I'm not sure where to look or how to identify the source of the data that is changing between Tabular Results and the report summary.

Thank you for taking the time to help educate me. I appreciate your expertise.