?
Solved

Crystal Report with two DataSets displays either only if both have data

Posted on 2010-01-12
10
Medium Priority
?
832 Views
Last Modified: 2013-11-08
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
0
Comment
Question by:Bitley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 26301434
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
 

Author Comment

by:Bitley
ID: 26303335
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
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 26305903
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:Bitley
ID: 26307027
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 26310624
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
 

Author Comment

by:Bitley
ID: 26314517
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 26327097
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
 
LVL 77

Expert Comment

by:peter57r
ID: 26329696
Michael, I think the poster indicated that there is no link between the tables.
0
 

Author Comment

by:Bitley
ID: 26337828
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 26342851
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question