Query PostgreSQL datamart based on flex fields

Create query scripts to query and extract the required information from the PostgreSQL datamart. Make sure these scripts are available to any user (including users with read-only permission) who wants to execute these scripts. This topic lists the functions and sample queries for a few specific use cases.

Important:

Log into TeamForge as a Reporting user and run the queries. Note that this is a one-time process.
Use the following flex field functions by joining the flex_field_dimension, artifact_flex_field and artifact_transaction_fact (or) artifact_daily_snapshot_fact tables as it depends on the XML data and flex field key generated by the platform.

Common script used across all flex field functions:

CREATE OR REPLACE FUNCTION array_search(needle anyelement, haystack anyarray) 
  RETURNS integer AS 
$BODY$ 
    SELECT i 
      FROM generate_subscripts($2, 1) AS i 
     WHERE $2[i] = $1 
  ORDER BY i 
$BODY$ 
  LANGUAGE sql STABLE 
  COST 100;

Function for Date flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables)
Flex field key (derived automatically from flex_field_dimension table and other tables)

Output arguments

Date flex field values

CREATE OR REPLACE FUNCTION get_artifact_date_value(artifact_xml xml, field_key integer) 
  RETURNS character varying AS 
$BODY$ 
DECLARE 
field_value_key varchar(9055) default ''; 
 BEGIN  
 SELECT cast((xpath('/fields/field/@val',artifact_xml)) [(array_search(field_key::text,(xpath('/fields/field/@key',artifact_xml))::text[]))] 
  as varchar) into field_value_key; 
 Return field_value_key; 
 END; 
$BODY$ 
  LANGUAGE plpgsql VOLATILE 
  COST 100;

Function for Text flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables)
Flex field key (derived automatically from flex_field_dimension table and other tables)

Output arguments

Text flex field values

CREATE OR REPLACE FUNCTION replacen(artifact_xml1 xml) 
  RETURNS text AS 
$BODY$ 
 select replace ((select replace(artifact_xml1::varchar(3000),'<!','&lt;!')),']>',']&gt;') 

$BODY$ 
  LANGUAGE sql STABLE 
  COST 100; 

   
CREATE OR REPLACE FUNCTION get_artifact_text_value(artifact_xml xml, field_key integer) 
  RETURNS character varying AS 
$BODY$ 
 DECLARE 
  field_value_key varchar(9055) default '';  
 BEGIN  
 SELECT cast((xpath('/fields/field/@val',replacen(artifact_xml)::xml)) [(array_search(field_key::text,(xpath('/fields/field/@key',replacen(artifact_xml)::xml))::text[]))] 
  as varchar) into field_value_key; 
 Return field_value_key; 
 END; 
$BODY$ 
  LANGUAGE plpgsql VOLATILE 
  COST 100;

Function for Single-select flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables)
Flex field key (derived automatically from flex_field_dimension table and other tables)

Output arguments

Values selected or stored in single-select flex field

CREATE OR REPLACE FUNCTION get_artifact_select_value(artifact_xml xml, field_key integer) 
  RETURNS character varying AS 
$BODY$ 
 DECLARE 
  field_value_key varchar(9055) default ''; 
 singleSelectValues varchar(9055) default ''; 
 BEGIN 
  SELECT cast((xpath('/fields/field/@val',artifact_xml)) [(array_search(field_key::text,(xpath('/fields/field/@key',artifact_xml))::text[]))] 
  as varchar) into field_value_key; 
  Select value into singleSelectValues  from flex_field_value_dimension where flex_field_value_key::text = (field_value_key::text); 

 Return singleSelectValues; 
 END; 
$BODY$ 
  LANGUAGE plpgsql VOLATILE 
  COST 100;

Function for Multi-select flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables).
Flex field key (derived automatically from flex_field_dimension table and other tables).
Specific value(s) stored in the field or the keyword 'ALL' for retrieving all values stored in multi-select flex field.
Logical conditional operator. Possible values are 'ALL' or 'ANY'. The argument value of 'ALL' performs a search equivalent to the 'AND' condition and the value of 'ANY' performs a search equivalent to the 'OR' condition.

