AdvancedSQL Server Reporting Services (SSRS)ReportServer catalog / Report Server REST API / URL access

Adding a Private AI Layer to an SSRS Report Estate

Question
how to add AI to SSRS reports on-prem

Also searched as

  • 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

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.

Applies to: SQL Server Reporting Services 2016 to 2022 native mode and Power BI Report Server; SharePoint-integrated mode needs a different approach

How to stand up a private AI layer on SSRS

  1. 1Inventory the catalog: count reports, shared datasets, data sources and subscriptions, and list the folders that matter.
  2. 2Read RDL definitions from the catalog and parse them to extract report names, descriptions, parameters, dataset queries and field names.
  3. 3Build a retrieval index over that metadata so a question maps to the right report and parameters.
  4. 4Choose 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.
  5. 5Capture rendered output as CSV or Excel for data questions, and let the model summarise it; keep PDF only for human delivery.
  6. 6Read ExecutionLog3 to learn which reports are used most and to seed the index with real parameter values and usage.
  7. 7Serve an open-weight model locally, audit every run, and pilot with one department before extending.

Real data access paths in SSRS

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.

-- 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

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

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

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.

Try it here, free

SSRS Estate AI Assistant Sizing and Value Calculator

Open full tool

Size GPUs, report index and monthly value of a private AI assistant over an SSRS estate from users and question volume.

Common 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.

How an ERP-grounded AI assistant handles this

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.

See ERPray

Frequently asked questions

Can the model query the database directly instead of using reports?

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?

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?

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?

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.

Related

Advanced

Adding a Private AI Layer to Dynamics GP On-Prem

Dynamics GP is an easy system to read from and a delicate one to write to. Read from curated SQL views on a replica of the company database, search documents with a local retrieval index, serve an open-weight model on your own GPU, and send any write through eConnect or another supported integration after a person approves it.

Advanced

Adding a Private AI Layer to Dynamics NAV On-Prem

Dynamics NAV can feed a private AI assistant through two safe doors: a read-only SQL Server copy for analytical questions and published web services (OData and SOAP) for anything that must respect NAV logic and permissions. Put an open-weight model and a retrieval index beside them on your own hardware and keep writes behind human approval and codeunit-based services.

Error fix

SyteLine error: an error has occurred during report processing

This generic SSRS error in SyteLine usually comes from one of three sources: the client cannot reach the configured report server URL, the report's data source credentials or connection string are wrong, or the report definition itself has a parameter or dataset problem. Check the report server URL setting in SyteLine first, then confirm the report is actually deployed to that server, then look at the SSRS server's own execution log for the real underlying error.

How-to

Creating and selecting custom report layouts in Business Central

Business Central reports can render through a Word layout (simple, single-level documents like invoices) or an RDLC layout (complex, multi-level, grouped reports). Custom layouts are uploaded and assigned per report through the Report Layout Selection page, or shipped inside an AL extension with the rendering(...) property.

Advanced

How to add a private AI layer to Infor LX / BPCS on-prem

BPCS and Infor LX run on IBM i with their data in Db2 for i, in files that carry short coded names by module. A private AI layer reads those files through read-only SQL views over a replica, indexes documents and attachments separately, exposes approved queries through a tool server, and serves an open-weight model on local GPUs. Writes, if any, go through the supported transaction programs with human approval.

Advanced

How to add a private AI layer to Infor System21 on-prem

System21 runs on IBM i with its business data in Db2 for i. The practical private AI pattern is a read-only replica or extract, SQL views that give files readable names, a retrieval index over documents, a tool server of approved queries, and a locally served open-weight model. User visibility is carried over from System21 security and company set-up, and writes only go through its supported entry points after approval.

AI for ERP

AI Agents for ERP, Running On-Prem

A practical guide to on-prem AI agents for ERP: what they can safely automate, where human approval belongs, and how to design the guardrails.

AI for ERP

AI for Dynamics GP and NAV, Before the Business Central Decision

Dynamics GP mainstream support is ending and NAV is long retired from active development. Add a private LLM over GP or NAV data now, on-prem, without a forced Business Central move.

Stuck on SQL Server Reporting Services (SSRS)?

Talk to engineers who work inside SQL Server Reporting Services (SSRS) every week, and who build private AI that answers these questions from your own ERP data.

Talk to an ERP engineerBrowse ERP Help
All ERP Help articles

Keep exploring

Free tools

ERP fixes and how-tos

Guides

AI on your ERP

Integrations

Software