Database view filters

[AnthonyMull]

Hi community

I would like to build a report based on the roles a user has and the type of the group the users are in.

I think I will use a database view, but I have 2 questions please.

1. Is database views the best approach here.

2. If using a database view, how can I filter the data on the view before building the report, for example if I join on the user sys_id of the both tables, how can I filter to say only show users that are active before the report

Thanks in advance

[Dr Atul G- LNG]

Hi @AnthonyMull 

Yes, that approach is correct. A Database View is a good place to start in this case. When you create the Database View and add the required tables as sources, you can apply filters before creating the report. This is similar to setting predefined conditions so that the report only works with the data you need.

[Tanushree Maiti]

Hi @AnthonyMull 

1. Yes . Data base view is best choice here .

You need to join following tables

sys_user

sys_user_grmember

sys_user_has_role

& if needed sys_user_has_skill

2. You have to write where clause accordingly to filter

[Philippe Casidy · Accepted solution]

Hi @AnthonyMull ,

Yes, I think this database view exists ootb:

/sys_db_view_list.do?sysparm_query=nameLIKEskill

• Skills By Category

• Users By Skill Category

• User Skills By Group

• User Group Skills

[Tejas Adhalrao]

Hi @AnthonyMull  ,

A Database View is a good option if you need to report across multiple tables .For your second question, Database Views do not support adding conditions directly within the join definition use Where Clause.