Sage Report Designer percentage increase in values?

SUGGESTED

Hi,

I have created a simple report that outputs information from purchase orders based on an item code or a supplier and it gives me when,whom and when those items where purchased, with po number quantity and unit buying price. 

What I would like to do is have a percentage increase/decrease value at the bottom by taking what the latest unit buying price and subtracting it from the earliest unit buying price and dividing it by the latest buying unit buying price. 

Let me see if can build a table: 

Supplier Supplier Account Name Document No Document Date Item Code Item Description Line Quantity Unit Buying Price
123 Some Co 000998 30/05/22 1234L Part Number 1 40 24.19
123 Some Co 000997 30/04/22 1234L Part Number 1 35 28.25
123 Some Co 000996 30/02/22 1234L Part Number 1 18 28.24
123 Some Co 000950 30/01/22 1234L Part Number 1 95 24.19
124 Some Co 2 000940 30/12/21 1234L Part Number 1 45 24.19
124 Some Co 2 000900 30/10/22 1234L Part Number 1 78 24.19
Total % inc/dec

So in the "Total % inc/dec" cell, I would like the value of first - last / first so its expressed as a percentage. obviously this will 0% on this entry, but we have prices that have gone up and we would like to know by how much. I can do this in Excel easily enough, but want to try and achieve the same effect in Report Designer, I just don't know how expressions work. 

Cheers....Derrick

Parents
  • 0

    Interesting!  as there are functions for Min and Max but not First or Last.   But it can be done using the Evaluate Section property,  I've uploaded a (rather crude) example here

    You'll need 2 footer sections for each item/supplier.  In the first section, add 2 expressions "FirstPrice" and "LastPrice". The value of each of these will be POPOrderReturnLines.UnitBuyingPrice; for the FirstPrice, set the Evaluate Section to be the item's Header section; for the LastPrice, set it to be the section it's in.  For both, the Function is None and the Reset Section the last item footer:

    eg. FirstPrice:

      

    LastPrice:

    Then in the 2nd Footer section, define the percentage change expression, using the FirstPrice and LastPrice, and set the Evaluate Section to be the first footer:

    It seems to work:

    What I think is happening:

    When the header section is processed, it sees that it needs to evaluate FirstPrice so it sets it to the current value of UnitBuyingPrice. And the current value is the first details record for that item/supplier

    When the footer section is processed, it evaluates Last Price using the current record - which is the last record for that group.

    So the FirstPrice and LastPrice values are then set correctly.  However, it they're then used in another expression in the same section, it doesn't work. But putting them in a later section does work.

    In fact, the first footer section with the FirstPrice and LastPrice expressions doesn't need to be printed.

    Tip: to get the % sign displaying nicely, use Formatting to set a Custom Currency symbol of "%" and the Format to "(1.1)£", like this:

  • 0 in reply to Geoff Turner

    Ah, that's much better than my attempt!

    My approach was to assume a single section header/footer. Then in the header, get the first and last dates - Min(POPOrderReturns.DocumentDate) and Max(POPOrderReturns.DocumentDate) - evaluating the Detail section and resetting on the footer - which I called sectionFirst and SectionLast.

    Then in the detail section I had two (hidden) columns doing this : 

    POPOrderReturns.DocumentDate = SectionFirst? POPOrderReturnLines.UnitBuyingPrice : 0

    ...and the same to compare against SectionLast to give expressions I called FirstBP and LastBP.

    Then in the footer do this : ((Max(LastBP) - Max(FirstBP)) / Max(LastBP) * 100) - evaluating the Detail section and resetting on the footer.

    It works, but it's far clunkier than your solution.

  • 0 in reply to Chris Burke

    I thought mine was rather clunky!

  • 0 in reply to Geoff Turner

    Well, I'm far too polite to say so.

    At least we've shown it's possible! 

  • 0 in reply to Chris Burke
    SUGGESTED

    Thank you for your updates.  Discussed Report Designer Team who have the details.

    Marked as answered.    Sage 200 Technical Support

Reply Children
No Data