Bitley
asked on
Crystal Report with two DataSets displays either only if both have data
Hi experts,
We have a number of reports (~15) implemented in Crystal 2008, used in a website implemented in C# 3.5. The reports are designed with XML files as their datasource and deployed with XSD files as their datasource. Each report calls an Oracle 10g stored procedures that ultimately generates a DataSet object containing two tables, DATA_RECORD and REPORT_PARAM, where DATA_RECORD is an array of records used to populate the report body and REPORT_PARAM is a single record containing the parameter values, for display in a cover page.
Here's the snippet where we're generating the report and writing it to disk:
1] DataSet ds = handler.Execute(criteria, GetStoredProcedureName());
2] ReportDocument rd = new ReportDocument();
3] rd.Load(GetRptFilePath());
4] rd.SetDataSource(ds);
5] rd.ExportToDisk(ExportForm
REPORT_PARAM always contains one row; DATA_RECORD may contain 0 rows if the user-specified parameters filter out everything. If we put a breakpoint on line 2 and examine the DataSet assigned in line 1, REPORT_PARAM is populated correctly regardless of the contents of DATA_RECORD.
Here's the problem: For some - repeat, some - of our reports, if DATA_RECORD contains no rows, the report does not display the REPORT_PARAM values either. We've compared RPT files, XSDs, etc. for those that work and those that don't, but haven't been able to identify the cause.
TIA for any help you can provide!
Steve
We have a number of reports (~15) implemented in Crystal 2008, used in a website implemented in C# 3.5. The reports are designed with XML files as their datasource and deployed with XSD files as their datasource. Each report calls an Oracle 10g stored procedures that ultimately generates a DataSet object containing two tables, DATA_RECORD and REPORT_PARAM, where DATA_RECORD is an array of records used to populate the report body and REPORT_PARAM is a single record containing the parameter values, for display in a cover page.
Here's the snippet where we're generating the report and writing it to disk:
1] DataSet ds = handler.Execute(criteria, GetStoredProcedureName());
2] ReportDocument rd = new ReportDocument();
3] rd.Load(GetRptFilePath());
4] rd.SetDataSource(ds);
5] rd.ExportToDisk(ExportForm
REPORT_PARAM always contains one row; DATA_RECORD may contain 0 rows if the user-specified parameters filter out everything. If we put a breakpoint on line 2 and examine the DataSet assigned in line 1, REPORT_PARAM is populated correctly regardless of the contents of DATA_RECORD.
Here's the problem: For some - repeat, some - of our reports, if DATA_RECORD contains no rows, the report does not display the REPORT_PARAM values either. We've compared RPT files, XSDs, etc. for those that work and those that don't, but haven't been able to identify the cause.
TIA for any help you can provide!
Steve
ASKER
Hi Peter, thanks for your comments and questions.
The report does see the tables as two separate tables - in Field Explorer or the Formula Workbench, the fields are grouped separately under DATA_RECORD and REPORT_PARAM. However, the two tables are not related/linked in any way - one is used in one place (the cover page) and the other is used in another place (the report body). I double-checked the links in Database Expert and there are none.
The report does see the tables as two separate tables - in Field Explorer or the Formula Workbench, the fields are grouped separately under DATA_RECORD and REPORT_PARAM. However, the two tables are not related/linked in any way - one is used in one place (the cover page) and the other is used in another place (the report body). I double-checked the links in Database Expert and there are none.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Interesting Peter, thanks. Given that unlinked tables result in a cross-join (which I double-checked elsewhere too, btw), you're certainly right that the presence of the parameter data in the other reports is more of a question than its absence in the problem reports.
So in some reports you get the parameter data even if there is no Data_Record data?
Were the reports built in the same way?
ie Was the Parameter table always chosen first?
Not sure that makes a difference.
In this case I would try the subreport method though I think I would make the parameter report the subreport since it is only 1 record.
mlmcc
Were the reports built in the same way?
ie Was the Parameter table always chosen first?
Not sure that makes a difference.
In this case I would try the subreport method though I think I would make the parameter report the subreport since it is only 1 record.
mlmcc
ASKER
That's right, mlmcc. Since REPORT_PARAM always has a record, the subject of this thread should actually be "Crystal Report with two DataSets sometimes displays first only if second has data". Most of the reports do display the contents of REPORT_PARAM even if DATA_RECORD has no records, but some do not. And it's not intermittent - the reports that work always work, and the ones that don't never do. It's weird, I know.
The reports were built in the same way, and the parameter table is always returned second. Based on what you guys are saying it sounds like separating the two tables and creating a subreport for the params is the way to go, but I'll leave this thread open awhile and see if there are other suggestions.
The reports were built in the same way, and the parameter table is always returned second. Based on what you guys are saying it sounds like separating the two tables and creating a subreport for the params is the way to go, but I'll leave this thread open awhile and see if there are other suggestions.
Check the tables
In the report
Click DATABASE --> DATABASE EXPERT
Click the LINKS tab
Verify the links are LEFT OUTER JOIN
Right click a link
Click PROPERTIES
mlmcc
In the report
Click DATABASE --> DATABASE EXPERT
Click the LINKS tab
Verify the links are LEFT OUTER JOIN
Right click a link
Click PROPERTIES
mlmcc
Michael, I think the poster indicated that there is no link between the tables.
ASKER
Peter, that's correct.
Michael, thanks for the suggestion. I tried creating a link just to see if it would work, but since there's no meaningful way to link the two tables (I picked two varchar fields that will never be equal) the result is that even though the params always showed up, the data never did. An explicit full outer join would be another option, but it's disabled in the dialog and I haven't been able to figure out why.
Michael, thanks for the suggestion. I tried creating a link just to see if it would work, but since there's no meaningful way to link the two tables (I picked two varchar fields that will never be equal) the result is that even though the params always showed up, the data never did. An explicit full outer join would be another option, but it's disabled in the dialog and I haven't been able to figure out why.
If there is no common field then a join won't work.
WHen I made that suggestion, I didn't go back and read the inital comment that said there is no common field.
I think using a subreport may be the way to do this.
mlmcc
WHen I made that suggestion, I didn't go back and read the inital comment that said there is no common field.
I think using a subreport may be the way to do this.
mlmcc
However, I don't know how CR sees your dataset - does it see it as one table or two? (Database>DatabaseExpert menu in CR). If it sees it as a single table then this post is redundant.
To produce a report from tableA and tableB showing tableA even if tableB is empty you would need to use a left join from tableA to tableB. That sounds a bit unlikely from your description.