crystal report database

cltuser
10-26-2004, 04:05 AM
I have a situation where I have 100 different clients to support. Can you create one report that will satisfy all clients in terms of databases that will be used for their crystal report?? These are all Pervasive DB's. For instance, each client has the same structure for their database but their database dbq and servernames are different. I know I can have the user to supply the servername and dbq to connect to db to populate listboxes or comboboxes etc; in VB to set up criteria to run report before it is displayed. But, can you dynamically change the System DNS data source name that was used when the report was created in crystal report?? I created a .rpt file then added that report in VB to create my .dsr. Is this ok?? I find that doing it this way was alot easier for me. When I setup the crystal report I used client A's database to create my report. The System DNS data source name is called "Florida". Now I want to move report to client B, C, D etc;. Database contained in that connection is named BASE. So the fields in report are BASE.name BASE.address, BASE.City BASE.state etc;. Now I move that same report to client B and the report is looking for BASE.address, BASE.city, BASE.state etc; but now the System DNS data source name is called "Texas". I don't want to create a separate report for every client being that the structures are identical. One thing I noticed in my testing was once you remove a database from the report it also removes any reference to any fields used in the report. That means I have to create some 100 different reports. Is there anyway around this issue?? Like a property that can be set within visual basic to change the System DNS data source name used but keep all the same fields in the report or am I SOL??

cltuser
10-26-2004, 04:46 AM
I have a situation where I have 100 different clients to support. Can you create one report that will satisfy all clients in terms of databases that will be used for their crystal report?? These are all Pervasive DB's. For instance, each client has the same structure for their database but their database dbq and servernames are different. I know I can have the user to supply the servername and dbq to connect to db to populate listboxes or comboboxes etc; in VB to set up criteria to run report before it is displayed. But, can you dynamically change the System DNS data source name that was used when the report was created in crystal report?? I created a .rpt file then added that report in VB to create my .dsr. Is this ok?? I find that doing it this way was alot easier for me. When I setup the crystal report I used client A's database to create my report. The System DNS data source name is called "Florida". Now I want to move report to client B, C, D etc;. Database contained in that connection is named BASE. So the fields in report are BASE.name BASE.address, BASE.City BASE.state etc;. Now I move that same report to client B and the report is looking for BASE.address, BASE.city, BASE.state etc; but now the System DNS data source name is called "Texas". I don't want to create a separate report for every client being that the structures are identical. One thing I noticed in my testing was once you remove a database from the report it also removes any reference to any fields used in the report. That means I have to create some 100 different reports. Is there anyway around this issue?? Like a property that can be set within visual basic to change the System DNS data source name used but keep all the same fields in the report or am I SOL??
One other thing I notice was I can't change these properties below because they are read only.
'Report.Database.Tables.Item(1).DecriptiveName = "ODBC - " + DNS
'Report.Database.Tables.Item(1).LogOnDatabaseName = UCase(DNS)
'Report.Database.Tables.Item(1).LogOnServerName = DNS

Is there anyway to modify these??

Harkon
10-26-2004, 04:49 AM
Dim crxApp As CRAXDRT.Application
Dim crxRpt As CRAXDRT.Report
Dim crxTable As CRAXDRT.DatabaseTable
Dim strString As String

Set crxApp = New CRAXDRT.Application

'open the recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = aduseClient

strString = "SELECT * FROM BASE"
rs.Open strString, cn, adOpenKeyset, adLockOptimistic, adCmdText

'set report object
Set crxRpt = crxApp.OpenReport(App.Path _
& "/reports/reportPersonnel.rpt", 1)

'i suppose this is what you need
For Each crxTable In crxRpt.Database.Tables
crxTable.SetLogOnInfo server, dbname, username, password
Next

'remove any saved data
crxRpt.DiscardSavedData

'bind the new recordset
crxRpt.Database.SetDataSource rs



you will need to provide the server,dbname,username,password values which will differ from client to client. Either hardcode them or keep them stored and retrieve their values (I usually create registry keys where i keep them)

ps:I don't really know if it means something but I don't use .dsr here. I guess it is the same way though not excactly the same

Hope this helps, let us know.
regards

cltuser
10-26-2004, 11:13 AM
Dim crxApp As CRAXDRT.Application
Dim crxRpt As CRAXDRT.Report
Dim crxTable As CRAXDRT.DatabaseTable
Dim strString As String

Set crxApp = New CRAXDRT.Application

'open the recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = aduseClient

strString = "SELECT * FROM BASE"
rs.Open strString, cn, adOpenKeyset, adLockOptimistic, adCmdText

'set report object
Set crxRpt = crxApp.OpenReport(App.Path _
& "/reports/reportPersonnel.rpt", 1)

'i suppose this is what you need
For Each crxTable In crxRpt.Database.Tables
crxTable.SetLogOnInfo server, dbname, username, password
Next

