# Adding a Private AI Layer to an SSRS Report Estate > Source: https://netray.co/erp-help/private-ai-layer-ssrs-estate-on-prem-architecture > Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems. > Section: ERP Help ## Short answer An SSRS estate holds years of business logic in RDL files, shared datasets and subscriptions. A private AI layer can make that estate searchable (which report answers this question), run reports with parameters on the user's behalf, and summarise the output, all on your own servers while keeping SSRS folder security and data source credentials in force. ## Kind advanced ## System on-prem-apps ## Erp SQL Server Reporting Services (SSRS) ## Module ReportServer catalog / Report Server REST API / URL access ## Query how to add AI to SSRS reports on-prem ## Alternate Queries - chat with SSRS reports on premises - SSRS natural language report search local LLM - SSRS REST API AI assistant - make SSRS report estate searchable with AI ## Applies To SQL Server Reporting Services 2016 to 2022 native mode and Power BI Report Server; SharePoint-integrated mode needs a different approach ## Steps ### How to stand up a private AI layer on SSRS **Items:** - Inventory the catalog: count reports, shared datasets, data sources and subscriptions, and list the folders that matter. - Read RDL definitions from the catalog and parse them to extract report names, descriptions, parameters, dataset queries and field names. - Build a retrieval index over that metadata so a question maps to the right report and parameters. - Choose the execution path: REST API or URL access with the user's identity, or a service identity with parameter filtering where per-user execution is not possible. - Capture rendered output as CSV or Excel for data questions, and let the model summarise it; keep PDF only for human delivery. - Read ExecutionLog3 to learn which reports are used most and to seed the index with real parameter values and usage. - Serve an open-weight model locally, audit every run, and pilot with one department before extending. ## Sections ### Real data access paths in SSRS **Body:** SSRS stores its catalog in the ReportServer database. The Catalog table holds every item with its Path, Name, Type and Content (the RDL as binary), and related tables hold data sources, subscriptions, roles and policies. The ExecutionLog3 view reports who ran which report, with columns such as ItemPath, UserName, TimeStart, TimeDataRetrieval, TimeProcessing, TimeRendering, Status and RowCount. Catalog Type values distinguish folders, reports, resources, linked reports, data sources and shared datasets. For supported access, SSRS 2017 and later offer a REST API (version 2.0) for catalog items, and the older SOAP endpoint ReportService2010 remains available. URL access lets you render a report to a format such as CSV with parameters in the query string. Parsing RDL XML tells you which datasets, queries and parameters a report uses, which is what makes the estate searchable. **Code:** -- Most-used reports in the last 90 days (read-only, ReportServer database) SELECT TOP 25 ItemPath, COUNT(*) AS Runs, AVG(TimeDataRetrieval + TimeProcessing + TimeRendering) AS AvgMs FROM ExecutionLog3 WHERE TimeStart >= DATEADD(day, -90, GETDATE()) AND Status = 'rsSuccess' GROUP BY ItemPath ORDER BY Runs DESC; -- Render one report to CSV with a parameter (URL access, runs as the caller) https://rs-srv/ReportServer?/Sales/OpenOrders&rs:Command=Render&rs:Format=CSV&Region=West ### Reference architecture and air-gapped option **Body:** Place a metadata indexer beside the report server that periodically reads the catalog and builds a retrieval index of report descriptions, parameters and dataset text. A tool server exposes two or three actions: find_reports, run_report and summarise_result. An open-weight model served on local GPUs handles the language work, and an audit log records the question, the report chosen, the parameters and the identity used. Because everything stays on your network, an air-gapped variant needs only a controlled transfer for model files and packages. The model does not need direct database access: reports already encode your approved queries, which is a safety advantage over free-form SQL generation. ### Permissions and write-back **Body:** SSRS security is role assignments on folders and items, evaluated by the report server, plus whatever the data source enforces. If data sources use stored credentials, every user who can run the report sees the same underlying data, so the assistant must not expose a report to someone who lacks item permission. Prefer running reports with the user's Windows identity through Kerberos delegation, or check the user's item-level permission through the REST API before running as a service identity. There is no write-back to speak of: SSRS is an output system. The assistant may email or save a rendered report only through existing subscriptions or delivery paths, with the user's approval. Never let the assistant edit RDL files or change data sources. ### Sizing, rollout and honest limits **Body:** A catalog of a few hundred to a few thousand reports indexes into well under a gigabyte of metadata, so sizing is driven by the model and concurrent users: one inference GPU for an 8B to 32B class model usually covers 100 to 500 users at modest daily question rates. Phase one is report discovery, phase two is running reports with parameters and summarising CSV output, phase three is scheduled summaries. Honest limits: reports built around precise formatting, subreports or drillthrough do not always render to clean CSV. Parameters with dependent cascading values are hard to fill without user input. If users only need to find reports, a well-organised folder structure and search may suffice. And SSRS is reporting software, so questions that need live transactional logic are better served by an ERP-level layer. ## Pitfalls - Running every report with one service account and bypassing item-level role assignments. - Indexing only report titles. Dataset queries and parameter prompts carry the real meaning. - Expecting PDF output to be useful for the model. Use CSV or Excel renderings for data questions. - Ignoring ExecutionLog3 and indexing every report equally, which buries the ones people actually run. - Letting the model invent parameter values instead of choosing from defaults or available values. ## Ai Angle On an SSRS estate the assistant finds the right report, fills parameters from the question, runs it as the asking user and summarises the result, showing which report and parameters were used. Netray builds the indexer, tool server and local model as a private layer in your environment. ## Faq ### Can the model query the database directly instead of using reports? **Answer:** It can, but reports already contain approved queries and logic. Starting from reports is safer, easier to audit and reuses work that users trust. Add direct SQL tools only where reports do not exist. ### Does this work with Power BI Report Server? **Answer:** The catalog and REST API patterns are similar for paginated reports. Power BI content needs separate handling, so scope the first release to paginated RDL reports. ### How are subscriptions affected? **Answer:** They are not changed. The assistant reads subscription metadata to understand usage and can suggest creating one, but any creation or change should be approved by a person and done through supported APIs. ### What about SharePoint-integrated mode? **Answer:** That mode stores items in SharePoint rather than a native catalog, so the access paths differ. Most current estates use native mode; confirm yours before designing the indexer. ## Search terms SSRS AI assistant, chat with SSRS reports, SSRS REST API, SSRS ExecutionLog3, SSRS catalog RDL search, private AI SSRS, local LLM reporting services, SSRS URL access CSV, air-gapped SSRS AI, Power BI Report Server AI --- Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json