You’re offline. This is a read only version of the page.
Dear All,
How can I link my GL queries to CompanyID, such that if I put company id parameter I can print any report from selected company.
I don't want to create 30 reports.
Regards,
RomRyan
*This post is locked for comments
Romryan,
Something like this maybe:
SELECT a.JRNENTRY, b.INTERID FROM TWO.dbo.GL20000 a
CROSS JOIN
DYNAMICS.dbo.SY01500 b WHERE b.INTERID = 'TWO'
UNION ALL
SELECT a.JRNENTRY, b.INTERID FROM THREE.dbo.GL20000 a
CROSS JOIN
DYNAMICS.dbo.SY01500 b WHERE b.INTERID = 'THREE'
If you installed the Analysis Cubes, you could just use the GLTransactions Table in the DynamicsGPWarehouse Database.
Regards,
Mick
RomRyan,
I usually use a cursor to go through the companies* in the DYNAMICS..SY01500. You could convert your script to an sproc that is dynamic (see the example above) that will insert the db name to the tables and execute it after it gets the dbname.
and the dbname could be a parameter in SSRS so the user can pull that first, and then the sproc would run with that company selected.
* our business need has multiple cursors/groups of companies so it's more complex but it still works
Use the code like below.
CREATE PROCEDURE VENDOR_MSTR @INTERID VARCHAR(MAX)
AS
BEGIN
DECLARE @SQLREC NVARCHAR(MAX)
SET @SQLREC = ''
SET @SQLREC = 'SELECT * from '+@INTERID+'..PM00200'
EXECUTE sp_executesql @SQLREC
END
Note: Instead of table PM00200 you can use SQL view
Hope this helps!!!
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (