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

Cognos Newbie Needs Help...

Started by C4rtm4N, 07 Mar 2011 04:41:10 AM

Previous topic - Next topic

C4rtm4N

Hi

We have a copy of ReportNet 1.1 deployed with a system that we use & it's heavily integrated without much access to the back end database.  I need to customise one of our current reports but am running into issues I'm guessing due to my inexperience with Cognos so hopefully someone here will be able to guide me. Due to the lack of DB access realistically I can't change the data that is being presented to me so all calculations will need to be done in the report itself.

I'm presented with some data which essentially has the fields of Date, Time interval, Forecast Calls, Actual  Calls and I need to be able to group things together to show both an end of day "forecast accuracy" and whether each interval was within a +/- 10% range (with a total % for those that were).  I'm fine with this & just added calculation columns to the report, the final one having an if statement to check to see whether it was within the acceptable range, showing either a 1 or a 0.

I also however need to "scale" the forecast so that I can check how accurate the intra day spread of work would have been if the person doing the forecasting had been exactly correct which is where I'm having the issues.

After unsuccessfully playing around with child datasets, I created a 2nd query for my report & removed the time interval element from it, just keeping the day so by day I still get the forecast accuracy, then added a master - detail relationship so that [Main].[Day of Week]=[Day].[Day of Week].  I was hoping that I would then be able to simply add calculations based upon both queries to the same list but quickly realised that this was not so.  I worked around this by adding 3 sub lists that calculate the scaled forecast, scaled forecast accuracy & finally whether the accuracy was within +/- 10%. Each of these lists is shown in a cell in the main list against the time of day element, a layout calculation then brings the interval data for that period from the main query in & combines it with the day data from the 2nd to give me the calculated fields that I need.  So far so good but finally I need to be able to total my +/- 10% column at the end of each day which is where I can't figure out how to pass the data from the sub lists into the main one or total up all of the 1's & 0's that I see so that I can get an overall score.  In Crystal Reports I could have easily done this using a variable (& would have manged to get this to work without a 2nd query or subreport) but I can't see any way of doing this in Cognos. 

How can I do this or is there another method that I should have employed that would do away the need for sub-lists?

Regards

Steve

C4rtm4N

LOL - maybe I should ahve looked closer into how to create joins before posting!

All sorted - I managed to add a subquery & link that through to the main one so that I could get all of my data in the same dataset