Cognos Controller

 View Only

  • 1.  Share Cognos controller updated Excel workbook.

    Posted Thu March 21, 2019 04:38 AM
    Hi


    Our financial manager is responsible to update the company packages with Cognos Controller, when this is done, he often needs to share it with our Financial director.
    The problem is that when our CFO opens Excel it autoruns the formulas and all the content change to #Value! as he doesn't have access to Cognos Controller.

    How can we fix that?

    Thanks in advance

    John



    ------------------------------
    John Friberg
    ------------------------------

    #CognosController


  • 2.  RE: Share Cognos controller updated Excel workbook.

    Posted Mon June 28, 2021 12:04 PM
    Hi John,

    Probably a bit late, what we do in our Excel reports is we have a data sheet that pulls back all the data from Cognos Controller (with the fgetval formula) and then we have another sheet called data values in the Excel report which is an exact replica of the data sheet but created using paste values. All the formulas in our Excel reports link to the data values sheet. A user will refresh the Excel pack and run a macro to paste values. The Excel pack is then saved and can be viewed by anyone regardless of whether they have access to Cognos Controller as although the data sheet goes to #VALUE! the data value sheet doesn't and all tabs within the report are linked to the data values sheet.

    Thanks
    Natascja​

    ------------------------------
    Natascja Barsanti
    ------------------------------

    Original Message


  • 3.  RE: Share Cognos controller updated Excel workbook.

    Posted Tue June 29, 2021 01:54 AM
    Hi John,

    I have seen this problem many many times over the years - it is extremely common. By default, Excel will typically try to run (update / autocalculate) an Excel spreadsheet when it is opened (especially from an email).

    The solution is very simple - before the Controller end user saves their Excel spreadsheet, they should untick the box which makes it autocalculate when opening
    => Then when the non-Controller user opens the Excel spreadsheet (typically from an email attachment) it will not try to run/refresh/update itself.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Specifically, the Controller end user must click "File - Options" - "Formulas" - then:
    • change "Workbook Calculation" setting from 'Automatic' to 'Manual'
    • Untick "Recalculate workbook before saving"
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    See "Method #1" here for more details: https://www.ibm.com/support/pages/excel-cell-values-change-name-or-zeros-when-spreadsheet-xlsx-or-xls-file-opened

    @Natascja - Lovely to hear from you - hope you are well! :-)

    Good luck!
    -Richard.

    ------------------------------
    Richard Collins
    ------------------------------

    Original Message