Posts tagged crystal reports server 2008

SI_Viewer_URL2

EDIT the SI_VIEWER_URL property in Crystal Reports Server 2008

I had an issue were I started to publish reports via Crystal Reports Server 2008, but if I included the variable SI_VIEWER_URL so that a link to the report would be included, it always went to wrong host name, and not to the DNS Alias that I setup.  Also, It kept referring to the “JAVA” openDocument viewer, and I am using the .NET version.

 

So in order to change it:

Log In to the CMC

Under Management Click “Applications”

 

Under the Applications View Right Click on CMC and Select “Processing Settings”

 

Then Finally Replace the URL that is in the Box, with whatever it is you need your URL to look like.  Note the Extension change to ASPX from JSP because i’m running the .NET Viewer

Thats all there is.  Now Next time you include the SI_VIEWER_URL variable, it should point to the URL you entered in the CMC Settings Above.

Happy Reporting!!

Facebook Twitter Email Linkedin Digg Delicious

Resetting Crystal Reports Server 2008 Administrator Password

Recently someone asked me how to reset the Administrator password on a Crystal Reports Servers 2008 installation without losing all the data. I’m going to share here the solution, just in case someone else is searching for the same thing.

In the Crystal Database, there is a table called “InfoObjects6”, maybe you have already heard of it in one of my previous post.  In this table, the object with objectID = 12 is the Administrator account.

So, in order to reset the admin password, run this query:

For those whom are curious what this really does. The CRC is the “hash” that verifies that the value of the “Properties” column has not been changed. By setting it to blank, it makes whatever password is stored under Properties Invalid. Next time you try to log in, a new “default” password hash will be created along with a new CRC.

Basically, it will set the Administrator password to blank, at which point you can log in and set it to whatever you want!!

Facebook Twitter Email Linkedin Digg Delicious
noprinter

Error Adding Reports To Crystal Reports Server

So I was Trying to add reports to Crystal Reports Server through the CMC, and I was getting an error that was not letting me upload the reports.  Adding the report via InfoView did not work either, InfoView only gave you a “time out” error but I suspect in the background, it was the same as the error I was getting through the CMC

The Error Reads:

So of course, I check out the SAP support forums, and most questions, if not all questions that I found went unanswered and unresolved.  The SAP Notes (The Paid Support) were no help either.

After much troubleshooting, this is what I came up with to fix the issue:

  • Open up the report in Crystal Reports 
  • From the Menu pick File -> Page Setup
  • Select the check box that says “No Printer (Optimized for screen display)”

And that’s all there is to it, once you do that, “reportadd.exe” wont error out anymore.  I guess this is some issue with the Crystal Report File format, but whatever the case, this is a quick and painless fix for me.

 

Facebook Twitter Email Linkedin Digg Delicious

How to Change Owner for a Crystal Reports Server Report

In Crystal Reports Server 2008 (and Older versions too), Whenever a user gets deleted or reports are imported, and if the original owner does not exist, the reports get automatically assigned to the “Administrator” User

This can become a pain if you already have security setup, where only owners can change reports.

In order to change report owners, you have to use the SDK, but SAP does a poor job even explaining how to do it, which is why im posting my own quick and dirty .NET solution to changing report owner (SI_OWNERID) in Crystal Report Server

The Code at the heart of it all:

– This function will pull the reports from Crystal, and bind itself to the gridview control called gridview1

 

– The next piece of code.  This is a built in Gridview Method, so you have to write your own code to update the Infostore

 
– The Last piece of code is the function I use to retrieve the list of users. Once I have that list of users, I create a datatable and bind it a combo box which displays the user name, but returns the user ID. With that User ID, I set the new SI_OWNERID

Thats pretty much it, Those 3 functions are the heart of my application and should either help you complete your project, or get it started.

Im not a .NET developer, I learned to develop C++, Basic, PHP, Java, etc, so if you find mistakes in my code or better ways to do things, please post a comment.  If you want a full copy of my project, where you can just download it and use it, just Contact Me and I will email you a copy of the entire project.

I dont have a Java Version of my code, but If you are looking for a Java Solution, look at Bob Loblaw’s Code from Forumtopics.com, his bit of code can put you in the right direction

Enjoy!!

Facebook Twitter Email Linkedin Digg Delicious
ccm

