SQL Command returns multiple records, but I see only one record in report

[Former Member]

I work with Crystal Reports XI R2 SP3 and Oracle 10g R2 database.

I have an SQL Command that returns multiple records. Command name is "CommDivisionNames" and it returns column "CommDivisionNames.DIVISION_NAME". When I place this field into report details section of the report, I can see all 10 records returned and this is how it should be. I actually need this field to be placed in the report header section, and when I place the field there, then I see only the first record. I set that field as "can grow = true". When I do "browse field data" for this field, I see that all 10 records are there, but only the first one is displayed in report header section.

I thought that I can place SQL Command field anywhere on the report (page header, footer, details) and that it will always show all records that it returns from the database. Can that be done?

My "main part" of the report returns different set of records, and that's what I have in "report details" section. I need this list of divisions to be in the report header section, so user can see that the report was executed for DivA, DivC, DivE.

Thank you,

Milan

[Former Member · Accepted solution]

Milan,

The Report header section does not repeat the way the details section does. If you want all of the records from the command to appear in the report header, you will have to use a sub-report.

HTH,

Jason

[Former Member · Accepted solution]

My report that I'm talking about here (the one with that SQL Command) is actually already a sub-report. Can I have more than one sub-reports in the main-report, or can a sub-report have its own sub-report(s)?

In this case I have a main-report which is just a "shell" with parameters and formulas. There I use formulas to join multiple vallue parameters in order to pass them as strings to sub-report parameters and SQL commands. My sub-report is actually my "real" report with defined header, footer, detalis, and in that sub-report I need to show multiple division names in sub-report-header. This way of passing multiple value parameters to stored procedures and SQL commands has been described many times in this forum, and that's how I decided to use it in my case.

Thank you,

Milan

[Former Member · Accepted solution]

You can't have a sub-sub-report. Only one level deep is supported.

Can you use a cross tab in the subreport header to list all of the names?

HTH,

Carl

[Former Member · Accepted solution]

do they select the divisions from a parameter,

you want to just display in the report header what the divisions are being displayed as a string,

did you try the join command?

join(,',')

[Former Member · Accepted solution]

sharonamt:

Users select divisions from parameter, but the parameter multi values are division_numbers (1,5,10), not division_names. Division_names are visible in parameter_prompt_window as description, but parameter remembers only numbers and I don't know how I can reuse division_names later in formula.

I do join for division_numbers and make them into one string variable and pass to sub-report, but I think that I can only get these division_names by calling an SQL command or calling stored procedure.

If I try to do join({MySQLcommand.DIVISION_NAME}) I get error message "A string array is required here".

Carl:

I'm playing with cross-tab and I can use it to see all division_names in the report-header section. Since I need them in only one column or only one row, I have to edit cross-tab object and turn all unneeded border lines to white to make them look invisible. So, cross-tab could be a solution for my problem here.

Another option could be to re-write my SQL command. Since I've read here that SQL command could be written in the same way as I would write a stored procedure, then I could use a bit more complex code to get all multiple division names from the database table into a local cursor, and then use do-while loop to concatenate them into one string, and then to return that string as one record (like 'DivA, DivB, DivC'), and then it should not be a problem to display only that one record/string in report header. It is my understanding that Crystal Reports can call stored procedure that works with many cursors/recordsets and CR will use only the last recordset from the stored procedure. Do you think it could be done this way?

Thank you,

Milan

[Former Member · Accepted solution]

"My report that I'm talking about here (the one with that SQL Command) is actually already a sub-report. Can I have more than one sub-reports in the main-report, or can a sub-report have its own sub-report(s)?"

You can have multiple subreports in a report, but the subreports can not contain a subreport.

[Former Member · Accepted solution]

Crystal will only read data from one result set from a SP or SQL Command. I think it's the first, but it could be the last. (I never tried...) But, you can always have more than one SP or SQL Command in a report...

IMHO it'd be a lot clearer to future folks that might modify the report if the cross tab was used. There's some minor up-front formatting to get it looking the way you want, but I see that as a small price to pay for maintainability.

HTH,

Carl

[Former Member · Accepted solution]

We have decide to use cross-tab object in the report header section. It is easy to connect cross-tab object to our SQL command that returns multiple records. Then some formatting is needed on cross-tab object to make all unneeded lines invisible (white colour), but that's also easy.

Thank you all for your help.

Milan