Output arguments

Values selected or stored in multi-select flex field

     -----First Execute Inner Function------
-------Inner function start------	 
CREATE OR REPLACE FUNCTION get_artifact_multiselect_values_any(artifact_xml xml, field_key integer, selectedfields text[])
RETURNS text AS
$BODY$
DECLARE
fieldValues text;
multiFieldValues text default '';
splitfield text[];
splitfield2 text[];
loop1 integer DEFAULT 1;
 loop2 integer DEFAULT 1;
arrayLength integer DEFAULT 1;
 arrayLength2 integer DEFAULT 1;
multifield text DEFAULT '';
filteredField text Default '';
BEGIN
 Select cast((xpath('/fields/field/@val',artifact_xml))
[array_search(field_key::text,(xpath('/fields/field/@key',artifact_xml)::text[]))]
 as varchar(9055)) into fieldValues;
Select array_length(regexp_split_to_array(fieldValues, ','),1) into arrayLength;
Select regexp_split_to_array(fieldValues, ',') into splitfield;
IF arrayLength IS NOT NULL THEN
        FOR loop1 IN 1..arrayLength LOOP
        Select value into multiFieldValues from flex_field_value_dimension where flex_field_value_key = splitfield[loop1]::integer;
        multifield := concat(multifield,',',multiFieldValues);
        EXIT WHEN loop1 > arrayLength; 
        END LOOP;


        multifield := rtrim(multifield,'" ');
        multifield := ltrim(multifield,',');

Select array_length(regexp_split_to_array(array_to_string(selectedFields,','), ','),1) into arrayLength2;
Select regexp_split_to_array(array_to_string(selectedFields,','), ',') into splitfield2;
IF selectedFields = Array['ALL'] THEN
filteredField:=multifield;
Return filteredField;
ELSE
FOR loop2 IN 1..arrayLength2 LOOP
IF   multifield ~ splitfield2[loop2] THEN
     	filteredField := concat(splitfield2[loop2],',',filteredField);
END IF;
EXIT WHEN loop2 > arrayLength2; 
END LOOP;
END IF;
END IF;
filteredField := rtrim(filteredField,'" ');
filteredField := rtrim(filteredField,',');
Return filteredField;
 END;  
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100; 
-------Inner function end------


