Nth most frequent N=1, etc 1

[jchewsmith]

I am trying list all of the acct numbers that I grouped in a report on the report footer.
acct# Name
1234 ABC Co
5678 ABC Co
9012 ABC Co
2687 ABC Co
Total ABC

My report body only shows the Total ABC lines not the detail, so I want to include all acct#'s in the report footer.

(This report included acct#'s 1234, 5678, 9012, 2687)
I am currently using Nth most frequent N=1, N=2, etc. but not all account numbers are showing.

Any other ideas?

 

[briangriffin]

Use an array variable - check each record to see if the account number has already been encountered, and if not add it to the array:

whileprintingrecords;
stringvar array v_accounts;

if not ({Account} in v_accounts)
then
(redim preserve v_accounts[(ubound(v_accounts) + 1)];
v_accounts[ubound(v_accounts)] := {Account})

Then display the results in another formula:

whileprintingrecords;
stringvar array v_accounts;
 "Includes accounts " + join(v_accounts,", ") + "."

This assumes your account number is a text field. If it's a number, your best bet is to convert it to text, then use the formula instead of the database field in the array formula.

 

[jchewsmith]

I tried this and I am not getting any results.

My field name is {cust #} and is a string.

Here is what I put in the first formula:

whileprintingrecords;
stringvar array v_accounts;

if not ({SSRpt_SalesbyInvoice.Cust #} in v_accounts)
then
(redim preserve v_accounts[(ubound(v_accounts) + 1)];
v_accounts[ubound(v_accounts)] := {SSRpt_SalesbyInvoice.Cust #})

and if I add this to the report it shows me one account number and that is the last one in the report.

Then my second formula is:
whileprintingrecords;
stringvar array v_accounts;
"Includes accounts " + join(v_accounts,", ") + "."

and it only displays: Includes accounts .

What am I missing.

Oh and Thank you so much for the expert help.

 

[briangriffin]

The first formula is just to accumulate the values; you can suppress it. If it's displaying the latest value then it's working correctly.

Where are each of these formulas placed? Is the display formula only to appear in the report footer?

 

[jchewsmith]

Yes.

 

[briangriffin]

Where is the first formula placed? It should be either in the details, or else group header/footer if the report is grouped by account number.

 

[jchewsmith]

That did it.

Thank you very much.