My Datasource is xml How can Crystal Reports Help me?

[vbMarkO]

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 haven't 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

[MrPaul]

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 don't 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]

Edited April 12, 2007 by MrPaul

[vbMarkO]

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

[techmanbd]

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

   Dim dsData As New DataSet

 

Then while form is loading.

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

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

 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

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.

 

 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 can't 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

 

<?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>

[techmanbd]

I was thinking too, you may want to look into xml schema as well

 

imports system.data

imports system.xml

        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)