Updating Dynamics SL BP to SL 7.0 and Publishing Reports to BP

Goal 

There are a lot of pit falls to implementing Publishing of Reports after an upgrade of BP and SL to 7.0. I am trying to put together a complete list of how to do things so that you get it working right first time out of the box. There are a lot of things that need to be implemented or installed in a specific way.   Instructions are not crystal clear either.  Hopefully this helps you implement this functionality after an upgrade with little need for support.

One thing you should keep in mind is that this technology is outdated and “twilight”.  It will be retired soon. There are many cooler ways to publish data to your Business Portal.  

Environment

This is your first pitfall. Make sure that you install in a 32 bit environment. Application Server must be 32 and so must BP. Publishing reports will not work in 64 and BP won’t install. Also Crystal Does not play nice with Server 2008, so don’t. 2003 is better if you want to use this functionality. So windows 2003 for the BP Server, 32 bit. For App Server 32 bit xp. You can run App Server on the BP Server but this requires a session always logged in on the server. I do it as a separate box.

Issue

            The things you must do to make Publishing Reports from SL work out of the box:

1)      Install SL Client on BP Server First. If you are setting up a separate Application Server, it doesn’t matter. You must have SL installed on the Business Portal Server. You must install it before you install Business Portal.

2)      When creating your Web Application in WSS or MOSS that Business Portal will live in, IF you are installing to port 80, you will notice that it defaults to create new web application called Sharepoint – 80.  If you click nothing and go forward this will create a new port 80 web application AND DISABLE the current default – 80 web app. You cannot have two applications on port 80 unless you are using http headers. This will create problems by default for BP. It installs a virtual app called crystalreportviewers10 in the default – 80 web app EVEN if it is disabled.

3)      After the BP install, you must run the BPSLClientSetup.exe Utility to tell BP where SL is on the BP server.

4)      After the BP install, you must share out the SampleReports directory in BP. Make sure all users that will use this function have full rights to NTFS and Share Rights (they are different).

5)      Make sure the Application Pool User for BP has rights to the SampleReports Folder and Share.

6)      Make sure the FileService Com++ component Identity has rights to the SampleReports Folder and Share.

7)      On the Application Server, you must download CDONTs. This is not included in the Application Server Install and email functionality from BP report publishing will not work.

Resolution

            For issues 1, if you don’t install the SL Client before BP then you just have to install the full install on the BP Server. When you do this, the BPSLClientSetup.exe cannot be used to say where SL is. You must manually edit the file to say Local:

C:\Program Files\Microsoft Dynamics\Business Portal

Open:  Microsoft.Solomon.SolomonInstall.xml  in notepad

<SolomonInstall>
                <UNCPath>Local</UNCPath>
</SolomonInstall>

            For Issue 2,  You will know you are in this boat when you get this error when clicking the List of published reports in BP:

            You see your report, but  all of the buttons at the top only have red x in them. Export Function doesn’t work. Print Function doesn’t work.  What you must do to fix is to :

·         Delete the crystalreportviewers10 virtual directory from the disabled default web site in IIS.

·         Create a new virtual directory called crystalreportviewers10 (yes lower case) in the new SharePoint – 80 root in IIS. It must point to the C:\Program Files\Common Files\Crystal Decisions\2.5\crystalreportviewers10 folder.

o   Also Exclude this folder from SharePoint

§  Go into Central Admin

§  Application Management / Under SharePoint Web Application Management  

§  Click on Managed Paths

§  Add crystalreportviewers10 as a path (typed out just like here).

            For Issue 3, if you did a client install just run the utility and remember to use //ServerName/Share/Path to SL. If you had to do full install then edit the file and put Local as in 1.

            For Issue 3, 4 and 5, You will know you have this one if Report List shows but you get error or message saying that the file is missing or not published yet for all reports. To find out each user, just go to IIS, look at the advanced properties of the web site or just properties (depending on version of IIS). It will list the Application Pool. Click on the App Pool and look at the identity. For the File Service component, goto administrative tools, select components. Drill down in My Computer, under com fine the Microsoft File Services. It defaults to the FileServiceUser, I always change to the same user that SharePoint is using. Finally to assign rights just browse to the program files/Dynamics/ Business Portal set of folders. You will find the Application folder and under that will be Reports/SampleReports. Right mouse and select properties. Make sure it is shared and permissions are to all users that will use and the accounts above.  

            For 7, just download CDONTS and install.

