{"id":4284,"date":"2024-02-14T20:42:19","date_gmt":"2024-02-14T20:42:19","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/?p=4284"},"modified":"2026-01-16T15:44:42","modified_gmt":"2026-01-16T15:44:42","slug":"pafe-reporting-hints","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/pafe-reporting-hints\/","title":{"rendered":"What Helpful Tips and Best Practices Should I Know for Building Reports with Planning Analytics for Excel (PAfE)?"},"content":{"rendered":"

IBM Planning Analytics for Microsoft Excel\u00a0(or \u201cPAfE\u201d) is a Microsoft Excel-based tool that can be used to build complex, multiple-worksheet, multiple-query reports against multiple databases. You can use the information here as a quick start guide (and then a reference) providing helpful suggestions on using PAfE.<\/p>\n

If you haven\u2019t yet installed PAfE and need help with that (or you want to upgrade to a newer version), you can find that information here<\/a>.<\/p>\n

PAfE Version<\/strong><\/p>\n

Newer versions of PAfE are routinely made available to address reported issues, offer updates to features and sometimes even add new functionalities.<\/p>\n

It is highly recommended to keep your version of PAfE current<\/u> and to stay current, it is important to know which version of PAfE you are currently using. To find which version you have installed, you can follow these steps:<\/p>\n

    \n
  1. Click the\u00a0IBM Planning Analytics<\/strong>tab<\/strong> to open the\u00a0IBM Planning Analytics<\/li>\n
  2. Click the\u00a0Help<\/strong>drop down (located in the\u00a0Getting Started<\/strong>\u00a0group).<\/li>\n
  3. Click\u00a0About<\/strong>.<\/li>\n<\/ol>\n

    \"\"<\/p>\n

    Once you verify the version of PAfE you have, you can check online here<\/a> for that particular versions highlighted features and fixes.<\/p>\n

    Creating a Connection<\/strong><\/p>\n

    To create reports with PAfE using Planning Analytics data, you need to first create a connection<\/a>.<\/strong> To create a connection, you can follow these steps:<\/p>\n

      \n
    1. Open the\u00a0IBM Planning Analytics<\/strong>ribbon and click\u00a0Options<\/strong>\u00a0to open the\u00a0Options\u00a0dialog box.<\/li>\n
    2. In the navigation pane of the\u00a0Options<\/strong>dialog box, click\u00a0IBM<\/strong>.<\/strong><\/li>\n
    3. Click Add<\/strong>to create a new connection to a datasource.\"\"<\/li>\n
    4. On the Add\/Edit connection dialog, select the datasource \u201cIBM Planning Analytics\u201d as the Datasource Type.<\/strong><\/li>\n
    5. In the Connection URL<\/strong>box, enter the URL of the Planning Analytics server that you want to access. This will be the Connection URL<\/strong> for the Planning Analytics Workspace (PAW) Server (either the computer name or IP address). Ask your administrator for this if you are not sure (it will be a https\u2026). While you are at it, ask if there are URLs for your production (live) environment AND a test or sandbox environment.<\/li>\n
    6. In the\u00a0Friendly Name<\/strong>box, enter a name for your new connection.<\/li>\n
    7. Click\u00a0Test Connection<\/strong>to assess if the URL that you entered is live (if the connection fails, you need to verify the connection URL).<\/li>\n
    8. Click\u00a0Save<\/strong>.<\/li>\n<\/ol>\n

      Report Types<\/strong><\/p>\n

      There are a number of PAfE report \u201ctypes\u201d<\/a>, including: Exploration Views<\/em>, Lists<\/em>,\u00a0Quick Reports<\/em>,\u00a0Dynamic Reports<\/em>,\u00a0Universal Reports<\/em>, and\u00a0Custom Reports<\/em>\u00a0(you can even use multiple types in the same workbook or report). Each report type has its own benefits and limitations. This document will highlight a helpful approach for creating a simple custom<\/strong> report.<\/p>\n

      Building a Custom Report<\/strong><\/p>\n

      To quickly and easily create a custom<\/strong> PAfE report, you can start by following these steps:<\/p>\n

      First, use the connection documented above (or one previously created) to connect to the desired Planning Analytics environment, then determine a cube to report on (if you are connected to Planning Analytics you should see a list of cubes in the task pane<\/a>). Here<\/a> is a helpful link that identifies where to find the Task pane as well as all of the major areas of the\u00a0IBM Planning Analytics for Microsoft Excel\u00a0(PAfE) user interface.<\/p>\n

      \"\"<\/p>\n

      Task Pane<\/em><\/p>\n

      For this example, I used a GL Summary<\/strong> cube so in the above-mentioned task pane, I expanded the GL Summary cube by clicking on the plus<\/strong> sign<\/strong> to the left of the cube name. Then, under the cube, I expanded the Views<\/strong> and located one that \u201cfit\u201d my report requirements, right clicked on the view and selected Custom Report<\/strong>, then On this sheet. <\/strong>Hint: If you are not sure which view to use, you can right-click on a cube and select Open in Viewer<\/strong>, then, using the cube viewer, you can create and save a view that fits the needs of the report you want to build.<\/p>\n

      Shown below is the view I created showing FY2024 (along with quarters), and using the account dimension subset named \u201cDetailed Income Statement\u201d:<\/p>\n

      \"\"<\/p>\n

      Sample Custom Report<\/em><\/p>\n

      Next, I clicked on Save to Server<\/strong> and named my view \u201cReport Example 001\u201d:<\/p>\n

      \"\"<\/p>\n

      Saving a New View to the Server for Later Use<\/em><\/p>\n

      Now that the new view is listed in the task pane under Views, you can add it to your worksheet as a custom report.<\/p>\n

      Hint: Give some thought to report names, try to make them obvious to the consumer as to what the report contains and\/or its objective. Additionally, strive for consistency with your organization\u2019s naming practices, paying specific attention to case, use of special characters, punctuation, and formatting (like YYYYMMDD).<\/p>\n

      Customization<\/strong><\/p>\n

      Once a view has been added to a worksheet as a custom report, if you click on cells in the worksheet you can view, add and\/or edit formulas (as shown below):<\/p>\n

      \"\"<\/p>\n

      Custom Report (added to a worksheet) Showing Cell Formula Highlighted<\/em><\/p>\n

      In this example, you can change the period values from FY2024, Q1 2024, Q2 2024, Q3 2024<\/em> and Q4 2024<\/em> to: FY2022, FY2023, 202401, 202301<\/em> and \u201cDelta\u201d<\/em> by simply typing those values into the cells. You can also add MS Excel formulas directly to cells (perhaps the math to calculate each the difference between columns as the delta) a and then recalculate the worksheet to see the results.<\/p>\n

      \"\"<\/p>\n

      Updated Custom Report<\/em><\/p>\n

      Formatting <\/strong><\/p>\n

      One benefit of working in\u00a0IBM Planning Analytics for Microsoft Excel (PAfE)\u00a0in \u201ccell-based mode<\/em><\/a>\u201d is that the features of Microsoft Excel become readily available to you. You are able to use Excel features, such as formatting, sorting, and creating formulas using individual cells. Hint: \u201cless is more\u201d \u2013 meaning that you have the ability to hide any cell\/row\/column in your report that may be irrelevant to the reports message or contains information that the consumer need not see. For more information on some of the ways that\u00a0IBM Planning Analytics for Microsoft Excel\u00a0interacts with frequently used Microsoft Excel features and functions, read this<\/a>.<\/p>\n

      Another neat feature worth mentioning is that you can create and share customized report themes<\/a>.<\/p>\n

      Worksheet Functions<\/strong><\/p>\n

      IBM Planning Analytics\u00a0Worksheet functions manage data between Planning Analytics and a Worksheet. You can use TM1\/Planning Analytics worksheet functions anywhere in an\u00a0IBM Planning Analytics for Microsoft Excel\u00a0(PAfE) worksheet. For help adding functions to your report, you can reference (and build) these functions (in Microsoft Excel), you can click on\u00a0Formulas<\/strong>\u00a0then\u00a0Insert <\/strong>Function, or by clicking on Insert Function in the formula bar:<\/p>\n

      \"\"<\/p>\n

      The Insert Function dialog box<\/em> that appears gives you a list of operations that Excel can perform, including all of the TM1\/Planning Analytics formulas<\/a>. You can also use this dialog to construct and then add functions to a report. Working functions can also be copied from cell to cell and then edited.<\/p>\n

      \"\"<\/p>\n

      Insert Function Dialog Box<\/em><\/p>\n

      Slow Reports \u2013 Thinking about Performance<\/strong><\/p>\n

      When building reports , keep in mind that there can be issues with performance caused by the use of certain Excel formula functions. Certain functions, or the way the functions have been implemented within a report, can cause unwanted recalculation<\/em>. One example of this is using Excel functions on column headings (or other cells) that DBRW formulas implanted on the rows reference. Every entry into a cell has the potential to cause the entire worksheet to recalculate. \u201cUnpredictable\u201d behavior may also be caused by using OFFSET, INDIRECT and SUMIF (Excel functions).<\/p>\n

      If any TM1\/Planning Analytics formulas are dependent on the results of these functions, you may want to rethink their use (you can read more about PAfE performance here<\/a>).<\/p>\n

      Additionally, there is a difference in the way some TM1\/Planning Analytics functions behave which can have a material impact on your report\u2019s performance and therefore you should consider when to use each (in fact, for a larger or more complex report, a little time spent experimenting with different functions is time well spent).<\/p>\n