CREATE OR REPLACE FUNCTION get_artifact_multiselect_value(artifact_xml xml, field_key integer,selectedfields text[],condition character varying)
  RETURNS text as
  $BODY$
  DECLARE
   fieldValues text;
   loop1 integer default 1;
   
   loop2 integer default 1;
  arrayLength1 integer default 1;
  arrayLength2 integer default 1;
  arrayLength3 integer default 1;
  multiFieldValues text default '';
  multifield text DEFAULT '';
  returnvalues text DEFAULT '';
    i integer default 1;
    j integer default 1;
     v_array1 text[]; 
     v_array2 text[]; 
     v_array3 text[];
     results text Default '' ;
     filteredField text Default '';
     count integer default 0;	 
   BEGIN
       Select cast((xpath('/fields/field/@val',artifact_xml))[array_search(field_key::text,(xpath('/fields/field/@key',artifact_xml)::text[]))]
       as varchar(9055)) into fieldValues;
       Select array_length(regexp_split_to_array(fieldValues, ','),1) into arrayLength1;
       Select regexp_split_to_array(fieldValues, ',') into v_array1; 
  IF arrayLength1 IS NOT NULL THEN
       FOR  loop1 in 1..arrayLength1 LOOP
       select value into multiFieldValues from  flex_field_value_dimension where flex_field_value_key=v_array1[loop1]::integer;
       multifield := concat(multifield,',',multiFieldValues);
       EXIT WHEN loop1 > arrayLength1;
       multifield := rtrim(multifield,'" ');
       multifield := ltrim(multifield,',');
       END LOOP;
       Select array_length(regexp_split_to_array(multifield, ','),1) into arrayLength2;
       Select regexp_split_to_array(multifield, ',') into v_array2;
       Select array_length(regexp_split_to_array(array_to_string(selectedfields,','), ','),1) into arrayLength3;
       Select regexp_split_to_array(array_to_string(selectedfields,','), ',') into v_array3;

             IF (UPPER(condition)=UPPER('ALL')) THEN
                  IF selectedFields = Array['ALL'] THEN 
                       results:=multifield;
                  ELSE
					IF (v_array2 is not  null AND v_array3 is not null) THEN 
						   FOR i in 1..arrayLength3 LOOP
							  FOR j in 1..arrayLength2 LOOP
							       IF (v_array3[i]= v_array2[j]) THEN 
								   filteredField := concat(v_array3[i],',',filteredField);
								   
								   count=count+1;
								   EXIT;
							       END IF;
					
							 END LOOP;
						     END LOOP;
					ELSE
						 results = false;
					       
				        END IF;
				        IF (count=arrayLength3) THEN
				        results=filteredField;
				        ELSE 
				        results='';
				        END IF;
	           END IF;
           ELSE 
      
            select get_artifact_multiselect_values_any(artifact_xml,field_key,selectedfields) into returnvalues;
                IF returnvalues IS NOT NULL THEN 
                    results=returnvalues;
                ELSE 
                     results='';
                END IF;
      
           END IF;
  END IF;
  results := rtrim(results,'" ');
  results := rtrim(results,',');
 RETURN(results);
 END;  
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Function for Multi-user flex fields

Input arguments

Flex field XML (derived automatically by joining the artifact_transaction_fact (or) artifact_daily_snapshot_fact and artifact_flex_field tables).
Flex field key (derived automatically from flex_field_dimension table and other tables).
Specific value(s) stored in the field or the keyword 'ALL' for retrieving all values stored in multi-select flex field.
Logical conditional operator. Possible values are 'ALL' or 'ANY'. The argument value of 'ALL' performs a search equivalent to the 'AND' condition and the value of 'ANY' performs a search equivalent to the 'OR' condition.

Output arguments

Values selected or stored in multi-user flex field

     -----First Execute Inner Function------

 ------Inner function start---
CREATE OR REPLACE FUNCTION get_artifact_user_values_any(artifact_xml xml, field_key integer, selectedfields text[]) 
  RETURNS text AS 
$BODY$ 
DECLARE 
fieldValues text; 
multiFieldValues text default ''; 
splitfield text[]; 
splitfield2 text[]; 
loop1 integer DEFAULT 1; 
 loop2 integer DEFAULT 1; 
arrayLength integer DEFAULT 1; 
 arrayLength2 integer DEFAULT 1; 
multifield text DEFAULT ''; 
filteredField text Default ''; 
BEGIN  
 Select cast((xpath('/fields/field/@val',artifact_xml)) 
[array_search(field_key::text,(xpath('/fields/field/@key',artifact_xml)::text[]))] 
 as varchar(9055)) into fieldValues; 
Select array_length(regexp_split_to_array(fieldValues, ','),1) into arrayLength; 
Select regexp_split_to_array(fieldValues, ',') into splitfield; 
IF arrayLength IS NOT NULL THEN
   FOR loop1 IN 1..arrayLength LOOP 
      Select full_name into multiFieldValues from user_dimension where user_key = splitfield[loop1]::integer; 
      multifield := concat(multifield,',',multiFieldValues); 
      EXIT WHEN loop1 > arrayLength; 
   END LOOP; 
      multifield := rtrim(multifield,'" '); 
      multifield := ltrim(multifield,','); 

     Select array_length(regexp_split_to_array(array_to_string(selectedFields,','), ','),1) into arrayLength2; 
     Select regexp_split_to_array(array_to_string(selectedFields,','), ',') into splitfield2; 
      IF selectedFields = Array['ALL'] THEN 
      filteredField:=multifield;
      Return filteredField;
      ELSE 
          FOR loop2 IN 1..arrayLength2 LOOP 
          IF   multifield ~ splitfield2[loop2] THEN 
     	  filteredField := concat(splitfield2[loop2],',',filteredField); 
          END IF; 
          EXIT WHEN loop2 > arrayLength2; 
          END LOOP; 
      END IF; 
