Solved

Crystal Report does not show data when 1 of the 2 datatables in dataset return empty rows

Posted on 2004-04-27
11
2,074 Views
Last Modified: 2012-06-27
Hi all, I'm using VS.net and crystal reports and currently facing this problem.

I have 2 datatables in the dataset

The first datatable is queried from the stored prod in sql sever 2000.
The second is a date, made into a datatable and added as a second table to the dataset that was queried

                        'Add datefrom and dateto into dataset
                        Dim dt As DataTable = New DataTable
                        Dim dr As DataRow
                        dt.Columns.Add("StartDate", Type.GetType("System.DateTime"))
                        dt.Columns.Add("EndDate", Type.GetType("System.DateTime"))
                        dr = dt.NewRow()
                        dr(0) = startDate
                        dr(1) = endDate
                        dt.Rows.Add(dr)
                        dt.TableName = "Table1"
                        ds.Tables.Add(dt)

The report shows fine when the first datatable return rows but when it the first datatable returns no rows, I still want the report to show the values of the secondtable.

Help Needed, Much Appreciated
0
Comment
Question by:alvinlau
  • 6
  • 4
11 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 10927633
You need to change your relations into CR between your 2 tables from INNER JOIN to LEFT JOIN (into the Visual Linking).

Sorry not being able to be more precise as I don't have .Net on this PC right now!

0
 
LVL 42

Expert Comment

by:frodoman
ID: 10928217
emoreau is correct.  

Go to your report and in Field Explorer right-click on Database Fields and select "Visual Linking Expert".  Then select the links tab.   Click directly on the line linking the tables and this will enable the "Link Options" button.  Click it, then change the link type from "Inner Join" to "Left Outer Join"

0
 

Author Comment

by:alvinlau
ID: 10934768
Hi, the 2 tables are not linked together. Its just purely a dataset with 2 datatables passed through. Here is the xsd of it.

<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:Locale="en-GB">
        <xs:complexType>
            <xs:choice maxOccurs="unbounded">
                <xs:element name="Table">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="ID" type="xs:string" minOccurs="0" />
                            <xs:element name="DepCode" type="xs:string" minOccurs="0" />
                            <xs:element name="ListedCountryCode" type="xs:string" minOccurs="0" />
                            <xs:element name="AccountGroup" type="xs:string" minOccurs="0" />
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
                <xs:element name="table2">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="dateFrom" type="xs:dateTime" minOccurs="0" />
                            <xs:element name="dateTo" type="xs:dateTime" minOccurs="0" />
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:choice>
        </xs:complexType>
    </xs:element>
</xs:schema>
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 10934881
It is not the schema. It is really the report.

Right-click anywhere it's grey on your report, select Database from the popup menu and then "Visual Linking expert".
From the "Database Expert" dialog, right-click the link between the tables and select "Link Options".
From the "Link Options" dialog, select "Left Outer Join" as the join type.
0
 

Author Comment

by:alvinlau
ID: 10934941
I went to Visual Linking expert and there are no link joining the 2 tables together.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 10937116
If your 2 tables are used in the Details, add links and be sure they are Left Joined
0
 

Author Comment

by:alvinlau
ID: 10945136
Hi emoreau,

Thanks for being with me.

The second table contains the dateFrom and dateTo columns with 1 row. This datatable is used in the header portion of CR to display the stored input dates that the user keys from the web application. This datatable is added to dataset which contains the first table(used in the details section of the CR) that is queried from the database. So if there is no rows returned, I want the dateFrom and dateTo to be still shown in the header portion. Unfortunately, it does show only when the first table contains rows.

There is no relation between the both tables and I don't see a need to link the tables. Thanks

Help still needed =)
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 70 total points
ID: 10945184
You don't need a datatable to pass that kind of values.

Add textobjects to your report, set their (name) and use this method to set their values:

    Protected Sub ApplyTextObject(ByVal vstrTextObject As String, ByVal vstrTextValue As String)
        Try
            CType(ReportToPrint.ReportDefinition.ReportObjects(vstrTextObject), CrystalDecisions.CrystalReports.Engine.TextObject).Text = vstrTextValue

        Catch ex As Exception
            'For i As Int32 = 0 To ReportToPrint.ReportDefinition.ReportObjects.Count - 1
            '    Trace.WriteLine(ReportToPrint.ReportDefinition.ReportObjects(i).Name)
            'Next
            Throw ex
        End Try
    End Sub

0
 

Author Comment

by:alvinlau
ID: 10945646
Hey emoreau,

I'm trying your method now, how do you set the name to the text objects?
0
 

Author Comment

by:alvinlau
ID: 10946270
Silly me I've found it. I think I'll be able to proceed from here thanx
0
 

Author Comment

by:alvinlau
ID: 10976035
hey thanx emoreau, it certainly solved my prob. Kudos to ya
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

696 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