Formulas should be typed by hand adjacent to Cognos list. When data in Cognos list is refreshed, formulas will recalculate themselves.
Alternative is to refresh Cognos list with button and VBA. Then you can use the VBA behind the button to replace "#" with "=".
Original Message:
Sent: Mon December 11, 2023 02:33 AM
From: Samuel SUM
Subject: Report on EXCEL with Formula
Thanks for your clues.
I have tried to use Cognos for office but it seems to be a text cell. Is there any special treatment for the formula field in Cognos Analytics Report Studio?
I try to create a formula as a text for a calculated field / data field as shown below. For rich text item, it doesn't appear in Excel.
=B3+C3
It will give me a single quote ' additionally before =B3+C3.
------------------------------
Samuel SUM
Original Message:
Sent: Fri December 08, 2023 02:53 AM
From: N D
Subject: Report on EXCEL with Formula
You could perhaps use "Cognos for Office" as an alternative. Using the Excel Add-in, you can create complex Excel workbooks and just update special ranges with data from Cognos at runtime
------------------------------
N D
Original Message:
Sent: Sun December 03, 2023 06:20 AM
From: Samuel SUM
Subject: Report on EXCEL with Formula
I do understand that it is nearly impossible to have Exccel formula working with the 2007 format (XML) output.
I had tried to do something like printing them as "Rich Text" item as in Cognos 10 with "EXCEL 97" output. It is no longer working in Cognos 11 / 12.
Sample Output of Cognos in Excel:
Quantity | Unit Cost | Total Cost
===================================
256 | 15.62 | =(B2*C2)
92 | 49.69 | =(B3*C3)
162 | 80 | =(B4*C4)
Does anyone with work around? It is because the finance / accounting users will use the Excel to do other calculation. Please share your clues if any. Actually, I have tried a number of Python library openpyxl / win32com. Openpyxl was failed to create a "real" xls and win32com can create format "56" /
"39" (Excel 97). The formulas are still reading as text with win32com.
------------------------------
Samuel SUM
------------------------------