Complex Report Aggregation Question

[dwnels2]

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!

[bdbits]

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.