Conclusion 

         There are a few problems that can happen when you install/upgrade BP to 7.0 and try to use Publishing of Reports. Just do it right the first time and you don't have to deal with most of them. If not there are fixes.  

Dynamics SL Reports cannot print preview or print after restore of databases.

Goal 

You want to backup your live databases and create test databases on the same SQL Server for Dynamics SL.

     

Environment

 Dynamics SL 7.0.

Issue

           You change the Database Name field in the System Tables (Company and Domain Tables). You update views in Database Maintenance. You even sync Security just in case. But you get an error for any report you try to print preview or print.  You get a CRPEHLPR.EXE error:

You also get a ROI Get RIPARAM failed error:

All reports, all modules.

Resolution

            The rptExtra Table contains a single record. It is in the System Database. If you look at it, it has your live database name in the databasename field. You cannot just change the value to the test database name. You have to delete the record then run Synchronize All Ownership and Security in the Database Maintenance Screen. This screen is usually installed under Admin Tools on the server. The screen number is 98.290.00.  This will create the record in RptExtra with the proper values for reports to run in Dynamics SL.
            Finally you must fix the master60sp user after you run Database Maintenance Synch all Security. It’s a bug. Run this script in all application databases after:

exec sp_grantdbaccess 'Master60SP';

exec sp_addrolemember 'Public','master60sp';

exec sp_addrolemember 'msdynamicssl','master60sp';

exec sp_addrolemember 'db_datareader','master60sp';

Conclusion

                Restoring Live to Test needs a few extra steps to get Reports working in Dynamics SL.

Dynamics SL BP Install “Cannot open database ‘System Database Name’ requested by the login. Login failed. Login failed for user ‘BusinessPortalUser’

Goal 

You are trying to install/upgrade Business Portal for Dynamics SL 7.0. You do the install and get no error messages. You try to open business portal and no error message shows you get that Error. An Unexpected Error has occurred.  You turn off simple HTTP errors and still you get the same nondescript error.  

You look at %temp% (Hit Start, then run and type %temp% in the command line) and all the install logs (place you should always start with a problem in installing BP), but find no errors you can see. You also check the logs under Application and Security and nothing is there.  SharePoint runs fine but you can’t find what the actual error is in BP. IT just won’t run.

     

Environment

 Dynamics SL 7.0. Windows Server 2003 Enterprise SP 2. SQL 2008 SP 1.  

Issue

            First Trick, when you are getting errors that don’t tell you anything and nothing is logged, Open IIS Manager. Right mouse on the site and select browse. In this case drill down to the BusinessPortal Virtual Directory and browse. When you do this you get the actual error which is: “Cannot open database ‘System Database Name’ requested by the login. Login failed. Login failed for user ‘BusinessPortalUser’.

            Sounds Simple enough! You go into SQL Enterprise Manager and log in as BusinessPortalUser. You make sure you can get in see the system database and the application. If you cannot get in then the password is different then you thought and in the upgrade you told SL the wrong password. Change the password in SQL by double clicking under Security and Logins and BusinessPortalUser. Type new password twice then hit ok.
            If you had to change or not, then search for the pcconfigure.exe. It should be in port 80’s virtual directory. Double click and enter the server, system database, BusinessPortalUser and the password.
            9 times out of 10 even if you do this, you will still get the error. This happens to me a lot so you are bound to see it. Ergo this post.  

