Reporting API process reviews

Badge +2

Hi all

I am trialling the new reporting API with the excel template and have noticed that archived processes are pulling through in the process reviews table. Does anyone know a way to exclude these from reporting?


Best answer by Penny 11 May 2022, 04:24

View original

3 replies

Hi Anna, 

Thanks for post about this, it is something that we will need to resolve, so these are excluded from the API.  Can you send this through to the support ( team (feel free to reference this post in the case) so we can get this in front of the dev team.  


Hi Anna,

To edit the data so that the archived process reviews were not showing I added the 'State' column from the Processes table into the ProcessReviews table in Power Query Editor. Once the 'State' column was added to the Process Reviews table I could then filter out everything in the 'Archived' state. 

To add the column I used the 'Merge queries' option in Power Query Editor - here is a link that shows the steps for this: Merge queries (Power Query) ( I used the 'UniqueId' column as the matching column for both. Using this method will mean you can filter the archived data in Power Query so it doesn't show in the spreadsheet at all. 


Alternatively if Power Query is something you're not familiar with you could add a column to the ProcessReviews sheet which does a COUNTIF of the UniqueId column against the UniqueId column in the ActiveProcesses sheet (formula of "=COUNTIF([@UniqueId],ActiveProcesses[@UniqueId])" if you're using the template). If the formula comes back with a value of 0 you can filter it out as this means it is not an Active process (i.e. Archived). This method will work but will mean that the Archived processes show up again if the filter is cleared. 

Badge +2
Thanks so much, I ran the merge process and appears to have worked 🙂