This recipe is the sequel to our team’s Data View Finder. Like data views, we have plenty of reports, dynamic data block pages, and Power BI reports in Rock. We wanted a tool that listed all those items in one place, with searchability. This recipe breaks down how we did that by utilizing a dynamic data block and some SQL code.

Report Finder
Purpose: Offer a means of searching for Rock Reports, Dynamic Reports, Dynamic Data Blocks, Power BI Reports, and other custom reporting tools in one location.
How: This Finder is built on a dynamic data block that uses a SQL query to find reports and pages with reports on them. Here’s the step-by-step process for building your own. (Disclaimer: Part of the query uses pages where we store reports by ministry or team. Depending on where you store your reporting tools, you may have to be a little creative with the code presented in this recipe to meet your unique set up and needs. Feel free to contact us if you have additional questions about this!)

1. Create a Page with a Dynamic Data Block
This tool is built on a dynamic data block, because it is looking at both Rock reports, as well as pages that house reporting tools. Our version of this tool uses only the one dynamic data block on the page, as the filter section of this block proved to be a sufficient searching feature. If you are inclined, this could also use a Page Parameters block if you’d prefer that more.

2. Craft the SQL Query
The query is broken up into two pieces, Rock reports and all other reporting tools (we called these ‘Static’ and ‘Dynamic’ in our version). The query also uses folder and page names to populate the Category column. It may be valuable to review your structures before building this. Comments have been added directly to the query.

    
SELECT p.[Id] -- to identify page 
   ,p.[PageTitle] -- title of page/reporting tool
   ,p.[Description]	  
   ,p.[ParentPageId] -- to identify parent page of the reporting tool
   ,p2.[InternalName] as 'Category' -- uses parent page name to classify Category
   ,'Dynamic Report' as 'Type' -- to differentiate from Rock reports
   ,CONVERT(varchar,p.Id) AS 'Link' -- to allow for navigation to the reporting tool's page
  FROM [dbo].[Page] p
  LEFT JOIN [Page] p2 ON p2.Id = p.ParentPageId -- links the parent page to the report page
  WHERE p.ParentPageId IN (1120, 1116, 1117, 1118, 1121, 1326) -- these are the pages on our dynamic reports menu that   store all of our reporting tools
UNION ALL
SELECT r.Id -- report ID
   ,r.Name -- report name
   ,r.Description
   ,r.CategoryId as 'ParentPageId' -- provides ID of the parent folder the report is in
   ,c.Name as 'Category' -- name of the report's parent folder
   ,'Static Report' as 'Type' -- to differentiate from other reporting tools
   ,CONCAT('149?ReportId=',r.Id) AS 'Link' -- to allow for navigation to the specific report
  FROM [Report] r
  LEFT JOIN [Category] c -- links report to its parent folder
  ON c.Id = r.CategoryId;

    

3. Update Formatting
To clean up the display, it is valuable to hide a few of the identifying columns. Setting up the rows to navigate to their respective reporting tools is also incredibly simple and easy. That screenshot is below.

4. Add Instructions
Help other users and future you out by adding very specific instructions as to how the tool work and what key words should be used when searching for reporting tools. We explained each of the four columns and how each can be searched. The final product is available below.

Now all the pieces are in place, and you can find all your reporting tools with one Report Finder. There are screenshots below that show our Dynamic Reports page and the Report Finder page. If you have questions about how we structure our reporting tools or anything else related to this, feel free to reach out!