END IF;
filteredField := rtrim(filteredField,'" '); 
filteredField := rtrim(filteredField,','); 
Return filteredField; 
 END;  
$BODY$ 
  LANGUAGE plpgsql VOLATILE 
  COST 100; 
 
 ------Inner Function End-----

create or replace FUNCTION get_artifact_user_value(artifact_xml xml, field_key integer,selectedfields text[],condition character varying)
   RETURNS text as
  $BODY$
  DECLARE
   fieldValues text;
   loop1 integer default 1;
   
   loop2 integer default 1;
  arrayLength1 integer default 1;
  arrayLength2 integer default 1;
  arrayLength3 integer default 1;
  multiFieldValues text default '';
  multifield text DEFAULT '';
  returnvalues text DEFAULT '';
    i integer default 1;
    j integer default 1;
     v_array1 text[]; 
     v_array2 text[]; 
     v_array3 text[];
     results text Default '' ;
     filteredField text Default '';
     count integer default 0;	 
   BEGIN
       Select cast((xpath('/fields/field/@val',artifact_xml))[array_search(field_key::text,(xpath('/fields/field/@key',artifact_xml)::text[]))]
       as varchar(9055)) into fieldValues;
       Select array_length(regexp_split_to_array(fieldValues, ','),1) into arrayLength1;
       Select regexp_split_to_array(fieldValues, ',') into v_array1; 
  IF arrayLength1 IS NOT NULL THEN
       FOR  loop1 in 1..arrayLength1 LOOP
       select full_name into multiFieldValues from  user_dimension where user_key=v_array1[loop1]::integer;
       multifield := concat(multifield,',',multiFieldValues);
       EXIT WHEN loop1 > arrayLength1;
       multifield := rtrim(multifield,'" ');
       multifield := ltrim(multifield,',');
       END LOOP;
       Select array_length(regexp_split_to_array(multifield, ','),1) into arrayLength2;
       Select regexp_split_to_array(multifield, ',') into v_array2;
       Select array_length(regexp_split_to_array(array_to_string(selectedfields,','), ','),1) into arrayLength3;
       Select regexp_split_to_array(array_to_string(selectedfields,','), ',') into v_array3;

             IF (UPPER(condition)=UPPER('ALL')) THEN
                  IF selectedFields = Array['ALL'] THEN 
                       results:=multifield;
                  ELSE
					IF (v_array2 is not  null AND v_array3 is not null) THEN 
						   FOR i in 1..arrayLength3 LOOP
							  FOR j in 1..arrayLength2 LOOP
							       IF (v_array3[i]= v_array2[j]) THEN 
								   filteredField := concat(v_array3[i],',',filteredField);
								   
								   count=count+1;
								   EXIT;
							       END IF;
					
							 END LOOP;
						     END LOOP;
					ELSE
						 results = false;
					       
				        END IF;
				        IF (count=arrayLength3) THEN
				        results=filteredField;
				        ELSE 
				        results='';
				        END IF;
	           END IF;
           ELSE 
      
            select get_artifact_user_values_any(artifact_xml,field_key,selectedfields) into returnvalues;
                IF returnvalues IS NOT NULL THEN 
                    results=returnvalues;
                ELSE 
                     results='';
                END IF;
      
           END IF;
  END IF;
  results := rtrim(results,'" ');
  results := rtrim(results,',');
 RETURN(results);
 END;  
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Sample use cases and queries

Note:

