Formula Logic to Format String Data into Fixed Columns

[mrdjsm1th]

We have a business requirement to print a list of ticket numbers into a fixed column format and include an asterisk at the beginning of each row.

The following @PopulateList formula is currently used within the detail section of the report:

// {@TicketNo} is the trimmed Ticket
// {@TicketLength} is a variable used to define the number of characters for each ticket including spaces
WhilePrintingRecords;
stringvar stTickets :=
if (instr(stTickets, {@TicketNo}) >0 )
then stTickets // if the ticket is already in stTickets then do nothing
else stTickets + replicatestring(" ",{@TicketLength}-len({@TicketNo})) + {@TicketNo} // add the ticket with prefix spaces so all tickets are fixed width
-------------------
The above @PopulateList formula currently produces the following output:
100 1000 10000 100000 200000 300000
400000 1000000 3000000 10000000 20000000 500000
600000 700000 8000000 4000000 60000000 70000000
101 1001 10001 100001 200001 300001 400001
1000001 3000001 10000001 20000001 201 2001
20001 500001 600001 700001 8000001 4000001
60000001 70000001
-------------------
Our business requirement is for the @PopulateList formula to produce the following output, where the number of tickets included per row is defined in a variable (5 in this example) and each row begins with an asterisk:

* 100 1000 10000 100000 200000
* 300000 400000 1000000 3000000 10000000
* 20000000 500000 600000 700000 8000000
* 4000000 60000000 70000000 101 1001
* 10001 100001 200001 300001 400001
* 1000001 3000001 10000001 20000001 201
* 2001 20001 500001 600001 700001
* 8000001 4000001 60000001 70000001
-------------------
An @DisplayFixedColTickets formula was created in a group footer in an effort to format the @PopulateList results to meet the business requirements, but it only produces the first row, and it needs to execute multiple times until all of the tickets are included (this is where I am stuck):

numbervar StringLength := len({@DisplayTickets});
numbervar TicketLength := 15;
numbervar TickstsPerRow := 5;
numbervar TicketPosition := 1;
stringvar Identifier := " * ";
stringvar Output := "";

Output := mid({@DisplayTickets}, TicketPosition, (TicketLength * TickstsPerRow));
Output := Identifier + Output + chr(13)
// need logic to read through @DisplayTickets that will:
// allow the number of tickets per row to be controlled by the TicketsPerRow variable
// Include the " * " identifier at the beginning of each row
// Dynamically generate enough rows until all tickets are printed
-------------------
Please share any ideas on how to modify these formulas so the output will include all tickets within the fixed-column format.

The following SQL includes sample ticket information for two plants. There are some intentional duplicate tickets in the sample data; the printed ticket list should only include distinct tickets (no duplicates):

Select '100' as Ticket, 1 as Plant
Union ALL Select '100', 1
Union ALL Select '1000', 1
Union ALL Select '1000', 2
Union ALL Select '10000', 1
Union ALL Select '100000', 1
Union ALL Select '200000', 1
Union ALL Select '300000', 1
Union ALL Select '400000', 1
Union ALL Select '1000000', 1
Union ALL Select '2000000', 2
Union ALL Select '3000000', 1
Union ALL Select '3000000', 2
Union ALL Select '10000000', 1
Union ALL Select '20000000', 1
Union ALL Select '200', 1
Union ALL Select '2000', 1
Union ALL Select '2000', 2
Union ALL Select '20000', 1
Union ALL Select '200000', 1
Union ALL Select '500000', 1
Union ALL Select '600000', 1
Union ALL Select '700000', 1
Union ALL Select '8000000', 1
Union ALL Select '9000000', 2
Union ALL Select '4000000', 1
Union ALL Select '4000000', 2
Union ALL Select '60000000', 1
Union ALL Select '70000000', 1
Union ALL Select '101', 1
Union ALL Select '1001', 1
Union ALL Select '1001', 2
Union ALL Select '10001', 1
Union ALL Select '100001', 1
Union ALL Select '200001', 1
Union ALL Select '300001', 1
Union ALL Select '400001', 1
Union ALL Select '1000001', 1
Union ALL Select '2000001', 2
Union ALL Select '3000001', 1
Union ALL Select '3000001', 2
Union ALL Select '10000001', 1
Union ALL Select '20000001', 1
Union ALL Select '201', 1
Union ALL Select '2001', 1
Union ALL Select '2001', 2
Union ALL Select '20001', 1
Union ALL Select '200001', 1
Union ALL Select '500001', 1
Union ALL Select '600001', 1
Union ALL Select '700001', 1
Union ALL Select '8000001', 1
Union ALL Select '9000001', 2
Union ALL Select '4000001', 1
Union ALL Select '4000001', 2
Union ALL Select '60000001', 1
Union ALL Select '70000001', 1

 

