• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

One-click export Crystal Report to CSV

You know how Crystal Reports in Visual Basic 2008 has a button you click to export the report to a variety of formats?  Well, I want to make my own button, which when clicked, automatically saves the report to a specified location with a specified name, with no prompting to the user.  TIA!
0
LD147
Asked:
LD147
  • 27
  • 17
  • 2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
0
 
LD147Author Commented:
Thank you for the reply.  I  will already have the report open, displayed on the screen.  When I try to use the code on that page, I get lots of "underlined' words, meaning errors.  Assume I have a report named "weaksignals.rpt", displayed on the screen, how would I export that to CSV?  I installed the 30 day trial of the full version of CR this morning, btw.
0
 
mlmccCommented:
Look at the code he uses to do the export.  Basically put that code behind the button.

The code you are using to display the report is basically also what you need, you just change the destination to a file and the CSV format.

Are you using the version that comes with Visual Studio?  I don't believe it supports the CSV export.

mlmcc
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LD147Author Commented:
Ok, I'll give it a shot.   I installed the 30 day trial of the full version of CR this morning.
0
 
LD147Author Commented:
ok, I am trying to use just the code shown here as I don't think I need the rest of it (based on the fact the report is already showing on the screen, which BTW I only use "FrmReport.CrystalReportViewer2.ReportSource = New EOMTotals" to display it.

I'm stuck now.  Here's how it looks:

I guess I have to change "PortableDocFormat" to "separatedvalues" (?)  and "Forms.Application.StartupPath......" to something else (specify the report already being shown, somehow).
ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Forms.Application.StartupPath & "\rptTest.PDF")

Open in new window

0
 
LD147Author Commented:
Oh, here's the pic.
exportcode.JPG
0
 
LD147Author Commented:
This may work for me, but it's the last line that's giving me the problem.  The "myReportDocument" bit is underlined with the error "Name 'myReportdocument' is not declared".  This is from:

http://msdn.microsoft.com/en-us/library/ms226402.aspx

I don't understand why it doesn't work as is, especially as it's on MSDN.
Dim myExportOptions As New ExportOptions
         Dim myTextFormatOptions As TextFormatOptions
         myTextFormatOptions = ExportOptions.CreateTextFormatOptions()
         myTextFormatOptions.LinesPerPage = 60
         myExportOptions.ExportFormatOptions = myTextFormatOptions
         myExportOptions.ExportFormatType = ExportFormatType.Text
         Dim myDiskFileDestinationOptions As New DiskFileDestinationOptions
         myDiskFileDestinationOptions.DiskFileName = fileName
         myExportOptions.ExportDestinationOptions = myDiskFileDestinationOptions
         myExportOptions.ExportDestinationType = ExportDestinationType.DiskFile

         myReportDocument.Export(myExportOptions)

Open in new window

0
 
LD147Author Commented:
I'm now using this code, which appears to have no errors.  Good news!  But, when I click my button to save the report to CSV, I get this error:


Dim rptDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
        Dim diskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions()
        Dim exportOpts As CrystalDecisions.Shared.ExportOptions = New CrystalDecisions.Shared.ExportOptions()
        rptDocument.Load("c:/gatewaydata/LargeLeak.rpt")
        exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.CharacterSeparatedValues
        exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
        diskOpts.DiskFileName = "C:/gatewaydata/myData.csv"
        exportOpts.ExportDestinationOptions = diskOpts
        rptDocument.Export(exportOpts)

Open in new window

newerror.JPG
0
 
Éric MoreauSenior .Net ConsultantCommented:
does your report requires any parameters?
0
 
LD147Author Commented:
It uses 2 parameters, DF (Date From) and DT (Date To) both of which are of type 'Date'.  The report itself displays perfectly fine using the date range specified in the parameters.
0
 
Éric MoreauSenior .Net ConsultantCommented:
but your current code is not providing any values. my first article on CR was showing how to pass them: http://www.emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx
0
 
LD147Author Commented:
I looked at the article and saw where you were talking about passing a parameter when you typed "mrptDoc.SetParameterValue("PopulationThreshold", 5000000)"

I changed my code to the attached.  As you can see, I added a line before the final line whch is passing a parameter.  It's still giving the same error so I guess my formatting is not right.  I also need to pass along another parameter (DF).  I hard coded the date into the code just to  test it.  What I really need is something like:

rptDocument.SetParameterValue("DT", DT)  ' DT is Date To
rptDocument.SetParameterValue("DF", DF)  ' DF is Date From

