How to approach grouping strings - data is postcode related

[shanedevlin]

I'm looking for some help with a problem around grouping some data together and the best way to achieve it in a report.

Very simply, I have a table with a list of postcodes (string ie BA10), which is also split into two seperate fields PC1 (string - "BA") and PC2 (number - "10"). I'm trying to produce a report to group these together (by office).

Rather than having BA1, BA2, BA3, BA4 on four seperate detail lines, im trying to achieve either "BA1 BA2 BA3 BA4" as one formula/on one line, or even better would be "BA1-4" though i think that might be asking too much.

Hopefully the screenshot below helps explain things a little better. Any help/suggestions would be great. Thanks.

[former_member751964]

Thank you for visiting SAP Community to get answers to your questions. Since you are new, I recommend that you familiarize yourself with our Q&A Tutorial: https://developers.sap.com/tutorials/community-qa.html. It provides tips for preparing questions that draw responses from our members. Should you wish, you can revise your question by selecting Actions, then Edit.

By adding a picture to your profile you encourage readers to respond: https://www.youtube.com/watch?v=46bt1juWUUM

Thank you!

[DellSC]

Your screenshot didn't attach.

[shanedevlin]

Now attached, thanks for pointing that out.

[DellSC · Accepted solution]

Try something like this:

1. Group on PC1

2. Create a formula like below to append PC2 to the end of PC1

{@PostalCode}
{table.PC1} + ToText({table.PC2}, 0, "")

3. Create a formula like below that will concatenate the postal code values for the PC1 group

WhilePrintingRecords;
StringVar postcodes;
If OnFirstRecord or {table.PC1} <> previous({table.PC1}) then 
  postcodes := {@PostalCode}
else
  postcodes := postcodes + " " + {@PostalCode};
""

4. Put this formula in the details section and then suppress the details.

5. Create a final formula like below to display the concatenated values in the group footer:

WhilePrintingRecords;
StringVar postcodes

-Dell

[shanedevlin · Accepted solution]

Dell thats amazing, and very much nearly there. The only problem I have is that i need the formula to reset on group footer 2. See screenshot - Bristol South is 1-16, Weymouth should only be 8, 20, 21, 22 not 1-16 and then 8, 20, 21, 22 (screenshot attached).

[DellSC · Accepted solution]

Assuming that you're also grouping on OfficeName, change the If statement in the middle formula from

If OnFirstRecord or {table.PC1} <> previous({table.PC1}) then 

to

If OnFirstRecord or {table.OfficeName} <> previous({table.OfficeName}) then 

-Dell

[shanedevlin · Accepted solution]

Perfect, thank you very much 🙂