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 Fri March 22, 2019 05:23 AM
    Hi John

    For the moment the only way to do it is manually via the step copy/paste values.

    We are looking for a future release the possibility to automate this process.

    It has been requested by an other customer. You can vote for this idea by connecting via this link

    https://ibmanalytics.ideas.aha.io/ideas/BACC-I-159


    Regards

    Max

    ------------------------------
    Maximilien de Chestret
    ------------------------------



  • 3.  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
    ------------------------------



  • 4.  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
    ------------------------------