I don't know if I can combine them into one line or if two lines like this will be ok.  Does this look right?  
Dim rptDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
        Dim diskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions()
        Dim exportOpts As CrystalDecisions.Shared.ExportOptions = New CrystalDecisions.Shared.ExportOptions()
        rptDocument.Load("c:/gatewaydata/LargeLeak.rpt")
        exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.CharacterSeparatedValues
        exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
        diskOpts.DiskFileName = "C:/gatewaydata/myData.csv"
        exportOpts.ExportDestinationOptions = diskOpts
        rptDocument.SetParameterValue("DT", "10/20/2009")
        rptDocument.Export(exportOpts)

Open in new window

0
 
LD147Author Commented:
I changed the penultimate line to:
rptDocument.SetParameterValue("DT", "DF")

I get this error:

The types of the parameter field and parameter field current values are not compatible.

When I displayed the report on the screen, I set DF and DT to "10/05/2009"
0
 
Éric MoreauSenior .Net ConsultantCommented:
you need to set both parameters on different lines

rptDocument.SetParameterValue("DF", "2009/01/01")
rptDocument.SetParameterValue("DT", "2009/10/20")

0
 
LD147Author Commented:
That prob would work if I wanted to hard code the date, but at this point the user has already chosen a From and To date from the date picker thing, and I want it to take those chosen dates.
0
 
Éric MoreauSenior .Net ConsultantCommented:
pass the datetimepicker value

rptDocument.SetParameterValue("DF", yourDTPFromcontrolName.value.tostring("yyyy/MM/dd"))
0
 
LD147Author Commented:
When I run the program and generate a report, I get this Date Picker (see pic).  This pops up due to me having previously set DT and DF parameters and put them inside the report.  For "yourDTPFromControlName I tried "DF" but it doesn't work, so I'm not sure what to put there now.
DTP.JPG
0
 
Éric MoreauSenior .Net ConsultantCommented:
ok so try rptDocument.SetParameterValue("DF", yourDTPFromcontrolName.value.tostring("MM/dd/yyyy"))
0
 
LD147Author Commented:
It keeps saying that DF and DT have not been declared

        rptDocument.SetParameterValue("DF", DF.value.tostring("MM/dd/yyyy"))
        rptDocument.SetParameterValue("DT", DT.value.tostring("MM/dd/yyyy"))
notdec.jpg
0
 
Éric MoreauSenior .Net ConsultantCommented:
haven't you said that you have 2 parameters named DF and DT?
0
 
LD147Author Commented:
Yes, see attached pic.  They were set up using the paramaters section in the field explorer....
DFDT.JPG
0
 
Éric MoreauSenior .Net ConsultantCommented:
but in your application calling this report, where are the dates coming from?
0
 
LD147Author Commented:
When the "Map Data" button is clicked (see pic), it loads a report, and in that report I have the following formula behind the IntDate field, which automatically brings up the Date Picker window, from which the To and From dates are selected.  That's where the dates are coming from.

not(isnull({main.IntDate})) and (Date({main.IntDate}) in {?DF} to {?DT})
map.JPG
0
 
LD147Author Commented:
Here's what the report looks like in the editor window
report.JPG
0
 
Éric MoreauSenior .Net ConsultantCommented:
Do you have an application calling the report which could provide the values or not? The code you took from my article requires this.
0
 
LD147Author Commented:
Here's the entire code for the form that calls the report (attached).  On my other (main) form, I have a menu item that calls this report.  The code behind that menu selection is:

    Private Sub AllDurationsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AllDurationsToolStripMenuItem.Click
        FrmReport.Show()
        FrmReport.CrystalReportViewer2.ReportSource = New LargeLeak
    End Sub

Beyond this, I don't know what else to give you.

