Setting Locations of Many Reports

[IanWaterman]

I have just built 29 new reports, which will be migrated from Development to Test to Production.

Is there any way of doing a mass set location or must it be done the hard way, one at a time using Database - Set Location.

Thank you

Ian

 

[synapsevampire]

Somebody around here has a utility that helps with this, it might be Ido.

Try emailing him, I don't have his email handy, check:

milletsoftware,com

-k

 

[zqtqzq]

Changing Database Location during Runtime

------------------------------------------------------------------
I am suprised to see a lot of people with this problem. I thought the Crystal people
are not even bothered with this problem or making it simpler for programmers.
However, i encounterred the same problem when i convert my .RPT files to using
Crstal report viewer control.

Im in love with this control but the problem of "..cannot open file" when changing the
database location during runtime nearly put me off. However, with the help of inspiration of
the Holy Spirit, i scaled through it.

Are you ready to go with me? Here lets go.
Please i am still using DAO data access because my program works very fine and i dont
have the
time to convert it to ADO data access.

If it works for your ADO data access please post your own contribution to me
--------------------------------------------------------------------
1. Add the Crysal Viewer control(.ocx) to your form - Form1.
2. Create your report with using the Crystal Report Designer component - CrystalReport1
3. Put the following code in the Form1 module

option Explicit
Dim oDB As New CRAXDRT.Application
Dim oTb As CRAXDRT.DatabaseTable
Dim Report As New CrystalReport1

Public strFormula as String 'Selectio formula property

Private Sub Form_Load()
Screen.MousePointer = vbHourglass

On Error GoTo HandleError

For Each oTb In Report.Database.Tables
oTb.Location = gdb.Name
'
'Note gdb is my database object
'gdb.Name returns the path name of the database
'e.g c:\VBProject\Stock.mdb
Next oTb


With Report
.DiscardSavedData 'refresh report data
.ConvertNullFieldToDefault = True
.RecordSelectionFormula = strFormula 'my selection formula like "{Stock.Class}='LS'"

CRViewer1.ReportSource = Report
CRViewer1.ViewReport
End With
End Sub


Run your code and change the database location and rerun it again.

Please post me if you encounter any error

Thanks.

Tunde Aransiola
tundearansiola@hotmail.com

 

[beardenb]

I have the same problem but I've got 180 reports that need the location changed! I am using Crystal 8.5 and ODBC DSNs for these reports and this weekend, the server (AS/400) is being changed out. Apparently the database name of the new server is not the same as it is on the current server. When I go to Set Location, for one table in one report I see the following settings:

Table: S105V15M.PKPRD29SPD.ERRRPT
Server Type: ODBC - BURNS_PKPRD29MAD
Server Name: BURNS_PKPRD29MAD
Database: S105V15M
User ID: XXXXXXXX

I need to change 'S105V15M' to 'A105V15M' in each of the reports. For reports that have multiple tables, I can't always let CR propagate the changes because the libraries are not the same across tables (library in example above is PKPRD29SPD).

I have tried doing a binary edit, but this corrupts the rpt file. Ian, did you turn up any utilities to allow you to do this in bulk? Didn't find anything from MilletSoftware but rptInspector from Software Forces holds some promise but no success yet on changing the database name with this tool. I sent an email to rptInspector support with this question.

Anyone know of any utilities that would allow me to make this change without going through Set Location for each table in every report?

 

[vidru]

I created a crude little utility to do this for myself in Visual Basic. There's no way I would unleash this on anyone else at this point, but just so you know, it's possible to do it with minimal coding.

-dave

 

[crystalreporting]

Not sure what version of Crystal you are using, but Crystal makes a utility to do this for you. Go to Crystal's website and search for update8x.exe. The blurb reads:

"This file contains a utility which modifies several properties in a RPT file without using the Crystal Reports designer. Areas which can be modified include printer and database information. This utility also allows you to batch edit several RPT files."


Peter Shirley

http://www.linkcorp.com

 

[dgillz]

If you ar using ODBC DSNs, just change the location of the DSN and it should take care of everything. No need to change all the crystal reports.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[beardenb]

Thank you for your responses. To Peter, the reports in question are 8.5. I downloaded the utility and it almost got me there. Here's a snapshot of the table info before/after for a report I ran update8x on to change the database name:

Before:
Table: S105V15M.PKPRD29SPD.ERRRPT
Server Type: ODBC - BURNS_PKPRD29MAD
Server Name: BURNS_PKPRD29MAD
Database: S105V15M
User ID: XXXXXXXX

After:
Table: S105V15M.PKPRD29SPD.ERRRPT
Server Type: ODBC - BURNS_PKPRD29MAD
Server Name: BURNS_PKPRD29MAD
Database: A105V15M
User ID: XXXXXXXX

So as you can see, it did change the database name from S105V15M to A105V15M in one place, however it did not change the prefix on the fully qualified table name. Unfortunately, when I ran the report, it still looked for the table in the database S105V15M. If update8x also changed the table prefix, then I think I'd be home free.

Dave, any suggestions on where to look for code samples to get me on the right path? So does your VB program actually modify and save the report with new table specs? I looked at tunde's sample in an earlier post but that wouldn't help. Our reports are being run from CE 8 as unmanaged reports.

To dgillz, I have looked at the DSN setup for these reports (the DSNs, 15 of them, are using IBM Client Access ODBC driver for DB2/iSeries). Nowhere is the database name referenced. Apparently when we initially specified the tables for these reports, Crystal picked up the database name somehow from the server and prepended it to the library/table name.

If you're not familiar with AS/400-iSeries parlance, the info you see in the table field above, S105V15M.PKPRD29SPD.ERRRPT, can be broken down as follows:

