My Datasource is xml How can Crystal Reports Help me?

vbMarkO

vbMarkO

Well-known member
Joined
Aug 19, 2005
Messages
157
I have an application of which I would like to be able to generate a report of certain data contained in my xml file.


I have never used Crystal Reports before .... and from what I can see of most tutorials they use Database files to bind to the report ....

I havent found any tutorial or example that uses what I have .... an application with textboxes which the info entered is saved to an xml file.

I want to display that info as a report ..... I am using vs2005 .....

Anyone point me int he right direction ... or provide an example .... I am willing to provide more detailed info if needed such as the xml structure or what ever else you might need ...

I am a hard worker, I learn fast but, just need a good starter example of how I might can make this work.

vbMarkO
 
XML vs Relational models

Mainstream databases, such as those you would use with Crystal Reports, store data using a relational model - data is stored in different tables with relationships and constraints controlling how they are interlinked. XML uses a hierarchical model - data structes are nested within each other - which is significantly different from a relational model.

The upshot of this is that many of the operations you might perform on a relational database make little or no sense in the context of XML, and indeed both models have their own set of query languages (eg SQL for relational, and XQuery/XPath for XML). For this reason I dont know of any ODBC providers for working with XML, which Crystal Reports would require to work with it.

As far as a solution to your problem goes, you probably have two courses of action:
  1. For the purposes of reporting, temporarily convert your XML into a relational model. Although I have never worked with Crystal Reports I assume it can work with any ODBC or OLE DB provider, and I know that the Jet provider supports text files. Therefore you could format your XML into a plain text file (eg CSV) and create a schema.ini to accompany it.

  2. Do not use Crystal Reports and instead create your own reporting system around XQuery and XPath, which is designed for querying XML data.

Good luck :cool:

[edit]A quick Google search reveals there actually are a couple of commercial ODBC drivers for querying XML data, eg the DataDirect Connect® for ODBC XML driver[/edit]
 
Last edited by a moderator:
MrPaul,

I am looking at the third party driver and also found CR has a driver too ... but then I know nothing of how to go about using a driver ....

I looked at the setup of a driver on CR site and it to me is complex enough that it might be easier to consider either learning to create a relational Database as you mentioned and write my program to work with it or to try converting ...

Since my app is written, I would like to explore converting to a relational model ...

Could you point me to any examples of how I might go about doing just that?

vbMarkO
 
Hi vbMark0,

I use xml files for storing parameters for tests. But basically same general idea as it is data and sometimes I change items and save. Maybe this can help you. Here is some of my code and hopefully get you where you need to go.

It all depends on where you want to put things as well. I will give my examples and maybe you can massage it into your needs. I use dataset and dataview.

first I dim a dataset when the form loads
Code: 
    Dim dsData As New DataSet

Then while form is loading.
Code: 
Private Sub frmCalibration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dsData.ReadXml(strPath & "HBLabDevices.xml") strPath = where the folder is of where my xml file is located
end sub

This is a sub where I read from the xml file
Code: 
 Private Sub GetCalOffset()
        Dim dvData As New DataView
        Me.PT.arryVrmsOffsets.Clear()

        With dvData
            .Table = dsData.Tables("outputcal")
            If .Table.Rows.Count <> 0 Then
                For intX As Integer = 1 To (.Table.Columns.Count)
                    Me.PT.arryVrmsOffsets.Add((.Item(0).Item("offsetfreq" & intX.ToString)))
                Next
            End If
        End With
    End Sub

then through another sub, I use an array list to gather data then goto this to save it
Code: 
  Private Sub savedata(ByVal strSineOrSquare As String)
        Try
            Select Case strSineOrSquare
                Case "square"
                    dsData.Tables("outputcalsquare").Rows(0).Item("offsetpeak") = _
                                            (Convert.ToDouble(Me.txtGenOut.Text) - Convert.ToDouble(Me.txtOutFromOscope.Text)).ToString
                Case "sine"
                    For intX As Integer = 0 To (Me.arryNewOffset.Count - 1)
                        dsData.Tables("outputcal").Rows(0).Item("offsetfreq" & (intX + 1)) = Me.arryNewOffset(intX)
                    Next
            End Select
            dsData.AcceptChanges()
            dsData.WriteXml(strPath & "HBLabDevices.xml")
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbCr & "Unable to save data. Check if file exists!", "Data Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