Flex field Name, Value, Tracker title and so on that are used in the filtering conditions are case-sensitive.
See Case 7 below for building a case-insensitive search using upper() or lower() functions.

Use case 1: Filter Date and Text flex fields

Suppose you want to retrieve data based on the following assumptions:

Date flex field named 'CreatedDate' that has a value of '2015-09-02 00:00:00'
Text flex field named 'Text102' with part of its value containing the pattern 'soft'
Tracker title is 'Tracker101'

Note:

You can change the field name, tracker title and values based on the data in your system
The date passed as input should be of the format “YYYY-MM-DD HH24:MI:SS”

Sample query

select 
	t.Date as Date,
	t.Project as Project,
	t.Tracker as Tracker,
	t.Planing_folder as Planing_folder,
	'P'||t.Priority as Priority,
	count(distinct t.artifact_key) 
from 

(            select 		b.date_of_trans as Date,
				e.title as Project,c.title as Tracker, 
				d.title as Planing_folder,
				'P'||a.priority as Priority,
				get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) as TextFlexField,
				a.artifact_key
from 
				artifact_transaction_fact a, 
				date_dimension b, 
				artifact_flex_fields ff, 
				tracker_dimension c, 
				pf_dimension d, 
				project_dimension e ,
				flex_field_dimension ffd
where 
				a.trans_date_key=b.date_key 
				and ff.artifact_flex_fields_key = a.flex_fields_key 
				and a.tracker_key=c.tracker_key 
				and a.pf_key=d.pf_key 
				and a.project_key=e.project_key 
				and a.tracker_key=ffd.tracker_key
				and e.title='Project SCD-2 Test'
				and ffd.name in ('Text102')
				and c.title in ('Tracker101') 
				and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) like 'soft%'

union  

	  select 		b.date_of_trans as Date,
				e.title as Project,c.title as Tracker, 
				d.title as Planing_folder,
				'P'||a.priority as Priority,
				regexp_split_to_table(get_artifact_date_value(ff.flex_fields, ffd.flex_field_key),E',') as DateFlexField,
				a.artifact_key
	
from 
				artifact_transaction_fact a, 
				date_dimension b, 
				artifact_flex_fields ff, 
				tracker_dimension c, 
				pf_dimension d, 
				project_dimension e ,
				flex_field_dimension ffd
where 
				a.trans_date_key=b.date_key 
				and ff.artifact_flex_fields_key = a.flex_fields_key 
				and a.tracker_key=c.tracker_key 
				and a.pf_key=d.pf_key 
				and a.project_key=e.project_key 
				and a.tracker_key=ffd.tracker_key
				and e.title='Project SCD-2 Test'
				and ffd.name in ('CreatedDate')
				and c.title in ('Tracker101') 
				and  get_artifact_date_value(ff.flex_fields, ffd.flex_field_key)='2015-09-02 00:00:00') as t
group by 1,2,3,4,5 
order by 1,2,3,4,5;

Use case 2: Filter Single-select flex fields

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'FSS1'
Value selected or stored: 'S1'
Tracker title: 'Tracker2'

Note:

You can change the field name, tracker title and values based on the data in your system

Sample query

select
		b.date_of_trans as Date,
		pd.title as Project,
		td.title as Tracker,
		d.title as Planning_folder,
		'P'||a.priority as Priority ,
		get_artifact_select_value(aff.flex_fields,ffd.flex_field_key) as SingleSelectFlexField,
		count(distinct a.artifact_key) TotalArtifacts 
from
		artifact_transaction_fact a,
		date_dimension b,
		artifact_flex_fields aff,
		project_dimension pd ,
		tracker_dimension td,
		pf_dimension d, 
		flex_field_dimension ffd 
where 
		a.trans_date_key=b.date_key 
		and a.flex_fields_key=aff.artifact_flex_fields_key
		and a.tracker_key=td.tracker_key
		and a.project_key=pd.project_key
		and td.tracker_key=ffd.tracker_key
		and a.pf_key=d.pf_key 
		and pd.title='ProjectTestFunctionSCD-2'
		and td.title in ('Tracker2')
		and ffd.name='FSS1'
		and get_artifact_select_value(aff.flex_fields,ffd.flex_field_key)='S1' 
		--and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6
