External SSRS reports data source setup?

Epicor ERP 10
1

With the intent to create a new database to store custom procedures feeding information back to our external SSRS reports. I’m having a dilemma on how to link the data source/data set back to the E10 production database. I’m assuming most people probably went the route of storing there stored procedures in the E10 production database so the report server only needs one data source configured. But we wanted to keep all of the customizations separate from production. Has anyone found a way to only utilize shared data sources for there external SSRS reports?

*Looking at how other sites did it, they created a shared data source to reference the external database that holds the stored procedures. And inside each report they referenced the stored procedure, which in return the code specifies the SQL Database and the tables. I want to avoid this as having to change a large amount of stored procedures vs one shared data source would be ideal.

Example:

select PackNum, ShipDate
from E10_WP_HQ_PROD.Erp.ShipHead with (nolock)
where readytoinvoice <> 1 and company = ‘wp’
order by ShipDate

image

2

What I ended up doing to simplify this, was create a config table to reference in my stored procedures with dynamic SQL.

–create DB_CFG to your liking
declare @DBName varchar(max),@SQL varchar(max)
set @dbname = ‘BHFO’ --This will pull from your DB_CFG table instead of static

–Format your sql string
set @sql = 'select * from ’ + @DBName + ‘.dbo.order_header’
–Execute sql can actually add error handling around this as well. Like a try catch rollback, etc
EXEC (@sql)

2 Likes