[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
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
29
Medium Priority
?
1,075 Views
Last Modified: 2013-11-15
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
Comment
Question by:tiburondelcaribe
  • 13
  • 12
  • 4
29 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 37021695
Are you trying to show the total for a page? a group? or the full report?

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 37023486
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

by:tiburondelcaribe
ID: 37024367
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:tiburondelcaribe
ID: 37024397
mlmcc:

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

Expert Comment

by:mlmcc
ID: 37024610
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

by:tiburondelcaribe
ID: 37025469
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 101

Expert Comment

by:mlmcc
ID: 37025560
Do you have the formulas formatted to grow?

mlmcc
0
 

Author Comment

by:tiburondelcaribe
ID: 37026588
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 101

Expert Comment

by:mlmcc
ID: 37026655
Can you upload the report?

mlmcc
0
 

Author Comment

by:tiburondelcaribe
ID: 37026835
Here it is. thanks
report1dcab.rpt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37027180
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

by:tiburondelcaribe
ID: 37027414
OKay, this one I saved with data.   thanks
report1dcab.rpt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37027609
I don't see the data.  It asks to connect to the repository

mlmcc
0
 

Author Comment

by:tiburondelcaribe
ID: 37027865
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 101

Expert Comment

by:mlmcc
ID: 37028197
Run this copy and export it to PDF

mlmcc
Q-27413053Rev1.rpt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37028230
I may have forgot to save my change

mlmcc
Q-27413053Rev1.rpt
0
 

Author Comment

by:tiburondelcaribe
ID: 37030851
Here are the latest report results in PDF, thanks

Tiburon
report-2.pdf
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37031722
Try this one

mlmcc
Q-27413053Rev2.rpt
0
 

Author Comment

by:tiburondelcaribe
ID: 37034376
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 101

Expert Comment

by:mlmcc
ID: 37034747
Here is another tweaked version.

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

Without access to the data it is very difficult to tell

mlmcc
Q-27413053Rev3.rpt
0
 

Author Comment

by:tiburondelcaribe
ID: 37035781
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 101

Accepted Solution

by:
mlmcc earned 1800 total points
ID: 37036217
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 35

Assisted Solution

by:James0628
James0628 earned 200 total points
ID: 37036892
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 101

Expert Comment

by:mlmcc
ID: 37037546
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

by:tiburondelcaribe
ID: 37039956
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 35

Expert Comment

by:James0628
ID: 37043719
 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 101

Expert Comment

by:mlmcc
ID: 37044759
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 35

Expert Comment

by:James0628
ID: 37045935
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

by:tiburondelcaribe
ID: 37076474
This solution worked as aupposed to, thanks.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question