Solution: Using PowerBI and parameters (on-premise only)

This solution lets you to get the list of the users with their role for the particular date, so basically saying by specifying date you'd be able to extract product membership historical data.
Please note, that this solution only works for one product  at a time.

  • Open PowerBI Desktop
  • Choose SQL Database as source
  • Specify the Server and Database names, choose Import as data connectivity mode (to avoid working directly in the database)
  • Click Advanced Options and insert SQL query and then click OK:
DECLARE @project_id INT = 24;
DECLARE @snapshot_date DATETIME = '2024-05-19 00:01:00:000';

WITH MaxChangeDates AS (
    SELECT ARTIFACT_ID, MAX(CHANGE_DATE) AS MaxChangeDate
    FROM RPT_HISTORYCHANGESETS
    WHERE ARTIFACT_TYPE_ID = -3  -- Filter on ARTIFACT_TYPE_ID
      AND CHANGE_DATE <= @snapshot_date
    GROUP BY ARTIFACT_ID
)
SELECT @project_id AS Project_id,
       @snapshot_date AS Date,
       thc.ARTIFACT_ID AS User_id,
       thc.ARTIFACT_DESC AS username,
       (
           SELECT TOP 1 thd.NEW_VALUE
           FROM RPT_HISTORYCHANGESETS thc2
           JOIN RPT_HISTORYDETAILS thd ON thc2.CHANGESET_ID = thd.CHANGESET_ID
           WHERE thc2.ARTIFACT_ID = thc.ARTIFACT_ID
             AND thc.ARTIFACT_TYPE_ID = -3
             AND thc2.CHANGE_DATE <= @snapshot_date
             AND thc2.CHANGETYPE_ID IN (1, 3) -- Modified or Added
             AND thc2.PROJECT_ID = @project_id
           ORDER BY thc2.CHANGE_DATE DESC
       ) AS Role_id
FROM RPT_HISTORYCHANGESETS thc
JOIN MaxChangeDates mcd ON thc.ARTIFACT_ID = mcd.ARTIFACT_ID 
                       AND thc.CHANGE_DATE = mcd.MaxChangeDate
WHERE thc.ARTIFACT_TYPE_ID = -3
  AND NOT EXISTS (
      SELECT 1
      FROM RPT_HISTORYCHANGESETS thc3
      WHERE thc3.ARTIFACT_ID = thc.ARTIFACT_ID
        AND thc3.ARTIFACT_TYPE_ID = -3
        AND thc3.CHANGETYPE_ID = 2  -- Delete
        AND thc3.CHANGE_DATE <= @snapshot_date
        AND thc3.PROJECT_ID = @project_id
  );

You should get something like this:

  • You will then see the preview of the output results with options to Load or Transform the data
  • Click Transform to modify the data before loading, since this query is able to filter the data using declared variables
  • Click Manage Parameters -> New Parameters in the Power Query Editor window:

  • Define two parameters:
    • project_id, choose Text for the type and set the Current Value for projectID like 1 or 24; 
    • snapshot_date, choose Text for the type and set the Current Value as 2024-05-19 00:01:00:000 or any other;
  • Click OK to apply changes
  • Click Close&Apply to get the data structure in the main PBI window
  • Choose the Table on visualization board and then columns to create a table with filtered data
  • Set to 'Don't summarize' for project_id and user_id, also set a Date for the date column so to see the data in common format
  • The table now is displaying the data filtered by the plain date and projectID specified as current value in parameters
  • Now go back to Transform Data -> Advanced Editor settings
  • Here we need to replace static values with the parameters as variables
  • Replace projectID and snapshot date with "&project_id&" and '"&snapshot_date&"' accordingly, so the final PowerQuery script should look like this:
let
    Source = Sql.Database("YOURSQLSERVERNAME", "SpiraPlan", [Query="#(lf)#(lf)DECLARE @project_id INT = "&project_id&";
    #(lf)DECLARE @snapshot_date DATETIME = '"&snapshot_date&"';#(lf)#(lf)
    --CREATE VIEW ActiveProjectUsers AS#(lf)#(lf)WITH MaxChangeDates AS (#(lf)    
    SELECT ARTIFACT_ID, MAX(CHANGE_DATE) AS MaxChangeDate#(lf)    FROM TST_HISTORY_CHANGESET#(lf)    
    WHERE ARTIFACT_TYPE_ID = -3  -- Filter on ARTIFACT_TYPE_ID#(lf)      
    AND CHANGE_DATE <= GETDATE () - 11#(lf)    
    GROUP BY ARTIFACT_ID#(lf))#(lf)SELECT thc.PROJECT_ID AS Project_id,
    #(lf)       thc.CHANGE_DATE AS Date,
    #(lf)       thc.ARTIFACT_ID AS User_id,
    #(lf)       thc.ARTIFACT_DESC AS username,
    #(lf)       (#(lf)           SELECT TOP 1 thd.NEW_VALUE#(lf)           
    FROM TST_HISTORY_CHANGESET thc2#(lf)           
    JOIN TST_HISTORY_DETAIL thd ON thc2.CHANGESET_ID = thd.CHANGESET_ID#(lf)           
    WHERE thc2.ARTIFACT_ID = thc.ARTIFACT_ID#(lf)             
    AND thc.ARTIFACT_TYPE_ID = -3#(lf)             
    AND thc2.CHANGE_DATE <= GETDATE () - 11#(lf)            
     AND thc2.CHANGETYPE_ID IN (1, 3) -- Modified or Added#(lf)             
     AND thc2.PROJECT_ID = "&project_id&"#(lf)           ORDER BY thc2.CHANGE_DATE DESC#(lf)       ) 
     AS Role_id#(lf)
     FROM TST_HISTORY_CHANGESET thc#(lf)JOIN MaxChangeDates mcd ON thc.ARTIFACT_ID = mcd.ARTIFACT_ID #(lf)                      
      AND thc.CHANGE_DATE = mcd.MaxChangeDate#(lf)WHERE thc.ARTIFACT_TYPE_ID = -3#(lf)  
    AND NOT EXISTS (#(lf)     
     SELECT 1#(lf)      
     FROM TST_HISTORY_CHANGESET thc3#(lf)      
     WHERE thc3.ARTIFACT_ID = thc.ARTIFACT_ID#(lf)        
     AND thc3.ARTIFACT_TYPE_ID = -3#(lf)       
      AND thc3.CHANGETYPE_ID = 2  -- Delete#(lf)        
      AND thc3.CHANGE_DATE <= GETDATE () - 11#(lf)        
      AND thc3.PROJECT_ID = "&project_id&"#(lf)  );", CommandTimeout=#duration(0, 0, 5, 0)])
in
    Source
  • Click Done, then Close&Apply to load the changes
  • Now, each time you'd like to get the results filtered for different products and dates you can just click on Edit Parameters and replace the values upon need:

 

Sample output data is displaying the list of users/roles which were a members of that product for particular date specified in parameter: