{"id":1460,"date":"2021-10-01T10:02:11","date_gmt":"2021-10-01T15:02:11","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/docs\/?page_id=1460"},"modified":"2024-06-12T11:17:35","modified_gmt":"2024-06-12T16:17:35","slug":"using-the-run-time-calculator","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/docs\/knowledge-base\/using-the-run-time-calculator","title":{"rendered":"Total-level\/Margin Calculations"},"content":{"rendered":"\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-info\">The legacy documentation for this feature may found <a href=\"\/legacy\/runtime-calculations\">here<\/a>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Overview<\/h2>\n\n\n\n<p><strong>Total-level \/Margin<\/strong> calculations are a must for developers who wish to perform calculations like averages or percentages at any subtotal level(s) and\/or grand total level of their report applications.<\/p>\n\n\n\n<p>For instance, let&#8217;s say that we would like to pull up a list of customer sales made in a report. Looking at the sales and cost values, I&#8217;d want to calculation to find the average profit for each customer. This documentation will walkthrough this example and explain how to utilize Total-level\/Margin calculations.<\/p>\n\n\n\n<h1 class=\"wp-block-heading\">Option 1: Calculation Based Approach<\/h1>\n\n\n\n<h2 class=\"wp-block-heading\">Supported Templates<\/h2>\n\n\n\n<p>Total-level\/Margin calculations are accessible in <strong>all <\/strong>report templates:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Regular Report<\/li>\n\n\n\n<li>Interactive Report<\/li>\n\n\n\n<li>OLAP Pivot Table Report<\/li>\n\n\n\n<li>Report Designer<\/li>\n\n\n\n<li>Cross Tab Report<\/li>\n\n\n\n<li>Ranking Report<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Walkthrough<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Report Example\/Scenario<\/h3>\n\n\n\n<p>I have a sales report that shows the sales and costs of good sold by customer. <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs3.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs3.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 1 &#8211; Sales report by customer<\/figcaption><\/figure>\n\n\n\n<p>Using Figure 1 as a reference, I want to calculate the average profit per sale for customer, which would be the MTD Sales &#8211; MTD Cost, divided by the total number of sales made for that customer. <\/p>\n\n\n\n<p>Using Amboy Development as an example, this value should roughly compute to <code>$49,387<\/code>. <\/p>\n\n\n\n<p><code><strong>$49,387<\/strong> = ( $262,276 - $15,341 ) \/ 5<\/code>.  <\/p>\n\n\n\n<p>The <code>5<\/code> represents five different sales made for that customer (Amboy Development). If looking at Glenwood Manufacturing, this would be <code>3<\/code>: <\/p>\n\n\n\n<p><code><strong>$35,115<\/strong> = ( $112,778 - $7,431 ) \/ 3<\/code><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Creating the Calculations<\/h3>\n\n\n\n<p>Back in the data model of the application is where these calculations will be created. <\/p>\n\n\n\n<h4 class=\"wp-block-heading\">1. Sale Counter calculation (Database)<\/h4>\n\n\n\n<p>First, I&#8217;ll create a counter calculation to get the total count of sales for each customer. This will be a  numeric <strong>Database <\/strong>type calculation:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs6.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs6.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 2 &#8211; Creating a counter calculation<\/figcaption><\/figure>\n\n\n\n<p>The expression of this calculation is just <code>1<\/code>. At runtime, m-Power puts a 1 on each detail record, which will then be automatically totaled up on subtotal line for each detail record found. (see Figure 6 further in the documentation for a visual reference).<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">2. Average Profit per Sale calculation (Total-level\/Margin)<\/h4>\n\n\n\n<p>A second calculation, a<strong> Total-level\/Margin<\/strong> calculation is needed here as the average needs to be computed on a subtotal lines of the report, rather than at the detail lines.<\/p>\n\n\n\n<p>Make sure the calculation type is set to Total-level\/Margin as shown Figure 3:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs1.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs1.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 3. Creating a Total-level\/Margin calculation<\/figcaption><\/figure>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon is-style-alert\">Note &#8211; The <em>Calc Type<\/em> setting can only be changed when creating a <strong>new<\/strong> calculation. <\/p>\n\n\n\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-9d6595d7 wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:33.33%\">\n<p>To create this second calculation, I select my fields from the blue &#8220;Fields&#8221; dropdown selection on the right of the calculation expression body. After manually wrapping my subtraction in parentheses, my calculation expression is:<\/p>\n\n\n\n<p><code>(&amp;MTDSALES - &amp;MTDCOST) \/ &amp;CALCULA001<\/code><\/p>\n<\/div>\n\n\n\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\" style=\"flex-basis:66.66%\">\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs4.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs4.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 4 &#8211; Defining the calculation expression<\/figcaption><\/figure>\n<\/div>\n<\/div>\n\n\n\n<p>Once finished I now have two calculations.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs5.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs5.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 5. Both calculations within my data model<\/figcaption><\/figure>\n\n\n\n<h5 class=\"wp-block-heading\">Note about calculation setup (Optional)<\/h5>\n\n\n\n<p>It&#8217;s worthy to note that my example, as shown in Figure 5, uses a total of <strong>two <\/strong>calculations:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>#<\/strong><\/td><td><strong>Description<\/strong><\/td><td><strong>Expression<\/strong><\/td><td><strong>Type<\/strong><\/td><\/tr><tr><td>CALCULA001<\/td><td>Sale Counter<\/td><td><code>1<\/code><\/td><td>Database<\/td><\/tr><tr><td>CALCULA002<\/td><td>Avg Profit Per Sale<\/td><td><code>(MTD Sales - MTD Costs)\/CALCULA001<\/code><\/td><td>Total-level<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>If you prefer to compute the subtraction in one calculation, then do the division in another calculation, that is also fine. Meaning this alternative setup, with a total of <strong>three <\/strong>calculations, will produce the same outcome:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>#<\/strong><\/td><td><strong>Description<\/strong><\/td><td><strong>Expression<\/strong><\/td><td><strong>Type<\/strong><\/td><\/tr><tr><td>CALCULA001<\/td><td>Sale Counter<\/td><td><code>1<\/code><\/td><td>Database<\/td><\/tr><tr><td>CALCULA002<\/td><td>Profit<\/td><td><code>MTD Sales - MTD Costs<\/code><\/td><td>Database<\/td><\/tr><tr><td>CALCULA003<\/td><td>Avg Profit Per Sale<\/td><td><code>CALCULA002\/CALCULA001<\/code><\/td><td>Total-level<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p class=\"wp-block-ht-blocks-messages wp-block-hb-message wp-block-hb-message--withicon\">The three calculation approach is advised if you need to use the computed Profit (sales &#8211; cost) value for other calculations.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">3. Runtime <\/h4>\n\n\n\n<p>After rebuilding the report, now my two new calculations appear at the very right-hand side of the screen, with the correct Avg. Profit Per Sale value. <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs7.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs7.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 6 &#8211; The report with the new calculations added in<\/figcaption><\/figure>\n\n\n\n<p>Because the Avg Profit Per Sale calculation is a Total-Level\/Margin calculation, notice in Figure 6 this calc&#8217;s arithmetic is only performed on the total lines of the report. On the detail lines, a 0 value (<code>$.00<\/code>) is present, by default. <\/p>\n\n\n\n<p>As this 0 value is not necessary to see, I can remove it from detail lines of the report in the m-Painter editor. <\/p>\n\n\n\n<h4 class=\"wp-block-heading\">4. Removing the calculations from the detail level (optional)<\/h4>\n\n\n\n<p>Within the m-Painter editor, any total-level\/margin value on the detail level of the report can be removed by selecting that field (ex.<code>{CALCULA002}<\/code>) and pressing the delete option:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs8.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs8.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 7. Removing values from the Detail Level of the report via m-Painter<\/figcaption><\/figure>\n\n\n\n<p>The same can be done with the Sale Counter (ex.<code>{CALCULA001}<\/code>) as it&#8217;s also not necessary to print a 1 on each detail row. <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs9.jpg\" alt=\"\"\/><figcaption class=\"wp-element-caption\">Figure 8. CALCULA001 and CALCUA002 removed from the Detail Level<\/figcaption><\/figure>\n\n\n\n<p>Once removed and m-Painter is saved, the updated result will look like this at runtime:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"\/docs\/vue-images\/runtimecalcs10.jpg\"><img decoding=\"async\" src=\"\/docs\/vue-images\/runtimecalcs10.jpg\" alt=\"\"\/><\/a><figcaption class=\"wp-element-caption\">Figure 9. Updated report at runtime<\/figcaption><\/figure>\n\n\n\n<h1 class=\"wp-block-heading\">Option 2: m-Painter Based Approach<\/h1>\n\n\n\n<p>If you prefer to add your total level calculation via m-Painter for your report, you may select &#8220;Total Calc&#8221; from the Featured Component menu in m-Painter.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"\/docs\/images\/newruntimecalc1.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<p>First, select where in your report output you want this runtime calculation to be placed, then click the Total Calc button.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"\/docs\/images\/newruntimecalc2.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<p>Specify a length, decimal, and edit code along the top. Then use the &#8220;Field&#8221; and &#8220;Level&#8221; options to select which specific fields to add to your equation. Once you have made your selection, click the &#8220;Add Field to calculation&#8221; button. Add your own operators to the &#8220;Calc Expression.&#8221; When done, click the &#8220;Finish&#8221; button.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Overview Total-level \/Margin calculations are a must for developers who wish to perform calculations like averages or percentages at any subtotal level(s) and\/or grand total level of their report applications. For instance, let&#8217;s say that we would like to pull up a list of customer sales made in a report&#8230;.<\/p>\n","protected":false},"author":1,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[255],"ht-kb-tag":[],"class_list":["post-1460","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-mpainter"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/1460","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/comments?post=1460"}],"version-history":[{"count":55,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/1460\/revisions"}],"predecessor-version":[{"id":14101,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/1460\/revisions\/14101"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/media?parent=1460"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-category?post=1460"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-tag?post=1460"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}