Mar 232014
 

An additional trick for the users of Jedox web reports, recently when presented with a challenge to pop out a confirmation message box for the macro operation confirmed by users, the following solution came to the rescue.

blog17_pic1

In the above solution using widget, we are displaying a button with the same style as of the normal Jedox button which in turn calls the JavaScript confirmation dialog box which then call’s up the Jedox macro upon user choice of operation to be performed.

Hope it helps!!!

Downloads:
Download

Mar 232014
 

So here’s another trick out of the bag for dynamic hide and unhide of the drop downs on the web report.

At times due to permission issues we have to hide unhide certain controls on the report based on the user privileges granted. So here a little trick for the same which can be tweaked to another controls visibility.

blog16_pic1

Note: The critical in the above is to accurately catch the control in the document space via JavaScript/jQuery currently which I am doing via values which is displayed in the drop down. But for other controls it would require other technique which depends on the situation. The id/name of the control cant be used as they are being dynamically generated by Jedox Web portal.

Hope it helps!!!

Download:
Download

Mar 232014
 

Recently I faced a challenge where user requested to have a slider mechanism to change the report parameter/variable values, at the times when it’s not provided in the Jedox portal by default.

??So, how do I put a jQuery control changing values of report/worksheet cell??

Widgets came to the rescue and rather me playing with more words and lengthening my blog length is rather hit with the solution for you…

1. Create a new worksheet, and assign a cell location whose value would be changed by the slider control. In my case it’s ‘C3’

blog15_pic1
2. Add a widget control on the sheet, with the following settings

blog15_pic2

3. Add the following code to the widget:

<html>
<head>
 <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
</head>
<body>
<p>
  <label for="amount">Selected Value($50 increments):</label>
  <input type="text" id="amount" style="border:0; color:#f6931f; font-weight:bold;">
</p>
  <div id="slider" style="width:350px;margin-left:50px;"></div>
</body>
  <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
  <script src="http://code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
	<script>
	  //function __exec(params){
	  $( "#slider" ).slider({
		min: 0,
		max: 500,
		step: 50,
		slide: function( event, ui ) {
		  $( "#amount" ).val( "$" + ui.value );
		  setValue(ui.value);
		}
	  });
	  $( "#amount" ).val( "$" + $( "#slider" ).slider( "value" ) );
	  __set(0);
	  //}
	  function setValue(sld_value){
	  	__set(sld_value);
	  }
  </script>
</html>

4. Voila… There you go, now if all goes well you should have a fully working slider control provided by the jQuery UI control changing the values on your worksheet which can source as your report parameter

blog15_pic3

Hope it helps you in your future projects!!!

Note: In the Jedox widgets using the jQuery/JavaScript standard ready mechanism to trigger widget on load code is not a good idea, as widgets are loaded by the portal and when they are loaded, the on ready event has already been kicked off as widget is only a contained in the bigger page. Hence to keep it simple jQuery on load/ready function doesn’t trigger of behave as expected, hence leaving the code out in open is the only way I was able to make it work as desired.

Downloads:
Download

Feb 222014
 

Recently moving to Australia, Sydney and landing up in my new role, I got introduced with yet another new technology spanning there base’s in the arena of BI tool world primarily specializing in the area of companies financial planning and similar…

And it gives me a great pleasure to introduce you to “Mr. JEDOX”… tadaaaa (global introduction music)

Well after a film style introduction of JEDOX product suite, I rather get back to the IT side introduction of the same rather lengthening my post with my imaginations.

Jedox (About Us): “Business Intelligence and Performance Management (PM) thrive on speed and agility. Our principle of “self-service Bl” ensures both—and improves efficiency, while contributing to the users’ corporate bottom line. Jedox customers can quickly recognize and take advantage of any optimization potential. This lets them respond to changes in the market more quickly, giving them an edge over their competition. Kristian Raue, Jedox Founder and CEO

This is what the company home page says in summary but to my technical head the above even don’t represent the “Interface level” of the product qualities… hence to add more to its product actual features I attempted to write this post:

Features:

The feature list of Jedox is best listed in the comparison page of Palo/Jedox:

Palo Vs Jedox Feature Comparison

History:

Now to give a little history to my best understanding of the story, Palo was an open source community version which was introduced in the Pre 2002 era which was the core engine MOLAP, which is currently still (though with added features) is the base server of Jedox storing the data.

Jedox is feature rich BI platform which is built upon PALO, enriching and expanding its power via multiple mediums of data dissemination and manipulation. Jedox has some very interesting and handy features which in my couple of future post I will touch upon.

Architecture:

Jedox is composed of the following components:
1. Jedox Web
2. Jedox Excel
3. Jedox Mobile
4. Jedox OLAP Engine (CUBE)
5. Jedox ETL (Comprising Scheduler)

blog14_pic1

1. Jedox Web: This feature of the Jedox I feel is the very impressive part of the Jedox where they have provided a great platform in the form of an “Excel”, YES you heard me correct Excel. I myself was quite impressed to have something as excel on the web and the guys have really done a great work of replication excel closely as possible on the web via PHP (that’s the language Jedox Web is built upon)

blog14_pic2

This portal based excel has features as formulas, custom macro, sheet controls and so much more. Stay tuned for my upcoming post on the same. There’s lot to illustrate…

And this portal is also composed of report manager, so in the file manager where all the excels workbook we create can be posted on to report manager which can be user area to play with the report (something similar to SSRS but with little more power of user entering data in the reports)

blog14_pic3

This portal is also composed of the user and system administration of the platform, along with cube browser, ETL job manager (termed as Task manager) and ETL portal.

User and System Administration portal

blog14_pic4

Cube Browser

