Reports and Reporting Components Archives - Page 3 of 4

How to sort attributes?

February 21, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

In this article, we will show you how to sort attributes in the way you like. When building dimensions in Jedox it could happen that some of them require a huge number of them. The modeler screen in normal zooming conditions can fit around 10 attributes. For all the rest we will need to use a scroller. If you are someone who works with Jedox for a few years you will notice that adding attributes on the end of the page when scrolling could be annoying. Especially because after adding any attribute manually, the page refresh and bring you back to starting position. We can resolve it by pushing the last attribute at the beginning. There are two ways to do it: Create attribute directly on desired position Moving attribute around in #_MONTH_ cube For the second case, arrows, in the end, will be blocked, so the best way is to drag and drop elements around. Maybe at first sight useless tip, sorting manually really...

Building Dynamic Calendars in Jedox

January 5, 2022November 12, 2023 by Jedox 4 BeginnersNo Comments

In this guide, we'll walk you through the process of crafting a calendar widget within the Jedox platform. This endeavor requires the presence of a Day dimension enriched with an additional attribute named 'NextDay.' The overarching goal is to fashion a dynamic calendar capable of accommodating additional dimensions, effectively transforming it into a versatile data planning hub. This widget can prove invaluable for various use cases such as task planning, reminders, and monitoring planning cycles availability, all achieved seamlessly within a spreadsheet environment without the need for any coding expertise. Basic familiarity with Excel is all that is required. Creating the Time Dimension The foundation of our calendar widget is laid with the creation of a Time dimension. Utilizing a straightforward dimension creation approach, we will employ the Dimension Template 'Day.' This template will undergo augmentation with an extra attribute known as 'NextDay,' dynamically generated through Extract, Transform, Load (ETL) processes. The 'NextDay' attribute serves as a representation of the following day...

Add element to the Jedox database via macro

January 3, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

In this post, we are going to show you how to use multiple PALO functions in the macro to successfully add element in the dimension. For this purpose we are going to use the Biker database and elements will be added to the Product dimension. Have in mind that example is for the users who use the cloud instance of jedox. First we need to establish connection with the OLAP. $host = $_JEDOX['OLAP_HOST'];$port = $_JEDOX['OLAP_PORT'];$conn = palo_init($host, $port, $_JEDOX['OLAP_SESSION_ID']); Then we need to formulate Palo.EADD function. this function consists of the following parameters. PALO.EADD(Server/Database, Dimension, Type, Element, Parent Element, Weight, Clear, Error suppr.) Possible values for Clear are 0 (or FALSE), 1 (or TRUE) and 2 0: Removes no elements of the dimension before importing 1: Removes all elements of the existing dimension before importing 2: Removes all existing elements of the C-dimension before importing. Basic elements are not deleted. More about this and other functions can be found here. When these are combined code should look something like this: function...

Excel download using macro

January 2, 2022June 28, 2023 by Jedox 4 Beginners2 Comments

Here is one quick way to download a file as an Excel. In normal conditions, Jedox offers two possibilities to Export to Excel: XLSX Snapshot XLSX OLAP Snapshot More about differences can be found here. Sometimes, we want to limit end users from seeing these buttons. That can be done in this way [see bellow] by clicking on report properties and unchecking Interface checkboxes. Now, when the report does not contain anything related to Jedox bars we can install the macro for exporting. The code behind the macro is very simple: return array ( array('actn', "exportToXLSX",2), ); The final output looks like this: The whole project can be downloaded from here. ...

Mapping Matrix

January 2, 2022November 12, 2023 by Jedox 4 BeginnersNo Comments

When working with dimensions like products there must be a use-case when mapping matrix is necessary. This little mapping matrix is used when there are certain years where some products are plannable and some others not. For this example, we will use the Mapping_Matrix database and cube Mapping Products. This cube will contain dimensions like Years, Products, and Measures [not necesarry]. In some terminologies "Control cube " is also used. As we could see from the image above in hidden columns we will store PALO.DATA formula. Behind the Check and Uncheck icons we could see the following code: =HYPERLINK("SELF",IF(I8>0,iCheck,iUncheck),"",IF(I8=0,"!1","!0"),I8) This hyperlink function just make difference if the value is 0 or higher. In case it's zero then every click on the iUncheck image will splash ! 1 on PALO.DATA function. Otherwise !0 would be splashed. More on splashing commands can be found here. The whole project can be downloaded from here. ...

