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 second sheet. In simple words, the function is looking for a product from the first sheet and then returning the data for 2022 in this case. NOTE: SUMIF formula doesn’t get exported when trying to export in EXCEL. When the formula is created it is super necessary to select the whole function and at the same time click. CTRL + SHIFT + ENTER. After that little array, brackets should appear around. More about these functions can be found here.
Now when exported, the second sheet will read dynamically what is happening in the first one. if there are some #value errors appearing, check if your name ranges are correctly constructed.
This little project can be downloaded here. Please note that you need the Biker Best database installed.