Create a Custom Monthly Business Unit Trend Report

mVerve Partner Article

This KB article was developed by mVerve in consultation with the Inflectra team.

Prerequisites

• CUST_04 is a custom property for UAT sign off data and CUST_05 is for move to live. UAT sign off data and Move to live dates are required to calculate the requirements which fall under 30, 60, 90 trend.
• Custom properties can be created from cogwheel > requirements > custom properties.

• Ensure that you have 3 to 4 requirements.
• Also, ensure that you have 3 to 4 requirement types and data for them. This will help you with the final screenshot and graph. Shown below is an example of data set up for the two custom properties for a requirement.

Steps

1. To create the custom report you need to:

   1. Go to Administration  > Edit Reports

   2. Create a new Report, give it a name

   3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats

   4. Choose to add a Custom Section

   5. Add the SQL Query below. 

   6. Save the Report

SQL Query

Given below is the SQL Query.

 select R.REQUIREMENT_TYPE_NAME,
SUM(CASE WHEN C05.NAME= 'Aunpam guha' and R.CUST_04 IS NOT NULL THEN
        CASE
            WHEN R.CUST_05 IS NOT NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) < 30 THEN 1
                    ELSE 0
                END
            WHEN R.CUST_05 IS NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CurrentDateTime()) < 30 THEN 1
                    ELSE 0
                END
            ELSE 0
        END
     ELSE 0 END)  AS Under30Days,
    SUM(CASE WHEN C05.NAME= 'Aunpam guha' and R.CUST_04 IS NOT NULL THEN
        CASE
            WHEN R.CUST_05 IS NOT NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) > 30 AND DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) < 60 THEN 1
                    ELSE 0
                END
            WHEN R.CUST_05 IS NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CurrentDateTime()) > 30 AND DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) < 60  THEN 1
                    ELSE 0
                END
            ELSE 0
        END
     ELSE 0 END)  AS Under60Days,
 
    SUM(CASE WHEN C05.NAME= 'Aunpam guha' and R.CUST_04 IS NOT NULL THEN
        CASE
            WHEN R.CUST_05 IS NOT NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) > 60 AND DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) < 90 THEN 1
                    ELSE 0
                END
            WHEN R.CUST_05 IS NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CurrentDateTime()) > 60 AND DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) < 90  THEN 1
                    ELSE 0
                END
            ELSE 0
        END
     ELSE 0 END)  AS Under90Days,
         SUM(CASE WHEN C05.NAME= 'Aunpam guha' and R.CUST_04 IS NOT NULL THEN
        CASE
            WHEN R.CUST_05 IS NOT NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CAST(R.CUST_05 AS DATETIME)) > 90 THEN 1
                    ELSE 0
                END
            WHEN R.CUST_05 IS NULL THEN
                CASE
                    WHEN DiffDays(CAST(R.CUST_04 AS DATETIME), CurrentDateTime()) > 90 THEN 1
                    ELSE 0
                END
            ELSE 0
        END
     ELSE 0 END)  AS Above90Days,
     
sum( case WHEN C05.NAME= 'Aunpam guha' and R.CUST_04 IS NULL THEN 1
    ELSE 0 END
    )  as Invalid
FROM SpiraTestEntities.R_Requirements AS R
LEFT JOIN (
    SELECT DISTINCT C05.NAME, C05.CUSTOM_PROPERTY_VALUE_ID
    FROM SpiraTestEntities.R_CustomListValues AS C05
) AS C05 ON CAST(R.CUST_02 AS INT32) = C05.CUSTOM_PROPERTY_VALUE_ID
WHERE R.IS_DELETED = False
AND
(R.REQUIREMENT_STATUS_NAME = 'Developed' or R.REQUIREMENT_STATUS_NAME = 'Tested')
AND
R.PROJECT_ID IN (select value Proj.Project_ID  from SpiraTestEntities.R_Projects as Proj where Proj.IS_Active = True)
group by R.REQUIREMENT_TYPE_NAME 

Query Explanation

1. This SQL query analyzes the status of requirements in the Requirement types, specifically focusing on the time elapsed between two dates (CUST_04 and CUST_05) to categorize them into different aging buckets. It also identifies requirements with missing dates (CUST_04 is NULL). 
2. The query then sums the inner query results under different age categories (Under 30 Days, Under 60 Days, Under 90 Days, Above 90 Days, Invalid) 
3. It then joins the SpiraTestEntities.R_Requirements table (R) with a subquery that selects distinct values of the CUSTOM_PROPERTY_VALUE_ID and NAME columns from the R_CustomListValues table (C05) and aliases them as C05.
   1. Join: Joins the R_Requirements table (R) with the R_CustomListValues table (C05) to retrieve the department's name based on the CUSTOM_PROPERTY_VALUE_ID.
   2. Conditions: Filters requirements based on certain conditions like IS_DELETED, REQUIREMENT_TYPE_NAME, REQUIREMENT_STATUS_NAME, and PROJECT_ID.
   3. Age Calculation: Uses the DiffDays function to calculate the age of requirements based on CUST_04 and CUST_05 dates.
4. The WHERE clause filters the requirements based on certain conditions:
   1. R.IS_DELETED must be False.
   2. R.REQUIREMENT_TYPE_NAME must be 'User Story'.
   3. R.REQUIREMENT_STATUS_NAME must be 'Developed' or 'Tested'.
   4. R.PROJECT_ID must be active (IS_ACTIVE = True).
   5. The query calculates the age of requirements by comparing the dates in the CUST_04 and CUST_05 columns using the DiffDays function. If CUST_05 is NULL, it compares CUST_04 with the current date (CurrentDateTime()). The results are then categorized into different age buckets.
5. Then, the query calculates the count of requirements falling into each age category and those with missing dates, providing a comprehensive analysis of requirement statuses in the Requirement Types

Report and Graph Output

The result of the above query execution  will generate the following output.