Solved

# Crystal reports, I need to pass an multiple array field to an array and then print the count for each array

Posted on 2011-10-24
1,058 Views
I have an array field called DCAB, and I need to count how many aere within each record, and then total them with a description and count for each one.

Record1:  DCAB-USA DCAB-JAP  DCAB-ITO
Record2:  DCAB-ITO DCAB-USA DCAB-JAP
Record3:  DCAB-JAP DCAP-ITO DCAB-USA
Record4:  DCAB-USA
Record5:  DCAB-USA DCAB JAP

As you can see above each record can have 1 or multiple fields, so I need to count the DCABS for each record, build an array for the array field, and print at the and the following sample:

DCAB-JAP 4
DCAB-USA 5
DCAB-ITO 3

Here is the array that I wrote, but am gettting wrong count within each group, and all zeros at the page footer:

*** At the page header ***

//ArrayDCABInit
whileprintingrecords;
numbervar counter := 0;
numbervar positionCount:=count({Change.RFC_DCAB_Groups});
global stringvar Array DCABNameArray;
global numbervar Array DCABCountArray;
while counter < positionCount do
(
counter:=counter + 1;
redim preserve DCABNameArray[counter];
redim preserve DCABCountArray[counter];
DCABNameArray[counter] := trim({Change.RFC_DCAB_Groups}[counter]);
DCABCountArray[counter] := 0
);

*** In the detail section ***/

/ArrayDCABIncrement
whileprintingrecords;
numbervar counter := 0;
numberVar lastCounter;
global stringvar array DCABNameArray;
global numbervar array DCABCountArray;
stringVar arrayDCAB;
stringVar fieldDCAB;
numbervar positionCount:=count(DCABCountArray);
while counter < positionCount do
(
counter:=counter + 1;
arrayDCAB := trim(DCABNameArray[counter]);
fieldDCAB := trim({Change.RFC_DCAB_Groups});
if arrayDCAB = fieldDCAB Then
DCABCountArray[counter] := DCABCountArray[counter]+1;
lastCounter:=counter;
counter:=positionCount

);
DCABNameArray[lastCounter] + " " + Chr(9) + " " + Cstr(DCABCountArray[lastCounter]) + Chr(9) + {Change.RFC_DCAB_Groups} + Chr(9) + Cstr(lastCounter) + Chr(9) + Cstr(positionCount) + chr(9) + arrayDCAB + Chr(9) + fieldDCAB;

*** At the page footer to print ***

//ArrayDCABPrint
whileprintingrecords;
numbervar counter := 0;
numbervar positionCount:=count(global numbervar array DCABCountArray);
stringvar printme := "";
while counter < positionCount do
(
counter:=counter + 1;
if (counter=1) Then
printme := global stringvar array DCABNameArray[counter] + chr(9) + chr(9) +  cstr(global numbervar array DCABCountArray[counter]) + chr(10)
else
printme := printme + global stringvar array DCABNameArray[counter] + chr(9) + chr(9) +  cstr(global numbervar array DCABCountArray[counter]) + chr(10)
;
);
printme

ANY HELP WITH THIS IS APPRECIATED, THANK YOU.
0
Question by:tiburondelcaribe

LVL 100

Expert Comment

Are you trying to show the total for a page? a group? or the full report?

mlmcc
0

LVL 34

Expert Comment

Do you really have "an array field"?  Does CR even have such a thing?  What type does CR show for {Change.RFC_DCAB_Groups} ?  My guess is that it's just a string.

If so, then Count ({Change.RFC_DCAB_Groups}) is just going to return the record count for the report (not the number of elements in an array in the current record), so it's going to be the same number for every record.  And {Change.RFC_DCAB_Groups}[counter] will extract individual characters from the string field, as opposed to elements from an array.

Once you confirm what type of field {Change.RFC_DCAB_Groups} actually is, we can go from there.  If it really is an "array field" in CR, we can try to figure out why your formulas aren't working.  If, as I suspect, it's actually just a string, obviously your formulas will need to be changed accordingly.

James
0

Author Comment

James,

{Change.RFC_DCAB_Groups}  is an array field considered as  a string filed that may contain up more than one DCAB within the string as shown below: I need to count and summarize how many are there for each record.

DATA
StringArray1:  DCAB-USA DCAB-JAP  DCAB-ITO
StringArray2:  DCAB-ITO DCAB-USA DCAB-JAP
StringArray3:  DCAB-JAP DCAP-ITO DCAB-USA
StringArray4:  DCAB-USA
StringArray5
:  DCAB-USA DCAB JAP

I want to get this output:

DCAB-JAP 4
DCAB-USA 5
DCAB-ITO 3

0

Author Comment

mlmcc:

Yes I would like to break the total by group and total at the report footer..
0

LVL 100

Expert Comment

Something like this report.  It assumes the lists are separated by a space and have DCAB-xxx as the format

mlmcc
Q-27413053.rpt
Q-27413053.mdb
0

