Not all data showing in Crystal Report

Crystal Report with XSD data definition not providing all data element

Page 1 of 1

0 Replies - 2092 Views - Last Post: 01 June 2009 - 11:57 AM Rate Topic: -----

#1 jmaynar8  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 25-September 08

Not all data showing in Crystal Report

Post icon  Posted 01 June 2009 - 11:57 AM

Hello

I have an application that was recently converted from VB 6.0 to VB .NET (Visual Studio 2005) One of the converted components was a Crystal Reports (8.5) report. Crystal Reports version I am using now is 10.2. For the most part the report is working fine. The original version used .TTX data definitions which consisted of 20 data elements (fields). It was converted to use XSD/XML. However, I am only getting 18 of the 20 data elements (fields) to print on the report. I would really appreciate it if someone has any ideas on why it is dropping the last two data elements.

		l_strSQL = "SELECT R.OLEV1_SA_LEVEL1_C "
		l_strSQL = l_strSQL & vbCrLf & "  ,	R.OLEV2_SA_LEVEL2_C "
		l_strSQL = l_strSQL & vbCrLf & "  ,	Q.DSC_SALES_C "
		l_strSQL = l_strSQL & vbCrLf & "  ,	M.PAACCT_CODE "
		l_strSQL = l_strSQL & vbCrLf & "  ,	M.DDLR_DEALER_ID_R "
		l_strSQL = l_strSQL & vbCrLf & "  ,	NVL (O.DDLR_TRADE_N, '****DELETED*****') AS DDLR_TRADE_N "
		l_strSQL = l_strSQL & vbCrLf & "  ,	S.GCITY_CITY_N "
		l_strSQL = l_strSQL & vbCrLf & "  ,	S.STPR_STATE_C "
		l_strSQL = l_strSQL & vbCrLf & "  ,	S.GLOC_POSTAL_C "
		l_strSQL = l_strSQL & vbCrLf & "  ,	NVL (N.FINIST_INSTIT_N, '****DELETED****') AS FINIST_INSTIT_N "
		l_strSQL = l_strSQL & vbCrLf & "  ,	NVL (L.GODY18_VAL_OLD_X, '<<NULL>>') AS GODY18_VAL_OLD_X "
		l_strSQL = l_strSQL & vbCrLf & "  ,	NVL (L.GODY18_VAL_NEW_X, '<<NULL>>') AS GODY18_VAL_NEW_X "
		l_strSQL = l_strSQL & vbCrLf & "  ,	TO_CHAR (M.GODY17_CREATED_Y, '" & g_strOrclShortDate & "  HH24:MI:SS') AS GODY17_CREATED_Y "  'PATCH 20090529JTM (added time format)
		l_strSQL = l_strSQL & vbCrLf & "  ,	M.GODY17_OSUSER_N "
		l_strSQL = l_strSQL & vbCrLf & "  ,	NVL (T.OLEVD_LITRAL_X, 'UNKNOWN')" & "  AS LEVEL1_DESC "
		l_strSQL = l_strSQL & vbCrLf & "  ,	NVL (U.OLEVD_LITRAL_X, 'UNKNOWN')" & "  AS LEVEL2_DESC "
		l_strSQL = l_strSQL & vbCrLf & "  ,	TO_CHAR (M.GODY17_CREATED_Y, 'YYYY-MM-DD HH24:MI:SS')" & "  AS CREATED_SORT "
		l_strSQL = l_strSQL & vbCrLf & "  ,	M.ODIV_DIVISION_C "
		l_strSQL = l_strSQL & vbCrLf & "  ,	NVL (V.ODIV_DESC_X, 'UNKNOWN')  AS ODIV_DESC_X "
		l_strSQL = l_strSQL & vbCrLf & "  ,	DECODE (Y.FDSACT_TERM_Y, NULL, 'A', 'T') AS FINIST_STATUS_C "

		'  From section

		l_strSQL = l_strSQL & vbCrLf & "  FROM MGODY18_AUDIT_DTLS L "

'  This is a long query so I am currently only posting a partial - if you need the full, please let me know.

