gl TABLE AND COMPANY ID

(0) Share

Report

Posted on by Romryan

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

I have the same question (0)

All responses (7)

Answers (1)

Suggested answer

soma 24,410 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Romryan on at

Like (0)

Report

Soma,

I just need to link my script for GL transactions to a table that has company id but on GL side I cant get cmpanyid or interid.

RomRyan

Was this reply helpful? Yes No
RogerRogerATX 1,515 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

soma 24,410 on at

Like (0)

Report

Get the company ID from GP system database table SY01500 and pass the inter id with using parameter query in ssrs report.

Hope this helps!!!

Was this reply helpful? Yes No
Romryan on at

Like (0)

Report

How can I join sy01500 to GL tables?

RomRyan

Was this reply helpful? Yes No
Verified answer

Mick Egan 3,561 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Romryan on at

Like (0)

Report

Yes MICK you killed it. This has unlocked my mindset. I've got an idea now. Big up!!!!

Was this reply helpful? Yes No