Hi Community,

I'm looking for some guidance on what approach to take in the short and medium term to help address the business requirements I have in my role as PA Lead at a new company.

A little context: We have IBM Planning Analytics on Cloud that is used by the Finance function for both month end reporting processes and for forecasting and budgeting. Where I'm looking for guidance from the PA Community is around the reporting side of things as we have the requirement to produce monthly reports from the PA data. 

At the moment, we have a number of fixed templates that are produced in PAfE using DBRW - they are not dynamic other than some simple dropdowns that vary the time periods and versions displayed in the report. A lot of the structure and calculations are done using native excel formulas. Most views are the same with differing filters applied per division, region, cluster etc but we have many sheets per workbook to achieve this. Each sheet will have rows/columns grouped etc for some degree of "drill down" but this is just done by hide/show of rows using excel feature and not the PA functionality.

The Finance team will update these reports, snapshot the values (or do a paste special values) and save the Excel file. This is then distributed via email or shared location to be merged into various presentations or other report packs.

I know that the maintenance of these reports isn't ideal at all. I'd like to improve things incrementally using best practices so I've been considering Dynamic Reports, updating the model to improve reporting calculations, updating structures in the model to ensure we have the right elements.

My request really is around the introduction of Universal Reports to the ways of working. I'd like to take advantage of Hierarchies in the model itself to really simplify the many confusing alternative rollups we have in a few of our key dimensions but the way we report currently using the DBRW formulas - we cannot switch. Is this the use case for UR and the general direction of travel for the excel reporting side of things?

What would anyone recommend for this use case given we require data to be distributed wider than the licence holders of PA (costs would stop us from rolling out licenses wider than the Finance function right now)? My instinct is to try and provide as much flexibility and dynamic analysis from the toolset as I can for licenced users but still provide some Excel-based analysis/dynamic features.

Any help/comments would be appreciated.

Chris

If this is still available I would suggest starting here. 

https://community.ibm.com/community/user/businessanalytics/discussion/webinar-planning-analytics-for-excel-new-features-in-universal-reports?ReturnUrl=%2fcommunity%2fuser%2fbusinessanalytics%2fcommunities%2fcommunity-home%2fdigestviewer%3fcommunitykey%3d8fde0600-e22b-4178-acf5-bf4eda43146b

And then look in Youtube et al for some of the discussions published by IBM Partners

No doubt there is a long term roadmap for the enhancement of UR's and hierarchy worksheet functions but the detail and timeline are not clear and that makes it difficult for you to build a solid plan. 

There are many too many aspects to your question about supporting hierarchies to be able to answer succinctly but here are some thinking points. 

1) Firstly , if you have rules and you wish to implement hierarchies then you will need to rework the rules - this may be an involved activity. Also if hierarchy based cube views are to be used as data sources for TI's then you need to consider that. 

2) Views and Explorations have always supported hierarchies. Quick Reports supported hierarchies but were very limited.  Universal reports support hierarchies. snapshot of the PA component exists. 

3) Hierarchy reports are currently just datasets returned by a MDX statement - This is equivalent to just a base case use of a dynamic report. So the usefulness of that depends a lot on whether your reporting needs are limited to that. 

4) TM1Val was released I think in December. But it doesn't accept input like DBR/DBRW

5)  TM1Set and DEFINECALC support hierarchies but other than there there are limited worksheet functions to support hierarchies compared to the list of the legacy worksheet functions ELCOMP etc   https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=reference-worksheet-functions

6) Other than hierarchy support there are many things dynamic reports can do which UR's at this stage cannot

7) If you don't need the workbooks rendered as websheets then maybe you can use the existing functions and build solutions using code

8) At least one IBM partner has built their own add-in (With similar functions to dynamic reports) to support hierarchies

------------------------------
John O'Leary

Original Message:
Sent: Fri February 09, 2024 11:00 AM
From: Chris Davidson
Subject: Thoughts on Roadmap for Reporting

Hi Community,

I'm looking for some guidance on what approach to take in the short and medium term to help address the business requirements I have in my role as PA Lead at a new company.

A little context: We have IBM Planning Analytics on Cloud that is used by the Finance function for both month end reporting processes and for forecasting and budgeting. Where I'm looking for guidance from the PA Community is around the reporting side of things as we have the requirement to produce monthly reports from the PA data. 

At the moment, we have a number of fixed templates that are produced in PAfE using DBRW - they are not dynamic other than some simple dropdowns that vary the time periods and versions displayed in the report. A lot of the structure and calculations are done using native excel formulas. Most views are the same with differing filters applied per division, region, cluster etc but we have many sheets per workbook to achieve this. Each sheet will have rows/columns grouped etc for some degree of "drill down" but this is just done by hide/show of rows using excel feature and not the PA functionality.

The Finance team will update these reports, snapshot the values (or do a paste special values) and save the Excel file. This is then distributed via email or shared location to be merged into various presentations or other report packs.

I know that the maintenance of these reports isn't ideal at all. I'd like to improve things incrementally using best practices so I've been considering Dynamic Reports, updating the model to improve reporting calculations, updating structures in the model to ensure we have the right elements.

My request really is around the introduction of Universal Reports to the ways of working. I'd like to take advantage of Hierarchies in the model itself to really simplify the many confusing alternative rollups we have in a few of our key dimensions but the way we report currently using the DBRW formulas - we cannot switch. Is this the use case for UR and the general direction of travel for the excel reporting side of things?

What would anyone recommend for this use case given we require data to be distributed wider than the licence holders of PA (costs would stop us from rolling out licenses wider than the Finance function right now)? My instinct is to try and provide as much flexibility and dynamic analysis from the toolset as I can for licenced users but still provide some Excel-based analysis/dynamic features.

