Tips and Tricks Archives - Page 6 of 7

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...

Setting up default page for groups – 3 ways

January 2, 2022January 10, 2022 by Jedox 4 BeginnersNo Comments

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...

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...

Exchange rate Cubes

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

In this post, we will show you examples of an exchange rate cube, a planning cube, and how to set up the rules between them. This topic is very much present in a lot of projects and therefore we will explain the simple logic behind it. Exchange rate cube For every project exchange rate functionality is probably one of the must-have cubes. The logic behind is simple. We need a place to store rates and the place where we calculate them with the planned data. For keeping rates somewhere we need a cube with at least 2 dimensions: Month or Day Currency It is important to dedicate one reporting currency. In our case the central currency is EUR. All the other currency data will be converted to EUR. Another cube is the place where we plan the data. This cube will contain more dimensions like: Month Version Country Currency Measure In the end connection between these two cubes will be done by a rule: In this rule, we are dividing data that is...

Version blending

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

Version blending is functionality that brings actual figures to be visible in the forecast versions. Each forecast element in the Version dimension can be configured in the Actual Months attribute to contain actual figures for the first given number of months, starting with January. Here we could see that versions have 3,6,9 months of actual data for corresponding forecast versions. This can be achieved if in the version dimension we add the attribute Actual Month. [is already there if you use Version template dimension when creating]. After that rule needs to be set on the cube level. Here is a good description of this feature in Jedox Knowledge Base. Also, you can download the mini-project of version blending from here....

Audit in Jedox

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

When the cycle of planning starts it is important to track all inputs. One reason is for monitoring purposes [Who did when what and why] and the second is to keep the log of how data looked before that step. For that purpose, we will use Audit functionality. This functionality can be found in the Administration tab: By default, an audit is not started until the user enables it in Settings Panel. Auditing can be done for 1,7,30 or 365 days. Have in mind that a larger auditing period is selected, the file on your server will take more memory. Once you select the cube which should be monitored, add some value inside. In our case, we will add 400 Units for Handlebars inside Orders Cube which we previously enabled in Audit. After hitting that splash functionality new value is visible in the Audit section. Now, you can check why the values are appearing in the report since everybody claims it was not their...

Creating sudoku in Jedox

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

Primarily designed as a planning tool, Jedox offers a wide range of possibilities when it comes to development. If we forget for a second HR, cost center, or some financial consolidation model and let our creative mind flow, we could come to this kind of application. Sudoku is a game that is designed in matrix form where the goal is to fill the cells with numbers from 1 to 9 that are not repeating on any axes. For our little project, we will design a database that will keep scores of users and set up the Sudoku. Dimensions inside will be: User (Copied from System database) Day Version of Sudoku [Easy,Medium,Advanced] Measure [Duration] For the Sudoku setup itself, we would have two dimensions Xaxe and Uaxe. Series of conditional formatting values will be responsible for checking if the numbers are in the correct order. Here you can find the whole project. ...