Reporting no generation of data 1

[moben]

I have a CR version 8.5 report that uses Access 2000 as backend.

The report contains some record selection criteria which is working fine.

The problem is that if zero records are selected, only the report layout and special fields (date, pageno) get printed.

I have a formual to count the number of record printed, but this is blank, when I would prefer it to be 0. I also have a field for the user printing the report contained in a system table within the Access db, but this is also blank.

The report as it is printing is correct i.e. no data met the selection criteria, but I want it print it print some of the details mentioned above.

Any advice would be appreciated.

Moben

 

[Madawc]

Have a section saying 'No Details Found'. Give it a suppression formula,
not isnull ({your.field}).

When there are no records, this is treated as null and you get the message.

Madawc Williams
East Anglia, Great Britain

 

[DannyLondon]

The Gods must be smiling. I came into the forum for just this issue, and there it was, second from the top of the list.

Thanks for your help Madawc.

Danny

 

[moben]

Hi,
Your solution only allows me to print a "No Data" type message.

I still want to be able to print the report criteria and the person printing the report. These values are saved to s system table within VB6 prior to calling the CR report. CR then usess these values to print data andheader information. If no records match the criteria, then only the report outline (Page headers/footers, date and page numbers are printed).

If zero records are printed then I want to be able to:

1. Print the message "No Data found" - this can be done
2. Print the criteria & user name - which are located within a Access table.

 

[DannyLondon]

How are you calling the report? Do you have a front end?

If so, pass these details to the report as parameters.

Danny...

 

[moben]

HI Danny,

I am using VB6 with Access 2000. Prior to calling the report, certains fields (user name, date criteria) are saved to a system table within the Access DB.

In CR, I open the data table and the system table, and in the selection criteria option, I use the date value from the system table and compare it with a value from a table containing the data that is to be printed.

i.e. system.date1 >= sales.date

If no records are found, then I would at expect the Systems.date1 to print in my header i.e.

Sales figures for sales dated >= sales.date

In the system table, I also have a field that contains the person printing the report. This is also added to the report header, but appears blank.

Please note that if the date criteria is changed to value that picks out records, then I get values for these fields.

Any help that you can give is greatly appreciated, otherwise I might have to write these reports within VB using the Printer object, which I really don't want to do.

You say pass these values as Parameters, I've not done this using CR, so I appreciate more advice/how to achieve this.

 

[DannyLondon]

Hi Moben

You could use parameters to pass the user name and sales date to Crystal. Regardless of what records are returned, you will be able to print these parameters in the report. Furthermore, you can use the SalesDate parameter in your record selection criteria, doing away with the system table. The parameter will be available in the formula editor for the record selection criteria. Have a look at the help screen "Pushing Down Record Selection - An Example" for some tips on optimising this.

To add parameters to a Crystal report:
- Open the report in Crystal.
- Menu - Insert - Field Object to open the Field Explorer.
- Select Parameter Fields, then Ctl-N to create a new one.
- Give the parameter a name and type (eg UserName, String)
- Add other parameters if you require (eg SalesDate, Date). The order of these is important as this is the order they will receive their values from VB. If you have more than one parameter, use the arrows in the Field Explorer to sort them appropriately.
- Drag the parameters into your report, as you would for other fields.

To pass values to the report from VB:
- I presume you are using the CrystalReport control, say called cryReport.
- Pass parameter values using the property cryReport.ParameterFields(1) = "Danny". The index (in this case 1) refers to the parameters in the order you have them listed, starting at 1.
- Use the method cryReport.PrintReport to display the report.

Danny

 

[moben]

Danny,

Thanks for your quick responses, I will follow your instructions. The system is basically being used to hold 'values' that I then manipulate within CR. Using parameters maybe better ?

Thanks once again, if I have any problems I'll post a message.

Regards,

MoBen.

 

[moben]

Danny,

Hopefully you will read this.

I've followed the instructions that you provided. I have created 2 parameter fields (pDate & pUserName).

In my VB code I have assigned values to these as follows:

frmMain_Menu.CryRep.ParameterFields(1) = TxtDate1.Text
frmMain_Menu.CryRep.ParameterFields(2) = gb_user_name

where TxtDate1.text and gb_user_name contain the values that I want to be printed in the CR report. I have added the parameters onto the report.

I am getting a run-time error '20553' Invalid parameter field name.

frmMain_Menu.CryRep.ReportFileName = gb_path & "las_insrenewalsdue.rpt"
Screen.MousePointer = 0
frmMain_Menu.CryRep.action = 1
frmMain_Menu.CryRep.PageZoom (100)
frmMain_Menu.CryRep.PrintReport 'this line was added as per your instructions.

When debugging, the point of failure seems to be the .Action=1 statement.

Prior to adding parameters, the report was working ok.

Any help that you could provide would be grateful.

Regards,

Moben.

 

[DannyLondon]

Hi Moben

Sorry, it seems that the format for the OCX control is different from the RDC environment (the newer way of accessing report objects).

The format for the OCX is

CrystalReport1.ReportFileName = "C:\My report.rpt"
CrystalReport1.ParameterFields(1) "ApplicationUser;Larry;True"
CrystalReport1.ParameterFields(2) = "GreyLevel;245;True"
CrystalReport1.PrintReport

The parameter index does not seem important (1, 2 etc) but must be used and must be unique. What is more important is using the exact parameter name in the string, then the value and finally the True stops the user being prompted. If you wish to pass a date as a date rather than a string, have a look at

http://support.crystaldecisions.com/library/kbase/articles/c2006910.asp.

Your Action = 1 method is the same as the PrintReport method. You don't need both.

Danny

 

[moben]

Danny,

Thanks for your response. From the examples you gave, how would I pass a variable containing the value I want printed into the ParameterField, with the 'True' at the end i.e.

would it be

frmMain_Menu.CryRep.ParameterFields(2) = gb_user_name & ";True"

Thanks,

Moben.

 

[DannyLondon]

No, assuming your parameter in Crystal is called UserName, then:

frmMain_Menu.CryRep.ParameterFields(2) = "UserName;" & gb_user_name & ";True"

Danny

 

[moben]

Danny,

Thanks for all the advice you have given, as all my requirements have been met 100%, including using dates as parameter fields.

One final question, can you recommend a book (easy reading, good pace) for VB.Net database development.

Thanks once again,

Moben.

 

[DannyLondon]

Hi Moben

I am pleased it has worked for you.

Unfortunately I have not read any books on VB.Net, so can't give you any pointers.

Danny