SSRS - Nested Select Statement

Since I cannot reference the same table multiple times within RDD, I end up creating nested “Select” statement within my SQL Expression in my SSRS report. The only issue I am running into is that I have to put in the database name. We have multiple databases such as sandbox, dev, production, etc. Each time I deploy, I would have to constantly change the database name to match the correct database. Is there a way I don’t have to specify the database name?

Have you tried just using

[Erp].[PartRev]

without the DB name?

Because the database storing the temporary output data for the report is separate, you will inherently have to specify the other database’s name. That said, you could specify it in a view within the reports database that points to the desired environment (assuming they are in the same instance) for these tables.

Something as simple as this will create a view in my report data database pointing to the PartRev table in the ERP database:

USE [ERP10_ReportsDev]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[PartRev] AS
SELECT *
FROM [ERP10Test].[Erp].[PartRev]
GO

I have no idea what the performance will look like, but it should be a once-and-done setup since you never restore over that database. It’s also probably a good idea to put these views into a different schema so they don’t accidentally get picked up by an Epicor cleanup task.

I would suggest to maintain proper change control and make sure that the deployed report is pointing at the correct db that you use the freeware reportsync tool to move reports between SSRS instances and once synched you browse to the report within the SSRS console and click manage and change the data source.

Thanks all for the suggestions. I like the view idea, then just point it to that database/view and not touch it again.