Excel functions in Jedox export  – Part 1

Excel functions in Jedox export – Part 1

While doing different projects we noticed that one of the repeating requirements is to export the Jedox report in Excel and then perform some additional analysis. As you know from before there are a few ways to export to excel. xlsx - standard export xlsx_snapshot - standard snapshot export xlsx_olap_snapshot - OLAP snapshot export In the case of xlsx export, data will be downloaded as a constant value. In this article, we will show you how to use the famous Excel function SUM incorporated in dynaranges. How to create a button for exporting in Excel? SUM in Jedox dynarange Imagine that we have two sheets. The first one contains data in Dynarage which could be editable when exported to excel. The second sheet supposes to read all changes from the first one and sum it up accordingly. In order to do that we will need to set up some steps: Create name range for Products and for considered data. Remember: it is important to select one row before and after dynarange. Create SUM function in the...
Read More
How to sort attributes?

How to sort attributes?

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...
Read More
Create a calendar in Jedox

Create a calendar in Jedox

In this article, we will show you how to create a calendar widget inside Jedox. For this task, we will need a Day dimension filled with additional attributes NextDay. Idea is to create a calendar that can be extended with additional dimensions and serve as a data planning area. Possible use cases could be task planning, reminders, planning cycles availability, etc. Everything is built on a spreadsheet without any coding knowledge. We could say that Excel familiarity is sufficient. Creating Time dimension For this task, we will use simple dimension creating where Dimension Template will be Day. That dimension we will extend with an additional attribute called NextDay which is generated by ETL. NextDay will represent Date +1. The transformation function used is Previous. Example: [2022-05-02-----> 2022-05-03] Calendar Creation Once we have all the necessary elements we can start building up the template in a spreadsheet. The template will be mixed with excel formulas and conditional formattings. Selected month dates cells will be colored in white and...
Read More
Add element to the Jedox database via macro

Add element to the Jedox database via macro

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 importing1: Removes all elements of the existing dimension before importing2: 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...
Read More
Excel download using macro

Excel download using macro

Here is one quick way to download a file as an Excel. In normal conditions, Jedox offers two possibilities to Export to Excel: XLSX SnapshotXLSX 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. ...
Read More
Dynamic denormalization

Dynamic denormalization

One of the commonly used transformations in ETL is Table denormalization. Denormalization is the reverse of normalization. Row-based data with key-value pairs is transposed into column-based data. This allows an OLAP model with one measure in each row (with the measure name as a key) to be arranged into a relational model in which the measures are held in various columns. Jedox knowledge base In typical table denormalization, Measure fields are populated by the rows which we want to see in columns. Usually, it's enough to do it one by one but sometimes it happens that there are 30 or 50 elements that needed to be transposed into the columns. For that reason, we will use a little trick that can be found in Advanced settings. In case you are not familiar with this type of transformation here is the link that can help. Our file contains 3 columns and one of them contains data that should be denormalized. Instead of using static measures, we will introduce...
Read More
Mapping Matrix

Mapping Matrix

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. ...
Read More
Best way to copy data without using the ETL

Best way to copy data without using the ETL

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...
Read More
Setting up default page for groups – 3 ways

Setting up default page for groups – 3 ways

Setting the default page in Jedox for a specific group is not a big issue if your application contains a smaller number of groups. When a number of groups enlarge, we should look for a more alternative solution instead of checking the boxes one by one. This represents 1st way. Information of the default homepage is stored in the dimension GROUP_PROPERTIES, more specifically in the prefs element. More about the system database could be found here. Once we check this box from picture one, XML code is immediately generated and information on the default page is written. This code could be now pasted in every other cell. In that way, we could significantly decrease the time of checking and as well automate a bit process. This represents 2nd way. 3rd way would be to use this code and copy it via ETL. Import to have in mind is that once a new start page is chosen code needs to be arranged. Also, if the Start...
Read More
How to create a color picker in Jedox?

How to create a color picker in Jedox?

Jedox have wind range of possibilities when it comes to reporting since it is working based on Excel technology. Having that in mind it might happend that sometimes you will need to add color picker into your dashboard. Here is the example how it could be done. On the spreadsheet, you can put the colors which could be chosen Set up a macro which will be trigged when color 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 colors there are amazing patterns of colors on this website. An example of the project can be found here. ...
Read More