Need to use scaffold data local to page and in another page

  • 1
  • Question
  • Updated 1 year ago
  • (Edited)
I need to do a report-eval (multiplication of two values) on a value in a table local to the current page and a value retrieved by a reporter.

On a page, I want to calculate the cost associate with the number of FTE's required for a certain task. (FTE=Full Time Employee)

I have a table on a page called a Rate Table.  This table is a data-table that contains Rates for FTE's per each Fiscal Year.  On the child pages of another page are scaffold form that indicate how many FTEs are required per fiscal year.  I need to multiply these two together.

So far, I have tried capturing the value in a variable (which doesn't seem to work for a filtered report giving a single number result).  with a report eval of %data:CostperFY% * %variable:Rates%

However the variable Rates doesn't seem to pull results from the table outside the current reporter. (again, the Rate table is on the same page, containing two columns FY and Rate).

Thanks for any help!
Katy
Photo of Kathryn Moye

Kathryn Moye

  • 223 Posts
  • 12 Reply Likes

Posted 2 years ago

  • 1
Photo of Suresh Mathusuthanan

Suresh Mathusuthanan, Alum

  • 117 Posts
  • 9 Reply Likes
Hi Kathryn,

From your inquiry, looks like we will require more information. 
 
Could you please reach out to us at our Support channel : support@servicerocket.com

It would be great if you can share the storage format of the Scaffolding and Reporting when you get back to us.

Thank you.

Regards,
Suresh
Photo of Kathryn Moye

Kathryn Moye

  • 223 Posts
  • 12 Reply Likes
I replied to Support and I will post the code here.
This is the page that is doing the calculation.
<code>
<h2>Rate Table</h2>
<p> </p>
<ac:structured-macro ac:macro-id="079b2ad2-4301-4d2a-9c58-83bad0a8f0e3" ac:name="table-data" ac:schema-version="1">
  <ac:parameter ac:name="name">RateTable</ac:parameter>
  <ac:rich-text-body>
    <table>
      <tbody>
        <tr>
          <th>
            <p style="text-align: center;">FY</p>
          </th>
          <th colspan="1" style="text-align: center;">Rate</th>
        </tr>
        <tr>
          <td>
            <ac:structured-macro ac:macro-id="10fd1935-0536-411c-bf16-ea4de0e9a714" ac:name="list-data" ac:schema-version="1">
              <ac:parameter ac:name="name">FY</ac:parameter>
              <ac:parameter ac:name="atlassian-macro-output-type">BLOCK</ac:parameter>
              <ac:rich-text-body>
                <ac:structured-macro ac:macro-id="564984af-6330-4688-a141-3aefab9d2304" ac:name="report-block" ac:schema-version="1">
                  <ac:parameter ac:name="atlassian-macro-output-type">BLOCK</ac:parameter>
                  <ac:rich-text-body>
                    <ac:structured-macro ac:macro-id="86a73daa-1d2e-487b-9770-b39e0ce42d6e" ac:name="local-reporter" ac:schema-version="1">
                      <ac:parameter ac:name="source">OrionPPC:Form Dropdowns for TLO</ac:parameter>
                      <ac:parameter ac:name="">data:FiscalYears</ac:parameter>
                      <ac:rich-text-body>
                        <p> </p>
                      </ac:rich-text-body>
                    </ac:structured-macro>
                    <ac:structured-macro ac:macro-id="9a8d37a0-f1b4-4ba0-ad32-5bbd58dc5ce4" ac:name="report-body" ac:schema-version="1">
                      <ac:parameter ac:name="injected">true</ac:parameter>
                      <ac:rich-text-body>
                        <ac:structured-macro ac:macro-id="c8710aa9-2d13-45dd-88c6-72a92516e58f" ac:name="list-option" ac:schema-version="1">
                          <ac:parameter ac:name="sortValue">%data:FieldOrder%</ac:parameter>
                          <ac:parameter ac:name="value">%data:FiscalYearsSelect%</ac:parameter>
                          <ac:rich-text-body> </ac:rich-text-body>
                        </ac:structured-macro>
                      </ac:rich-text-body>
                    </ac:structured-macro>
                  </ac:rich-text-body>
                </ac:structured-macro>
              </ac:rich-text-body>
            </ac:structured-macro>
          </td>
          <td colspan="1">
            <p>
              <ac:structured-macro ac:macro-id="33eee96c-21cb-47c6-aee9-a186dd01b645" ac:name="number-data" ac:schema-version="1">
                <ac:parameter ac:name="format">#0.#</ac:parameter>
                <ac:parameter ac:name="name">Rate</ac:parameter>
                <ac:parameter ac:name="decimal">true</ac:parameter>
                <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
                <ac:plain-text-body><![CDATA[ ]]></ac:plain-text-body>
              </ac:structured-macro>
            </p>
          </td>
        </tr>
      </tbody>
    </table>
  </ac:rich-text-body>
</ac:structured-macro>
<p>
  <ac:structured-macro ac:macro-id="81c120ae-627c-4ba0-ad8f-c21924e3b420" ac:name="report-variable" ac:schema-version="1">
    <ac:parameter ac:name="name">Rates</ac:parameter>
    <ac:parameter ac:name="value">%data:Rate%</ac:parameter>
    <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
    <ac:rich-text-body>
      <p> </p>
    </ac:rich-text-body>
  </ac:structured-macro>
  <ac:structured-macro ac:macro-id="72a874c9-e0f6-498c-a91e-5a9562b3eb7c" ac:name="report-info" ac:schema-version="1">
    <ac:parameter ac:name="">variable:Rates</ac:parameter>
  </ac:structured-macro>
</p>
<h3>Non-Prime FTE Liens &amp; Threats</h3>
<ac:structured-macro ac:macro-id="5fbb3dee-e3ab-485b-a35c-9c707e36d6bc" ac:name="report-table" ac:schema-version="1">
  <ac:rich-text-body>
    <ac:structured-macro ac:macro-id="5831a4a7-eb3f-4ade-a4d1-4035925ab28c" ac:name="local-reporter" ac:schema-version="1">
      <ac:parameter ac:name="source">TLO List</ac:parameter>
      <ac:parameter ac:name="">page:children</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="519dc1b6-b337-4089-beb5-c086fdda66a1" ac:name="text-filter" ac:schema-version="1">
            <ac:parameter ac:name="value">Non-Prime</ac:parameter>
            <ac:parameter ac:name="">data:PrimeNonPrime&gt;first</ac:parameter>
          </ac:structured-macro> <ac:structured-macro ac:macro-id="8677366c-6b0d-41e8-b493-3a987714ce8c" ac:name="text-filter" ac:schema-version="1">
            <ac:parameter ac:name="include">.*FTE.*</ac:parameter>
            <ac:parameter ac:name="">data:ProcFTE</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="f0ab58ec-07a0-446f-9e62-8667d366b156" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Status</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="33d1fc7b-5046-4dd7-bf37-fd70aee06e06" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">data:Lifecycle</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="aa4e81a6-ea6c-40f3-92d2-ce22118b0e8a" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">TLO #</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="da18aa84-6df9-43f8-861f-0e5e68d55b4a" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="link">true</ac:parameter>
            <ac:parameter ac:name="">page:title</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="272327c9-e34f-4243-b105-af511be2ad9b" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">TLO Title</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="e61207c9-6ea3-496e-bd2e-86c148613c3a" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">data:TLOTitle&gt;text:first 50</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="5b640b38-9e93-4a9c-ba01-3321dbe7717e" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="width">150px</ac:parameter>
      <ac:parameter ac:name="title">Description</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="b952012e-06d5-44c3-b135-c2ae1aa9ffb9" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="render">richtext</ac:parameter>
            <ac:parameter ac:name="">data:Description&gt;text:first 100</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="031149f1-a541-4923-87e5-a0ee1af667e8" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">CAM</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="d81c0eaf-8e3a-4cc4-a282-d0ad545b6b88" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">data:CAM</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="bd9d0bc3-bbe4-4742-b288-6661160258f2" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">Cost Threat/Lien/Opportunity</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="174e22e7-e992-4aaf-b914-a150650084d5" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">data:TLO</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <br/>
    <ac:structured-macro ac:macro-id="129dc3f0-fe84-497f-903b-a22c91dbe5f6" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">FY16</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="cb52fe41-7195-4e90-bda1-4930d2c704a8" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="">variable:Rates</ac:parameter>
          </ac:structured-macro>
          <ac:structured-macro ac:macro-id="def89dba-1b5d-41ed-b04e-f648625b7f87" ac:name="report-eval" ac:schema-version="1">
            <ac:parameter ac:name="default">0</ac:parameter>
            <ac:parameter ac:name="format">#,##0.00</ac:parameter>
            <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
            <ac:plain-text-body><![CDATA[%data:CostperFY% * %variable:Rates%]]></ac:plain-text-body>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
  </ac:rich-text-body>
</ac:structured-macro>
</code>

The calculation of data:CostperFY * variable:Rates is not working.

I have tried also not using a variable but that isn't working.

Katy
Photo of André

André

  • 7 Posts
  • 0 Reply Likes
Hi Katy
What works for me:
{report-table}
{local-reporter:page:children:source=TLO List}
{local-reporter}
{report-variable:name=vCoxtperFY:value=0%data:CostperFY%:default=0}
{report-variable:name=vRates:value=0%data:Rate%:default=0}
{report-eval:name=eCalc:default=0:format=0.00}
((%variable:vCoxtperFY%)*(%variable:vRates%))
{report-eval}
{report-table}

This is "only" the raw-structure
Hope this helps
Photo of Suresh Mathusuthanan

Suresh Mathusuthanan, Alum

  • 117 Posts
  • 9 Reply Likes
Hi Kathryn,

From our findings, you were unable to capture the value in a variable is due to the Scaffold-data is nested in a Table-data. 

That said, we have tested whereby, we could capture the value of the Scaffold-data if it is not nested in a Table-data. 

Since you're using Report-variable, we wont be able to capture the value as the Scaffold-data is nested in a Table-data. 

As of now, we are still testing the possibility of using Expanded-Reporter to meet your needs. 

We will get back to you in a day or two with our progress. 

Thank you.

Regards,
Suresh
Photo of Kathryn Moye

Kathryn Moye

  • 223 Posts
  • 12 Reply Likes
I hope this will help simplify the problem.  I don't have to use a report variable.  I just need to be able to provide a table or something to the user to enter the FTE (employee) rates per the fiscal year and then use that table to compute the costs for FTEs per FY.  I have attached a picture i hope will help.
Photo of Suresh Mathusuthanan

Suresh Mathusuthanan, Alum

  • 117 Posts
  • 9 Reply Likes
Hi Kathryn,

Thank you for your clarification.

We will try to restructure the reporting to meet your needs. 

I'll get back to you with my findings. 

Regards,
Suresh
Photo of Kathryn Moye

Kathryn Moye

  • 223 Posts
  • 12 Reply Likes
I found a workaround.  The user can't use a table data, as you have pointed out but he is ok with editing report-variables directly.  So, I put the report-variable definitions with the value set to the appropriate constant for the FTE "rate" on a separate page called "FTE Rates" and did a page include on the report page (so I can use the rates anywhere).  The following code uses those variables set on the other page, FTE Rates.
<code>
<p>
  <ac:structured-macro ac:macro-id="9b17688c-2ac6-4d11-a71c-c82aa6d69eaa" ac:name="include" ac:schema-version="1">
    <ac:parameter ac:name="">
      <ac:link>
        <ri:page ri:content-title="FTE Rates"/>
      </ac:link>
    </ac:parameter>
  </ac:structured-macro>
  <ac:structured-macro ac:macro-id="41ef6894-304b-4b94-b58b-bfde583a81a9" ac:name="htmlcomment" ac:schema-version="1">
    <ac:parameter ac:name="hidden">true</ac:parameter>
    <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
    <ac:rich-text-body>
      <p>Cannot put the table below in a future macro or the formulas won't render to numbers, you get an error.</p>
    </ac:rich-text-body>
  </ac:structured-macro>
</p>
<ac:structured-macro ac:macro-id="2f01cc74-d8b6-43c7-9a16-9bb8df610058" ac:name="report-table" ac:schema-version="1">
  <ac:parameter ac:name="maxResults">5000</ac:parameter>
  <ac:rich-text-body>
    <ac:structured-macro ac:macro-id="f4c04265-5499-4dbf-a460-986178eaf087" ac:name="local-reporter" ac:schema-version="1">
      <ac:parameter ac:name="source">TLO List</ac:parameter>
      <ac:parameter ac:name="">page:children</ac:parameter>
      <ac:rich-text-body>
        <p> <ac:structured-macro ac:macro-id="cbd25dbd-1411-415a-9596-d36dccae16a7" ac:name="text-filter" ac:schema-version="1">
            <ac:parameter ac:name="value">FY17-32</ac:parameter>
            <ac:parameter ac:name="">page:title</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="3ff3e857-46dc-4c6c-b3b2-6b9f5e37856c" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">TLO #</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="22a94922-6e91-44f3-8200-34665f873bde" ac:name="report-info" ac:schema-version="1">
            <ac:parameter ac:name="link">true</ac:parameter>
            <ac:parameter ac:name="">page:title</ac:parameter>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
    <ac:structured-macro ac:macro-id="7f20e369-04a8-401f-8b1d-29cfae9933d5" ac:name="report-column" ac:schema-version="1">
      <ac:parameter ac:name="title">FY17</ac:parameter>
      <ac:rich-text-body>
        <p>
          <ac:structured-macro ac:macro-id="3d074c85-d037-4ca9-8d11-22fd7a3004c1" ac:name="report-block" ac:schema-version="1">
            <ac:parameter ac:name="maxResults">1</ac:parameter>
            <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
            <ac:rich-text-body>
              <ac:structured-macro ac:macro-id="d0ebc480-7744-4d55-944e-6e4b6ece9816" ac:name="expanding-reporter" ac:schema-version="1">
                <ac:parameter ac:name="as">CostTracking</ac:parameter>
                <ac:parameter ac:name="">data:CostTracking</ac:parameter>
                <ac:rich-text-body>
                  <ac:structured-macro ac:macro-id="7477b900-0a42-4035-935f-9e72be16170b" ac:name="local-reporter" ac:schema-version="1">
                    <ac:parameter ac:name="source">@self</ac:parameter>
                    <ac:parameter ac:name="">page:children</ac:parameter>
                    <ac:rich-text-body>
                      <p>
                        <ac:structured-macro ac:macro-id="a02d612b-4ab2-47d3-8f9e-c378c6556b60" ac:name="date-sort" ac:schema-version="1">
                          <ac:parameter ac:name="order">descending</ac:parameter>
                          <ac:parameter ac:name="">page:creation date</ac:parameter>
                        </ac:structured-macro>
                      </p>
                    </ac:rich-text-body>
                  </ac:structured-macro>
                  <p>
                    <ac:structured-macro ac:macro-id="adbc2bbc-5a32-45f3-850c-23f59e289882" ac:name="text-filter" ac:schema-version="1">
                      <ac:parameter ac:name="include">.*FY17.*</ac:parameter>
                      <ac:parameter ac:name="">data:FY&gt;first</ac:parameter>
                    </ac:structured-macro>
                  </p>
                </ac:rich-text-body>
              </ac:structured-macro>
              <ac:structured-macro ac:macro-id="ace4a97f-7735-40b8-a557-d6597486e035" ac:name="report-body" ac:schema-version="1">
                <ac:rich-text-body>
                  <ac:structured-macro ac:macro-id="fecaf4a6-f21b-477b-aa7c-fd075878ad53" ac:name="report-eval" ac:schema-version="1">
                    <ac:parameter ac:name="format">#0.00</ac:parameter>
                    <ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
                    <ac:plain-text-body><![CDATA[%CostTracking>data:CostperFY% * %variable:FY17Rate%]]></ac:plain-text-body>
                  </ac:structured-macro>
                </ac:rich-text-body>
              </ac:structured-macro>
            </ac:rich-text-body>
          </ac:structured-macro>
        </p>
      </ac:rich-text-body>
    </ac:structured-macro>
  </ac:rich-text-body>
</ac:structured-macro>
</code>
Photo of Suresh Mathusuthanan

Suresh Mathusuthanan, Alum

  • 117 Posts
  • 9 Reply Likes
Hi Kathryn, 

We are still in midst of investigation, please allow us more time to look into your request.

Thank you.

Regards,
Suresh