Comma delimited string in report header

[Former Member]

I am new to CR and am having trouble creating a comma delimited string in the report header. In the details of the report I have numerous records with YEAR as the point of interest. I would like to make a list of all the distinct YEARs and then present them in the report header as a comma delimited string. It is important that following the last entry there isn't a comma. It could also mean that there is a YEAR missing which is ok, depends on the records.

ie   2008, 2009, 2010, 2011, 2013

I would like to use this same logic with a list of AUTHORS for a particular publication. I would like to put the AUTHORS in a comma delimited string in the report header and in their order of authorship.

ie Brown, Jones, Smith  where Brown is author 1, Jones author 2 and Smith author 3

I would also like to use this logic for distinct DEPARTMENTS for a particular report. I would like to put the DEPARTMENTS in a comma delimited string in the report header.

ie BSTU, STHY, QUESR, DYTU (They could be order by their location in the report.

Any ideas

[abhilash_kumar · Accepted solution]

Hi Brian,

There are a few ways to do this:

Using Subreports:

1) Insert a Subreport with its datasource being the same table as the Main Report's in the Report Header.

2) Inside the Subreport, create a formula with a code that looks similar to the below:

whileprintingrecords;

stringvar dates := dates + {Year} + ", ";

Place the formula on the Details section

3) Create another formula and place it on the Report Footer:

whileprintingrecords;

stringvar dates;

Left(dates, len(dates)-2);

4) Suppress all the sections of the Subreport, except the Report Footer

5) Copy the record selection formula from the Main Report into the Subreport so that it returns the same records as the Subreport

If the data for 'Authors' and 'Departments' is available for each row just like the 'Year', you can actually use the same subreport and modify the above formula by adding 'shared variables' to bring back a comma delimited string of the 'years', 'authors' and 'departments'

Using SQL:

This is probably the most efficient way to do this. Just write a SQL that returns the list of years as a comma separated string along with the other columns > drag and drop the column on the report header

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Thanks for you response. I only work in this job days per week so sorry for the delay.

I like both answers. Could you explain point 5 a little further, I am not sure what I need to copy into the subreport.

I am very interested in the SQL option. Do I need to write the SQL as a command or is there another way. What do you mean by drag and drop the column. If it is a command do I drag a field from the command and place it in the RH or do you mean something else. How do I drag a column.

[abhilash_kumar · Accepted solution]

Point 5: You need both the Main Report and the Subreport to return same records. Hence you'd want to copy the Record Selection Formula (If any) from the Main Report into the Subreport's record selection formula as well.

The SQL option involves using a Subquery in your existing SQL query so that it returns the years for each row. You can then go to the Field Explorer and just drag and drop this new column that contains the comma separated years to the Report Header. This method will need you to create the report against a SQL Query (via the Add Command option).

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash,

Your code has worked well but there is one more thing. How can I return only distinct records for the dates. I have a delimited string with numerous records for the same year.

2011, 2011, 2011, 2012, 2012, 2013, 2013, 2013.

I would like to return:

2011, 2012, 2013

The Database -> Select Distinct Records option still returns numerous duplicates.

I have been able to use this same formula method for authors and departments. I have put these formulas into a Group in the primary report. I am having trouble refreshing the data in the subreport on the change of group. I have grouped by Publication year (Group #1) and Publication title (Group #2). I would like the detail in the subreport to refresh each time Group #2 changes. At this stage I don't have groups in the subreports as they duplicate the data.

[abhilash_kumar · Accepted solution]

Try modifying the formula #2 that is on the details section of the Subreport to:

whileprintingrecords;

stringvar dates;

If instr(dates, {Year}) = 0 then

     dates := dates + {Year} + ", ";

-Abhilash

[Former Member]

Excellent, that is the answer to the dates.

Did you have any suggestions for the second part to the question:

I have also been able to use this same formula method for authors and departments. I have put these formulas into a Group in the primary report. I am having trouble refreshing the data in the subreport on the change of group. I have grouped by Publication year (Group #1) and Publication title (Group #2). I would like the detail in the subreport to refresh each time Group #2 changes.

[abhilash_kumar]

Hey Brian,

Would you be able to put this second question as a new Discussion per SCN's rules please?

-Abhilash

[Former Member]

Hi Abhilash,

Thank you for all your help. I have opened a new discussion as advised.

"Refreshing subreport data on change of group in the main report"

Thanks

[abhilash_kumar]

Glad I could help.

Please don't forget to close this thread.

-Abhilash