Then when I exit this form I do this
Code: 
 Private Sub frmCalibration_Close(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Closed
        dsData.Dispose()
  End Sub



Here is another sub I have in a different form that is all in one sub, I open and look at data and if I need to change then it changes the data to the xml form.

Code: 
  Private Sub SetUpVrmsForNewFreq(ByVal intFreqNo As Integer, ByVal strVDCOffset As String)
        Dim dsDataOffset As New DataSet
        Dim dvDataOffset As New DataView
        Dim dblVoltRead As Double
        Dim dblExpected As Double = Me.PT.dblGenVrms
        Dim boolReady As Boolean = False
        Dim strNewOffset As String
        Dim strNextVolt As String = (Convert.ToDouble(Me.PT.arryVrmsOffsets(intFreqNo)) + dblExpected).ToString
        dsDataOffset.ReadXml(PT.strPath & "HBLabDevices.xml")
        With dvDataOffset
            .Table = dsDataOffset.Tables("outputcal")
            .RowFilter = "offsetbplus = " & strVDCOffset & ""
        End With
        Do
            Me.PT.comportwrite(Me.PT.comFuncGen, "VOLT " & strNextVolt & ";")
            Me.FGSysErr()
            dblVoltRead = Me.GetMMVAC
            Me.lblInfoCenter.Text = "Adjusting Vrms to " & Me.PT.dblGenVrms.ToString & "." & vbCr _
                           & "Currently @ " & Format(dblVoltRead, "#.###")
            If dblVoltRead > (dblExpected + (dblExpected * 0.02)) Or dblVoltRead < (dblExpected - (dblExpected * 0.02)) Then
                If dblVoltRead > (dblExpected + (dblExpected * 0.02)) Then
                    strNextVolt = Format((Convert.ToDouble(strNextVolt) - 0.002), "##.###")
                Else
                    strNextVolt = Format((Convert.ToDouble(strNextVolt) + 0.002), "##.###")
                End If
            Else

                boolReady = True
            End If
            doeventit()
        Loop While Not boolReady
        strNewOffset = Convert.ToDouble(strNextVolt) - dblExpected
        Me.PT.arryVrmsOffsets.RemoveAt(intFreqNo)
        Me.PT.arryVrmsOffsets.Insert(intFreqNo, strNewOffset)
        For intX As Integer = 0 To (Me.PT.arryVrmsOffsets.Count - 1)
            dvDataOffset.Item(0).Item("offsetfreq" & (intX + 1).ToString) = Me.PT.arryVrmsOffsets(intX)
        Next
        dsDataOffset.AcceptChanges()
        dsDataOffset.WriteXml(PT.strPath & "HBLabDevices.xml")
        dvDataOffset.Dispose()
        dsDataOffset.Dispose()


I know it is alot to read but I am hoping this will help you out. I cant remember where I found how to use datasets and dataviews with xml files. I would try googling those terms, I believe that is how I found it.

Oh yeah, this actually may help, my xml for data

Code: 
<?xml version="1.0" standalone="yes" ?>
<HBLabSpec xmlns="http://tempuri.org/HBLabSpec.xsd">
	<LabDevice>
		<Address>6</Address>
		<Device>functiongen</Device>
	</LabDevice>
	<LabDevice>
		<Address>22</Address>
		<Device>mmeter</Device>
	</LabDevice>
	<frequency>
		<freq1>70</freq1>
		<freq2>100</freq2>
		<freq3>400</freq3>
		<freq4>3000</freq4>
		<freq5>10000</freq5>
	</frequency>
	<outputcal>
		<offsetbplus>normalvdc</offsetbplus>
		<offsetfreq1>-0.26</offsetfreq1>
		<offsetfreq2>-0.257</offsetfreq2>
		<offsetfreq3>-0.256</offsetfreq3>
		<offsetfreq4>-0.253</offsetfreq4>
		<offsetfreq5>-0.253</offsetfreq5>
	</outputcal>
	<outputcal>
		<offsetbplus>hivdc</offsetbplus>
		<offsetfreq1>0.4</offsetfreq1>
		<offsetfreq2>0.4</offsetfreq2>
		<offsetfreq3>0.7</offsetfreq3>
		<offsetfreq4>1.0</offsetfreq4>
		<offsetfreq5>1.1</offsetfreq5>
	</outputcal>
	<outputcal>
		<offsetbplus>lovdc</offsetbplus>
		<offsetfreq1>0.4</offsetfreq1>
		<offsetfreq2>0.4</offsetfreq2>
		<offsetfreq3>0.7</offsetfreq3>
		<offsetfreq4>1.0</offsetfreq4>
		<offsetfreq5>1.1</offsetfreq5>
	</outputcal>
	<outputcalsquare>
		<offsetpeak>0</offsetpeak>
	</outputcalsquare>
	<Diagnostics>
	<boolOn>true</boolOn> //if set to true then at start up, will turn on diagnostics logging. Set to false to turn off.
  </Diagnostics>
</HBLabSpec>
 
I was thinking too, you may want to look into xml schema as well

imports system.data
imports system.xml
Code: 
         Dim strDataXSD As String = AppDomain.CurrentDomain.SetupInformation.ApplicationBase & "HBLabSpec.xsd"
            Dim strDataXML As String = AppDomain.CurrentDomain.SetupInformation.ApplicationBase & "HBLabSpecData.xml"
            dsGetID.ReadXmlSchema(strDataXSD)
            dsGetID.ReadXml(strDataXML, XmlReadMode.DiffGram)
 
Back
Top