Building a Comma Seperated List in a Field
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21045
Printed Date: 04 May 2024 at 1:37pm
Topic: Building a Comma Seperated List in a Field
Posted By: kurt
Subject: Building a Comma Seperated List in a Field
Date Posted: 19 Sep 2014 at 1:51am
Hello everyone, fairly new to CR, using CR2011 and SQL database to produce some reports. I have two linked tables, 1:N, and want to take the N side and instead of producing 4 entries, want to concatenate into a Comma Separated List. I found some pretty helpful stuff online, but it just isn't working. The goal is to have a sub report which produces this Comma Separated List and that is inserted in the detail section of my main report. In most cases the Comma Separated List only has like 3 or 4 values.
In the sub report, I am using 3 formulas. Formula 1 & 2 in the header, formula 3 in the detail section.
Formula One Initialize Array:
Shared stringVar array hazards := [""];
Shared numberVar x := 1;
Formula Two Load the array with values:
Shared numberVar x;
Shared stringVar array hazards;
hazards [x] := {HazardList.Name};
Redim Preserve hazards[UBound(hazards) +1];
x := x+1
Formula Three Print the Array:
Shared stringVar array hazards;
Join (hazards, ", ")
Here is the output I see in the detail section, and it is not an array list:
Hazard One,
Hazard One,
Hazard One,
What it should be is:
Hazard One, Hazard Two, Hazard Three
|
Replies:
Posted By: hilfy
Date Posted: 26 Sep 2014 at 10:14am
You don't need to do this with an array - that's the more complicated way of doing this!
Here's what I would do:
Formula 1 - put this in the report header section of the subreport:
WhilePrintingRecords;
Shared StringVar hazards := "";
Formula 2 - put this in the details section of the subreport:
WhilePrintingRecords;
Shared StringVar hazards;
if hazards = "" then
hazards := {HazardList.Name}
else
hazards := ", " + {HazardList.Name}
Formula 3 - Put this where you want the data to appear on the report:
WhilePrintingRecords;
Shared StringVar hazards
Note that there's no semi-colon at the end of this. If you put the semi-colon there, the value will not appear on the report.
I also do a couple of things in the main report when I work with subreports like this that only return a value for use in a report:
1. The subreport goes into its own section above where the data will be needed. I turn off the borders and turn off "Can Grow" under "Format Subreport..."
2. Make the height of the subreport as small as you can get it - I'll go to "Size and Position" on the right-click menu and set the height to something like .055 and the Y position to 0 so that the subreport is at the very top of the section.
3. Make the height of the section the subreport is in as small as you can. This way it won't really take up any space between the details sections on the report.
By doing it this way, you're not trying to fit a subreport into your data itself - you're just using a value generated by one.
Having said all of that, do you really need to use a subreport? If your report will only ever pull a very small number of rows, it's probably ok. However, every time a subreport is called in your report (in your case, for every row in the data) Crystal has to connect to and query the database for the data for that instance of the subreport. If you have a LOT of data, this will significantly slow the report down.
If you have the ability to create a stored function in the database (or have your DBAs do so) then I would create a function that takes whatever field you're currently using for the link to the subreport as an input parameter, queries the table for the values, and walks through the rows in the query to concatenate the values, which it will then return. You can then call this function in a SQL Expression in your report. The concatenated string from the SQL Expression can then be used in the same way that any field or formula in the report is used. SQL Expressions are evaluated by the database as part of the Select statement that pulls the data for the report. The database is more efficient at handling this sort of thing than Crystal is, so this will be MUCH faster than trying to do this in Crystal.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: kurt
Date Posted: 29 Sep 2014 at 7:27am
Thanks Dell, All great info.
I had to modify the last line of formula 2 to say
hazards := hazards + "," {HazardsList.Name}
Not sure if this is causing the problem, but I am getting this in my report...
KurtsNumberOne
KurtsNumberOne, KurtsNumberTwo
I can't figure out how to suppress the first line, it is like it is printing everytime it finds a hazard. Basically creating a new detail everytime it prints a hazard.
Thanks
Kurt
|
Posted By: hilfy
Date Posted: 29 Sep 2014 at 8:41am
What type of a section are you putting this formula in? If it's a details section, that's your problem. It won't display correctly unless it's in a group or report footer section.
Acutally... looking at this more closely, you need to put a semi-colon on the end of the formula. That way it will process in the details section but not display any values. You then put the third formula I gave you in a footer section to display the full value of the concatenated string.
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: kurt
Date Posted: 30 Sep 2014 at 3:51am
Many Thanks Dell,
Got it working today, placed it in the footer of the report and it looks great! I have been working on this for like 4 weeks off and on.
Regards,
Kurt
|
|