Resolution

            Business Portal has a registry key that it keeps login information for the business portal user. If the owner of the Business Portal does not have proper rights to the registry then it will fail with the error.  The Key is the PMA key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Business Portal\PMA

 So you go into RegEdit, drill down to the key. Right mouse on PMA and select security. Assign the owner of the businessportal to have read rights.  To find the owner, look in IIS under the Business Portal virtual Directory then go to the application pool for it and assign it to the identity in that application pool.

            There is a chance that the Capicom.dll is the wrong version. I have only seen this once or twice but know a that you may have to check that if its not fixed.

Conclusion

            A common error in BP can be better diagnosed by browsing in IIS and then fixed by assigning registry rights to the owner of BP.

               

Dynamics SL Screen is customized as Maximized and will not return to normal state

Goal 

A user that has All User Customization Rights (Rights to create customizations everyone sees) keeps customizing a screen or series of screens to be maximized. You need to be able to fix this customization or remove it. But you have other ligitimate Customizations so a total delete of custom is not possible.

     

Environment

 Dynamics SL 7.0.

Issue

           When you Look at properties of the Form there is no Maximized or Windows State Property to edit. You can only view the property by hitting Shift F4 which is the Customization Property Browser. It has a property called windows state. But that property as everything on the Customization Property Browse is not editable.   The regular Property window reached by hitting F4 does not have Windows State.

            If you physically un-maximize the window in custom mode and hit save, then you open the screen again, it is still customized.
            Often this problem has the effect of hiding the grid on the screen. Like the GL Journal Transactions Screen. With this problem if it is customized as maximized the grid will disappear (become left justified OFF THE SCREEN).  You have to get rid of the maximized then move the grid back to the center of the screen manually.  But once again you have the issue that the screen will not un-maximize.

Resolution

            The easy fix to this is to export your customizations to a text file. Export only all user customization. Open the Text file and look for  windowstate. You will find something like this:

Begin Form Form1
'' Version         =   6
   height          =   539
   left            =   186
   top             =   137
   width           =   652
   windowstate     =   1
End

Change the 1 or 2 to 0. Save the Text File then Import the Custom back in.

Conclusion

                Sometimes users can save a screen as customized and distort the screen so that key information does not show. To get rid of a customization like this, export all user customizations and look for windowstate = 1 and change to  = 0.

                Do yourself a favor, if you do a customization for all users in SL, export when it is complete so you can revert back. If you have good export backups, you can just go to the screen in question and select remove all customizations for the screen then reimport.  Can save you some grief.

Exports to Excel in Dynamics BP using Office 2010

Goal 

You are running BP for Dynamics SL or GP on SharePoint 2007.  Your organization is deploying Office 2010 and Windows 7. You want to export to Excel from Query Web Parts. You find issues with BP exports and Office 2010.   

Environment

Dynamics BP for GP 10 or 2011 or Dynamics BP for SL 7.0 or 2011. SharePoint 2007, WCC or MOSS. Workstation is Windows 7 and Office 2010.

Issue

            Trying to export from web parts to Excel generates the following error:

“The Office Web Components OWC must be installed to copy results to Excel”

You lookup OWC and to your dismay you find that Microsoft  stopped supporting this technology. There is not a OWC for Office 2007 or Office 2010.

You can export using this method from two different places:

1)      The Query Web Part shown below:

2)      A Rich List Viewer Web Part as Below:

Resolution

If you lookup this issue on Partnersource, you find that it suggests for Office 2007 to install 2003 and XP Components (10 and 11) until one works.

For Office 2010 on Windows 7 I have found you just need to install 10 (XP Components). Microsoft did not seem to mention Office 2010 (see related articles) 

Download Office XP Tools: Web Components. This is OWC10.exe.

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=982b0359-0a86-4fb2-a7ee-5f3a499515dd&displaylang=en

install on Windows 7 and export of data from Query Web Parts in BP will work to Excel 2010.

Conclusion 

Even though OWC is not supported anymore you can still support this technology under Office 2010 and Windows Seven by installing Office XP Tools: Web Components OWC 10.