[hilfy]

I would split the string and add the asterisks while you're creating it. Something like this:

WhilePrintingRecords;
Shared Numbervar rowCount;
Shared stringvar stTickets;
StringVar formatTicket;
numbervar TickstsPerRow := 5;

if OnFirstRecord then 
(
 rowCount := 1;
 stTickets := '*';
);
if rowCount mod TicketsPerRow = 0 then //we've processed the fifth record
  stTickets := stTickets + chr(13) + '*'; //Add the new line + *

//Format the ticket number
formatTicket := replicatestring(" ",{@TicketLength}-len({@TicketNo})) + {@TicketNo};

//only add if it's not already in there
if (instr(stTickets, formatTicket) = 0 ) then 
  stTickets + FormatTicket;

stTickets

NOTE: From experience with stuff like this, you need to be sure to search for the formatted ticket number in stTickets, not just the ticket number. If your data was in this order:

10000
1000
100

Neither 1000 nor 100 would appear in the string because both are part of 10000. Make sense?

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[mrdjsm1th]

Thanks Dell.

I tried using the suggested logic in the @PopulateList formula in the report detail section, but it just prints an asterisk for every row. It looks like the issue is the rowCount variable does not increment after each distinct ticket is added, and I am not sure how to do that in Crystal syntax. In addition, the report has a group for Plant, so it looks like the OnFirstRecord function will only work for the first plant.

Please tell me if I have missed something in your suggestion.

 

[lbass]

Add this formula to the detail section:

WhilePrintingRecords;
stringvar stTickets;
numbervar cnt;
if instr(stTickets, {@TicketNo})>0 then (
stTickets := stTickets;
cnt := cnt
);
if onfirstrecord or
instr(stTickets, {@TicketNo})=0 then
(
cnt := cnt + 1;
if onfirstrecord or
remainder(cnt-1,5)=0 then
stTickets := stTickets + chr(13)+"*"+
replicatestring(" ",{@TicketLength}-len({@TicketNo})) + {@TicketNo}+" " else
stTickets := stTickets +
replicatestring(" ",{@TicketLength}-len({@TicketNo})) + {@TicketNo}+" "
);

In the report footer, add a formula like this:
WhilePrintingRecords;
stringvar stTickets;

Format it to can grow and use a nonproportional font like Courier New.

-LB

 

[mrdjsm1th]

Thanks LB.

I added the formula to the detail section, and also added the second formula to the group footer because the report groups the tickets for each plant. There are some issues with the logic in the detail section formula:

1.) A duplicate ticket number (100) was included twice in the results and it should only be included once.
2.) The first line in stTickets does not have the "*" row identifier and it only contains one ticket
3.) The second line in stTickets starts with the "*" row identifier and it looks all of the tickets are on this line, but there should only be five. All of the reaming tickets should be on separate rows, with five per row and each row starting with the "*" identifier.

 

[mrdjsm1th]

Sorry LB, I forgot to remove another formula from the report that was also using the stTickets variable. It looks like the logic is working correctly! Thanks for taking the time to help.

 

[mrdjsm1th]

The logic submitted by lbass works for the first plant (the report groups by plant), but not for the second plant. The second plant shows one ticket in the first row and I believe it is because the onfirstrecord function only works for the first group. I am trying to update the logic to use "if cnt = 0" in place of the onfirstrecord function. However, I tried to reset the cnt (counter) variable to 0 with a formula in the plant group header, but I can't seem to get the varialbe to reset.

Here is the formula I am using in the plant group header:

whileprintingrecords;
stringvar stTickets:="";
numbervar cnt = 0; //reset this to zero for each plant

Do I need to reset the variable differently?

 

[mrdjsm1th]

I figured out the issue with resetting the cnt (counter) variable. The colon was missing (should be cnt := 0, not cnt = 0) , and now everything appears to be working correctly. Thanks lbass and Dell for your suggestions!