It would be nice if there was a function that said "Ok, we have all the information we need for the report, including parameters, now dump it to a CSV instead of (or as well as) showing it on the screen", lol.
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class FrmReport

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'assign a new map to the map object - 2 is the European version, 1 is the North American version
        FrmMain.AxMappointControl2.NewMap(1)

    End Sub


    Sub OpenDataSet()

        Dim objDataSets As MapPoint.DataSets
        Dim objDataSet As MapPoint.DataSet
        Dim zDataSource As String
        Dim myLong As String
        Dim myLat As String
        Dim myName As String
        Dim location As MapPoint.Location
        Dim pushpin As MapPoint.Pushpin
        Dim centreMap As MapPoint.Pushpin
        Dim xFieldArray(0 To 2, 0 To 1) As Object

        'Use the lat field as the Latitude
        xFieldArray(0, 0) = "lat"
        xFieldArray(0, 1) = MapPoint.GeoFieldType.geoFieldLatitude
        'Use the long field as Longitude
        xFieldArray(1, 0) = "long"
        xFieldArray(1, 1) = MapPoint.GeoFieldType.geoFieldLongitude
        'Use the name field as the Name
        xFieldArray(2, 0) = "name"
        xFieldArray(2, 1) = MapPoint.GeoFieldType.geoFieldName

        zDataSource = "C:\gatewaydata\myData.csv" 'this is our data file
        objDataSets = FrmMain.AxMappointControl2.ActiveMap.DataSets
        objDataSet = objDataSets.LinkData(zDataSource, "name", xFieldArray, MapPoint.GeoCountry.geoCountryUnitedStates, MapPoint.GeoDelimiter.geoDelimiterComma) 'links to the data file rather then imports it 

        'now loop through each data element, assign it to a variable and use the variable to plat a pushpin position
        For Each objField In objDataSet.Fields
            myLong = objDataSet.Fields("long").Value 'this is my longitude
            myLat = objDataSet.Fields("lat").Value 'this is my latitude
            myName = objDataSet.Fields("name").Value ' name of the position
            location = FrmMain.AxMappointControl2.ActiveMap.GetLocation(myLong, myLat) 'plot the position
            pushpin = FrmMain.AxMappointControl2.ActiveMap.AddPushpin(location, myName) 'add a pushpin to that position
            pushpin.BalloonState = 2 'set the balloon display WHEN THE PUSHPIN IS CLICKED
            pushpin.Symbol = 0 'set the pushpin symbol
        Next objField

        'when all the positions are plotted, line up on one in the middle
        centreMap = FrmMain.AxMappointControl2.ActiveMap.FindPushpin("2")
        centreMap.GoTo()

        'zoom into the display
        FrmMain.AxMappointControl2.ActiveMap.ZoomIn()

    End Sub


    Private Sub Form1_FormClosing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles MyBase.FormClosing

        'close the map down when exiting from the form
        FrmMain.AxMappointControl2.ActiveMap.Saved = True
        FrmMain.AxMappointControl2.CloseMap()
        FrmMain.AxMappointControl2 = Nothing
    End Sub

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMap.Click
        'export currently displayed report to csv
        Dim rptDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
        Dim diskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions()
        Dim exportOpts As CrystalDecisions.Shared.ExportOptions = New CrystalDecisions.Shared.ExportOptions()
        rptDocument.Load("c:/gatewaydata/LargeLeak.rpt")
        exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.CharacterSeparatedValues
        exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
        diskOpts.DiskFileName = "C:/gatewaydata/myData.csv"
        exportOpts.ExportDestinationOptions = diskOpts

        rptDocument.SetParameterValue("DF", DF.value.tostring("MM/dd/yyyy"))
        rptDocument.SetParameterValue("DT", DT.value.tostring("MM/dd/yyyy"))
        rptDocument.Export(exportOpts)

        ' place pushpins on map
        OpenDataSet()

        ' Automatically switch to the Map tab
        FrmMain.Show()
        FrmMain.tabMain.SelectedTab = FrmMain.TabPage3
    End Sub

    Private Sub CrystalReportViewer2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CrystalReportViewer2.Load

    End Sub

End Class

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
>>Beyond this, I don't know what else to give you.

Can you provide the dates from your application instead of having CR asking for them?
0
 
LD147Author Commented:
Well, I want the user to be able to choose a date range for the report, instead of hard coding it.  I could, if you recommend it, add a regular DateTimePicker to the form for the To and From date, or simply add text boxes and have them type in the dates, and then assign a value to a variable that way.  But then I need to be able pass those values to the Report.
0
 
Éric MoreauSenior .Net ConsultantCommented:
>>Well, I want the user to be able to choose a date range for the report, instead of hard coding it.  

I never meant that.

>>I could, if you recommend it, add a regular DateTimePicker to the form for the To and From date, or simply add text boxes and have them type in the dates, and then assign a value to a variable that way.  

That's what I want from the beginning.

>>But then I need to be able pass those values to the Report.

This is exactly what my code is doing.
0
 
LD147Author Commented:
Ok, I'll change things around (and back up my existing code) but I will leave this open for now just in case someone comes along and has a fix for the way I was trying to do it.  Many thanks!
0
 
LD147Author Commented:
I set up two text boxes to accept a date (format checking can come later) named DTPFrom and DTPTo, with a button which when clicked runs the attached code.

I get the error as shown in the screenshot.  I don't think I need the two parameters (DT and DF) in the Crystal Report now, do I?  If not, I need to modify the code accordingly.  
Dim rptDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
        Dim diskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions()
        Dim exportOpts As CrystalDecisions.Shared.ExportOptions = New CrystalDecisions.Shared.ExportOptions()
        rptDocument.Load("c:/gatewaydata/LargeLeak.rpt")
        exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.CharacterSeparatedValues
        exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
        diskOpts.DiskFileName = "C:/gatewaydata/myData.csv"
        exportOpts.ExportDestinationOptions = diskOpts
        rptDocument.SetParameterValue("DF", DTPFrom.Text("MM/dd/yyyy"))
        rptDocument.SetParameterValue("DF", DTPTo.Text("MM/dd/yyyy"))
        rptDocument.Export(exportOpts)

        End

        ' place pushpins on map
        OpenDataSet()

        ' Automatically switch to the Map tab
        FrmMain.Show()
        FrmMain.tabMain.SelectedTab = FrmMain.TabPage3

