#SYNTAX error with report built based on many excel dataproviders (BO 6.5 SP3)

[Former Member]

Post Author: MicheleGuchez

CA Forum: Desktop Intelligence Reporting

Hello every body,

I've built a BO report based on many excel files (personal files). I used the range option to select a subset of data different for each excel file.I've created variables for each dataproviders.

After building my report, I tested it by refreshing it. For a few of the variables, I've got a warning such as "some of objects are different would you like to keep or display data in an other report..." and an error message: "#SYNTAX" appears for a few variables.

I've check the dataproviders. Everything is ok but It seems that it doesn't taking account the specified range of cells. I had to open the excel files to get the data as specified in the dataprovider.

Do you have any idea on the source of the problem ? How can I fixe it ?

Thanks a lot, Michèle

[Former Member]

Please try following steps:-

To resolve the error message, modifications need to be made in both the spreadsheet and Designer.

To modify the spreadsheet

Open the first worksheet tab.

Highlight the entire table by dragging the mouse from the top row, left column down to the bottom row, right column.

Click Insert > Name > Define. The Define Name window appears.

Delete any existing references to other names.

Click the Add button. Enter a new name for this particular table.

Click OK. The Name box located in the upper-left corner under the font names has a drop-down list which allows selection of the chosen area.

Repeat steps 2 through 6 for each worksheet. Clicking each name returns the correct worksheet.

To perform modifications in Designer

Click File > New to build a new connection pointing to the spreadsheet.

Click the Definition tab > New.

Click ODBC drivers > OK. The ODBC Drivers window appears.

Click the ODBC Admin button. The ODBC Data Source Administrator window appears.

Click the Drivers tab > Microsoft Excel Driver > OK.

Click Generic ODBC datasource in the Database engine drop-down list. Name the connection.

Click the data source name from the data source name drop-down menu > click the Test button. Click OK until the Designer window appears.

Double-click in the right-hand window to get the table browser. Drag the desired tables into the table structure pane.

Drag these tables into the objects pane.

All of the objects parse.

[Former Member]

Hi,

It will be quiet difficult to reach the root cause without looking into the report.

#syntax error comes in Reporter whenever we create a formula and mistype any variable.

It also occurs when the variable used in the formula is unavailable in the report.

For example, suppose you have four variables in the report: "first", "second", "third" and "fourth".

Lets say you have created a formula like:

=(first * second) + (third/fourth)

Now you can receive #syntax error in below conditions:

-- if you mistype any of the variable for example, "fisrt" or "thrid".

-- if you delete any of the above variables from the report (in case it is created at report level).

A bug was also found in the product in which the common variables coming in different data providers where losing the name of data provider on refresh of data. This issue was fixed in a hot fix after SP4(dont know exactly which hotfix).

Hope this will help!!