question regarding Note 976218 - Upgrading XL Repo...

Former Member · ‎02-08-2010

Hi,

We are upgrade a customer with B1 2005A SP20 to 2007A SP01 PL05. I have a question regarding Note 976218 - Upgrading XL Reporter with user defined fields exposed.

the step for (B)Upgrade from 2005A to 2007A, does that to be done before upgrade on 2005A or after upgrade on 2007A?

Thanks,

David

===

Symptom

You are using Business One user defined fields (UDFs) in XL Reporter

reports, and you want to:

(A) upgrade Business One 2007 or XLR to a higher version

OR

(B) upgrade Business One 2005 or XLR to 2007

After upgrading XL Reporter the names of the UDF's do not have suffixes

like _1, _2 any more and the reports containing UDF's don't work anymore

Other terms

Financial Reports

Reason and Prerequisites

XLR has to make sure all fields exposed to XLR have different names to

each other. Therefore, if two UDFs have the same name in Business One

they are given a different suffixes.

When you remove a UDF from XLR (unselect it from the User-Defined Fields

window), the meta names are removed from the meta data in your company

DB as well as related XLR views, while it still remains in the meta data

in the common DB. If the UDF is added again to XLR, it will be treated

as a new field to XLR, and be given a new meta name since the original

name is still in the common DB. Therefore, the UDF's meta name will have

a larger suffix than the last time it was added. As a result of this

behavior, if you add and remove a UDF several times in XLR, the UDF can

have a meta name with a big suffix.

When you upgrade XLR to a newer version, the entire meta data stored in

SBO-COMMON DB (IXMetaSBOOEM DB in 2005 versions) is refreshed. The

upgrade program will not keep any meta names for UDFs but just use the

default information in the installation package to rewrite all tables

including the tables where UDF information is stored. Therefore when you

start XLR again, all the meta names stored in the common DB are lost.

In 2007 version, since XLR meta data is merged into Business One common

DB, each time upgrading Business One will also cause this issue.

Solution

SAP plans to fix this issue in a patch. Please refer to the info file on

SAP Service Marketplace to confirm.

Until the issue is fixed, a temporory solution is provided.

Apply solution

A)Upgrade within 2007A version

or

B)Upgrade from 2005A to 2007A

A)Upgrade within 2007A version

To apply this solution, follow the steps as below (NOTE: Starting from

now, all customers using UDFs in XLR are REQUIRED to perform steps 1 and

2 whenever they are going to upgrade.):

1) Run the queries in file UDF_Backup_Common.txt attached to this note

on SBO-COMMON DB.

2) Run the queries in file UDF_Backup_Company.txt attached to this note

on your company DB.

3) Backup you company DB and SBO-COMMON DB.

4) Perform Business One upgrade as normal.

5) If after upgrading, you find problems with your UDFs in the reports,

please do not change any UDF settings or report definitions containning

UDFs. Instead, log a support message on SAP Service Marketplace and

upload the two backups made in step 3.

(B)Upgrade from 2005A to 2007A

To apply this solution, follow the steps as below:

1. Backup the SBO-COMMON and Company Databases

2. Open the XL reporter --> Go to Tools --> User defined fields -->

Select previously added UDFs --> now uncheck all the UDFs and click ok.

Wait for the update to finish.

3. Shut down XLR, restart Business One, and then start XLR again

4. Invoke the UDF window of XLR again.

5. Add the same UDFs again (as per step 2) and click ok.

i) In MSSQL Management Studio run the query to verify the NEW names in

column MetaName: SELECT * FROM XRUDF

6. Go to the XL reports containing UDFs and edit the reports as follows:

a) Highlight the relevant row expansion and change the UDF in the

expansions (before changing it in the formula as the content available

for selection in the formula is affected by the expansion definition.)

i) Select Apply > Save

b) Highlight UDF on report

c) Open Formula Builder

d) Locate correct UDF (tooltip shows new and correct meta name) and

double-click to insert correct UDF name.

e) Save > Run report

Reports should now run correctly without errors.

Edited by: David Wei Zhu on Feb 8, 2010 3:08 PM

Former Member · ‎02-08-2010

Hi,