add STRING_AGG transform to reporter

[Llewellyn Marshall]

Evergreen 3.12

I've created a small patch that adds String Aggregate (STRING_AGG) as a transform to the reporter module. This allows users to aggregate all of the rows from a query within a comma separated string. Data is automatically cast as text so that any datatype except Boolean can be used with this transform. Duplicate and blank values are skipped.

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/lew/reporter_string_agg_rebase

[Mike Rylander (mrylander)]

Llewellyn, I like this a lot.

I have some feedback that may make future-us happy. What do you think of adding two versions of this aggregate:

 * "Comma-separated list" -- what you have now, but renamed and relabeled
 * "String aggregate with delimiter" -- a version of this that specifies one parameter to collect, which would be the delimiter to use instead of a comma.

I would be in favor of a "include empty strings" version for each that skips the NULLIF() part, but not so strongly in favor that I'd stop inclusion without that.

Thanks!

[Llewellyn Marshall (lbmarshallv)]

Hi Mike,

Thanks for your feedback! I think it's a good idea to have a configurable string_agg and I see that it wouldn't be too difficult to add it into the code. What I'm confused about is how to input the parameters in the reporter. I don't see it in the template edit screen. I've been testing with the substring transform and it's making the reporter module crash on both our test server and production server

[Chris Sharp (chrissharp123)]

Removing pull request since it sounds like more work is required.

[Benjamin Murphy (benjamin.murphy)]

So, if I understand correctly, in the reporter templates we have the Core Source, Source Path and Transform. This string agg function would appear in the Transform section list like Raw Data or other grouping functions like max. Renaming this to "Comma-separated list" as suggested could add it in that context as well.

Mike, is there another example of a Transform like the idea you have for "String aggregate with delimiter" where its not just something you select from the list of Transforms, but instead has a mechanism where you're supplying a value via a popup or interface?

If not, should the implementation of that potential functionality block the deployment of this current functionality?

[Terran McCanna (tmccanna)]

I think this would be good to move forward after:

1) "String aggregate" is changed to "Comma-separated list"
2) My understanding is that the PO files shouldn't be included in the patch as they are built separately

For the sake of progress, I think the additional suggestions could be a follow-up wish list.

[Terran McCanna (tmccanna)]

(Removing 3.12 milestone since it still needs a little work, but it seems to be close.)

[Llewellyn Marshall (lbmarshallv)]

New branch off of Main:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/lew/reporter_comma_separated_list

String aggregate renamed to "Comma-Separated List" and PO files are removed.

[Terran McCanna (tmccanna)]

Llewellyn, can you please modify your commit message to start with the LP# and title and to include a "Release-note" line? There's an example here:

https://wiki.evergreen-ils.org/doku.php?id=newdevs:git:create#make_your_changes

Thank you!

[Llewellyn Marshall (lbmarshallv)]

reworded commit message and force updated

[Susan Morrison (smorrison425)]

Testing for bug squashing, I see the option for Comma-Separated List in the Transform options, but when running a report to test the output, I keep getting this fail message: Can't call method "add_worksheet" on an undefined value at /openils/bin/clark-kent.pl line 480. Is this possibly a server issue?