SQL Query to use in MS SSRS server to give you your report structure.

Question

SQL Query to use in MS SSRS server to give you your report structure.

SSC Veteran

Points: 211

January 17, 2025 at 8:58 pm

Code that can be used in order to create dashboard or SSRS report on report statuses based on Report Structure that can be used to be filtered.

/*
Created By:wolfsvein
Create Date:2025-01-17
Details:Used to see distinct list of Report Folder Structure.

Revisions:
VersionByNotes
1.0wolfsveinCreated
*/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#tmp_Data') IS NOT NULL
DROP TABLE #tmp_Data

SELECT distinct
replace(path, '/' + Name, '') AS Path
INTO #tmp_Data
FROM Catalog
WHERE Type = 2
order BY Path

IF OBJECT_ID('tempdb..#tmp_Sort') IS NOT NULL
DROP TABLE #tmp_Sort

select DISTINCT Path, value, 
ROW_NUMBER() over (PARTITION BY Path ORDER BY Path) AS rownumber
INTO #tmp_Sort
from #tmp_Data
CROSS APPLY STRING_SPLIT(Path, '/')
WHERE value <> ''

IF OBJECT_ID('tempdb..#tmp_Filter') IS NOT NULL
DROP TABLE #tmp_Filter

SELECT Path,
   value,
   rownumber,  
   row_number() over (partition by value, rownumber order by path, value) AS RowValue
INTO #tmp_Filter 
FROM #tmp_Sort

IF OBJECT_ID('tempdb..#tmp_Final') IS NOT NULL
DROP TABLE #tmp_Final

SELECT Path,
   value,
   rownumber,
   RowValue
INTO #tmp_Final
FROM #tmp_Filter
WHERE rownumber >= rowvalue
ORDER BY Path, rownumber

SELECT Path,
   value,
   rownumber,
   iif(rownumber -1 = 0, '', REPLICATE('    ', rownumber -1) + CHAR(149)) + ' ' + value AS ReportStructure
FROM #tmp_Final
where RowValue = 1
ORDER BY Path, rownumber

January 18, 2025 at 5:42 am

This was removed by the editor as SPAM
January 29, 2025 at 6:01 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247037 More actions · Answer 1

While I'm sure that people appreciate the effort behind this blog post, this is not really the place for it. If you want to submit technical articles like this, I suggest you follow the 'Contribute' link at the bottom of the page.

Having said that, a few quick comments:

Is there really a need to specify dirty reads across the entire script?
Writing table names in queries without including their schema is considered bad practice and incurs a slight performance hit.
Modern syntax for temp table removal is DROP TABLE IF EXISTS #t.
Use of the double-dot notation (tempdb..#t) was always considered bad practice (as per point 2)
That's a lot of temp tables for a fairly simple set of queries. While I haven't tried, I'd expect to be able to optimise this to require fewer, or none.

Jeff Moden SSC Guru Points: 1004495 More actions · Answer 2

I agree... great post but it should be an article.

I also add...

6. While even Microsoft hasn't followed suit, NOT using semi-colon terminators has been deprecated since 2005 came out.

7. Both in real life and in articles, readers shouldn't actually have to read the code to determine the intent of each statement/query. Add some inline comments.

I also strongly agree about Phils #2 point (it's endeared by the name of "2 part naming convention) but with Temp Tables being an exception because they are exceptional.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)