alvinlau
asked on
Crystal Report does not show data when 1 of the 2 datatables in dataset return empty rows
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.DateT ime"))
dt.Columns.Add("EndDate", Type.GetType("System.DateT ime"))
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
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"
dt.Columns.Add("EndDate", Type.GetType("System.DateT
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
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"
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"
ASKER
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-ms data">
<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>
<?xml version="1.0" standalone="yes"?>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-
<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>
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.
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.
ASKER
I went to Visual Linking expert and there are no link joining the 2 tables together.
If your 2 tables are used in the Details, add links and be sure they are Left Joined
ASKER
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 =)
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 =)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey emoreau,
I'm trying your method now, how do you set the name to the text objects?
I'm trying your method now, how do you set the name to the text objects?
ASKER
Silly me I've found it. I think I'll be able to proceed from here thanx
ASKER
hey thanx emoreau, it certainly solved my prob. Kudos to ya
Sorry not being able to be more precise as I don't have .Net on this PC right now!