Mass update of location 2

[shellacct]

Greetings all! At my job, we have about 450 crystal reports that for each and every update/upgrade/etc at least HALF of them have their location changed at least once or twice. For example, in the testing phase they will be on the "TEST" server, then for the UAT phase will use the UAT server, then in production, they will use the "PROD" server.

The tables and/or stored procs never change, its just the server that always changes.

We USED TO have CR 8.5 in which i had written a program SPECIFICALLY for this purpose, and it worked fine, until we upgraded to Business Objects XI:Crystal Enterprise. I am wholly unfamiliar with XI, and perhaps I am just looking at this the wrong way. (To be honest, I haven't programmed SINCE then, so I am a bit rusty on top of it all).

It seems that this is a fairly common problem for many people and when you contact Seagate, basically they say "oh just go to set location and change it there." Of course this would not be a problem if it was only three or four reports, but when you have over 400, doing this manually is rather time consuming. I am sure there has to be some type of simple resolution for this. Does anyone have a script or advice, or could someone point me in the right direction?

Thanks!

 

[shellacct]

P.S.

http://www.tek-tips.com/faqs.cfm?fid=5374

was teh closest i could find, but I can't update it at run time, i need to be update all of them beforehand.

 

[synapsevampire]

Check out the product referenced at the top of the page:

http://www.softwareforces.com/?pid=ri31es&ad=tekries:2

It does mass editing of data sources.

Also APOS products might be of interest to you for CE:

http://www.apos.on.ca/downloads/

and they have some freeware:

http://www.apos.on.ca/Freeware/

They have an Excel based product that allows you to change many things across all reports.

-k

 

[shellacct]

Thanks synapsevampire... so do you think it makes more sense to buy the product "off the shelf" rather than to try to code it out? I am rather interested in programming it myself, but if it would be more trouble than it is worth, I guess I should start filling out some forms at work.

 

[synapsevampire]

Depends on your budget and time constraints.

Download the trial version and see what you think.

-k

 

[hilfy]

If you're a programmer who's familiar with .NET, this is VERY easy to do in the .NET SDK - I've written a utility that will set a number of report-specific properties, such as the logon, database, and oracle table-prefix. It took me a while to walk through the documentation on the developer's site, but the actual code is fairly easy. Here's some C# code for updating a single report that might get you started:

    private void UpdateRpt(int rptID)
    {
      string query = "Select * "
            + " From CI_INFOOBJECTS "
            + " Where SI_ID = " + rptID.ToString();
      InfoObjects rptObj = common.BOEInfoStore.Query(query);
      Report rpt = (Report)rptObj[1];
      for (int j = 1; j <= rpt.ReportLogons.Count; j++)
      {
        rpt.ReportLogons[j].UseOriginalDataSource = false;
        rpt.ReportLogons[j].CustomServerName = rptDB.Text;
        rpt.ReportLogons[j].CustomUserName = rptUser.Text;
        rpt.ReportLogons[j].CustomPassword = rptPassword.Text;
        for (int l = 1; l <= rpt.ReportLogons[j].TableLocationPrefixes.Count; l++)
        {
          rpt.ReportLogons[j].TableLocationPrefixes[l].UseMappedTablePrefix = true;
          rpt.ReportLogons[j].TableLocationPrefixes[l].MappedTablePrefix = rptPrefix.Text;
        }
      }
      common.BOEInfoStore.Commit(rptObj);
    }

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[shellacct]

thanks again synapsevampire, but i think it would make more sense at this point to just code it. It would be rather difficult to justify the purchase based on only one of its functions, but i will definately use that as my "plan B" and pitch it to my boss anyway in case they just want it now.

hilfy- thanks a TON! that actually explains half the problems I was having. I was trying to build this in VB6 rather than .NET, and couldn't quite figure out how to deal with the CR enterprise server and the way it handles files, but with .NET you are probably right that it would be significantly easier. Looks like I have alot of reading to do. The C# code is helpful as well, thanks a million!

 

[shellacct]

Just an update... I am still working on this, and as hilfy said, it takes a while to get through their documentation, which really leaves ALOT to be desired. Several sections seem almost counter intuitive at some points!

What i have at this point is a simple form when you first start the program- it has text boxes which prompt for usernames, passwords, what the old DB *WAS* and what you want it to be set to. It will update the location of one report hard coded in about half the time, but I can't seem to figure out how to get the whole list of subfolders and reports to also be appended. (obviously it could be done with some type of loop, but i have no idea what it would be called. Ok, its been way too long of a day, I'm going home.

 

[hilfy]

You can get to the reports in a couple of ways - you have to write a query against the infostore to get to them. To get folders:

Select SI_NAME, SI_ID
from CI_INFOOBJECTS
where SI_PARENTID=xxx
  and SI_KIND = 'Folder'

You replace "xxx" with the parent ID - I usually walk down the folder tree using recursive functions. To get reports in a folder, you substitute "CrystalReport" for "Folder" (I think...)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.