order by 1,2,3,4,5,6;

Use case 3: Filter Text flex fields

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'Text101'
Value contains: 'hello'
Tracker name: 'Tracker101'

Note:

You can change the field name, tracker title and values based on the data in your system

Sample query

select
   	 b.date_of_trans as Date,
		e.title as Project,
		c.title as Tracker, 
		d.title as Planing_folder,
		'P'||a.priority as Priority,
		get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) as TextFlexField,
		count(distinct a.artifact_key) Artifacts
from 
		artifact_transaction_fact a, 
		date_dimension b, 
		artifact_flex_fields ff, 
		tracker_dimension c, 
		pf_dimension d, 
		project_dimension e ,
		flex_field_dimension ffd
where 
		a.trans_date_key=b.date_key 
		and ff.artifact_flex_fields_key = a.flex_fields_key 
		and a.tracker_key=c.tracker_key 
		and a.pf_key=d.pf_key 
		and a.project_key=e.project_key 
		and a.tracker_key=ffd.tracker_key
		and e.title='Project SCD-2 Test'
		and ffd.name = 'Text101'
		and c.title in ('Tracker101') 
		and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) like 'hello%' 
		--and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6 
order by 1,2,3,4,5,6;

Use case 4: Filter Date flex fields

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'CreatedDate'
Value: '2015-09-02 00:00:00'
Tracker name: 'Tracker101'

Note:

You can change the field name, tracker title and values based on the data in your system

Sample query

select
		b.date_of_trans as Date,
		e.title as Project,
		c.title as Tracker, 
		d.title as Planing_folder,
		'P'||a.priority as Priority,
		regexp_split_to_table(get_artifact_date_value(ff.flex_fields, ffd.flex_field_key),E',') as DateFlexField,
		count(distinct a.artifact_key)
	
from 
		artifact_transaction_fact a, 
		date_dimension b, 
		artifact_flex_fields ff, 
		tracker_dimension c, 
		pf_dimension d, 
		project_dimension e ,
		flex_field_dimension ffd
where 
		    a.trans_date_key=b.date_key 
		    and ff.artifact_flex_fields_key = a.flex_fields_key 
		    and a.tracker_key=c.tracker_key 
		    and a.pf_key=d.pf_key 
		    and a.project_key=e.project_key 
		    and a.tracker_key=ffd.tracker_key
		    and e.title='Project SCD-2 Test'
		    and ffd.name in ('CreatedDate')
		    and c.title in ('Tracker101') 
		    and  get_artifact_date_value(ff.flex_fields, ffd.flex_field_key)='2015-09-02 00:00:00'

group by  1,2,3,4,5,6
order by 1,2,3,4,5,6;

Use case 5: Multi-select flex fields

Suppose you want to retrieve data based on the following assumptions:

Multi-select flex field name: 'Country'
Value: 'Russia,India'
Tracker name: 'TrackerN'
Conditional parameter: 'ALL'

Note:

You can change the field name, tracker title and values based on the data in your system
Flex field name, value and tracker title that are used in the SQL filter conditions are case sensitive
If you want to select all the values in User flex field, then pass 'ALL' as the conditional parameter
If you want to select any value, then pass 'ANY' as the conditional parameter

Sample query

select b.date_of_trans as Date, e.title as Project,c.title as Tracker, 
d.title as Planing_folder,'P'||a.priority as Priority,
 get_artifact_multiselect_value(ff.flex_fields,ffd.flex_field_key,'{Russia,India}','ALL') as MultiselectFlexField,
count(distinct a.artifact_key) Artifacts
--,a.artifact_key
from 
    artifact_transaction_fact a, 
    date_dimension b, 
    artifact_flex_fields ff, 
    tracker_dimension c, 
    pf_dimension d, 
    project_dimension e ,
    flex_field_dimension ffd
