I have an excel sheet in which I have a field categorie that I want to show in my report.
The first 4 columns are also in my report so I mergend Business, Customer Class, Sales Distr. and Sales District.
Then I made a key on this. Next I made a detail variable based on this key of merged fields to get this field ‘Categorie’.
How can I show categorie with Company and EoM Date in the layout?
These first 2 columns are just not in the excel file.
I could add Company in the excel but not EoM Date because this is a date and of course varies.
Certainly there must be a way to get the Categrie field based on the 4 merged fields that are in both data sets??
Ok update…
Managed to fix this by making the field categorie from the excel sheet input a ‘measure’.
Bit strange to set a text as a measure but now using directly this measure field in my layout fixes the problem I had.
Not sure if this is the correct way to do it or that my problem could also have be solved in another way.
After merging your dimensions, you need to create a detail object for your category. Start by creating a new variable. Set the qualification to a detail instead of a dimension. Then select your merge field as the associated dimension. In the formula, select the category field from your excel sheet query.
Well @dtolley that is what I did.
Made the merges and then made a variable that was a detail field called “category” (that you can see in the output)
And for the associated dimension I linked it to the variable “rccp_key” that I made by concatenating the 4 merged fields.
That way I was sure I had a unique combination for each line out of the excel lookup table.
