How to get a Sharepoint List in Xcelsius and publish outside Sharepoint

If eg. Treasury is maintaining a Tax Rate by country list in the Corporate Sharepoint directory, you can import that list into Xcelsius for use in your dashboard without setting up a portal provider as you do not want to publish the dashboard in Sharepoint itself.

The easiest way is to use the url built up:

http://{0}/_vti_bin/owssvr.dll?Cmd=Display&List={1}&XMLDATA=TRUE

{0} is the server path.
{1} is GUID from the list (go to settings of your list, in the url it is the long code after List= )

Import into Excel, add an XML Map, import that into Xcelsius and set up the data connection with Excel maps.

Link your xml url to a cell containing the url + random number to be sure the cache is cleared in windows explorer. [="http://{0}/_vti_bin/owssvr.dll?Cmd=Display&List={1}&XMLDATA=TRUE&r="&RAND()]

Set the refresh on open. Now each time the dashboard loads, the xml list from sharepoint is loaded.

Filed under Connection | Tagged , , , | 4 Comments

Suppressing BEx Variable pop-up screen when opening dashboard in SAP portal

When the dashboard is published in SAP portal and it is based on a BEx query connected with a Netweaver dataconnection and that BEx has mandatory variables without default values, the variabele screen is always displayed before actually loading the dashboard. It does not matter if you set the default values within Xcelsius or have not set to refresh a dataconnection before loading.

If you cannot set a default value to the mandatory variable in BEx, then you could call the dashboard with the mandatory variable value defined in the URL in order to suppress the variable screen, as most likely your dashboard will override it anyway.

Eg technical name variable = zcs_calendar_year with value 2012
Eg technical name dashboard = zcs_dashboard

URL:
/irj/servlet/prt/portal/prtroot/pcd!3aportal_content!2fcom.sap.pct!2fplatfo rm_add_ons!2fcom.sap.ip.bi!2fiViews!2fcom.sap.ip.bi.bex?DASHBOARD=ZCS_DASHBOARD&BI_COMMAND_1-BI_COMMAND_TYPE=SET_VARIABLES_STATE&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1- VARIABLE_TYPE=VARIABLE_INPUT_STRING&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1-VARIABLE_TYPE- VARIABLE_INPUT_STRING=2012&BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1-VARIABLE=ZCS_CALENDAR_YEAR

So add the 4 parts of the variable to the url call
1: &BI_COMMAND_1-BI_COMMAND_TYPE=SET_VARIABLES_STATE
2: &BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1- VARIABLE_TYPE=VARIABLE_INPUT_STRING
3: &BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1-VARIABLE_TYPE- VARIABLE_INPUT_STRING=[value]
4: &BI_COMMAND_1-VARIABLE_VALUES-VARIABLE_VALUE_1-VARIABLE=[tech.name var]

In case of a second mandatory variable add the 4 parts but replace BI_COMMAND_1 with BI_COMMAND_2.

By the way: the same url built up can be used for opening a BEx report like the BO open document statement. All you have to do is change the DASHBOARD= into TEMPLATE=xxxx&QUERY=xxxxxx .

Filed under BW / Bex / BICS | Tagged , , , , | 2 Comments

“Empty” value in optional prompt in Bex query

I finally found the solution for the issue with the optional prompts and the empty value string when connecting through BICS to a Bex query.

Unfortunately BW does not view the Excel formula =”" as an empty string as thus your query returns no data. Formerly with the Live Office connections you needed to pass “” as the empty value string. This does not work either.

I logged this error at SAP and they told me to create the optional prompts in the Bex query itself and then use the prompt selector. However I do not want to use this selector as I use the interactivity from the components (charts) itself to fill in the prompt. This huge box I do not want to use at all. If I wanted to bypass the prompt selector component then I needed to create 2 BW queries, one with a prompt and one without one. Come on, not really an option either!

After some trial and error I found the solution. If you want to use a formula to get the prompt values you could instead of the =”" a formula like =9/0. This returns a #DIV/0 error and when you have the general Excel setting to suppress errors like #DIV/0 then it will turn into an empty cell value and the prompt value is ignored by BW.

Note: The general Excel setting can be found here:
File – Preferences – Excel options – Ignore Excel Formula Errors

Filed under BW / Bex / BICS, Connection, Excel, Xcelsius | Tagged , , , , , | 6 Comments

19 Beer Infographics To Make You See Double

It is just that my current project is at a beer company that I would like to share this post with you by by Drew Skau. He created 19 Infographics about the topic of beer.

http://blog.visual.ly/best-beer-infographics-and-data-visualizations/

Filed under Miscelleneaous | Tagged , | Comments Off

XComponent and Combobox without drop down triangle

It took me the better part of a day to fix the following error:

When I added any one of the XComponents provided by Antivia and I previewed my dashboard, the combo boxes were not rendered proberly anymore. The drill down arrow disappeared. Functional the combos still worked.

As another developer had no problems (thus with same settings and patches), I thought it must be one of those bugs which mess up your whole dashboard. So I started to create from scratch the whole dashboard again. And indeed all worked as it should …. untill I added my connections. Suddenly the error was back. Removing the connections and the bug was fixed. So it definetely had to do with connections.

After some trial and error I figured out it had to do with the “enable load cursor” selection. When enabled the bug appears, disable the option and the dahboard works fine.

So instead of building from scratch I could have fixed it by simply removing that option ;-)

Filed under Components, Xcelsius | Tagged , , , , | 3 Comments

Maximum character output size limit reached

If you come across the following error in Xcelsius with either with QaaWS or BIWS (WebI):
Maximum character output size limit reached. Contact your BusinessObjects administrator. (Error: ERR_WIS_30272)

2 things you might try to solve it:
1. This error can occur when there is a mismatch between the datatype of an Xcelsius cell when sent as a prompt value to a Xcelsius QWAAS connection expecting a different datatype. eg. the xcelsius prompt sends a number but the WebI prompt expects a text.

2. change the WebI Server setting in the CMC. (Kbase article: https://service.sap.com/sap/support/notes/1370045)
[The Web Intelligence Processing Server (BO XI R2: Web Intelligence Report Server) configuration parameter Maximum Character Stream Size (MB) (BO XI R2: Maximum Character File Size) is too small for the output. Increase this value in the Central Management Console (CMC).]

Filed under Connection, Xcelsius | Tagged , , , , , | 3 Comments