Query Oracle datamart based on flex fields

Create query scripts to query and extract the required information from the Oracle 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 these 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.

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-07-07 00:00:00'
  • Text flex field named 'TEXT' with part of its value containing the pattern 'cre'
  • Tracker title is 'TestDateTracker'
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_of_trans  "Date",
       t.Project  ,
       t.Tracker  ,
       t.PlaningFolder  ,
       t.Priority,
       count(distinct t.artifact_key) "TotalArtifacts"  FROM (SELECT          b.date_of_trans,
                        e.title  AS Project,
		 c.title AS Tracker,
                        d.title AS PlaningFolder,
                        'P'||a.priority AS Priority,
                        a.artifact_key
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                      inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                      inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	          inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
	          inner join  project_dimension e on  (a.project_key=e.project_key) 
                  inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                  and ffd.name='Date'
                  and c.title='TestDateTracker'
	          and get_artifact_date_value(ff.flex_fields,ffd.flex_field_key)='2015-08-12 00:00:00'

UNION


 SELECT            b.date_of_trans,
                           e.title  AS Project,
		    c.title AS Tracker,
                           d.title AS PlaningFolder,
                           'P'||a.priority,
                            a.artifact_key
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                       inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                       inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	           inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
               	   inner join  project_dimension e on  (a.project_key=e.project_key)
                   inner join  flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                   and ffd.name='TEXT' 
 and c.title='TrackerTest'
	          and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) LIKE 'cre%'
	          and d.id='plan1004' ) t


GROUP BY  t.date_of_trans,t.Project,t.Tracker,t.PlaningFolder,t.Priority
ORDER BY  t.date_of_trans,t.Project,t.Tracker,t.PlaningFolder,t.Priority
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: 'FTracker'
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  "Date",
                   e.title "Project",
                   c.title  "Tracker",
                   d.title  "Planingfolder",
                   'P'||a.priority  "Priority",
                  count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                             inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                             inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		     inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		    inner join  project_dimension e on  (a.project_key=e.project_key)  
                    inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                    and ffd.name='FSS1' 
                    and c.title='FTracker' and get_artifact_select_value(ff.flex_fields,ffd.flex_field_key)='S1'
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority
Use case 3: Filter Text flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Flex field name: 'TEXT'
  • Value contains: 'cre
  • Tracker name: 'TrackerTest'
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  "Date",
                           e.title "Project",
		    c.title  "Tracker",
                           d.title  "Planingfolder",
                           'P'||a.priority  "Priority",
                           count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                       inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                       inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	           inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
               	   inner join  project_dimension e on  (a.project_key=e.project_key)
                   inner join  flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                   and ffd.name='TEXT' 
 and c.title='TrackerTest'
	          and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) LIKE 'cre%'
	          and d.id='plan1004'  
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority
Use case 4: Filter Date flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Flex field name: 'Date'
  • Value: '2015-08-12 00:00:00'
  • Tracker name: 'TestDateTracker'
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  "Date",
                        e.title "Project",
		 c.title  "Tracker",
                        d.title  "Planingfolder",
                        'P'||a.priority  "Priority",
                        count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
                      inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
                      inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
	          inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
	          inner join  project_dimension e on  (a.project_key=e.project_key) 
                  inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
                  and ffd.name='Date'
                  and c.title='TestDateTracker'
	          and get_artifact_date_value(ff.flex_fields,ffd.flex_field_key)='2015-08-12 00:00:00'
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority
Use case 5: Multi-select flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Multi-select flex field name: 'Multiselect'
  • Value: 'M12,M11'
  • Tracker name: 'TrackerTest'
  • 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  "Date",
                              e.title "Project",
		                      c.title  "Tracker",
                              d.title  "Planingfolder",
                         'P'||a.priority  "Priority",
	             count(distinct a.artifact_key)
 FROM       artifact_transaction_fact a   inner join date_dimension b on (a.trans_date_key=b.date_key) 
               inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
               inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		       inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		       inner join  project_dimension e on  (a.project_key=e.project_key) 
               inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
               and ffd.name='Multiselect'
               and c.title='TrackerTest'
               and
get_artifact_multiselect_value(ff.flex_fields,ffd.flex_field_key,'M11,M12','ALL') IS NOT NULL  
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority
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: 'TrackerTestUser'
  • 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  "Date",
          e.title "Project",
		  c.title  "Tracker",
          d.title  "Planingfolder",
         'P'||a.priority  "Priority",
         count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
        inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
        inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		inner join  project_dimension e on  (a.project_key=e.project_key) 
        inner join flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
        and ffd.name='Select User'
        and c.title='TrackerTestUser'
        and get_artifact_user_value(ff.flex_fields,ffd.flex_field_key,'user1,user2','ALL') IS NOT NULL  
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority
Use case 7: Filter Text flex fields, case-insensitive search
Suppose you want to retrieve data based on the following assumptions:
  • Flex field name: 'TEXT'
  • Value contains: 'cre'
  • Tracker name: 'TestDateTracker'
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  "Date",
            e.title "Project",
 		   c.title  "Tracker",
            d.title  "Planingfolder",
           'P'||a.priority  "Priority",
            count( distinct a.artifact_key) "TotalArtifacts"
 FROM  artifact_transaction_fact a inner join date_dimension b on (a.trans_date_key=b.date_key) 
            inner join  artifact_flex_fields ff  on (ff.artifact_flex_fields_key = a.flex_fields_key)
            inner join  tracker_dimension c on (a.tracker_key=c.tracker_key) 
		    inner join  pf_dimension d on  (a.pf_key=d.pf_key) 
		    inner join  project_dimension e on  (a.project_key=e.project_key)  
            inner join  flex_field_dimension ffd on (a.tracker_key=ffd.tracker_key)
            and UPPER(ffd.name)=UPPER('TEXT')
            and upper(c.title)=upper('TestDateTracker')
		    and upper(get_artifact_text_value(ff.flex_fields,ffd.flex_field_key)) like upper('cre%')   
--where a.effective_from <= sysdate and a.effective_till > to_date('2012-04-01', 'YYYY-MM-DD')
GROUP BY  b.date_of_trans,e.title,c.title,d.title,a.priority
ORDER BY  b.date_of_trans,e.title,c.title,d.title,a.priority