Best way to copy data without using the ETL

January 2, 2022June 28, 2023 by Jedox 4 BeginnersNo Comments

During the planning process, there will be activities that require to copy data from the previous year or another version. There are multiple ways to achieve it via ETL but in this article, we are going to show you how to do it via hyperlink. Copying using native copy/like function If you ever attended any Jedox training you have probably learned splashing commands like Copy and Like. Both of them can be found on the knowledge base and this is good pre-knowledge for the following segment. Copying using hyperlink function Copy and Like function could be converted into the hyperlink and do the same job. In the cell where you would like to create a hyperlink copy the following code. =HYPERLINK("SELF","Copy Data","Copy Data","Copy "&F$5,$G6) This code is another way to represent "Copy 2017" and G6 is a cell to where we want data to be applied. Copying using hyperllink in the button A similar thing is done in the next example but only instead of the hyperlink, we are...

How to create a colour picker in Jedox?

January 1, 2022November 12, 2023 by Jedox 4 BeginnersNo Comments

Jedox has a wide range of possibilities when it comes to reporting since it is working based on Excel technology. Having that in mind it might happen that sometimes you will need to add a colour picker to your dashboard. Here is an example of how it could be done. On the spreadsheet, you can put the colours which could be chosen Set up a macro which will be trigged when colour is clicked ="<a href=""#"" title='"&U5&"' style=""height:100%;width:100%;display:inline-block;color:"&U5&";background-color:"&U5&";"" onclick=""Jedox.wss.macro.exec([true,['Module1.saveColorToVariable','"&V5&"']])""> </a>" Create the macro in the Macro Editor function saveColorToVariable ( $color ) { define_variable ( 'varColor' , $color ); //return do_close(); } That is it!! In case you are not a person who has much sense for colours, there are amazing patterns of colours on this website. An example of the project can be found here. ...

How to return IP address from the Spreadsheet in Jedox

December 31, 2021June 28, 2023 by Jedox 4 BeginnersNo Comments

Here is the sample of the macro PHP code which can help you to return the IP address of the current server from the spreadsheet. It could be useful when reports contain download functionality which is the source on the server name. So in order not to have a hardcoded server DNS name you could use this funcionality. -------- function temp(){ $localIP = getHostByName(getHostName()); return __msgbox("myComplexVariable=".print_r($localIP , TRUE ), 'Test', 'Info'); } -------- ...

How to create paging in Jedox?

December 31, 2021November 12, 2023 by Jedox 4 BeginnersNo Comments

When crafting reports in Jedox, it's not uncommon to encounter scenarios where the large volume of rows becomes unwieldy. In such cases, the implementation of paging proves invaluable. Paging not only ensures a clear presentation of the table but also serves to optimize server resources during report access. In the example provided below, we illustrate a two-step approach to introduce a paging solution, leveraging the Jedox BikerBest database. Step 1: Create the Table of Elements The table, presented via Dyna-range, reveals an extensive list of elements that stretches to the bottom, posing navigation challenges. To address this, a subset of Dyna-range is strategically employed to showcase all base elements within the 'Product' dimension. Step 2: Limit the number of rows based on the variables and introduce paging It's common practice to incorporate external variables into the subset. StartPosition - > from which element we want to start [Inital 0]. NumberOfElements -> how many elements we want to see in the table [editable but set on 30 for...

Create slideshow widget in Jedox

December 31, 2021November 12, 2023 by Jedox 4 BeginnersNo Comments

Jedox provides many possibilities when creating a report or a dashboard. Apart from native components like buttons, combo, and checkboxes, lists and dynaranges Jedox allow developers to code their own widgets. One of them could be the slideshow widget. This widget can make your dashboard more interactive and interesting. Widgets in Jedox are coded via HTML and this is how you can create one. For our example, we will use Biker's best database whereas for dimension Product we will add attribute images. Value of attribute is the location of the image behind [ Example /pr/jedox/images/Bikes/Mountain Bikes.png ] Idea is to create an array of elements that could be used in the widget, which will contain: Product Name Product Image Data of selected Measure Measure That we will do with Dynarange and it should look something like this: Since coding behind the widget is a bit complex there will be a project available for download here. To learn more about widgets and what possibilities you can have with them check the...