Custom Dimension

[former_member729889]

Hi,

I want to create a custom dimension as follows.

Standard Values

UK

DE

FR

Custom

UK

DE

FR

DE&FR

So I write

CASE

WHEN COUNTRY = 'UK' THEN 'UK'

WHEN COUNTRY = 'DE' THEN 'DE'

WHEN COUNTRY = 'FR' THEN 'FR'

WHEN COUNTRY IN ('DE','FR') THEN 'DE&FR'

END

As expected this custom dimension does not help to sum the measures for DE & FR.

How can I achieve this at universe level?

Thanks!

[amitrathi239 · Accepted solution]

Hi,

Instead of using case statement edit the "custom dimension" click on the edit under the object properties tab.

In Query Panel, click the SQL button. Type the following SQL in SQL Viewer:

SELECT

Table.custom dimension

FROM

Table

UNION SELECT 'DE&FR'

FROM

dual

Select the Do not generate SQL before running/save option.

Save the universe and export it to the repository.

With this DE&FR is not going to relate with country DE or FR.

Amit

[former_member729889 · Accepted solution]

Sorry, I guess I wasn't clear. The "custom" is the output that I'm trying to generate with the "standard" values.

So simply, I have sales data associated with countries.

I'm trying to get an output which includes the sum of multiple countries.

And I have to handle this within IDT.

Sorry for the missing information again.

[amitrathi239 · Accepted solution]

Hi

in this case what you showed case statement will work..

have you tried that?

Amit

[Former Member · Accepted solution]

Amit,

I don't think it will work that way. If I understood well, Steve wants to display fact data for DE and FR twice.

One time by individual country, a second time by country group.

I would solve this in the report, by making a subtotal when the country is DE or FR.

This could also be solved in the universe, but I don't consider the solution very good practice. One could for instance make a derived table with something like :

select country, sum(measure1), sum(measure2) from fact

union all

select 'DE+FR', sum(measure1), sum(measure2) from fact

where country in ('DE','FR')

This way fact data is actually twice in the result set. There is of course the danger that fact data is accidently aggregated when the country dimension is not taken into the result set.

[former_member729889]

Any suggestions?