In this post David Lai, a BI Consultant and the author of davidlai101.com explains how to generate Web Services using Flynet Web Service Generator and use the Web Services to build live Xcelsius Dashboards.
For those who are using Xcelsius but without Business Objects Enterprise, you will either have to grab data from the database using XML mapping or Webservices. XML mapping not very flexible in terms of generating complicated queries so most people will go to the webservice route. The only draw back with generating your own webservices is the time it takes to code or cost to hire a developer to build the web services.
One way to generate webservices quickly is to use the Flynet Web Service Generator.
Its quite easy to install and setup as long as you follow the instructions which are included with the installation. Flynet works simplest with IIS but for those using apache like me, you will need to follow the Apache Web Server instructions which is included in the Flynet help file.
Generating a Web Service
First you will want to generate your webservice and you can follow the interactive walk-through found here
The tutorial shows how to create a web service from simple queries, HOWEVER if you want to create queries that involve Aggregates, Case Statements, and Procedures, the code will be a little bit more complicated. The manual and tutorial doesn’t really teach you how to do these things so the first thing you might think is that it is not possible and just stop using it.
Here is how you create queries with Aggregates, Case Statements, and Procedures
Aggregate
If you want to perform a count query, you will need to enclose the row counted and use the as ~row~ otherwise flynet will throw and error
Case Statements
Case Statements are similar to the Count statements where the case section is enclosed in the brackets.
Procedures
Procedures need to have the text EXEC at the start of the Query SQL statement followed by the name of the Stored procedure.
Please note that you must include the — for the output parameters
Connect the webservice to the Xcelsius Dashboard
Finally you can connect the webservice to your Xcelsius Dashboard but before you do so you should be careful of values that get returned that are of type DATE. Apparently a T gets inserted into the date row and Excel will think that the field is a text field, thus any date functions on the date cells will screw up.
To fix this, in Excel just use the DATEVALUE(), LEFT() and RIGHT() functions to strip out the T into the new cells
To try out Flynet Viewer go to http://www.flynet.co.uk
Pingback: QAAWS vs. Flynet Web Service Generator | Practical Business Intelligence()