If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Take summary information from a crosstab and use in a list

Started by pcs, 04 Sep 2015 04:28:10 PM

Previous topic - Next topic

pcs

I have some reporting that I would like to do that involves getting some summary information (for a 5 year period) like this:

Product   Manufacturing Location   Dest State   Dest City     Avg Time to Ship Rail Car to Dest   Avg Time to Return Rail Car from Dest
--------   --------------------------   ------------   ----------     -------------------------------------   -------------------------------------------
Prod1     Loc1                              CA               San Diego   7.5                                              8.2
Prod2     Loc2                              ID                Boise          4.2                                             6.5

etc.

The crosstab above would return the weighted average for the Avg Time to Ship Rail Car to Dest and Avg Time to Return Rail Car from Dest for each Product -> Manufacturing Location -> Dest State -> Dest City combination

I want to then create a list report that shows all in transit shipments along with some calculations measures for the days (shipment is on the way to the customer, at the customer but not yet unloaded, unloaded and ready to return, on the way back).  I would pull the averages from the crosstab above in order to create a query calculation that would add those averages to dates I have in my list in order to project when a rail car might return.  Is this possible to do in Report Studio with dimensionalized data in Cognos 10.2.1?  Thanks.

My list report might look like this:

RailCar   Product   Manufacturing Location   Dest State   Dest City   Date of Shipment  Days to Customer  Days at Customer  Return Days
--------   --------   --------------------------   -----------    ----------   -------------------   -------------------   --------------------  -------------

I am a new Report Studio user, so I haven't figured out exactly how to solve this one.  I wondered about a Master Detail relationship.  I am pulling this information from a dimensional model if that helps