where 
    a.trans_date_key=b.date_key 
    and ff.artifact_flex_fields_key = a.flex_fields_key 
    and a.tracker_key=c.tracker_key 
    and a.pf_key=d.pf_key 
    and a.project_key=e.project_key 
    and a.tracker_key=ffd.tracker_key
    --and e.title='TestMultiFunction'
    and ffd.name = 'Country'
    and c.title in ('TrackerN') 
    and get_artifact_multiselect_value(ff.flex_fields,ffd.flex_field_key,'{Russia,India}','ALL')!='' 
    --and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6 
order by 1,2,3,4,5,6;

Use case 6: Filter User flex fields

Suppose you want to retrieve data based on the following assumptions:

User flex field name: 'Select User'
Value: 'user1,user2'
Tracker name: 'TrackerN'
Conditional parameter: 'ALL'

Note:

You can change the field name, tracker title and values based on the data in your system
Flex field name, value and tracker title that are used in the SQL filter conditions are case sensitive
If you want to select all the values in User flex field, then pass 'ALL' as the conditional parameter
If you want to select any value, then pass 'ANY' as the conditional parameter

Sample query

select b.date_of_trans as Date, e.title as Project,c.title as Tracker, 
d.title as Planing_folder,'P'||a.priority as Priority,
 get_artifact_user_value(ff.flex_fields,ffd.flex_field_key,'{user1,user2}','ALL') as UserFlexField,
count(distinct a.artifact_key) Artifacts
--,a.artifact_key
from 
    artifact_transaction_fact a, 
    date_dimension b, 
    artifact_flex_fields ff, 
    tracker_dimension c, 
    pf_dimension d, 
    project_dimension e ,
    flex_field_dimension ffd
where 
    a.trans_date_key=b.date_key 
    and ff.artifact_flex_fields_key = a.flex_fields_key 
    and a.tracker_key=c.tracker_key 
    and a.pf_key=d.pf_key 
    and a.project_key=e.project_key 
    and a.tracker_key=ffd.tracker_key
    --and e.title='TestMultiFunction'
    and ffd.name = 'Select User'
    and c.title in ('TrackerN') 
    and get_artifact_user_value(ff.flex_fields,ffd.flex_field_key,'{user1,user2}','ALL')!='' 
    --and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6 
order by 1,2,3,4,5,6;

Use case 7: Filter Text flex fields, case-insensitive search

Suppose you want to retrieve data based on the following assumptions:

Flex field name: 'Text102'
Value contains: 'soft'
Tracker name: 'Tracker101'

Note:

You can change the field name, tracker title and values based on the data in your system
The upper() function can be replaced with the lower() function appropriately for case-insensitive search

Sample query

select
        		b.date_of_trans as Date,
				e.title as Project,
				c.title as Tracker, 
				d.title as Planing_folder,
				'P'||a.priority as Priority,
				get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) as TextFlexField,
				count(distinct a.artifact_key) as TotalCounts
from 
				artifact_transaction_fact a, 
				date_dimension b, 
				artifact_flex_fields ff, 
				tracker_dimension c, 
				pf_dimension d, 
				project_dimension e ,
				flex_field_dimension ffd
where 
				a.trans_date_key=b.date_key 
				and ff.artifact_flex_fields_key = a.flex_fields_key 
				and a.tracker_key=c.tracker_key 
				and a.pf_key=d.pf_key 
				and a.project_key=e.project_key 
				and a.tracker_key=ffd.tracker_key
				and UPPER(e.title)=UPPER('Project SCD-2 Test')
				and UPPER(ffd.name)=UPPER('Text102')
				and UPPER(c.title) = UPPER('Tracker101')
				and UPPER(get_artifact_text_value(ff.flex_fields,ffd.flex_field_key)) like UPPER('soft%')

group by 1,2,3,4,5,6
order by 1,2,3,4,5,6;