Hi All,

We are experiencing an issue with the refresh functionality in the latest version of Planning Analytics for Excel (PAfE).

Environment:

• PAfE: Latest version installed
• Workbook: Contains 10 sheets, each populated with data from TM1 cubes using DBRW formulas

Issue:
When we attempt to refresh a single sheet individually, it refreshes correctly and returns the expected data from the cube.

However, when we use "Refresh Active Book" or "Refresh All Books", none of the sheets are refreshed - the data chances to #value. The only way to get updated data is by refreshing each sheet one at a time manually, which is not practical for a workbook with 10 sheets.

Expected Behavior:
The "Refresh Active Book" and "Refresh All Books" options should refresh all DBRW formulas across all sheets in the workbook simultaneously, similar to how it worked in IBM Cognos TM1 Perspectives.

Workaround:
Currently, we are refreshing each sheet individually one by one, but this is time-consuming and defeats the purpose of the bulk refresh feature.

Question:
Is this a known issue or limitation in the current version of PAfE? Is there a configuration setting or fix available to restore the bulk refresh behavior that existed in Perspectives?

Thanks

Hi Celso,

This behavior is a known defect in the current release of Planning Analytics for Excel (PAfE) when using DBRW formulas across multiple sheets. The "Refresh Active Book" and "Refresh All Books" options do not consistently trigger recalculation for DBRW, which results in #VALUE errors instead of updated cube data.

🔎 Why it happens

·         DBRW formulas in PAfE rely on a different refresh mechanism than Perspectives.

·         Bulk refresh commands lose the connection context when applied across multiple sheets, so the formulas fail to resolve.

·         This is not a configuration issue on your side - it is a limitation in the product at present.

✅ Workarounds

·         Refresh Sheet: Refresh each sheet individually (works reliably but is manual).

·         VBA Automation: Use the COM add‑in automation to loop through sheets and call RefreshSheet. Example:

vba

Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet

This can be scripted to refresh all sheets sequentially.

·         Recreate Workbook: If the workbook was migrated from Perspectives, rebuilding sometimes resolves broken DBRW links.

·         Check Installation: Ensure no legacy Cognos Office add‑ins are still installed, as they can interfere with PAfE refresh.

⚠️ Notes

·         Performance is slower in PAfE compared to Perspectives when refreshing large DBRW workbooks.

·         IBM has acknowledged this gap and is working on improvements in upcoming PAfE versions.

·         For heavy reporting, consider moving to Dynamic Reports or Explorations, which have more stable refresh handling.

📌 Recommendation

Until IBM releases a fix, the most practical solution is to implement a VBA macro to automate sheet‑by‑sheet refresh. This restores the "bulk refresh" behavior you were used to in Perspectives.

In summary: It's not a configuration error - it's a product limitation. Use VBA automation or refresh sheets individually as a workaround, and monitor IBM Support for updates in future PAfE releases.

Hi Saif,

The above is not quite accurate and is misleading - maybe the AI model is a bit behind the times.

Universal Reports are the way to go if you are looking for better performance, not Dynamic Reports as these still rely on DBRWs and would have the same issues Celso is trying to remedy.

UR's use MDX and thus do not rely on DBRWs. You can however add in your own columns to add DBRA, TM1Val etc. if you need to show attributes in the report or read values from other areas/cubes.

Keep in mind that there are two flavours of URs, one dynamic on rows AND columns and the other static.

Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer.Application("COR", "1.1").RefreshSheet