Aug 312014
 

Hi,

In this post I would like to share my view on few challenges I faced  by embarking on a large project powered by Jedox, which incorporated writing of several thousand lines of code (macro):

Challenges:

1. Editor Simplicity: Jedox is more geared towards business users providing the functionality they require as a BI product, than providing functionality for the developers to write better code. No problems, I guess they do best at what they deliver, and for the solutions area I have made out a way where I can leverage the same old friend’s of developer tools such as Eclipse for PHP (Macro) development.

2.Code Re-use: This was a major roadblock for me until I figured out a way to achieve the building of complete application styled solution for the projects delivered by Jedox. As when you write macro, it is encapsulated in its own shell, so if you want to achieve the same functionality for multiple spreadsheets (which is written once and used multiple times across spreadsheets) in the native installation of Jedox its hard to achieve, unless you expose out a single piece of functionality making tweaks to the “macro_engine_config.xml” file as mentioned in their admin manual.

Solution:

The magic bullet to resolve all and more issue as mentioned above and you may face, following illustrates a way , which helped me big time to write better code is as follows:

1. Create a folder “shared_macro” or any name you like, which will be the entry point for your macro based application, nested sub folders and php files representing macros (comprising your application) all can be created and referenced relatively beneath this folder. This is the folder which will be configured with Jedox to represent the entry point for your custom code.

C:\Program Files (x86)\Jedox\Jedox Suite\httpd\app\shared_macro

CropperCapture[11]

2. Edit the “macro_engine_config.xml” for the following enteries

C:\Program Files (x86)\Jedox\Jedox Suite\core\macro_engine_config.xml

CropperCapture[12]

Add the highlighted enteries into the above configration file and re-start the service : “JedoxSuiteCoreService

to reload the configuration.

That’s it , now Jedox can peek into this folder and utilize any php based files as macros, how to achive it is as follows:

- Create a file inside the folder created in the step 1 above as “dummy.php”, with the code content as follows:

CropperCapture[13]

- Reference the file from any spreadsheet macro and call upon the file contained function as follows:

CropperCapture[14]

And finally when you hit the button, you get the following output as desired:

CropperCapture[15]

Now, we have configured our Jedox to peek and utilize files from out custom folder, how now we edit amend and build a comprehensive complete PHP project styled application via eclipse:

That’s easy enough to achieve as well :

First download the eclipse package and install the necessary software as illustrated,

CropperCapture[17]

CropperCapture[16]

Then create a PHP project and add the above create “shared_macro” directory to the newly created project as follows:

CropperCapture[18]

(Ignore the name, it can be any thing, as I already have the project previously created for illustration i had to suffix the name with “.1″)

Once the project is successfully created, then attached the “shared_macro” folder as follows:

CropperCapture[19]

And that’s it, your project is now fully configured in eclipse to rock-n-roll out some comprehensive macros with all the benefits of code-reuse, intensense and more benefits available to the developers for their development purposes.

CropperCapture[22]

Hope the above steps, helps you go overdrive with Jedox…

Enjoy ..!!!

Aug 292014
 

Hi,

In this post I plan to share a trick in Jedox spreadsheet to lay out a dynamic formula driven calender based on your input month and year value the month laid out calender changes itself according to the week days monthly template is laid out.

The calender is driven via another sheet in the wss, where the formulas are laid out for the monthly template to look upon according to the week day index’s.

The inspiration for me to build this calender is partially a project requirement along with support from the following blog post laying out the same for excel.

http://chandoo.org/wp/2011/12/27/download-free-2012-calendar/

http://www.excelhero.com/blog/2010/01/live-calendar-musings.html

This is also a clear representation of the power of Jedox in  terms of bringing excel to web, in its complete essence.

CropperCapture[10]

Download the attached wss file for yourself’s to review and feel free to ask me any relevant questions relating to the same.

Enjoy !!!

Download:

http://www.amolpandey.com/home/downloads/blog19_download.zip

Aug 282014
 

Ooooooooh…

Well that’s a lot of O’s I am putting in this blog after a long time. Well the topic is fun, and getting it straight in this topic I am going to lay out a plan to produce OData feed using the PHP, Apache and MSSQL combination.