- S105V15M is the remote database name as assigned by the AS/400 operating system (just a name really, used by DRDA and apparently ODBC)
- PKPRD29SPD is the library name (a library is a collection of tables)
- ERRRPT is the physical table name (can be a view or logical file as well)

Through the DSN, I set the default library list, which in this case is one library, PKPRD29SPD. Through Crystal Reports, I choose the table I want to write the report against, in this case ERRRPT (or use Set Location). The database name, however, just comes along with the table name. I don't select it or otherwise set it in the first place.

We're looking at the other side of the equation now, possibly changing the database name on the AS/400. I haven't given up on finding a way to edit the reports, it's just not looking hopeful.
Thanks,
--Bearden

 

[vidru]

Bearden,

I'm not guaranteeing this will work for you. Primary requirements are that you've got VB6, and CRAXDRT.DLL, which I believe only ships with the Developer edition of CR.

This is how I set the location of several reports at once...

I've got a form with 4 controls:
1) DriveListBox - 'Drive1'
2) DirListBox - 'Dir1'
3) FileListBox - 'File1'
4) CommandButton - 'cmdOK'

I've got a reference to CRAXDRT.DLL ver. 8.0.0.371 (Crystal Report 8 ActiveX Designer Run Time Library).
I have also verified that this works with ver. 8.5.0.217 for me.

On my C: drive, I've got a folder named 'OrigReports' with my original report files (no subdirectories).
Also on the C: drive, I've got an empty folder named 'UpdatedReports'.

Here is all of the code that I'm using:

'General Declarations
Dim CrxApp As New CRAXDRT.Application
Dim CrxRpt As CRAXDRT.Report
Dim CrxSubRptObj As SubreportObject
Dim CrxSubRpt As CRAXDRT.Report
Dim CrxRptObjs As CRAXDRT.ReportObjects
Dim CrxSections As CRAXDRT.Sections
Dim CrxSection As CRAXDRT.Section
Dim CrxRptObj As Object


Private Sub cmdOK_Click()
Dim strPath As String
Dim strServerName As String  'Name of the ODBC DSN (always the same)
Dim strDBName As String      'Name of the 'new' database
Dim strOldDBName As String   'Name of the 'old' database

strServerName = "

BURNS_PKPRD29MAD

"
strNewDBName = "

A105V15M

"
strOldDBName = "

S105V15M

"

strPath = Dir1.Path
MousePointer = vbHourglass

'Get Reports
File1.Path = strPath

For i = 0 To File1.ListCount - 1

    Set CrxRpt = CrxApp.OpenReport(File1.Path & "\" & File1.List(i))
    For j = 1 To CrxRpt.Database.Tables.Count
        CrxRpt.Database.Tables(j).Location = Replace(CrxRpt.Database.Tables(j).Location, strOldDBName, strNewDBName)
        CrxRpt.Database.Tables(j).SetLogOnInfo strServerName, strNewDBName
    Next j
    
    'Subreports
    Set CrxSections = CrxRpt.Sections
    For Each CrxSection In CrxSections
        Set CrxRptObjs = CrxSection.ReportObjects
        For Each CrxRptObj In CrxRptObjs
            If CrxRptObj.Kind = crSubreportObject Then
                Set CrxSubRptObj = CrxRptObj
                Set CrxSubRpt = CrxSubRptObj.OpenSubreport
                For k = 1 To CrxSubRpt.Database.Tables.Count
                    CrxSubRpt.Database.Tables(k).Location = CrxRpt.Database.Tables(1).Location
                    CrxSubRpt.Database.Tables(k).SetLogOnInfo strServerName, strNewDBName
                Next k
            End If
        Next CrxRptObj
    Next CrxSection


CrxRpt.SaveAs "C:\UpdatedReports\" & File1.List(i), cr80FileFormat
Next i

Set CrxRpt = Nothing

MousePointer = vbDefault
End Sub


Private Sub Dir1_Change()
File1.Path = Dir1.Path
End Sub


Private Sub Drive1_Change()
Dir1.Path = Drive1.Drive
End Sub


Private Sub Form_Load()
Drive1.Drive = "c:"
Dir1.Path = "c:\OrigReports"
End Sub

Hope this helps somebody out...

-dave

 

[beardenb]

Thanks again for all the input on this problem. In this case, the resolution was changing the database name on the new server to match what it was on the old server. All the reports appear to be working fine with no changes required to reports or DSN's. The folks at Software Forces tell me the new version of rptInspector will have capabilities to make a bulk change to database (location) settings as needed in my case. I believe the new version, which will support CR9, will be available before the year's out.

--Bearden

 

[SFLLC]

Bearden,

That is correct. .rpt Inspector 2.0 will be out shortly and one of the hundreds of new features we've added is a data source / database wizard that allows you do batch conversion / migration. The way it works is you
1. Select the report(s) you want as the source.
2. Then you select the data source(s) you want to convert/migrate.
3. Select the destination data source

Unlike our version 1.0, we now support not just ODBC, but OLE DB, native drivers for DB2/Informix/Oracle/SQL Server/Sybase and allow you to connect to them to set the connection, etc. And we now support the same for preview of reports (1.0 supported only ODBC).

There will be two flavors of 2.0 released. One that supports Crystal Reports 5.0 through 8.5 and one that supports Crystal Reports 9.0. We also plan on releasing a Crystal Enterprise 9.x version before year's end.

A few users in this forum have been part of the beta team but are under NDA. So maybe they can comment briefly on their successes.

Thanks for your interest. Feel free to let us know about your challenges, we're open to new ideas and features.