Crystal Server: How to repoint report datasources after refresh?

[Former Member]

I'm new to Crystal Reports/Crystal Server. Hope someone can enlighten me.

In SSRS, there's a thing called Shared Data Source wherein you can configure a data source like server, database, user settings, etc. This data source can then be shared in multiple reports. This is very convenient especially if you anticipate to change the server name (ex: Production to UAT server) after some later time. You'll just change the connection in Shared Data Source and it will affect all reports using it.

In Crystal Server, I noticed that I also need to repoint the datasource if the report has schedule.

I recently refreshed from Production to UAT and all the reports are now pointing to Production database. How can I repoint the datasource to UAT database efficiently in Crystal Server? What are the alternatives or tools which can reduce this effort?

BTW, I'm using SAP Crystal Server 2013 and Crystal Reports 11.

Thanks in advance.

[abhilash_kumar]

Hi --,

As a best practise, you should create an ODBC connection (with the same DSN) in each of your environments. The report should then be based off of this ODBC connection.

This way, when you migrate reports between Dev, UAT and Prod the reports seamlessly connect to the right databases via the ODBC connection.

-Abhilash

[Former Member]

Hi Abhilash,

Thanks for your suggestion. I believe this is the easiest solution!

Unfortunately, the previous developer included the Production Server name as part of datasource. It may cause confusion that's why now, we need to change the datasource name, repoint to UAT server/database, etc.

Given this situation, what can be my possible best approach?

Thanks

[abhilash_kumar]

Because it refers to the Prod database, you would need to manually set the datasource to the UAT database.

Open the report in the CR Designer > Select Report option > Set datasource location > Use the pane at the bottom to connect to the UAT database > Highlight each table on the pane on the top and then highlight the corresponding table from the UAT database at the bottom and hit 'Update' - repeat this for each table.

-Abhilash

[Former Member]

Hi Abhilash,

I'm not familiar with CR Designer. Currently, I'm using Crystal Reports Developer XI. Is it a different tool?

Thanks

[abhilash_kumar]

Yes, both are the same.

-Abhilash

[Former Member]

Thanks Abhilash.

I performed the following steps:

1. Edit the rpt file and modify the datasource in Crystal Reports Developer on my local machine.

2. Manually copy and replace the .rpt file in Crystal Server (Input FileStore).

3. Access CMC.

4. Right click the report > Refresh Options > Default Database Logon Information

5. After that, I see that the new datasource is reflected when I right click report > Database Configuration.

But when I go to Instance Manager to repoint the datasources in schedule, I noticed that it's still using the old datasource as seen below. Note: I just edited the image and replaced the datasouce name.

How can I also update this?

Thanks

[DellSC]

Actually, instead of editing each report in Crystal, you should be able to go to the "Database Configuration" for each report in the Central Management Console and set it to use a "custom" data source.  Point that to the UAT database.

Another option, if you know how to program in Java or .NET, would be to write a program that will go through all of the reports and change any that have a specified connection to another connection in code.  If you have a lot of reports, this would be a good way to do a "bulk" update.

-Dell

[Former Member]

Hi Dell,

Thanks you for your response.

I had done this approach in one report and it worked. But I also need to modify the datasource in it's Schedule via Instance Manager (right click > Reschedule > Database Logon > Use custom database logon information from default settings).

Seems like the Java or .NET code would be a good solution. Will the Java/.NET code be able to update the "Database Configuration" to use custom datasource in both the report and schedule? I'll really appreciate if you can enlighten how to do this.

Thanks you very much!

[Former Member]

Hi Dell,

BTW, I had also tried using deploying "Schedule Manager" and successfully updated the datasources to custom and place the UAT database for all reports in folder.

Unfortunately, same thing happen for report schedules: It does not update the datasource in it. I need to manually edit all the schedules in "Instance Manager".

Is there a better approach for this?

Thanks

[Former Member]

Is it possible to code this in .NET/Java to refresh/modify only the datasource of report schedule?

I'll appreciate if there's a documentation on how to do this.

Thank you very much.

[DellSC]

Yes.  I'm not entirely sure you can update an existing schedule, but it's worth a shot.  If not, you should be able to reschedule it with the new database connection - you'll just need to make sure to "schedule for" the original owner of the report.

You can get more info about the SDKs here:

-Dell

[Former Member]

Hi Dell,

Thank you for introducing me with the SDK.

Had managed to modify the database configuration (server name, database name, user) of the report schedule thru the SDK.

For the password, it's setting the password in clear text when I queried it on AdminTools. I used the following codes to set the DB config:

boInfoObject.ProcessingInfo.Properties["SI_LOGON_INFO"].Properties["SI_LOGON1"].Properties["SI_SERVER"].Value = "SERVER_NAME";

                boInfoObject.ProcessingInfo.Properties["SI_LOGON_INFO"].Properties["SI_LOGON1"].Properties["SI_DB"].Value = "DATABASE_NAME";

                boInfoObject.ProcessingInfo.Properties["SI_LOGON_INFO"].Properties["SI_LOGON1"].Properties["SI_USER"].Value = "DATABASE_USER";

                boInfoObject.ProcessingInfo.Properties["SI_LOGON_INFO"].Properties["SI_LOGON1"].Properties["SI_PASSWORD"].Value = "DATABASE_PASSWORD";

I had also tried retrieving the password from its SI_PARENTID and place it into SI_PASSWORD, but with no luck.

Is there a way to encrypt the password and pass it into SI_PASSWORD? Hope this is possible.

Thank you very much!

[DellSC]

Instead of setting the individual properties on the existing schedule, I would create a new schedule using the properties from the old one and pause the old schedules (so you can go back and verify that the new ones are scheduled correctly.)  That way you can securely set the db password.  It's a bit more complex process - especially setting up the parameters and the destination, but it's more secure.

If you reset the default login on the reports prior to updating the schedules, you won't even have to set the login for the schedules - they'll get it from the report default.

-Dell

[Former Member]

Hi Dell,

Thanks for your response.

Will only need to modify the database configuration (server, database, user, password) of report schedule. Seems like it's not possible to set the password correctly in SDK; that's why we need to recreate ALL the schedules?? This is not really intuitive. Is there an alternative approach?

Found this link which shows samples on Report Scheduling:

NET BusinessObjects Enterprise SDK Samples - Business Intelligence (BusinessObjects) - SCN Wiki

Unfortunately, can't open files with .sda extension.

Thanks again for your help

[Former Member]

Hi Dell,

Is there a way to copy existing schedule (scheduled date/time, prompt values, destination directory, email settings, format, etc) thru the SDK?

Thanks in advance.

[DellSC]

Yes, but....   I think you might run into the same issues with setting the password.  I have Java code that I've written for rescheduling reports based on existing schedules, but it doesn't set anything with the database - just uses the connection info from the existing schedule.

-Dell

[Former Member]

Hi Dell,

Thanks for the info.

Guess there's no other way but to set the database configuration (server, database, user) except for password via SDK then manually edit each schedule in Instance Manager.

This way I only need to change 2 things in Instance Manager:

• database password
• Recurrence - Start Date/Time

Just let me know if there's an easier way.

Thanks for your help!