{"id":3385,"date":"2023-04-12T15:32:53","date_gmt":"2023-04-12T15:32:53","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/"},"modified":"2023-04-12T16:58:39","modified_gmt":"2023-04-12T16:58:39","slug":"dynamic-sub-totals-when-hierarchies-are-not-available","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/","title":{"rendered":"Dynamic sub-totals when hierarchies are not available"},"content":{"rendered":"

In any given TM1 model developers make use of attributes in a dimension. These attributes can have a multitude of uses, i.e. driving calculations or serve as reference in a report or sometimes they drive hierarchy structures and many others.<\/p>\n

The use case we will discuss in this instance is the use of attributes to create dynamic subtotals in a dynamic report using Planning Analytics for Excel<\/a> (PAx). We assume in this example that these attributes don\u2019t exist as a hierarchy in the dimension, and one will not be created for us (there are many reasons for not converting an attribute into a hierarchy, but that\u2019s a discussion beyond the purpose of this post).<\/p>\n

The request is a dynamic report that shows revenue by two attributes in the product dimension. The product dimension in this example contains elements which represent electronic products (Phones, PCs, Tablets). The products have an attribute called Stage<\/strong>, which indicates if it\u2019s Current or Phased Out. There is another attribute called TechLevel<\/strong> that flags it as High, Medium or Low.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

The only way to accomplish the request, and have a dynamic report, is to work with the Excel data range generated by the dynamic report.<\/p>\n

We start with a simple dynamic report of the cube containing our revenue data. In this case the cube is called Revenue.<\/strong> The rows will contain members of the product dimension and the columns months in the year.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

We will need to add the two attributes to the report. We will do this by entering a DBRA formulas in the columns adjacent to the product dimension.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Next, we need to create our sub-totals. Now, because we can\u2019t break the dynamic report, we need to add the sub-totals at the bottom, after the last line of the dynamic report. We will try to make the subtotals a little bit easier to maintain manually, so we will add a column between the format line column A and the product dimension, which are the rows of our report. We will then concatenate the Sub-total row title. Hopefully, this will make it a little easier if we need to add new categories in the future.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

We now get to the meat of the exercise and need to create a couple of Excel named ranges. We will use both the Excel OFFSET and ROWS formulae in conjunction and leverage the data named range that\u2019s automatically generated by PAx\u2019s dynamic report. So, we go to the Excel\u2019s Named Manager and check the existing ranges.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

We create a new named range called Stage, like this:<\/p>\n

=OFFSET(‘Dynamic Sub’!$C$21,1,1,ROWS(‘Dynamic Sub’!TM1RPTDATARNG35363151),1)<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Now, we need to add named ranges for each of the columns we need to calculate. This is where it might get tedious. In the example below we have 12 months, so we need to add 12 named ranges (i.e. Jan thru Dec). I will show the named for Jan below:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

The resulting list of named ranges ( I added a letter to the beginning of the named range so they would show organized in the screenshot):<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

We can now create Excel SUMIF formula for each of our Subtotals at the bottom of the report:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

If we filter the data using any dimensional context, the dynamic report will automatically move the subtotal lines as well as sum the correct data. As shown in the comparison below the screenshot on the left shows the context of Total Company<\/strong>, the screenshot on the right shows the context just for British Columbia. We see the number of rows dropped, which caused the subtotals to be shown in line 54 instead of 56 and the subtotals are also different.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

We now need to work on the subtotals for Tech Level. We only need to create one more named range, since we already created the named ranges for the months. So, we call the TechLevel named range with this formula:<\/p>\n

=OFFSET(‘Dynamic Sub’!$C$21,1,2,ROWS(‘Dynamic Sub’!TM1RPTDATARNG35363151),1)<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

PAx has a new feature, if you hide columns using Excel\u2019s group function, TM1 will automatically generate a group for the dynamic form. So you can hide the entire dynamic report and only keep the subtotals.<\/p>\n

I hide the Format Areas using Excel\u2019s Group function in the Data menu. Next time I change my context, in our example from Total Company to British Columbia, I will see a Group for the dynamic report rows.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

When you look at this on the PAW or TM1web. The report will only display the Subtotal rows.<\/p>\n

\"\"<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Save Time and automate your IBM Planning Analytics Reports with ReportWORQ<\/a>. Request your 30-day commitment free evaluation of ReportWORQ Today!<\/p>\n","protected":false},"excerpt":{"rendered":"

In any given TM1 model developers make use of attributes in a dimension. These attributes can have a multitude of uses, i.e. driving calculations or serve as reference in a report or sometimes they drive hierarchy structures and many others. The use case we will discuss in this instance is the use of attributes to…<\/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-3385","post","type-post","status-publish","format-standard","hentry","category-ibm","tag-planning-analytics","tag-planning-analytics-reporting","tag-reporting"],"acf":[],"yoast_head":"\nDynamic sub-totals when hierarchies are not available - 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\/dynamic-sub-totals-when-hierarchies-are-not-available\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic sub-totals when hierarchies are not available - QueBIT\" \/>\n<meta property=\"og:description\" content=\"In any given TM1 model developers make use of attributes in a dimension. These attributes can have a multitude of uses, i.e. driving calculations or serve as reference in a report or sometimes they drive hierarchy structures and many others. The use case we will discuss in this instance is the use of attributes to…\" \/>\n<meta property=\"og:url\" content=\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/\" \/>\n<meta property=\"og:site_name\" content=\"QueBIT\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-12T15:32:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-04-12T16:58:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-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=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/\"},\"author\":{\"name\":\"agoddard\",\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084\"},\"headline\":\"Dynamic sub-totals when hierarchies are not available\",\"datePublished\":\"2023-04-12T15:32:53+00:00\",\"dateModified\":\"2023-04-12T16:58:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/\"},\"wordCount\":878,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg\",\"keywords\":[\"Planning Analytics\",\"Planning Analytics Reporting\",\"Reporting\"],\"articleSection\":[\"IBM\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/\",\"url\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/\",\"name\":\"Dynamic sub-totals when hierarchies are not available - QueBIT\",\"isPartOf\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg\",\"datePublished\":\"2023-04-12T15:32:53+00:00\",\"dateModified\":\"2023-04-12T16:58:39+00:00\",\"author\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084\"},\"breadcrumb\":{\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage\",\"url\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg\",\"contentUrl\":\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/quebit.com\/askquebit\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic sub-totals when hierarchies are not available\"}]},{\"@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":"Dynamic sub-totals when hierarchies are not available - 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\/dynamic-sub-totals-when-hierarchies-are-not-available\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic sub-totals when hierarchies are not available - QueBIT","og_description":"In any given TM1 model developers make use of attributes in a dimension. These attributes can have a multitude of uses, i.e. driving calculations or serve as reference in a report or sometimes they drive hierarchy structures and many others. The use case we will discuss in this instance is the use of attributes to…","og_url":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/","og_site_name":"QueBIT","article_published_time":"2023-04-12T15:32:53+00:00","article_modified_time":"2023-04-12T16:58:39+00:00","og_image":[{"url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg","type":"","width":"","height":""}],"author":"agoddard","twitter_card":"summary_large_image","twitter_misc":{"Written by":"agoddard","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#article","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/"},"author":{"name":"agoddard","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"headline":"Dynamic sub-totals when hierarchies are not available","datePublished":"2023-04-12T15:32:53+00:00","dateModified":"2023-04-12T16:58:39+00:00","mainEntityOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/"},"wordCount":878,"commentCount":0,"image":{"@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg","keywords":["Planning Analytics","Planning Analytics Reporting","Reporting"],"articleSection":["IBM"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/","url":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/","name":"Dynamic sub-totals when hierarchies are not available - QueBIT","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/#website"},"primaryImageOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage"},"image":{"@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg","datePublished":"2023-04-12T15:32:53+00:00","dateModified":"2023-04-12T16:58:39+00:00","author":{"@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"breadcrumb":{"@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#primaryimage","url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg","contentUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/02\/1-min.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/quebit.com\/askquebit\/dynamic-sub-totals-when-hierarchies-are-not-available\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/quebit.com\/askquebit\/"},{"@type":"ListItem","position":2,"name":"Dynamic sub-totals when hierarchies are not available"}]},{"@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\/3385","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=3385"}],"version-history":[{"count":1,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3385\/revisions"}],"predecessor-version":[{"id":3723,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3385\/revisions\/3723"}],"wp:attachment":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/media?parent=3385"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/categories?post=3385"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/tags?post=3385"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}