I have an xclesius document using a live office xcel spreadsheet based off an XI r2 webi report. The report produces 2200 rows and the live office insert/refresh works fine in excel (although it takes a while).
I can import the spreadsheet model create my dashboard just fine. I can even view it and see all the data. But once I use the live office connector to refresh the live office content, I only get 512 rows.
I have set the max rows parameter to 3000
Does anyone have any thoughts?
Xcelsius designer 4.5 (applicaton version 4.2.4.3)
Some other general questions,
what is the practical limit for data in xcelsius (how many rows in the spreadsheet) I know there is a warning above 512 rows but, does anyone have any guidelines?
I see now BO has an enterprise version of xcelsius that appears to be more flexible with data source connections (it wasn’t clear on the website so I am guessing a bit), has anyone used this and what are your thoughts and how is it different.
I think that this is an error in Live Office. If you contact BO support, they should have the fix for this. I have seen this before but do could not find the response. It was a change in some config file if I remember correctly…
I was able to resolve the issue with BO support. There is a parameter buried in a .jar file. The thing that still confuses me is why when I initially populate the live ofiice document (excel file) it pulls all the rows (in this case 2,207). I would have thought if it was a live office issue, it would have truncated during the initial load as well.
anyway here is what to do to fix it.
With regards to the 512 row truncation error you are receiving in LO, there is a setting inside the LiveOffice web services that controls the maximum rows returned as well.
Inside dswsbobje.war, under WEB-INF\lib, there is a dsws-liveoffice-ws.jar.
Inside the .jar, under META-INF\BusinessObjects\DSWS, there is liveoffice_config.properties
The file looks like this:
#value separate char for parameter values
valueSeparateChar=,
#content to show when fails to fetch cell values
errorValueString=#ERROR#
#content to show when the cell value is empty
emptyValueString=
#max number of range rows and range columns to return
maxRowCount=512
maxColumnCount=512
Change maxRowCount to something higher.
Since you are touching the .jar file inside a .war file, you will need to re-deploy the .war file after you made the change.
We have BOXI R2 SP4 and we just got this fixed. For us, this is what we had to do:
We had to change the Lifeoffice_config.properties file inside dsws-liveoffice-ws.jar that’s found in c:\program files\business objects\tomcat\webapps\dswsbobje\WEB-INF\lib.
It didn’t work when doing this in the WAR file, or in the unpacked version of the liveoffice_config.properties file that’s found in c:\Program Files\Business Objects\Tomcat\work\Catalina\localhost\dswsbobje\loader\META-INF\BusinessObjects\DSWS
What we did:
Unpack the JAR file (winRar can do this), change maxRowCount in the file as described above, then re-pack the JAR file. Make sure when adding this file back to the JAR, the same path info as before is stored: META-INF\BusinessObjects\DSWS\liveoffice_config.properties!
I’d do this locally, not on the server. Also make sure to stop Tomcat before trying to put the file back in place, then re-start Tomcat once you’re done. Voila - worked perfectly for us when using Xcelsius 2008 SP1 FP1.
Now if we patch the server we’ll have to go through this again but at least we know how to do it now.
I’ve changed the MaxRowCount setting to 2048 in the LiveOffice_config.properties file within the dsws-liveoffice-provider.jar file (located here: C:\Program Files\Business Objects\Tomcat55\webapps\dswsbobje\WEB-INF\lib).
(Note: I do not have a dsws-liveoffice-ws.jar file)
OK, I’ve done some more digging around and I’ve changed the maxRowCount setting in all 3 of the LiveOffice_config.properties files in the dsws-liveoffice-provider.jar files found in these locations:
I’ve stopped and restarted Tomcat and all BOE servers (several times!) but I’m still only getting 512 rows returned in Xcelsius
Where else should I look for this setting, I’m out of ideas!
The only place you’ll need to change the 512 is in the file at C:\Program Files\Business Objects\Tomcat55\webapps\dswsbobje\WEB-INF\lib
But you have now done it properly so if you apply a path etc. your change will not be overwritten
If it’s not working after doing all this you may have overlooked the most obvious place…inside Xcelsius. Go to File -> Preferences and check your Excel options. You need to change that value as well…
Here is what should be done. I just typed this up so it should be easy to follow for either platform.
Navigate to the DSWS war file location.
(ie: F:\Program Files\Business Objects\deployment\workdir\tomcat55\application)
mkdir dswsbobje
cd dswsbobje
jar -xvf …\dswsbobje.war
Navigate to directory containing dsws-common-provider.jar: cd WEB-INF\lib
mkdir dsws-liveoffice-provider
cd dsws-liveoffice-provider
jar -xvf …\dsws-common-provider.jar
cd META-INF\BusinessObjects\DSWS
Type command. notepad liveoffice_config.properties ( use vim on Linux)
11.Change the following parms and save file: maxRowCount=1024 & maxColumnCount=1024
Change directories back to the root of dsws-liveoffice-provider. cd …
Change directories back to the root of dswsbobje. cd …
jar -uvf …\dswsbobje.war WEB-INF\lib\dsws-liveoffice-provider.jar; adding: WEB-INF/lib/dsws-liveoffice-provider.jar(in =1954779) (out= 1344659)(deflated 31%)
Stop Tomcat
Navigate to \webapps\ (ie: F:\Program Files\Business Objects\Tomcat55\webapps)
Rename dswsbobje folder to OLD_dswsbobje.
Start Tomcat.
After app is redeployed, navigate to the lib folder (ie: F:\Program Files\Business Objects\Tomcat55\webapps\dswsbobje\WEB-INF\lib); dsws-liveoffice-provider.jar file should have a new timestamp relative to the time you ran the jar -uvf command.
You can run jar -xvf dsws-liveoffice-provider.jar to verify the contents and that the new parms are in the newly deployed liveoffice_config.properties
mkdir dsws-liveoffice-provider
cd dsws-liveoffice-provider
jar -xvf …\dsws-liveoffice-provider.jar
cd META-INF\BusinessObjects\DSWS
notepad liveoffice_config.properties
In Xcelsius. Go to File -> Preferences and check your Excel options. Change Max Number of Rows setting.
Navigate to directory containing dsws-liveoffice-provider.jar: cd WEB-INF\lib
jar -xvf …\dsws-liveoffice-provider.jar
jar -uvf …\dsws-liveoffice-provider.jar META-INF\BusinessObjects\DSWS\liveoffice_config.properties META-INF/BusinessObjects/DSWS/liveoffice_config.properties
jar -uvf …\dswsbobje.war WEB-INF\lib\dsws-liveoffice-provider.jar WEB-INF/lib/dsws-liveoffice-provider.jar
(BOB member since 2007-02-06)
(BOB member since 2005-01-28)
(BOB member since 2006-10-09)
Helped a lot :o Never knew that editing .jar file would be so easy 
(BOB member since 2007-10-16)