'remove any saved data
crxRpt.DiscardSavedData

'bind the new recordset
crxRpt.Database.SetDataSource rs



you will need to provide the server,dbname,username,password values which will differ from client to client. Either hardcode them or keep them stored and retrieve their values (I usually create registry keys where i keep them)

ps:I don't really know if it means something but I don't use .dsr here. I guess it is the same way though not excactly the same

Hope this helps, let us know.
regards

I was able to change the database for the main report with the following code

Report.Database.Tables.Item(1).Location = DNS + ".BASE"
Report.Database.Tables.Item(2).Location = DNS + ".CBLD"
Report.Database.Tables.Item(3).Location = DNS + ".COBY"
Report.Database.Tables.Item(4).Location = DNS + ".LAND"
Report.Database.Tables.Item(5).Location = DNS + ".NOTE"
Report.Database.Tables.Item(6).Location = DNS + ".RBLD"
Report.Database.Tables.Item(7).Location = DNS + ".ROBY"
Report.Database.Tables.Item(8).Location = DNS + ".SALE"
Report.Database.Tables.Item(9).Location = DNS + ".VALU"

but I have subreports also. How can you change the database there. BTW pervasive does not support
rs.Open strString, cn, adOpenKeyset, adLockOptimistic, adCmdText It will let you open but you cannot send any perameters.

cltuser
10-28-2004, 01:23 PM
I was able to change the database for the main report with the following code

Report.Database.Tables.Item(1).Location = DNS + ".BASE"
Report.Database.Tables.Item(2).Location = DNS + ".CBLD"
Report.Database.Tables.Item(3).Location = DNS + ".COBY"
Report.Database.Tables.Item(4).Location = DNS + ".LAND"
Report.Database.Tables.Item(5).Location = DNS + ".NOTE"
Report.Database.Tables.Item(6).Location = DNS + ".RBLD"
Report.Database.Tables.Item(7).Location = DNS + ".ROBY"
Report.Database.Tables.Item(8).Location = DNS + ".SALE"
Report.Database.Tables.Item(9).Location = DNS + ".VALU"

but I have subreports also. How can you change the database there. BTW pervasive does not support
rs.Open strString, cn, adOpenKeyset, adLockOptimistic, adCmdText It will let you open but you cannot send any perameters.

Sorry about the previous post Harkon. It does support it. I played around until I got it to work but I get an error when I use the code Set crxRpt = crxApp.OpenReport(App.Path _
& "/reports/reportPersonnel.rpt", 1) Because I'm not using a RPT. I'm using a DSR. I get an error message "Invalid TLV record". At that point my program abends.

MKoslof
10-29-2004, 11:50 AM
So everything is currently working, but now you are getting an Invalid TLV record error? Does this occur on your development machine or when you deploy application? What version of Pervasive are you working with (2000i, 8.5, 8.6, etc.) How do you connection to this database Pervasive OLEDB provider, a system dsn? Can you post the current up to date code you are working with?

pkspence
05-18-2005, 12:16 PM
Dim crxApp As CRAXDRT.Application
Dim crxRpt As CRAXDRT.Report
Dim crxTable As CRAXDRT.DatabaseTable
Dim strString As String

Set crxApp = New CRAXDRT.Application

'open the recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = aduseClient

strString = "SELECT * FROM BASE"
rs.Open strString, cn, adOpenKeyset, adLockOptimistic, adCmdText

'set report object
Set crxRpt = crxApp.OpenReport(App.Path _
& "/reports/reportPersonnel.rpt", 1)

'i suppose this is what you need
For Each crxTable In crxRpt.Database.Tables
crxTable.SetLogOnInfo server, dbname, username, password
Next

'remove any saved data
crxRpt.DiscardSavedData

'bind the new recordset
crxRpt.Database.SetDataSource rs



you will need to provide the server,dbname,username,password values which will differ from client to client. Either hardcode them or keep them stored and retrieve their values (I usually create registry keys where i keep them)

ps:I don't really know if it means something but I don't use .dsr here. I guess it is the same way though not excactly the same

Hope this helps, let us know.
regards

What if I'm using a DSN? In particular, here's my problem. BTW, I'm running 8.5.

When I open the report in design mode, I need to change the name of the database where the stored procedure resides. Depending on which report is being run, the number of sp parameters could vary from 4 to 5. The first of which is called "@sdate". I click on Database|Set Location. At the bottom of the dialog, in the "Table" textbox, I change this:

Contracts.dbo.Proc(CAS_SP_SolnSubmit;1)

to this:

[CASReports-dev].dbo.Proc(CAS_SP_SolnSubmit;1)

The brackets are necessary because of the hyphen in the db name.

When I click Done, I get an error stating...
"Procedure 'CAS_SP_SolnSubmit' expects parameter '@sdate', which was not supplied".

Advice anyone, please. I'm coming in on the backend of this project and the former developer is no longer w/ the company.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum