Count UNIQUE values from String field - SPLIT? Array? Group?

[LWTri]

Hey all, could use help! Thanks ahead of time. Using CRYSTAL XI.


(1) SAMPLE DATABASE RECORDS/FIELDS:

VISITOR DEPARTMENTS (string field w/ comma separators)
------- ----------------------------------------------
Jane HR,IS,Finance
Bob IS,Marketing,CallCenter
Pat Printing,CallCenter,IS,Audits


(2) REPORT GOAL: Display Total Visitors (count) for each UNIQUE Department, and list the Visitors.


(3) REPORT DESIGN:

DEPT TOTAL VISITOR
----------- -------- -------
HR 1
Jane
IS 3
Jane
Bob
Pat
Finance 1
Jane
Marketing 1
Bob
CallCenter 2
Bob
Pat
Printing 1
Pat
Audits 1
Pat



(4) SOLUTION?

(A) I think the best method may be to create a GROUP (DEPT), Running Totals (at group level), and display the list of Visitors in Detail section.

(B) Any suggestions on how to do this? SPLIT the DEPARTMENTS string field into an ARRAY of Unique Departments (do this via a DEPARTMENT Formula)? Then Group on this DEPARTMENT formula?

Would need dynamic functions (not Static Variables) because there will be a long list of departments.
(Was hoping to combine methods suggested in Threads - "Collecting unique values for a group", "Need to split string so user can enter parameter on the split value", etc).

 

[lbass]

Do you have a separate table that contains the list of departments? I think the simplest solution would be to insert a subreport for the list of visitors and counts, that is linked to the main report on the department field. In the subreport, change the selection formula so that it looks like:

{table.departments} like "*"+{?pm-depttable.dept}+"*"

I'm suggesting this because you only have one record per visitor, so you wouldn't be able to get the visitor to appear in more than one group without using a subreport and still have the departments be dynamic.

-LB

 

[LWTri]

Thanks lbass!

(1) Just confirmed that we will have a table that contains a list of departments. That does make things MUCH easier. I decided to try it with no subreport and it actually worked.

(A) Added "depts" (list of departments) and "visits" tables, unlinked. (I put this data in an excel spreadsheet to serve as tables because we don't have tables in RDBMS yet).
(B) Created a GROUP by {depts.DEPT}
(C) Detail section: Displayed {visits.PERSON} and {visits.DEPARTMENTS}
(D) Created RUNNING TOTAL - count on {visits.PERSON} and reset on change of group {depts.DEPT}
(E) Selection Criteria:
{visits.DEPARTMENTS} like "*"+{depts.DEPT}+"*"

The visitors do appear under multiple groups. EX: Jane's single record shows up under all 3 groups (HR, IS and Finance).


(2) I still have a question: What if I didn't have this depts table that lists the departments? What if I still had to create report by determining/collecting the UNIQUE DEPARTMENTS from data in {visits.DEPARTMENTS} string field (comma separated)? May have to know how to do this in a future report. Was thinking that I'd have to use a SPLIT function or loop through the string field to load departments into an array variable (in a formula). I'd probably have to GROUP on and create running totals using that formula.

Thanks again! L