What is Odata ??

Simple, google it… There’s so much written about it on the web, I just keep this post focused on what I want to illustrate. It’s a web service protocol, to design and deliver data as a pre-defined XML format and design the web service on a layout laid by the foundation rules of OData (REST) combination

Wiki:Open Data Protocol
OData and REST
www.odata.org

Why you guess is this exciting??

Well the reason being there is a new library in town, called as “OData Producer Library for PHP”, this was started off in CodePlex which eventually moved to the GitHub as the development grew!!

Why would I go for it?? Why not ASP.NET MVC WebAPI (or) WCF

Well what if you environment is not windows…, what if you broke your iis server …, what if you are open source and member of “The Cheapskates Club”…

Getting serious, this library is one of the attempts of Microsoft to promote their odata format to a wider audience where the open souce stack is able to leverage out the framework for their needs using the framework…

Why a blog on configuration, they must have a well-documented API??

Good luck, it had to scratch off enough hair of mine to get it working, maybe I am more of .NET arena guy. But I found it little complex to set it up on my laptop, (just becose it wasn’t well documented) …

Required:

To get started you need the following:

(Note: A quick note, don’t be tempted to download any of the following x64 options though you run a super computer with x64 architecture , believe me you would realize a long way down when it strikes)

1. Apache HTTPD Server:
https://www.apachelounge.com/download/
httpd-2.4.10-win32-VC11.zip

2. PHP Framework (5.3.29):
http://windows.php.net/downloads/releases/php-5.3.29-Win32-VC9-x86.zip

3. MS Sql Drivers for PHP 3.0:
http://www.microsoft.com/en-au/download/details.aspx?id=20098

4. OData PHP Producer Library (GitHub):
https://github.com/MSOpenTech/odataphpprod

5. MSSQL Northwind Database Sample:
http://www.microsoft.com/en-us/download/details.aspx?id=23654

Steps:

1. Unzip all the above packages and place them as follows:

Apache: C:\Apache24
PHP: C:\php
MS SQL PHP Drivers: Copy the following files “php_sqlsrv_53_ts.dll” and “php_pdo_sqlsrv_53_ts.dll” and dump them into “C:\php\ext”
OData PHP Producer: In the folder “C:\Apache24\htdocs”, create a folder named as “odata” and dump all the content of the downloaded library

It should look some thing like this:

CropperCapture[1]

Once all the file operations are complete move on to next step (2) configuration.

2. Configurations:

Apache: ”C:\Apache24\conf\httpd.conf”

Also create a new file in the location ”C:\Apache24\htdocs\.htaccess” , which windows dont allow to create by default by right click. You would have to “Save As..” via notepad to create the same.

PHP: PHP doesnt intially have a “PHP.INI” , so take a copy of the “PHP.INI-development” and rename the file as “PHP.INI”

Also register the “C:\php” path in your environment vairable “PATH” for it to be available across system.

OData Producer:
“C:\Apache24\htdocs\odata\services\service.config.xml”
“C:\Apache24\htdocs\odata\services\IDataServiceQueryProvider Implementation\NorthWind\NorthWindQueryProvider.php”

For the above all mentioned configuration files, just do a code compare with the content available with this post for download, to transfer the necessary configuration as desired.

3. Setup the database:

Last step above all the discussed steps is to configure the service, have the database in place for the web service to query the server and produce the data

And finally

4. Run the service:

Open the command prompt (as Admin) and navigate to the “C:\Apache24\bin” and run the following command:

“httpd.exe”

And there you go, your server mu be up and running on the following URL, to display the following output in choice of your browser.

Note: The port and the IP address can vary depending on your version of configurations
http://127.0.0.1:88/NorthWind.svc
http://127.0.0.1:88/NorthWind.svc/$metadata

CropperCapture[2]

And rest from here the documentation provided by the PHP OData Producer library can guide you well to structure the service according to your requirements.

I wonder why they couldn’t provide more clarity on the getting started section of the library documentation ??

I hope it helps !!!

Download:

http://www.amolpandey.com/home/downloads/blog18_download.zip

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