Skip to Content
0
Former Member
Sep 13, 2011 at 12:03 PM

XML Data URL Connection String

51 Views

Hi Guys, when using a dashboard within SAP Business One, does anyone know what the XML Data URL string is to make the data connection between your query in SAP Business One and the spreadsheet in your Xcelcius file.

I was lead to believe it was proc?bpm.pltype=xml&bpm.encoding=utf-8 and that SAP Business One would auto build the full path, however, when running this in SAP Business One I get a Connection error message :

An error has occurred.

For more information, contact the file creator or your system administrator.

Error: Error #2032

Connection Type: XML Data

I have checked and SAP Business One Integration Server is running fine, so can only presume it is the above string, especially as all the standard SAP Dashboards work without any problem ? Does anyone have any ideas or thoughts on this ?

Below is the Query I am using for this Dashboard

select * from (SELECT DISTINCT Country, SUM(SalesYTD) as YTDSales, [MONTH] FROM

(SELECT distinct T2.Country, SUM(T1.Debit) - SUM(T1.Credit) as SalesYTD, T1.Shortname, MONTH(T1.RefDate) as [Month] FROM JDT1 T1 INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode

WHERE Year(T1.RefDate) = 2011 AND T1.TransType IN ('13','14') and T2.CardType = 'C'

group by T2.Country, T1.Shortname,month(T1.RefDate)) sYTD

group by Country,[MONTH]) Q

PIVOT (sum([YTDSales]) FOR [Month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

This query runs fine in Query Manager and SQL Management Studio.

I am using the string in the Data Manager, with MIME Type text/xml and under the usage Tab I have ticked Refresh Before Components are Loaded under Refresh Options.

As always any advice is gratefully received

Kind regards

Sean Martin