web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / querying GP for all li...
Microsoft Dynamics GP (Archived)

querying GP for all listed company

(0) ShareShare
ReportReport
Posted on by Joni J 425

hi all,

i'm new user of GP,  im doing my job as internal support, my company has 10 company registered on GP, 9 as subsidiaries and 1 as holding. i dont know the history of, but each company has different master data, coa and so on.

i wondering to querying all CoA master (Chart of Account) of all company (10), i dont have sql enterprise manager user id, i just have superuser authority on my GP.

i used to use sql manager to querying @ my former job, but now, i found it hard. i just do it one by one, extract all COA from all company, by login 1 company -> smartlist -> extracrt Coa -> export it to excel, and so on for 10 times.

my question is, can i querying all coa data of 10 company on one hit through this  system (GP)? i try it on smartlist, but still iv found it very hard.. i cannot write proper filter and choose any company..

appreciates all comment

thank you

*This post is locked for comments

I have the same question (0)
  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    As long as you are a user in GP that has access to all 10 companies you will be able to do this is SSMS. Create a script using the GL00100 and GL00105 tables and then preifx the select statement with the company database ID. For example:

    select [comp1]..[gl00100]

    select [comp2]..[gl00100]

    select [comp3]..[gl00100]

    etc and have one for each of your companies.

    From here you could get clever and copy them all into a temp table or whatever and do whatever is necessary.

  • Joni J Profile Picture
    Joni J 425 on at

    hi richard,

    thx for your prompt respond..

    but what is SSMS?  and whats the tools or menu to write those script on GP

    yes , i have access to all (10) company

    many thanks.

  • Joni J Profile Picture
    Joni J 425 on at

    sorry, did u mean, Sql Server Management Studio?

    i don't have it.

    and they didn't give me any authority for it (doesn't have and don't know "sa" password)

    i just have to solve and analyze any problem through the system it self (GP)

  • Suggested answer
    Richard Whaley Profile Picture
    Richard Whaley 25,195 on at

    It is not easy to reach other companies from Smart List.  Unless you have Smart List Builder, you would need to log into each company and pull a chart of accounts.  Then drop the list to Excel (include a company number in the spread sheet) and merge the 10 lists.  You can now sort that any way you want.

    I am would suggest linking the GL00100 as suggested above with GL00105.  Otherwise, the 100 table provides the segments of the account in separate fields that you will need to concatenate.  105 will give you an account number string field with the entire account (all segments plus dashes minus spaces).

  • Joni J Profile Picture
    Joni J 425 on at

    hlo sir,

    yes i have smart list builder,

    i'v try your suggest, linking gl 100 and 105,

    but i don't know what exactly you mean by :

    "you would need to log into each company and pull a chart of accounts"

    do i have to open 10 different application for all comp. and log on to?

    or there is log on menu on smart list menu..

    -newbie :)

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    If you have Smartlist Builder you can ask your IT staff to create a SQL view based on the SQL script I mentioned. Once the view is in place you can use SLB to accecss it.

  • 26P2ER Profile Picture
    26P2ER 1,775 on at

    With GP 2010 R2 and XL reports that get shipped with GP this is possible from GP client without having to use tools like SSMS etc.

  • Joni J Profile Picture
    Joni J 425 on at

    richard, thats the big problem,

    something wrong with the organization bla bla bla

    so i cannot request such your recommendation to IT staff..

    even i know it really possible, and i can write my own script

    :(

  • Joni J Profile Picture
    Joni J 425 on at

    hi  26P2ER

    thats out of my scope :D

    thx for your information

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at

    Are you allowed to apply scripts to your databases? If so, you could install SSMS on your local computer and just point to the server. You could do the same thing with MS Acess or Crystal Reports or any number of other reporting tools. If you cannot do that, then can you get a copy of the database and then copy it to your local computer and then run queries against that copy. Can you get then to export just the data you need to Excel or Access? Somehow we either get the data to your computer or you are given a tool with access to the server.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans