Offshoot of this thread: 
Fun with SSRS - Troubleshooting Tools
I figured this one might warrant a thread by itself.
Have you ever just wanted a report of all of the tables and columns available in the dataset in SSRS? Trying to see if that RDD edit you made worked? Well now you have a new tool.
This report is completely generic. I will attach the report and show you how to roll your own.
How it’s made:
Create a blank report.
Add a Parameter to the report: TableGuid
Add a Data Source to the report: IHaveSecrets
Add a Dataset to the report: GiveMeYourSecrets
Choose Use a dataset embedded in my report, and then choose IHaveSecrets
Add the Query Expression:
="
SELECT
LEFT(T.TABLE_NAME, CHARINDEX('_', T.TABLE_NAME) - 1) AS [TableName],
C.COLUMN_NAME AS [ColumnName],
C.DATA_TYPE AS [DataType],
C.ORDINAL_POSITION AS [ColumnOrder]
FROM
INFORMATION_SCHEMA.TABLES T
JOIN
INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
WHERE
T.TABLE_NAME LIKE '%" + Parameters!TableGuid.Value + "'
ORDER BY
T.TABLE_NAME, C.ORDINAL_POSITION;
"
Add the fields:
Then add a table to the report and adjust accordingly:
Save the report somewhere like MyDocuments\Reports\CustomReports\ShowMeYourSecrets\ShowMeYourSecrets.rdl
You are done with the report.
How to use it:
Open report style maintenance and select a report.
Create a new report style.
ShowMeYourSecretsSQL Server ReportingReports/CustomReports/ShowMeYourSecrets/ShowMeYourSecretsYour report is now ready for use. Choose that style to see all the fields.
ShowMeYourSecrets.zip (2.2 KB)
Thanks @Mark_Wonsil for the question and idea.
Also, if you upload it once, there is no need to reupload it for each report style.
Just point any new report styles to the new location:
Reports/CustomReports/ShowMeYourSecrets/ShowMeYourSecrets
You can do this on cloud? Just clarifying for cloud users.
He IS a cloud user.
Wild Mark
Why certainly.
It’s not dangerous. Some of Epicor’s own reports use this same technique.
PackSlip: (ReportImages Dataset)
="
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ReportImages_" + Parameters!TableGuid.Value + "'))
BEGIN
SELECT * FROM ReportImages_" + Parameters!TableGuid.Value + "
END ELSE BEGIN SELECT 1 WHERE 1 = 0 END
"
I’m having trouble opening this thread at work in our open office layout… many GIFs with interesting material.
Yes, that’s why I love the code section of their reports and also the dataset queries. Wonder just how much you can get at using those queries…
All you can see are the tables that Epicor dumps there.
Okay
Those aren’t necessarily the same thing when it’s arbitrary SQL. It’s worth taking a moment to query the executing account’s credentials in the same way and holler at your DBA if there’s anything more interesting than SELECT.
That’s what I was getting at.
This is fantastic, but you know what would be even more magical is if it showed you the value in each field too.
Well, you certainly could, but that could very well be 100s or even thousands of pages.
If you really want it, I’ll post template instructions you can modify per table.
How does that sound?
Sure!
Don’t know if anyone misses the crystal functionality where it showed you if a field was used in a report, but I wrote this c# console app (with the help of AI) in visual studio to parse through the xml of an RDL and output the unique fields in a csv file. You just need to download your .rdl from the server and then change the file extension from .rdl to .xml and then put the path to your file in the code below. It will then output a csv file of all the unique fields that are being used. An actual developer would be able to pretty this up and make it more consumable for anyone and everyone, but here’s the code:
using System;
using System.Collections.Generic;
using System.IO;
using System.Text.RegularExpressions;
using System.Xml;
namespace ConsoleApp44
{
class Program
{
static void Main(string[] args)
{
string xmlFilePath = @"C:\Users\asdfadfasfd\Desktop\a. Current Desktop\GF_PackLabel_Blind.xml"; // Replace with your XML file path
// Load the XML document
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlFilePath);
// Create a namespace manager
XmlNamespaceManager nsManager = new XmlNamespaceManager(xmlDoc.NameTable);
nsManager.AddNamespace("ns", "http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition");
nsManager.AddNamespace("rd", "http://schemas.microsoft.com/SQLServer/reporting/reportdesigner");
// Define the pattern to match the desired format
string pattern = @"Fields!([\w\d_]+)\.Value";
// Find all nodes with text content
XmlNodeList textNodes = xmlDoc.SelectNodes("//text()", nsManager);
HashSet<string> uniqueFields = new HashSet<string>();
foreach (XmlNode textNode in textNodes)
{
MatchCollection matches = Regex.Matches(textNode.InnerText, pattern);
foreach (Match match in matches)
{
uniqueFields.Add(match.Groups[1].Value);
}
}
// Write unique fields to a CSV file
using (StreamWriter writer = new StreamWriter("fields.csv"))
{
foreach (string field in uniqueFields)
{
writer.WriteLine(field);
}
}
Console.WriteLine($"Unique fields written to fields.csv.");
}
}
}
Very nice.
No,
Maybe ask the ChatBot for a PowerShell version? Might be easier for No-Coders in the group… 
