maintain reference code from database

Hello,

In our measure tables we have a lot of fields (100's) that are of type integer and that each number in each of those fields has a meaning. For every field a different meaning ofcourse.

For example a field like 'refusal type' might have like 3 options (1, 2, 3) each a description of a 'refusal type' like something as "is duplicate" or "missing mandatory data".

In another field like 'Gender' the options are 1, 2, 9 which mean the following, 1=Female, 2=Male and 9=Unknown.

We do have a datadictionary table that has for each registry and all field all possible value option with their descriptive value are stored. That looks like this (simplified) for the Gender field.

Joining is not really an option because if I have like 20 fields I need 20 alias tables of the datadictionary table and different join strategies.

I would like to be able to create a referencecode for each field where I can put some SQL that fetches all integer values and their corresponding desciptive values for the required registry and field combinations from the datadictionary table and then in the report change the format to referencecode and that the desciptive value is shown instead of the number (much like they work now).

Currently there is an option to use CSV files for reference code but when the CSV is altered (manual action) the referrence code is not refreshed. With an little bit of SQL this can be checked with every run or every night or something like so.

Even better would be if you could choose SQL in the formatter of a field and then add the SQL the fetches the correct desciption for each value (kind of a lookup table).

The reason for this is that the option value of a field change relative often (once or twice a year) and the description values can be rather long so storing it in the measure field is not optimal.

If you can suggest other options to use that might make the management of this issue easier would be very helpfull.

Regards,

JeRoen