'  The Crystal report set-up is shown below

		Dim l_rsRpt As New DataSet	  'Apply .Net Migration for here declare DataSet on 04/07/07 by Sundar
		Dim l_strDate As String
		Dim l_strRpt As String
		Dim l_strSQL As String
		Dim obj_rpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument		'Apply sundar
		Dim dr As IDataReader
		Dim cmd As OdbcCommand

		Try	 'Apply .Net Migration for On Error Goto replaced to Try Catch on 04/07/07 by Sundar

			Me.Cursor = Cursors.WaitCursor	 'Apply .Net Migration on 04/07/07 by Sundar

			l_strDate = "All changes made for period " & Me.ddcalStartDt.Text & " to " & Me.ddcalEndDt.Text
			l_strRpt = My.Application.Info.DirectoryPath & "\Reports\rptFinAudit.rpt"
			l_strSQL = Build_SummarySQL()

			If dbGOLDD.State = ConnectionState.Closed Then dbGOLDD.Open()
			cmd = New OdbcCommand(l_strSQL, dbGOLDD)
			dr = cmd.ExecuteReader

			' create instance of crystal reports and set master recordset

			obj_rpt.Load(l_strRpt)  'Apply sundar
			obj_rpt.SetDataSource(dr)	'Apply sundar
			obj_rpt.ParameterFields.Item(0).CurrentValues.AddValue(g_strCryDtFormat)	'Apply sundar
			obj_rpt.ParameterFields.Item(1).CurrentValues.AddValue(l_strDate)	'Apply sundar

			frmRptViewer.CRViewer.ReportSource = obj_rpt	'Apply sundar
			frmRptViewer.ShowDialog()   'Apply sundar

'  The XSD file I am using is shown below

<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="rptFinAudit" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
  <xs:element name="rptFinAudit" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
	<xs:complexType>
	  <xs:choice minOccurs="0" maxOccurs="unbounded">
		<xs:element name="rptFinAudit">
		  <xs:complexType>
			<xs:sequence>
			  <xs:element name="OLEV1_SA_LEVEL1_C" type="xs:string" default="011" minOccurs="0" />
			  <xs:element name="OLEV2_SA_LEVEL2_C" type="xs:string" default="  A" minOccurs="0" />
			  <xs:element name="DSC_SALES_C" type="xs:string" default="F12345" minOccurs="0" />
			  <xs:element name="PAACCT_CODE" type="xs:string" default="04523" minOccurs="0" />
			  <xs:element name="DDLR_DEALER_ID_R" type="xs:int" minOccurs="0" />
			  <xs:element name="DDLR_TRADE_N" type="xs:string" default="ABC Ford Lincoln Mercury" minOccurs="0" />
			  <xs:element name="GCITY_CITY_N" type="xs:string" default="Anytown" minOccurs="0" />
			  <xs:element name="STPR_STATE_C" type="xs:string" default="MI" minOccurs="0" />
			  <xs:element name="GLOC_POSTAL_C" type="xs:string" default="48120" minOccurs="0" />
			  <xs:element name="FINIST_INSTIT_N" type="xs:string" default="ABC FINANCE" minOccurs="0" />
			  <xs:element name="GODY18_VAL_OLD_X" type="xs:string" default="A" minOccurs="0" />
			  <xs:element name="GODY18_VAL_NEW_X" type="xs:string" default="A" minOccurs="0" />
			  <xs:element name="GODY17_CREATED_Y" type="xs:string" default="09-DEC-2004 12:04:03 PM" minOccurs="0" />
			  <xs:element name="GODY17_OSUSER_N" type="xs:string" default="GEGGART" minOccurs="0" />
			  <xs:element name="LEVEL1_DESC" type="xs:string" default="REGION" minOccurs="0" />
			  <xs:element name="LEVEL2_DESC" type="xs:string" default="ZONE" minOccurs="0" />
			  <xs:element name="CREATED_SORT" type="xs:string" default="2004-12-09" minOccurs="0" />
			  <xs:element name="ODIV_DIVISION_C" type="xs:string" default="1" minOccurs="0" />
			  <xs:element name="ODIV_DESC_X" type="xs:string" default="Ford Division" minOccurs="0" />
			  <xs:element name="FINIST_STATUS_C" type="xs:string" default="A" minOccurs="0" />
			</xs:sequence>
		  </xs:complexType>
		</xs:element>
	  </xs:choice>
	</xs:complexType>
  </xs:element>
</xs:schema>





I would deeply appreciate any help you can provide. Thanks in advance.

Jim

Is This A Good Question/Topic? 0
  • +

Page 1 of 1