issue in CASE filter

[philelmousse]

I have that filter to filter data depending on the user group but there is an error on the first "then". Can you help ? Thanks

CASE
WHEN [DATABASE LAYER].[User].[role]  = 'superuser'
THEN
[DATABASE LAYER].[cost_center].[cr_code] IN ([DATABASE LAYER].[User_cr].[cr_number])
WHEN [DATABASE LAYER].[User].[role] = 'user'
THEN
[DATABASE LAYER].[cost_center].[cr_code]
ELSE ''
END

[Lynn]

There are several problems with your statement. First of all, don't use "if" or "case" in a filter expression. Just specify a series of conditions separated by "or" where only one of them can evalute to true. Second, your second "then" clause doesn't contain a condition. It just contains a query item without defining it as equal to something (or less than or whatever). Third, the "else" as a null string is also not a condition, it is a literal value.

I think something like the below is closer to what you might need.

Code Select Expand


( [DATABASE LAYER].[User].[role]  = 'superuser'
  and
  [DATABASE LAYER].[cost_center].[cr_code] IN ([DATABASE LAYER].[User_cr].[cr_number])
)
or
( [DATABASE LAYER].[User].[role] = 'user'
  and
  [DATABASE LAYER].[cost_center].[cr_code] = {whatever this ought to equal in this situation, doesn't have to be =, could be in or <=, >=, etc. }
)
or
( [DATABASE LAYER].[User].[role] not in ('user', 'superuser' )
  and
{whatever condition ought to be if the role is neither user nor superuser. if there is no other possible role than omit this section}
)


[philelmousse]

Thanks for your answer, it seems to be better.

The second clause is because I want to display all "cr_number" if its a "user".

[philelmousse]

I just explain more what I want to do. I want filter the cost center depending on the user role. I have a table "user" and I filter the table with the user name in the session paramater in framework manager. I have a table with a column user_id and a column cr_id to link them.

If the user is member of the role 'superuser' I want display the associated cost center but if its not the case I want to display all the cost center. Thats the last part that i'm not able to do.

Thanks for helping

[Lynn]

So maybe the first bit of logic is all you need (the part with the filtering for superuser)? If you're not restricting the cost centers for the 'user' role then no need to specify any condition related to that.

I'm not sure where your join comes into play and the filter on the user table for the session parameter. Are you relying on that join in a model query subject to provide some data security as part of this? With minimized SQL the join won't be present unless data items from both tables are selected. If the join MUST be enforced then you can create a filter on the model query subject that references columns from both tables, ensuring the join is produced.

It isn't clear to me how the user/superuser role criteria fits in with the join and session parameter portions of your scenario, so apologies if i'm missing the big picture here.

[philelmousse]

In our database we have a table "User" with the lists of username and the roles. We also have a table "user_cr" to map a user and a cost_center. The first thing I do in framework manager is to filter the table "User" with the connected user to get his role. If the role is "user" I want to filter the dimension "cost_center" with the cr_number in the table "User_cr". If the role is "super user" I don't want apply any filter and take all the dimension "cost_center".
I created a filter for the query subject "cost_center" and I'm able to take the cr_number in "User_cr" table but I'm still not able to get all the cr if the role is "super user".

I used that filter and it works but I cant get all the cr_number :

(
[DATABASE LAYER].[User].[role]  = 'user'
and
[DATABASE LAYER].[cost_center].[cr_code] IN ([DATABASE LAYER].[User_cr].[cr_number])
)

[MFGF]

How about:

( [DATABASE LAYER].[User].[role]  = 'user'
and
[DATABASE LAYER].[cost_center].[cr_code] IN ([DATABASE LAYER].[User_cr].[cr_number]) )
OR
( [DATABASE LAYER].[User].[role]  = 'super user' )

Does this give you what you require, or have I misunderstood (which is the normal state of affairs for a muppet like me! :) )

MF.