Snippet Name: SSRS_Reports_Without_Shared_DS

Tags:

Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:05

Dependencies:

DBA Notes:

Sample Output:

Title: SSRS Reports Without Shared DS
Author: user
in SSRS, is it posible to find any reprots that are not using a shared data source

Author: ChatGPT
Yes, in SQL Server Reporting Services (SSRS), you can identify reports that are **not using a shared data source** by querying the `Catalog` table in the `ReportServer` database. Reports using **embedded data sources** will have their data source definitions stored in the `DataSource` field within the `Catalog` table.

### Query to find reports that are NOT using a shared data source:
```sql
SELECT 
    c.ItemID,
    c.Path AS ReportPath,
    c.Name AS ReportName,
    ds.Name AS DataSourceName
FROM ReportServer.dbo.Catalog c
CROSS APPLY 
    (SELECT 
         ds.name 
     FROM 
         ReportServer.dbo.DataSource ds
     WHERE 
         ds.ItemID = c.ItemID) ds
WHERE c.Type = 2 -- Type 2 represents Reports
AND c.ItemID NOT IN 
    (SELECT DISTINCT ds.ItemID 
     FROM ReportServer.dbo.DataSource ds
     WHERE ds.Link IS NOT NULL) -- Linked data sources are shared
ORDER BY c.Path;
```

### Explanation:
- The `Catalog` table contains information about reports.
- The `DataSource` table contains data sources linked to reports.
- The `Link` column in `DataSource` is `NULL` for embedded data sources (i.e., not shared).
- We filter reports (`c.Type = 2`).
- We exclude reports that use a shared data source (`WHERE ds.Link IS NOT NULL`).

#### Result:
- The list of reports with **embedded (not shared) data sources**.

Let me know if you need modifications or further filtering! ????

Author: user
but that report woudl show if it was defined without a report, but the SSRS interface allows me to override that witha s hared data source

Author: ChatGPT
You're absolutely right! The SSRS interface allows overriding an **embedded data source** with a **shared data source** at runtime. To accurately identify reports **not using a shared data source**, we need to check the `DataSource` definition stored in the `ReportServer.dbo.Catalog` table.

### **Better Approach: Parsing the DataSource Definition**
Instead of just checking for `NULL` values in the `Link` column, we should inspect the **actual XML definition** stored in the `Content` field of the `Catalog` table. The embedded data source will be present inside the `Content` column as XML.

#### **Query to find reports NOT using a shared data source**
```sql
WITH XMLNAMESPACES 
(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
SELECT 
    c.Path AS ReportPath,
    c.Name AS ReportName,
    ds.value('(Name/text())[1]', 'NVARCHAR(255)') AS DataSourceName,
    ds.value('(DataSourceReference/text())[1]', 'NVARCHAR(255)') AS SharedDataSource
FROM ReportServer.dbo.Catalog c
CROSS APPLY c.Content.nodes('//DataSource') AS t(ds)
WHERE c.Type = 2  -- Reports
AND ds.exist('DataSourceReference') = 0  -- If there is no reference, it's embedded
ORDER BY c.Path;
```

### **Explanation:**
1. **Extracts the XML definition** of the report using `CROSS APPLY c.Content.nodes('//DataSource')`.
2. **Checks for `<DataSourceReference>`**:
   - If `<DataSourceReference>` is present ??? **Shared data source**.
   - If **absent** ??? **Embedded data source**.
3. **Filters only reports (`Type = 2`)**.

### **Why this works:**
- Even if a shared data source is assigned in the SSRS UI, the report's **actual definition** in the `Content` column still determines if it is embedded.
- This method ensures that only **truly embedded** data sources are flagged.

Let me know if you need any refinements! ????