VB6, Crystal Reports 7 - Exporting the report from VB 1

[jbeale]

Brief history:
We are gathering information using Crystal Reports 7.0 and exporting it as a text file. This information is FTPd to the mainframe for further processing. We needed a simple way for the users to select the Userids they need for the Crystal Report, then run the report & export the data. We decided to create a VB form at the front....

I have created a simple form including 2 list boxes and an export command button. I have successfully collected choices of Userids from the 'available' Userid list box to the 'selected' Userid list box. I have also successfully exported the crytal report that I had previously imported, but I have yet to take the 'selected' Userids and fed them as new parameters to run the report then Export this new data. Is there a simple command/group of commands that I can use? I am not sure how to express the parameter list to create the new report to export.

Any help would be greatly appreciated. By the way, I thouroughly enjoy reading other people's questions/answers. This is a fantastic place to learn.
Thank you in advance for your help.
Jody

 

[Snaggs]

Jody,

Have you considered letting Visual BASIC crate the output file for you? You could still show the report to the user and they wouldn't necessarily have to export the data from Crystal Reports, but you could still get your formatted data file.

Since you have all the information in Visual BASIC and you know what you want to write to the file, why not have Visual BASIC do it for you?

If this is not a viable option, then take a look at the SelectionCriteria property on the Crystal Reports control or object model. This will allow you to filter your reports based on a set of criteria.

Be forewarned that this could be a slow process, since Crystal Reports is doing all the filtering and not the SQL statement that's driving the report. Snaggs
tribesaddict@swbell.net
2 wire mesh butchering gloves:
1 5-finger, 1 3-finger, pair: $15

 

[jbeale]

I have thought of letting VB create the report for me; however, I do not know how to do this...Can you help? I still need a text file with information positioned the way I have it so the mainframe COBOL program can recognize it. I am VERY new to VB and am trying to teach myself. Someone had already suggested pulling information out of Crystal Reports (extremely slow) so that is the initial route I took. I sure would like to know how to accomplish what you have suggested. Yesterday, I was able to pull the Userid names from the selected list box and create a statement. How do I use this information to allow VB to create the file I need?

 

[Snaggs]

Jody,

It's probably easier than you think. Try this code:

Dim intOutputFile As Integer
Dim strFileBuffer As String

intOutputFile = FreeFile

Open "C:\Datafile.txt" For Output As #IntOutputFile

Do While <whatever condition you're using goes here>
strFileBuffer = <build your output format here>

'Write the line to the file.
Print #intOutputFile, strFileBuffer
Loop

Close #intOutputFile

You will have to build the strFileBuffer by concatenating strings together. The Print statement just writes the entire string to the file. You can determine how the strFileBuffer contents look by changing the order of the fields that you append to them for example:

strFileBuffer = strFirstName &amp; &quot;,&quot; &amp; strLastName &amp; &quot;,&quot; &amp; strPhone

This will write the firstname,lastname,phone with a comma to the file. If you want a different order, change the fields around. If you need fixed width, you can use fixed width strings. NOTE: Be cautious about doing this, since the .NET framework will not support it if you upgrade to the next version of VB after version 6.0.

Dim strFirstName As String * 15
Dim strLastName As String * 20

Defining your variables this way will left justify the text, but preserve the space in the file. So a name like John Doe would look like this, without the quotes:

&quot;John Doe &quot;

You can use the concatenation technique I showed you above to put delimeters in the output file if you need them. I would use this method to write the file and if the user still wanted to see the report, show them what you already have working.

Hope this helps. Snaggs
tribesaddict@swbell.net
2 wire mesh butchering gloves:
1 5-finger, 1 3-finger, pair: $15

 

[jbeale]

