Create Reports with Efficient Design
One important rule to remember is that the sheets in the workbook must be calculated in strictly alphabetical order. For example, if you have a workbook with sheets ordered "a", "b", "c", "d" then sheet "d" can have formulas pointing to sheet "a" but not the other way around. This is because the Microsoft Excel dependency tree calculates the sheets in alphabetical order and it will affect the performance when calculating values in all sheets in the workbook. So, if sheet "a" were to have formulas pointing to sheet "d", it would begin by calculating sheet "a", then move on to the next alphabetical sheet. When the calculation of sheets reaches sheet "d", Excel must return to recalculate some of "a", then go back and finish "d".
If optimal performance is required, then the logical alphabetical ordering of sheets must be implemented in the workbook:
Insert all IBM® Cognos® formulas in a sheet named so that it will be the first to be calculated. For example, name it "a.Datasheet", then create worksheets named "b.Report", "c.Report and so on, where you make references to the "a.Datasheet".
Also, start the sheet names with letters, not numbers, since Excel's calculation order with "001", "002", ..., "021" etc. can be erratic. If you have more than 26 sheets, just use the names "aa", "ab", etc. You are still free to have sheet names like "a.Datasheet",..., "z.Results" etc.
IBM Controller is constantly developed to meet new demands. Sometimes the Controller functions are changed, which means that the IBM Cognos Controller Link for Microsoft Excel reports might need to be upgraded. Upgrade to new releases is made automatically on the Controller menu. However, IBM Cognos Controller Link for Microsoft Excel reports can be created in an infinite number of ways which can make the automatic upgrade very complicated. To certify the automatic upgrade of reports in the future they have to meet certain criteria (This is the way to build reports that is supported by Controller when upgrading).
Insert all Controller functions in a sheet with the name "Datasheet" in it, for example "a.Datasheet", thus observing the calculation order of worksheets according to the previous text. Then make references to the data sheet from the other sheets in the workbook. Note that there can only be one Controller function per cell in the data sheet. The number of data sheets can be more than one.
This will also result in the following benefits:
- The report will be easier to maintain.
- Every value will be fetched only once and fewer functions means increased performance.