great plains report to be opened in access

[swabunn]

Great Plains 8:00
sql server

is there a way to transfer great plains report to access other then printing as tablimited and opening it in access because tabdelimited file not alingned properly

-swabunn

 

[jazgeek]

not really. You could re-allign the report so it prints a more friendly version for tab delimited files or even comma delimited.

However, if you're going to do that, why not write the query in sql yourself to give you the necessary data and export it that way.



-----------
and they wonder why they call it Great Pains!

jaz

 

[swabunn]

sql server 2000
great plains 8

Thanks for your reply, I am trying print historical stock status report by account to reconcile with GL. query for this report is complex and I tried it but unable to get all the relevant data. do you ever worked or have any query which could help me? we have sop,pop and BOM data for inventory records.

Thanks

Swabunn

 

[jazgeek]

from what I understand the historical stock status uses stored procedures to create temporary tables. perhaps what you could do is run the report, find the temp table and query on it whilst the report is still on screen... not sure it that would work but it'd be worth a try. You could get fancy and do a sql trace to find out what stored procedures are used to call them yourself in a query or job.

As to how the query is put together to do it by yourself... I'm afraid I can't help you there as we didn't use GP for production prior to us switching to another package.

-----------
and they wonder why they call it Great Pains!

jaz

 

[swabunn]

thanks for your suggestion for store procedures

 

[swabunn]

query on temp table didn't work while report was on the screen

thanks anyway

 

[winthropdc]

Even if you could indentify the name of the Temp table in the TempDB it would be a private table and not accessible from another SQL Session.

David Musgrave [MSFT]
Senior Development Consultant
Escalation Engineer - Great Plains
Microsoft Dynamics Support - Asia Pacific

Micorosoft Dynamics (formerly Microsoft Business Solutions)

http://www.microsoft.com/Dynamics

Any views contained within are my personal views and
not necessarily Microsoft Business Solutions policy.
This posting is provided "AS IS" with no warranties,
and confers no rights.

 

[jymm]

have you thought about picking through the stored procs for the company database? I noticed that with version 9 that they are moving more and more things into SQL stored procedures and functions.

given that figureing out what MS called something might be 95% of the battle, but I have had success scanning the DB for a field that I know if probably used in the stored proc (or function) and then checking out the code (Obviously do not scan for common things like dex_row - you will get more to sort through than you can stand). Then my Crystal report (or in your case Access DB) just has to run that proc (function) and deal with the output.

I use this when I am looking for a field - and partial names work, but again might get more than you can use (btw - even though it says 'table name' this also looks at store procedures/functions in 2005 - and maybe in 2000 too)

DECLARE @FindStr varchar(20)
-- Use this variable to hold the string 
--     your searching the column names for
SET @FindStr = '<put partial name in here>'
SELECT T1.name AS 'TABLE Name', 
T2.name AS 'Column Name', 
T3.name + '(' + convert(varchar(4), T3.xtype) + ')' AS 'Data Type', 
T2.id
FROM sysobjects AS T1, 
 syscolumns AS T2, 
 systypes AS T3
WHERE T1.id = T2.id
 AND T2.xtype = T3.xtype
 AND lower(T2.name) LIKE '%' + lower(@FindStr) + '%'
Order By T1.name

HAPPY HUNTING!