Snaggs,
Thank you for your help with the code. I do understand how to build the text file, what I do not understand how to do (yet!) is how to refresh the information based on the newly selected Userids. Here is an example of my selection criteria...but how do I refresh the infomation before building a new file?! (Hopefully that is easier than I may think).
Private Sub cmdRun_Click()
Dim crxapplication As New CRAXDRT.Application
Dim Report As CRAXDRT.Report
Dim iTotalSelected As Integer
Dim strSelUserid As String
Dim strParameterList As String

strParameterList = &quot;{W20U999S.ENDSTATCD} in [&quot;&quot;DECLINE&quot;&quot;, &quot;&quot;DECLINED &quot;&quot;, &quot;&quot;UWCONDISS &quot;&quot;, &quot;&quot;UWISSUE &quot;&quot;] AND &quot; _
&amp; &quot;{W20U999S.ENDUSERID} in [&quot;
iTotalSelected = lstUndNamesR.ListCount
For i = 0 To (iTotalSelected - 1)
strSelUserid = lstUndNamesR.List(i)
If iTotalSelected = 1 Or (i = iTotalSelected - 1) Then
strParameterList = strParameterList &amp; &quot;&quot;&quot;&quot; &amp; strSelUserid &amp; &quot;&quot;&quot;&quot; &amp; &quot;]&quot;
Else
strParameterList = strParameterList &amp; &quot;&quot;&quot;&quot; &amp; strSelUserid &amp; &quot;&quot;&quot;&quot; &amp; &quot;, &quot;
End If
Next
MsgBox strParameterList

The code above was based on then trying to pass it to CR.
Thanks for taking a look.

 

[Snaggs]

I don't have the Crystal Reports object model on this computer, it's at work. Can you see if there is a .Refresh method on the Crystal Report object? If there isn't check for a similar method. If you can't find one, then you may have to close the report and then re-open it with the new criteria.

Another alternative is this. There is a Crystal Reports forum here. You might try asking the question there.

Let me ask this question. Is the only reason you're using Crystal Reports based on the criteria that you need to create an output file? If it is, then I would get rid of the report and create the file through Visual BASIC.

If your users need to see the report, then you might try the advice that I mentioned above. Snaggs
tribesaddict@swbell.net
2 wire mesh butchering gloves:
1 5-finger, 1 3-finger, pair: $15

 

[jbeale]

The only reason I am using Crystal Reports is that is the way the original project was presented to me. I don't actually present reports to the users until I have processed on the mainframe &amp; those are created with EZ+. I am more than willing to get the information to build a txt file using VB...I do not know how...I do not know what statements to use to knock on the database door to retrieve information from the tables using VB. If I have not exhausted your help, can you point me in the right direction? I have looked in books, asked people around here and read a lot of the questions/answers on this forum. Maybe I am being thick, but I have not seen a way to get my selection criteria from my VB form and pass it with the correct VB statements over to the database. Do I use SQL statements? HELP...if you have the time!!
Thank you for your patience.

 

[Snaggs]

If you have DB/2 on the mainframe, then you can setup an ODBC connection and conenct directly to the database. However, there are probably certain rules that are regarding how information gets put in a table. It sounds like you already have some import mechanism on the mainframe side. Crystal Report being set aside, it's a pretty easy application.

I see what you're asking for, but it would be too big to post here. I have your e-mail address from the message you forwarded to me. When I get something put together, I'll send it to you. Snaggs
tribesaddict@swbell.net
2 wire mesh butchering gloves:
1 5-finger, 1 3-finger, pair: $15

 

[Snaggs]

Program has been sent. Snaggs
tribesaddict@swbell.net
To define recursion, we must first define recursion.

 

[jbeale]

Thank you very much for your help. I tried to open the file. Acrobat sent a message back saying there was an error opening the file. Said file did not begin with %PDF- . I tried to detach it and rename it - that did not help so I sent it to my home email and will try it there. I'll let you know what I come up with.

 

[Snaggs]

It was a zip file. Snaggs
tribesaddict@swbell.net
To define recursion, we must first define recursion.