Hi Folks-
I'm a relatively new to Cognos, and am using Cognos 8 Report Studio. Here's my issue:
I'm attempting to create an inventory indicator report at an item level which adds a constant value and a value that will be aggregated by future days, and subtracts a different constant value plus another value aggregated by future days. I would like the report to return only the first associated date where a negative value is returned, but I don't need anything else. Here's the idea in another way:
[Inv On-Hand (which is a set value) + Inv to be received by receive date (tomorrow, then tomorrow+day after, etc)] - [Reserved Inv (also a set value) + Sales to be shipped by ship date (same structure as received inv)]
I'm trying to avoid creating individual data items for each aggregation and adding 21 columns to the report, as I'm looking over a 3 week period. Since this will also be by the item, of which we have a few thousand, I also don't want to manually hide/suppress columns for each item.
Any thoughts on where to begin, or how to refocus this and get a similar result? The only needed return is the item and the date we'll run out of inv, how much we're short would be nice but isn't necessary. Thanks folks!
For the calculations, how to go about it partly depends on whether you are dealing with a relational or a dimensional source. Part of your solution may involve creating an item using rank(), then a detail filter comparing that to the first x rows you want. You might need to build multiple queries within the report and join them together (this also depends on data source/structure).
I should think a crosstab will eliminate any need to maintain individual columns.
Hope this gets you pointed in the right direction.