How to FailOver Crystal Reports Server 2008

  • It is clearly stated on the SAP website, and I’ll quote:

Clustering, load balancing, and failover are not supported.

Well, that doesn’t mean failover won’t work, its just not supported by SAP.  Well, I have a solution for everyone that works beautifully for me, and yes, i’m currently using this setup in a production environment.

I just want to make 2 points, make sure you have the back up of CMS database and Filestore before trying the following steps.  Also, It’s been verified that the query works for ALL versions of Crystal Report Server / Business Objects Enterprise, so even though these instructions are written for CRS 2008, I’m pretty sure you can use this method for the other versions as well. If you do, and it worked for you, please drop a note on the comments section

OK, let’s get going!!

Software

  • Crystal Reports Server 2008 v1 with SP2

Servers

  • Server1 (Windows 2008 / 64bit / 2GB / VM)
  • Server2 (Windows 2008 / 64bit / 2GB / VM)
  • SQL 2005 Back-end
  • IIS7 – 64bit

Before Starting

  • I’m assuming this is a new installation. If you are trying to do this to an existing database it could be possible, but I’m not going to try to tackle that issue in this document
  • I’m assuming this install is being done in a Windows Server, with a MSSQL backend (Not MySQL, not SLQExpress) (Only Step 7 would be different, so if you can rewrite the query for you specific DB then I think this method will work for you)
  • I created one DNS alias, that points to the IP of server1, and it case the server fails, I can change the Alias to the IP of server2 (this way I don’t have to have my clients/users point to a different name or IP, its all done on the back-end)
  • When I installed the Crystal Reports Server, I installed it into its own “Site” and “Application Pool” in IIS7
  • I bound the New Site to the DNS Alias I created above, so the only way to reach the site is by the alias. The way I tested on each server, I added the alias and the loopback IP (127.0.0.1) to the host file on each server.
  • I setup a sync job using Robocopy, so it would keep the “Filestore” folder of the 2 Crystal Servers in sync (%Program Files%\Business Objects\BusinessObjects Enterprise $version$\FileStore)

Prepping the Failover Pair

1) Have both Crystal Reports Servers fully Installed and Service Packed

– Both Pointing to the same database or separate ones, you get errors if you install it to the same server, but that doesn’t matter, since we will be wiping out the configuration,
– Also, It doesn’t matter which order you install the Crystal Servers, since we will wipe out the database and refresh the configuration while setting up the Fail-over pair

2) Launch the CCM (Central Configuration Manager), and Shut down the SIA (Server Intelligence Agent) on both Servers

3) Update the SIA DB settings on both servers (Right Click the SIA, click “Properties” -> Click the “Configuration” Tab-> under the heading “CMS System Database Configuration” click the “Specify” button -> Choose Update Data Source Settings)

4) From Server1, right click on the SIA and Select Properties->Configuration->CMS System Database Configuration-> Specify -> Recreate the current Data Source.  This will wipe our your current CR database and add fresh records.

5) Once this is complete, it will prompt you for CMS, just Hit Ok, after that you will get a window that reads “Adding Servers”, ****It is very important that you allow this process to complete****

6) You will get a prompt titled “Central Configuration Manager with Some Instructions, just hit OK and then start the SIA

7) Wait a minute or two and then hit the “Manage Server” button , it should prompt you for credentials, and since the database has been wiped out, the Administrator password should be blank so just hit “Connect”

8) By now you should see all the services running and enabled. The one we care about the most here is Server1.CentralManagementServer, because we will end up deleting it from the database.

9) Stop the SIA, Fire up your Favorite SQL admin tool, and get ready to delete the Entry for the CMS by running the SQL Query bellow

You can also create a view, which will help in the futre for other “failovers”, I recommend this

10 ) Now after you run the query, find the row with ObjectName = SERVER1.CENTRALMANAGEMENTSERVER, and since we just recreated the Database from SERVER1, the Entry in the database will be SERVER1.CENTRALMANAGEMENTSERVER. Take note of the ObjectID

<img class="aligncenter size-full wp-image-514" title="query" src="http://pinchii.com/home/wp-content/uploads/2010/09/query.png" alt="" width="688" height="364" />

11) Now lets delete the CMS row from the Database, so now run a delete command
Delete from cms_infoobject6 where objectID=”CMS_ID”
********** DO NOT SKIP THIS STEP ****************