Any help/comments would be appreciated.

Chris

Hi Community,

I'm looking for some guidance on what approach to take in the short and medium term to help address the business requirements I have in my role as PA Lead at a new company.

A little context: We have IBM Planning Analytics on Cloud that is used by the Finance function for both month end reporting processes and for forecasting and budgeting. Where I'm looking for guidance from the PA Community is around the reporting side of things as we have the requirement to produce monthly reports from the PA data. 

At the moment, we have a number of fixed templates that are produced in PAfE using DBRW - they are not dynamic other than some simple dropdowns that vary the time periods and versions displayed in the report. A lot of the structure and calculations are done using native excel formulas. Most views are the same with differing filters applied per division, region, cluster etc but we have many sheets per workbook to achieve this. Each sheet will have rows/columns grouped etc for some degree of "drill down" but this is just done by hide/show of rows using excel feature and not the PA functionality.

The Finance team will update these reports, snapshot the values (or do a paste special values) and save the Excel file. This is then distributed via email or shared location to be merged into various presentations or other report packs.

I know that the maintenance of these reports isn't ideal at all. I'd like to improve things incrementally using best practices so I've been considering Dynamic Reports, updating the model to improve reporting calculations, updating structures in the model to ensure we have the right elements.

My request really is around the introduction of Universal Reports to the ways of working. I'd like to take advantage of Hierarchies in the model itself to really simplify the many confusing alternative rollups we have in a few of our key dimensions but the way we report currently using the DBRW formulas - we cannot switch. Is this the use case for UR and the general direction of travel for the excel reporting side of things?

What would anyone recommend for this use case given we require data to be distributed wider than the licence holders of PA (costs would stop us from rolling out licenses wider than the Finance function right now)? My instinct is to try and provide as much flexibility and dynamic analysis from the toolset as I can for licenced users but still provide some Excel-based analysis/dynamic features.

Any help/comments would be appreciated.

Chris

Hi Community,

I'm looking for some guidance on what approach to take in the short and medium term to help address the business requirements I have in my role as PA Lead at a new company.

A little context: We have IBM Planning Analytics on Cloud that is used by the Finance function for both month end reporting processes and for forecasting and budgeting. Where I'm looking for guidance from the PA Community is around the reporting side of things as we have the requirement to produce monthly reports from the PA data. 

At the moment, we have a number of fixed templates that are produced in PAfE using DBRW - they are not dynamic other than some simple dropdowns that vary the time periods and versions displayed in the report. A lot of the structure and calculations are done using native excel formulas. Most views are the same with differing filters applied per division, region, cluster etc but we have many sheets per workbook to achieve this. Each sheet will have rows/columns grouped etc for some degree of "drill down" but this is just done by hide/show of rows using excel feature and not the PA functionality.

The Finance team will update these reports, snapshot the values (or do a paste special values) and save the Excel file. This is then distributed via email or shared location to be merged into various presentations or other report packs.

I know that the maintenance of these reports isn't ideal at all. I'd like to improve things incrementally using best practices so I've been considering Dynamic Reports, updating the model to improve reporting calculations, updating structures in the model to ensure we have the right elements.

My request really is around the introduction of Universal Reports to the ways of working. I'd like to take advantage of Hierarchies in the model itself to really simplify the many confusing alternative rollups we have in a few of our key dimensions but the way we report currently using the DBRW formulas - we cannot switch. Is this the use case for UR and the general direction of travel for the excel reporting side of things?

What would anyone recommend for this use case given we require data to be distributed wider than the licence holders of PA (costs would stop us from rolling out licenses wider than the Finance function right now)? My instinct is to try and provide as much flexibility and dynamic analysis from the toolset as I can for licenced users but still provide some Excel-based analysis/dynamic features.

Any help/comments would be appreciated.

Chris

------------------------------
Chris Davidson
------------------------------

Hi Community,

I'm looking for some guidance on what approach to take in the short and medium term to help address the business requirements I have in my role as PA Lead at a new company.

A little context: We have IBM Planning Analytics on Cloud that is used by the Finance function for both month end reporting processes and for forecasting and budgeting. Where I'm looking for guidance from the PA Community is around the reporting side of things as we have the requirement to produce monthly reports from the PA data. 

At the moment, we have a number of fixed templates that are produced in PAfE using DBRW - they are not dynamic other than some simple dropdowns that vary the time periods and versions displayed in the report. A lot of the structure and calculations are done using native excel formulas. Most views are the same with differing filters applied per division, region, cluster etc but we have many sheets per workbook to achieve this. Each sheet will have rows/columns grouped etc for some degree of "drill down" but this is just done by hide/show of rows using excel feature and not the PA functionality.

The Finance team will update these reports, snapshot the values (or do a paste special values) and save the Excel file. This is then distributed via email or shared location to be merged into various presentations or other report packs.

I know that the maintenance of these reports isn't ideal at all. I'd like to improve things incrementally using best practices so I've been considering Dynamic Reports, updating the model to improve reporting calculations, updating structures in the model to ensure we have the right elements.

My request really is around the introduction of Universal Reports to the ways of working. I'd like to take advantage of Hierarchies in the model itself to really simplify the many confusing alternative rollups we have in a few of our key dimensions but the way we report currently using the DBRW formulas - we cannot switch. Is this the use case for UR and the general direction of travel for the excel reporting side of things?

What would anyone recommend for this use case given we require data to be distributed wider than the licence holders of PA (costs would stop us from rolling out licenses wider than the Finance function right now)? My instinct is to try and provide as much flexibility and dynamic analysis from the toolset as I can for licenced users but still provide some Excel-based analysis/dynamic features.

Any help/comments would be appreciated.

Chris