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(ExportFormatType.PortableDocFormat, m_PdfPath + pdfFileName);

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
BitleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
I can't comment on the programming aspects of this as it's completely outside my skills, but from a CR viewpoint, if you are basing a report on two 'tables' ,  then if either is empty I would expect the default result to be 'no report'.  So I start from the opposite position to you - I don't see why you sometimes DO get a report in this situation.

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.
0
BitleyAuthor Commented:
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.
0
peter57rCommented:
Then "no data" is what I would expect.
If you have not created a link then you are defaulting to a cross-join (cartesian join) between the two tables, and if one is empty there should be no output.
In a cartesian join every record in table1 is joined to every record in table2.

If you must show the parameters even if there is no data then you need a second report based solely on the parameter 'table' . This becomes your main report and you add your existing report (modifed to remove the parameters page) into the main report as a subreport
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

BitleyAuthor Commented:
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.
0
mlmccCommented:
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
0
BitleyAuthor Commented:
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.
0
mlmccCommented:
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
0
peter57rCommented:
Michael, I think the poster indicated that there is no link between the tables.
0
BitleyAuthor Commented:
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.
0
mlmccCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.