Filtering records before printing, stop print if no data

[DeafBug]

With VB6 and CR10 and RDC.

I was able to manage to preview records and then close it if there is no records to show. I use the crReport_NoData event to trap it, then display a message box that there are no records. When the user clicks the message box OK button, the preview form unloads.

Now if the user wants to print it, they still get a blank page with the headers coming out of the printer showing no data. Is there a way to have CR do the filtering beforehand so that if there are no records to show, it won't print that one page?

I know the Report.PrintOut statement is worthless on this issue. But it is useful on the other hand.

Thanks.

 

[vxxv]

Hi DeafBug,

Why would the user want to print an empty report?

 

[DeafBug]

The user doesn't print an empty report. I pass several options to the report via parameters. If the parameters or options happen to filter out all the records. It still prints a empty report.

In Crystal Preview, there is a event that triggers if there are no records to show. I trap that trigger and unload the preview form.

I just want the same for the PrintOut statement if possible. Or anything similar.

There is two buttons on the UI form, Preview and Print. Clicking on Preview button will display the CrystalPreview form. Clicking on the Print button just prints without seeing the preview form.

 

[vidru]

Have a look at this thread: thread768-721147

-dave

 

[DeafBug]

Thank you for the thread to point to. I read it and like that guy, I dug into it further.

There is an updated one.

http://support.businessobjects.com/library/kbase/articles/c2013862.asp

Apparently, ReadRecords is the key.

That lead me to something else if I could improve my code a little bit more. But one thing that got me concerning is the time elasped. Of course no one wants to wait around.

With the crxReport object, you call crxReport.ReadRecords.

Can you just simple assign the post-processed object to the report viewer.

I pass crxReport to the frmPreview as it has the crReportViewer object on the form. I use this statement.

crReportViewer.ReportSource = crxReport
crReportViewer.ViewReport

I am just wondering if it will immediately display the report in preview without processing it again?

I then could remove the NoData event trapping as I won't need it. Since the report is already read in and process at the main form.

I guess I will have to try it in the morning. I will report it back here so others could benefit what I find.

 

[vidru]

I'm pretty sure that forcing the ReadRecords will 'pre-load' the data, so if you then send it to a CRViewer, the only lag time would be the time it takes for CR to format the report - not to grab the data again.

-dave

 

[DeafBug]

Darn it. In the link I provided that is updated for CR9, there is a note on the bottom that it doesn't work with ADO recordset.

We use stored procedure on the MS SQL Server. It returns a recordset. While I am not an expert on SQL server, I know the recordset that gets passed back to VB is in ADO recordset.

While I wrote the middle tier code to query the SQL stored procedure. I am wondering if the recordset can be in a different type so that I can take advantage of it.

Is there a recordset other than ADO that is just as strong or equilvant to it that I can use instead? What other recordset does CR like that will also work in VB?

 

[vidru]

Why not just check the RecordCount property of the recordset? If it's 0, then you've got no records.

-dave

 

[DeafBug]

I do. But it is only what the stored procedure returns.

The issue is after you filter the records to meet the criteria the user selected in CR.

I have two solutions, one is to use RDO cuz I don't need the fancy ADO stuff such as MoveFirst, MoveNext, etc.

The other is to pass all user options to the stored procedure instead of passing it to CR. But the downside to that is the stored procedure has to do more CPU cycles, eating up the real-time cycles that the clients depend on for their customers. We have been beating on the idea of having a separate server just for reports and it will be updated twice a day during business hours. But will our clients want a second computer? Some will, some won't. That just makes CR a simple report file as it does nothing but sort and group. That's why we try to give CR some of the work as the CPU cycle it uses will be the workstation not the server.

 

[vidru]

This works for me using VB6, RDC, CR 10, a SQL Server stored procedure to create a recordset (which I know has data), with additional filtering in the Record Selection Formula:

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
cn.Open "Provider=sqloledb;" & _
           "Data Source=VIDRU;" & _
           "Initial Catalog=TestDB;" & _
           "User Id=sa;" & _
           "Password=MyPwd"
Set cmd.ActiveConnection = cn
cmd.CommandText = "ReportPickListEmployees"
Report.DiscardSavedData
Report.Database.SetDataSource cmd.Execute
Report.ReadRecords
If Report.PrintingStatus.NumberOfRecordSelected = 0 Then
    MsgBox "No data"
Else
    CRViewer1.ReportSource = Report
    CRViewer1.ViewReport
End If

Set cmd = Nothing
cn.Close
Set cn = Nothing

The 'NumberOfRecordSelected' property is the key here.

-dave

 

[DeafBug]

Thanks. That works better. I was using 'NumberOfRecordPrinted' instead.