{"id":3419,"date":"2023-04-12T15:33:06","date_gmt":"2023-04-12T15:33:06","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/"},"modified":"2023-04-12T16:59:50","modified_gmt":"2023-04-12T16:59:50","slug":"creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/","title":{"rendered":"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports"},"content":{"rendered":"

Level-based row indentation is a great way to add quick readability to your Planning Analytics for Excel (PAfE) reports. Instead of having a list of elements all left-aligned, we can add indentation based upon each member\u2019s level to help report readers understand hierarchies within the dimension.<\/p>\n

Exploration Views, Quick Reports and Dynamic Reports all have built-in indentation. Therefore, when you slice out your report from the Cube Viewer, your list of elements will have the same row indentation in the Excel report they do in the Cube Viewer.<\/p>\n

Below is a screen shot which compares row indentation seen in Cube Viewer (on the right) with a sliced our Dynamic Report (on the left):<\/p>\n

\"\"<\/p>\n

PAfE users can easily opt to turn on or off any\u00a0level-based indentation when working with Quick Reports.<\/p>\n

To do so, navigate to the \u201cOptions\u201d button, select the \u201cIBM Planning Analytics\u201d tab, then enable\/disable the \u201cUse level-based indents\u201d option.<\/p>\n

\"\"<\/p>\n

At this time, users only have the option to toggle on\/off the indentation for the Quick Reports, not for Exploration Views or Dynamic Reports.<\/p>\n

Notice that level-based indentation is not an option for Custom Reports. When slicing out a Custom Report from the Cube Viewer, all elements are left-aligned.<\/p>\n

\"\"<\/p>\n

The good news is report builders can create level-based indentation in a Custom Report manually. There are two solutions: one is a \u201cquick\u201d solution, and the other is a more robust solution.<\/p>\n

The Quick Solution<\/span><\/h3>\n
    \n
  1. From the Cube Viewer, create two reports: one Custom Report and one Dynamic Report.<\/li>\n
  2. Copy the list of elements from the Dynamic Report and paste them into your Custom Report. The formatting should remain and now your list of elements should have some sort of level-based indentation.<\/li>\n<\/ol>\n

    While this solution works, what happens if you need to add an element to this report in the future? Well, for this we can build out a more robust and dynamic solution.<\/p>\n

    The Dynamic Solution<\/span><\/h3>\n
      \n
    1. Insert two new columns to the left of your report. Columns A & B will be hidden from the end-users, Column C will be the \u201cnew\u201d list of elements end-users will see on their report.<\/li>\n
    2. Copy your list of elements over into the new Column A.<\/li>\n
    3. In Column B, we are going to use the ELLEV function to indicate each element\u2019s level.<\/li>\n<\/ol>\n

      Here\u2019s the syntax along with my generic example\u2019s syntax.<\/p>\n

      =ELLEV(“ServerName:DimensionName”, ElementName)<\/p>\n

      =ELLEV(“Reporting-Maria:Account”, A9)<\/p>\n

      \"\"<\/p>\n

       <\/p>\n

        \n
      1. OPTIONAL: Find the maximum number of levels in your dimension\u2019s hierarchy and use this number to drive the other element\u2019s indentation. To do this, use the DNLEV function.<\/li>\n<\/ol>\n

        =DNLEV(“ServerName:DimensionName”)<\/p>\n

        =DNLEV(“Reporting-Maria:Account”)<\/p>\n

         <\/p>\n

          \n
        1. In column C, you will want to use the REPT formula. Where the argument is for \u201cTotal Levels\u201d you do not have to use the maximum levels calculated above. You could instead use the maximum from the list of elements in your report, or hard code this number completely.<\/li>\n<\/ol>\n

          =REPT(” “,TotalLevels-ElementLevel)&ElementName<\/p>\n

          =REPT(” “,$B$7-B9)&A9<\/p>\n

          \"\"<\/p>\n

          In the future, if any more levels are added to the dimension, or a report builder wants to add other elements to this list, then this report will continue inherent the proper level-based indentation.<\/p>\n

           <\/p>\n

           <\/p>\n","protected":false},"excerpt":{"rendered":"

          Level-based row indentation is a great way to add quick readability to your Planning Analytics for Excel (PAfE) reports. Instead of having a list of elements all left-aligned, we can add indentation based upon each member\u2019s level to help report readers understand hierarchies within the dimension. Exploration Views, Quick Reports and Dynamic Reports all have…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[24],"tags":[36,58,37],"class_list":["post-3419","post","type-post","status-publish","format-standard","hentry","category-ibm","tag-planning-analytics","tag-planning-analytics-reporting","tag-reporting"],"acf":[],"yoast_head":"\nCreating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports - QueBIT<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports - QueBIT\" \/>\n<meta property=\"og:description\" content=\"Level-based row indentation is a great way to add quick readability to your Planning Analytics for Excel (PAfE) reports. Instead of having a list of elements all left-aligned, we can add indentation based upon each member\u2019s level to help report readers understand hierarchies within the dimension. Exploration Views, Quick Reports and Dynamic Reports all have…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/\" \/>\n<meta property=\"og:site_name\" content=\"QueBIT\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-12T15:33:06+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-12T16:59:50+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg\" \/>\n<meta name=\"author\" content=\"agoddard\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"agoddard\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/\"},\"author\":{\"name\":\"agoddard\",\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084\"},\"headline\":\"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports\",\"datePublished\":\"2023-04-12T15:33:06+00:00\",\"dateModified\":\"2023-04-12T16:59:50+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/\"},\"wordCount\":546,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg\",\"keywords\":[\"Planning Analytics\",\"Planning Analytics Reporting\",\"Reporting\"],\"articleSection\":[\"IBM\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/\",\"url\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/\",\"name\":\"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports - QueBIT\",\"isPartOf\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg\",\"datePublished\":\"2023-04-12T15:33:06+00:00\",\"dateModified\":\"2023-04-12T16:59:50+00:00\",\"author\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084\"},\"breadcrumb\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage\",\"url\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg\",\"contentUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/quebit.com\/askquebit\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/quebit.com\/askquebit\/#website\",\"url\":\"https:\/\/quebit.com\/askquebit\/\",\"name\":\"QueBIT\",\"description\":\"QueBIT\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/quebit.com\/askquebit\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084\",\"name\":\"agoddard\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"caption\":\"agoddard\"},\"sameAs\":[\"https:\/\/quebit.com\/askquebit\"],\"url\":\"https:\/\/quebit.com\/askquebit\/author\/agoddard\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports - QueBIT","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/","og_locale":"en_US","og_type":"article","og_title":"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports - QueBIT","og_description":"Level-based row indentation is a great way to add quick readability to your Planning Analytics for Excel (PAfE) reports. Instead of having a list of elements all left-aligned, we can add indentation based upon each member\u2019s level to help report readers understand hierarchies within the dimension. Exploration Views, Quick Reports and Dynamic Reports all have…","og_url":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/","og_site_name":"QueBIT","article_published_time":"2023-04-12T15:33:06+00:00","article_modified_time":"2023-04-12T16:59:50+00:00","og_image":[{"url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg","type":"","width":"","height":""}],"author":"agoddard","twitter_card":"summary_large_image","twitter_misc":{"Written by":"agoddard","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#article","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/"},"author":{"name":"agoddard","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"headline":"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports","datePublished":"2023-04-12T15:33:06+00:00","dateModified":"2023-04-12T16:59:50+00:00","mainEntityOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/"},"wordCount":546,"commentCount":0,"image":{"@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg","keywords":["Planning Analytics","Planning Analytics Reporting","Reporting"],"articleSection":["IBM"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/","url":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/","name":"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports - QueBIT","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/#website"},"primaryImageOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage"},"image":{"@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg","datePublished":"2023-04-12T15:33:06+00:00","dateModified":"2023-04-12T16:59:50+00:00","author":{"@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"breadcrumb":{"@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#primaryimage","url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg","contentUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2020\/07\/level1-min.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/quebit.com\/askquebit\/creating-level-based-row-indentation-for-planning-analytics-for-excel-pafe-reports\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/quebit.com\/askquebit\/"},{"@type":"ListItem","position":2,"name":"Creating Level-Based Row Indentation for Planning Analytics for Excel (PAfE) Reports"}]},{"@type":"WebSite","@id":"https:\/\/quebit.com\/askquebit\/#website","url":"https:\/\/quebit.com\/askquebit\/","name":"QueBIT","description":"QueBIT","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/quebit.com\/askquebit\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084","name":"agoddard","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","caption":"agoddard"},"sameAs":["https:\/\/quebit.com\/askquebit"],"url":"https:\/\/quebit.com\/askquebit\/author\/agoddard\/"}]}},"_links":{"self":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/comments?post=3419"}],"version-history":[{"count":1,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3419\/revisions"}],"predecessor-version":[{"id":3735,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3419\/revisions\/3735"}],"wp:attachment":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/media?parent=3419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/categories?post=3419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/tags?post=3419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}