Hello everyone,

This is my first post, so I hope it finds you all well and thanks in advance for any support or contribution.

I am a very beginner and on my way to creating a more "advanced" report I am facing some difficulties to achieve my goal and therefore here I am.

In my report I need to create a condition which populates a field  when a specific criteria is met. Basically, I have three fields my table Manufacturer, I have three columns: a product number, a manufacturer role and manufacturer country. I created extra columns BR , PI, QC and SP based on

WHEN "Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK' THEN 1 ELSE 0)

This is my first step to identify what is UK and non UK category for every 'role type' in column role. One product can have 4 different roles BR, PI, QC and SP and many country based sites and the rule is;

If UK is the only country with role BR then give me UK only

If UK is non of the countries with role BR then give me No Uk

IF UK is one of the countries in addition to any other then give me UK also.

With my basic sql skills, i would formulate the following

CASE

WHEN MIN("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 0

AND

WHEN MAX("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 0

THEN 'No UK'

WHEN MIN("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 1

AND

WHEN MAX("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 1

THEN 'UK only'

WHEN MIN("Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 0

AND

WHEN MAX(

"Manufacturer"."Role" = 'BR' AND

"Manufacturer"."Country" = 'UK') = 1

THEN 'UK also'

ELSE 'check'

END

GROUP BY "Manufacturer"."Product"

My first problem  is that... there will probably be a much easier way to do this and probably I am over complicating my life. Secondly I don't know why BI gives me errors with this and all the combinations I have tried.... I have also tried the advanced SQL group by, but get errors as well. So, I don't know what else to do. I think our table has behind by default the group by 'product number' column.

Thanks all for giving me a hand.

All the best,