Open in new window

newerror.JPG
0
 
Éric MoreauSenior .Net ConsultantCommented:
This is not a valid syntax. Use this instead (and insure you enter valid-formatted dates):

        rptDocument.SetParameterValue("DF", DTPFrom.Text)
        rptDocument.SetParameterValue("DF", DTPTo.Text)


0
 
LD147Author Commented:
I pasted those 2 lines in and now this appears:

(Actually I changed the second line to "DT"....)
error2.JPG
0
 
Éric MoreauSenior .Net ConsultantCommented:
are DTPFrom and DTPTo textboxes or DateTimePickers?

I would also suggest that you go through my article and play with the sample code to see how it is working.
0
 
LD147Author Commented:
Aha!  It's working!  Somehow i had removed the 2 parameters from the CR.  I put them back in and now it works.  Thank you for being so patient with me!  I will close this and award the relevant points.  I'd give you 5,000 if I could ;)
0
 
LD147Author Commented:
I've noticed that the report is exporting, but it's exporting the whole table's worth of data, and not the data specified inbetween the dates entered into the text boxes.  Any ideas?

Dim rptDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
        Dim diskOpts As CrystalDecisions.Shared.DiskFileDestinationOptions = CrystalDecisions.Shared.ExportOptions.CreateDiskFileDestinationOptions()
        Dim exportOpts As CrystalDecisions.Shared.ExportOptions = New CrystalDecisions.Shared.ExportOptions()
        rptDocument.Load("c:/gatewaydata/LargeLeak.rpt")
        exportOpts.ExportFormatType = CrystalDecisions.Shared.ExportFormatType.CharacterSeparatedValues
        exportOpts.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
        diskOpts.DiskFileName = "C:/gatewaydata/myData.csv"
        exportOpts.ExportDestinationOptions = diskOpts
        rptDocument.SetParameterValue("DF", DTPFrom.Text)
        rptDocument.SetParameterValue("DT", DTPTo.Text)
        rptDocument.Export(exportOpts)

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
have you used your dates variables to filter the report? since you deleted them, the condition related to them was also deleted.
0
 
LD147Author Commented:
I have 2 datepickers named DTPFrom and DTPTo, so I am using this:

        rptDocument.SetParameterValue("DF", DTPFrom.Value)
        rptDocument.SetParameterValue("DT", DTPTo.Value)

I assume this is what you refer to.  DF and DT refer to the parameters in the report (set to type "Date").  Anyway, I can open a new ticket for this if you wish so you can get credit if you solve the mystery, lol.
0
 
LD147Author Commented:
(I had previously used a text box in the code posted on 12/21 which is why I used .Text there)
0
 
Éric MoreauSenior .Net ConsultantCommented:
I don't mean on the screen, I mean in your report. You said that you have deleted your parameter from it. That means that the condition that was using them to filter your data is now incorrect. Check that formula.
0
 
LD147Author Commented:
Well, for the date, I had this formula in place previously.  This makes the window appear where I choose the Date for From and To.  I was using this prior to 12/21, before i tried the text boxes and DateTimePickers from the controls list.

not(isnull({main.IntDate}))  and   (Date({main.IntDate}) in {?DF} to {?DT})

I could email the report to you if it would help.
0
 
Éric MoreauSenior .Net ConsultantCommented:
I am not sure the formula is right (I never saw that syntax). Could you try a more traditional way of doing it like:
yourfield > ?DF and yourfield < ?DT
0
 
LD147Author Commented:
Well I tried:

{main.IntDate} in {?DT} to {?DF}

But this still brings up the Date picker, which is not what I need.  I have my own date picker controls.  When the report is being displayed in the viewer window (which displays perfectly, BTW), there should be a function to say "ok dump that exact data, which has already been passed paramaters such as Date From, and Date To, to disk".  I'm getting really stumped with this.  If it were up to me, there would be a function:  ExportCurrentReportView("C:\reports\datadump.csv") and that would be it! :)
0
 
Éric MoreauSenior .Net ConsultantCommented:
go back to my article (first part). there is a way of passing a complete dataset already filtered with only the data you want
0
 
mlmccCommented:
Try using .Date instead of .Value

        rptDocument.SetParameterValue("DF", DTPFrom.Date)
        rptDocument.SetParameterValue("DT", DTPTo.Date)

mlmcc
0
 
LD147Author Commented:
I actually got it working late this afternoon.  I can't remember off hand what I did, but it's working almost perfectly now.  I'll post again on Monday when I am back at work (or maybe I will remote in to my work computer before then).  Merry Christmas guys ;)
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 27
  • 17
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now