blog14_pic5

Task Manager

blog14_pic6

ETL Portal

blog14_pic7

2. Jedox Excel: This is another great way to interact with the cube data and I guess most of the user would use this tool more integrated with excel which is the most loved and the best friends of the data planners in any field of work.

Jedox is loaded up in excel as a COM based add in (I guess they should have used VSTO to make it so pretty, there goes my Sherlock home thoughts along the way of introduction). So to put words to picture here is quick snapshot of how excel add in shoes up when Jedox is installed for the client.

blog14_pic8

This add in again has many features which gives a user/developer of the cube a great level of flexibility to play and design cube to the requirements.

3. Jedox Mobile: Again this comes as a two part setup, where in first a server side has a specific setup as a mobile server (“Jedox Mobile Server 2.0”) and on the relevant applications like apple/android an application for the Jedox has to be downloaded and installed by the clients for usage of the application.

Some of its snapshots are as follows:

blog14_pic9

4. Jedox Cube and ETL: As by the name suggest I can’t catch any of its screen shots as if I can phrase it, they are like the souls of the Jedox (as of humans… which has no image except the window services in running state of the corresponding process. Now that was good philosophical analogy)

So yes, Cube and the ETL are also the core parts of the suite which allows to store and manage the data, user management, loading and transformation of data from multiple systems and more.

And not to mention Jedox operates on an in-memory data processing base, so as expected it should be lightning fast which I will verify and get back in some of my upcoming post, and it also features a GPU computation engine also in the newer versions.

So in summary from the above I am quite excited about this new shiny product in my hand and I aim to bring more and more as I catch upon the same for your interest and problems to be resolved at the glance of my post.

Feb 202014
 

In this post I am going to touch base on a recent work which I embarked upon where I was asked to highlight postal code areas on a google map depicting the performance of an individual sales person within that area via marker embed chart.

Similar as this:

blog13_pic1

In order to address the task I opted for google maps API for the reasons of its popularity and flexibility. The solution I worked was composed of the following components:

1. Google Geocoding API: This API usage was primarily to place the marker on the map requiring the latitude and longitude coordinate values mapping to the postal code in Australia. This API is responsible for translating the postal code value to the required coordinates for the map marker

Google Geocoding API

2. Google Charts API: This API is used to draw/plot the sales person performance data in the google map marker information window. Using this API in the sample I have rendered a column chart but, it has to offer a wide variety of chart types with great detailing tweaks and settings offered in the API

Google Charts API

3. Google Maps (Marker/Info window): This API sections have been used to place the marker and the associated Info window (containing the embedded chart)

Google Map Markers

Google Map Info windows

4. Postal code area Highlight: Now, here’s the tricky bit… Which took me a while to figure out myself.
For the postal code area to be highlight now a days google by itself offers a highlight mechanism as illustrated below:

blog13_pic2

In the above check the red dotted line mapping the postal code boundary

Google Map Default Postal Code Boundary (AU, NSW 2008)

Now first line of thoughts made me to leverage what’s available with the google by default and I tried using the same in conjunction with rest of my task to be accomplished involving setting the marker with an info window and embed chart inside the same. But scanning the entire google maps API I was unable to figure out a way as to where I can combine all the pieces of the puzzle together.

But then again googling examples to find a methodology for the same I stumbled upon the following link which partially answered my problem (and being very close to my requirements):

blog13_pic3

www.aus-emaps.com

Now only thing was to marry all together….

So investigating upon and with an aim to not to elongate the mystery of the solution, I found my solution with the use of KML files used in conjunction with google maps to custom draw shapes on the google maps driven by a custom directive files for the shapes termed as KML (Keyhole Markup Language)

Keyhole Markup Language (KML Wiki)

Google Maps KML Layer API

So now I was able to draw shapes on google maps, but still I wasn’t having any near plans to write or draw this KML files by myself in this life for Australian continent, landing me up to the page for Australian Bureau of Statistics

Postal Areas (POA) 2006 Digital Boundaries in ESRI Shapefile Format

Now next challenge which I faced was, how to convert the SHP file which I have obtained from the site to the required KML file for the google map.

The problem was answered by the following open source software which was very helpful to convert the shape file to KML, with also depicting how my shape file would lay out on the map.

QGIS, A Free and Open Source Geographic Information System

Now when I opened my newly generated KML file I faced with my next task, that it comprised the bulk of postcode data all in one file. This file needed to be split apart into to post code areas so that on demand the respective KML to the post code area can be picked up and rendered on the map.

That’s where my skills of Excel/VBA came into play to split the bulk KML file into their respective postal code files. And hence finally I was able to obtain what I wanted to bring all pieces of the puzzle into one place and render my final solution.

blog13_pic4

With just a last bit of a knack that my KML files didn’t work, I was able to see my map, with the respective marker and info window with the required chart inside it, but no postal area was highlighted.

Eventually after little hair pulling on my end I was able to resolve the issue by noticing that the KML files which was being passed to google for rendering, required some kind of parsing for which google can’t pick the files from the local host URL, it needs a publicly hosted URL to pick the relevant KML file and parse render it on the map, hence to overcome the last glitch the following JavaScript library was required:

KML processor for the Google Maps JavaScript API V3

And after usage of the above library… Voila… I ended up with my required result as desired

blog13_pic1

Here is the sample hyperlink template for you to check how the URL renders out the content for your purposes.

http://127.0.0.1:81/pr/custom/geomapping/index.html?postcode={postal code(AU)}&salesman={string}

Note: The above hyperlink is only a template which works when you download my work and run it locally on your machine

Attached along with this post is my solution and all the necessary files, and I hope it helps you in your future projects.

Downloads:
Download