Author Comment

mlmcc:

This code almost worked, but it only totals for the first DCAB found on the first string array, and it does not for the other DCABS. The count for the DCAB is correct, but it ignored the other DCABS in th efirst array and other string srrays as well.  It may help to know thast I am also grouping on date so I dnot know if that is throwing off the count,  thank you.
0

LVL 100

Expert Comment

Do you have the formulas formatted to grow?

mlmcc
0

Author Comment

I change them to grow, but am still not getting the results needed, please see attached doc.  it is still adding up on that first string dcab and ingoring the rest.
dcab-TOTALS.docx
0

LVL 100

Expert Comment

mlmcc
0

Author Comment

Here it is. thanks
report1dcab.rpt
0

LVL 100

Expert Comment

The name summary was not set to grow.

Can you save the report with data or build another report that just shows the summaries?

mlmcc
0

Author Comment

OKay, this one I saved with data.   thanks
report1dcab.rpt
0

LVL 100

Expert Comment

I don't see the data.  It asks to connect to the repository

mlmcc
0

Author Comment

Yup, the data it at a repository, I can run the report and export it to xls with data only.  DO you have any suggestions as far as getting the data to display?

I have attached a pdf copy and xls, and also the arrays, I also added a csv file that you may be able to build a quick report with it.  thank you.
report1.pdf
DisplayDCABSummary.txt
DisplayDCABCounts.txt
DeclareVariables.txt
BuildSummary.txt
table-export.csv
0

LVL 100

Expert Comment

Run this copy and export it to PDF

mlmcc
Q-27413053Rev1.rpt
0

LVL 100

Expert Comment

I may have forgot to save my change

mlmcc
Q-27413053Rev1.rpt
0

Author Comment

Here are the latest report results in PDF, thanks

Tiburon
report-2.pdf
0

LVL 100

Expert Comment

Try this one

mlmcc
Q-27413053Rev2.rpt
0

Author Comment

I have attached report-3 PDF, and also a report-3-notes.

The totals are not adding up for example, there should be a total of 35 for DCAB-US-CHMGR: a single total entry. there 5 entries that add up to 14.

DCAB-US-CHMGR 35

report-3.pdf
report-3-notes.docx
0

LVL 100

Expert Comment

Here is another tweaked version.

I think the issue is with how the DCAB strings are formatted in the data.

mlmcc
Q-27413053Rev3.rpt
0

Author Comment

I have attached a copy of how the dcab field looks in the data warehouse and also exported some data into a spreadsheet,and you will see that there are spaces between each dcab for each record.

I understand this is hard to troubleshoot, but perhaps that xls will help.
datawarehousefield.docx
report-dcab-data.xls
reportrev3-output.pdf
0

LVL 100

Accepted Solution

I don't see spaces.  They seem to be separated by CR/LF

Rev4 splits on CR/LF
Rev5 splits on " " but accounts for the CR/LF in the summary so the counts line up

mlmcc
Q-27413053Rev4.rpt
Q-27413053Rev5.rpt
0

LVL 34

Assisted Solution

FWIW, I had no problem seeing the data in the report in post 37027414.  I don't know why you couldn't see it mlmcc.

You're correct, mlmcc.  The strings (in that report at least) are separated by CR-LF, not spaces.  I haven't looked at your latest versions of the report, but if you're handling that, they should work.  Changing @BuildSummary (in that report with the saved data) so that it Split the string on ChrW (13) + ChrW (10), instead of " ", seemed to do the trick.

James
0

LVL 100

Expert Comment

I can't see it.  It asks me to log into the repository or the database.  Since I don't have either available or the universe database driver loaded it must drop the data.

I don't even hav ethe preview tab opened when the report loads.

mlmcc
0

Author Comment

mlmcc,

it seems to be working now, I will run the report a few more times, rev 4 is what I needed, but rev5 might be handy as well. sorry about the repository.  will awards points soon. thank you.
0

LVL 34

Expert Comment

mlmcc,

Strange.  I've never used the CR repository.  I know almost nothing about it, but I think it's a pretty safe bet that if the repository was required, I wouldn't see the report's saved data on this system.  However, I do have BO 6.5 installed on this system.  Maybe that's why it works.  That's about all that I can think of.

James
0

LVL 100

Expert Comment

It my be the BO install but I doubt it since they weren't that integrated at that point.  It may also be a difference between your CR10 and my CR XI

mlmcc
0

LVL 34

Expert Comment

I wouldn't have thought the BO install was an issue, but it was all I could think of.  Maybe it is the CR version.  If one was going to have a problem, I'd expect it to be CR 10, not XI, but you never know.

James
0

Author Closing Comment

This solution worked as aupposed to, thanks.
0

## Featured Post

### Suggested Solutions

How to increase the row limit in Jasper Server.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you sâ€¦
how to add IIS SMTP to handle application/Scanner relays into office 365.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet sâ€¦