12) We are done with Server1, lets move on to adding Server2 to the Fail-over Pair.
First by launching the CCM, then locate the SIA (Server2) service ****DON’T START IT****

13) Click the “Add Server Intelligence Agent” button (server icon with the blue plus)

14) You will get the Add Server Intelligence Wizard, click Next
Name, Choose Server2 (Same Name as the SIA that Already Exists)
Port 6410
Pick “Create default servers on the new node”
Check “Recreate Server Intelligence Agent on local host……”

15) It will then take you to the CMS Configuration, for the New CMS port use port 6400, also specify one more time the DSN connection to the Crystal Server Database, the same database that Server1 is pointing to (uncheck auditing database source)

16) Just hit next until you get to the end, after that you will get a window that reads “Adding Servers”, ****It is very important that you allow this process to complete****

17) Once that is complete, start the SIA (Server2) Service, and wait for it to finish loading

18) Click on the Manage Servers Icon (server with a check mark), when prompted for credentials, just hit ok. You will now see that you have duplicate processes for both of your servers except for the CentralManagementServer, there should only be one named Server2.CentralManagementServer

This is pretty much it; you now have 2 servers that can be failed over to each other. If Server1 is your primary server, then use the Fail-back instructions bellow to get back from Server2.  With these instructions you can probably add more then 2 servers, I would guess you could probably put up to 5 servers or more and have them all as failover servers, but if you need more then 2 servers to fail over, you should probably be talking to SAP / Business Objects about a clustering solution

Failing Over

  1. So we assume that server1 died. It won’t power on, etc. (This means the SIA on server1 is down, obviously)
  2. Go to your SQL Admin tool, run the Query to view the CMS ObjectNames and Delete the Row that has the CMS information (Steps 7, 8 and 9 above)
    If you created the view:
    SELECT * FROM [CMS_ObjectNames6] WHERE (ObjectName LIKE '%CENTRALMANAGEMENTSERVER') OR (ObjectName LIKE '%CMS')
  3. Start up the SIA service on your failover server, give it a few minutes
  4. Update your DNS Alias to the Failover server, and you are good to go

Fail back

  1. In order to fail back to the original server, stop the SIA on your backup server
  2. Go to your SQL Admin tool, run the Query to view the CMS ObjectNames and Delete the Row that has the CMS information (Steps 7, 8 and 9 above)
    If you created the view:
    SELECT * FROM [CMS_ObjectNames6] WHERE (ObjectName LIKE '%CENTRALMANAGEMENTSERVER') OR (ObjectName LIKE '%CMS')
  3. Start up the SIA service on your failover server, give it a few minutes
  4. Update your DNS Alias to the Primary server, and you are good to go

UPDATE:

It seems that you can skip step 2 of the fail-over and the fail-back procedure.  As long as you follow the “setting up the fail-over pair” instructions, you just need to make sure that only One SIA is running at a time, if both are running at the same time, the SIA will start, but your CMS services wont, and you won’t be able to use either.

Facebook Twitter Email Linkedin Digg Delicious

Decoding the Crystal Reports Server 2008 Database – CMS_InfoObjects6

I’ve been looking for a way to run some sort of audit or just a peak at what’s included in the Crystal Reports Server 2008 Database.  This database controls all the aspects of Crystal Reports Server including the parameters that are used to launch the WACS server, SIA, and the CMS (If you use CRS2008 you know what these terms mean, otherwise you wouldnt be looking at this blog right now)

So, in order to run some sort of audit on the database, you have to go through and look at the CMS_InfoObjects6 table.  This table contains all the information on CRS2008 including Nodes, Folders and Reports.

If you look at the database table itself you notice that alot of the fields are in “Binary Data” format which makes it almost impossible to look at, well, almost….

Here is a nice query, that will help you get started in understanding and using the CMS_InfoObjects6 database

There is really no real magic to this query, and nothing really special.  It tells you what the object names are, and converts the lastmodifydate  from binary to human readable format.  I have yet to figure out how to decrypt the SI_GUID, SI_CUID, SI_RUID and the Properties Field, but I will leave that for another day since the query above gives me the information I was looking for

And before I forget, I have to give credit to Andu over at the BOB forums his post is basically where this query comes from

Facebook Twitter Email Linkedin Digg Delicious
Go to Top