One of the big changes for existing Jedox web users and consultants is the advent of Data Validation. This opens up a lot of possibilities for development of web entry templates and forms. The validation is essentially a format (like Excel), so therefore you can easily copy it across to other cells, or use in a Dynarange report. The list validation is similar to data validation in excel but with an important tweak: you can add additional columns to the validation array which allows you to display one value and enter another. I will run through a couple of example below:
Validation – the Basics
Data validation lives in the Data menu :
When you click on it, you will see the familiar options you get in Excel:
Under Settings is is where the real action occurs. You can use a spreadsheet based formula to drive your list, a subset or even an ODBC query. You can set user messages on input (on the Input message tab) and also the type of error message they will receive (Error Alert tab).
Numeric validation is really cool. For example, you might want to construct a template that allows users to enter any number that they want, but at the same time provide some pre-built options for them. I have put together a basic example for you here using a formula input. You can see that the display values are different from the values being entered into the cube.
The table that drives the validation is on the right. I have put here a bunch of possible splashing options I may want my user to use. Also note, that the last option “900000 like 2014” is dynamic based on variables column. The text as well as the values sent to the cube changes dynamically depending on what is entered. The other awesome thing is the possibility to group data validation options, by assigning a level (see the 3rd column in the data validation array). You can see that “2014 Actuals” is a grouping which contains other data validation options. Download the example here.
There are many times when you require some pre-formatted text to be entered by the user. Left to their own devices, users invariably give you incorrect spelling, etc in the string cell. This is especially problematic when you have calculations being driven off this entry. Text validation solves this.
What is especially helpful here is the capability to select a description from the drop list, but actually enter the corresponding code into the cube.
Check out the text example here.
Pingback: Jedox 5.1 – A Consultants Perspective : Part 4 – Wrap up | the naked leaf
to copy the data validation “format” cross cells is unfortunately only possible if you do it manually. Do you know of a way to assign validation by macro code?
Hi Oliver, not at this point in time. I confirmed with Jedox it is on the future roadmap.