sum up quantity based on least transaction date

[Former Member]

Hi all,

          I have some data in the following way. I need to sum up the qty of least(minimum) transaction date and display on group header (group 1 :item)

Item	tran date	qty
CB-5M-921-003	2/25/2014	4
CB-5M-921-003	2/25/2014	6
CB-5M-921-003	03/12/2014	11
CB-5M-921-003	3/22/2014	5
CB-5M-921-003	3/22/2014	8
CB-5M-921-003	4/25/2014	9

group 1 : item

group 2 : tran date

output in group header 1 :      item : CB-5M-921-003   Qty : 10

please let me know your views .

[abhilash_kumar · Accepted solution]

Hi Akram,

This is fairly easy to achieve if you wish to display this on the Group Footer.

Since you need to display this on the Group header, here's what you need to do:

1) Save the existing report in a folder

2) Go to Insert > Subreport > Select 'choose an existing report' and browse to the report you saved in step 1

3) Place the Subreport on the Group Header 1

4) Right-click the Subreport > Change Subreport Links > Move the 'Item' field to the pane on the right > From the drop-down at the bottom right where it says 'Select data in Subreport based on field', choose the 'Item' field

5) Double-click the Subreport so that you enter the design pane > Suppress all sections except the Report Footer

6) Create a formula with this code and place this on the details:

whileprintingrecords;

numbervar s;

if {tran_date} = Minimum({tran_date}, {Item}) then

     s := s + {qty};

'';

7) Create another formula with this code and place this on the Report Footer of the Subreport:

whileprintingrecords;

numbervar s;

😎 Format the Subreport so that it matches any formatting on the Main Report.

-Abhilash

[Former Member · Accepted solution]

Hi abhilash,

Could you please let me also know how to achieve it in group footer.

Regards,

akram

[abhilash_kumar · Accepted solution]

1) Right-click Running Total Fields under Field Explorer > New.

Choose the 'Qty' field as the Field to Summarize.

Under 'Evaluate', select 'Use a formula' and use this code:

{tran_date} = Minimum({tran_date}, {Item})

Under 'Reset', select 'Never'.

2) Place this Running Total on the Group Footer.

-Abhilash

[Former Member · Accepted solution]

Thanks for the immediate reply abhilash,

In both the cases i.e sub report and report footer  i am  getting the result as 4 i.e the first value of qty and not the sume i.e 4+6 = 10 .

Please suggest.

Regards,

Akram

[abhilash_kumar · Accepted solution]

Does the transaction date field have a timestamp?

Try this:

1) Create a formula with this code:

date({tran_date})

2) Modify the 'Evaluate' formula of the Running Total to:

date({tran_date}) = Minimum({@formula_tran_date}, {Item})

-Abhilash

[Former Member · Accepted solution]

Hi,

transaction does not  have timestamp .

[abhilash_kumar · Accepted solution]

Please attach the report with saved data or send it to my email.

-Abhilash

[Former Member · Accepted solution]

Hi abhilash,

               Group footer works... thanks for the support . I have marked it correct answer.

akram

[Former Member]

Hi Akram,

Create a new Running Total, say RTQty. In general properties of RTQty, change the Reset Condition to "Use Formula" and use the formula as:

{tran date}=minimum({tran date})

and use this formula in the group header.

-Ankush