×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

query help

query help

query help

(OP)
I'm new to the MAS 90 data structure and use access to look at the data. Does any one have a simple query that summarizes the GL? I would like to extract a useful datacube to a  MS SQL server and use a report writer. I am having trouble understanding where and how the data is stored and linked together. Are there any good resources?

RE: query help

IF you are a new user then you should try to take the Data Files class. Also you should start posting on the new Sage Community. Dawn has a link in her post.  Need to know what version of MAS you are on so I can tell you where to look.

RE: query help

(OP)
thanks for the link. ill check it out

RE: query help

(OP)
I'm using MAS 90 4.2. The crystal financial reports are using a table named "GL_FinancialReportDetailWrk" to pull there information, but there is no data when i access them. I imagine MAS populates the table spits out the report and then clears them out. Is there a way to populate the table or recreate it with sql query?
Thanks in  advance for your help.

RE: query help

(OP)
Here is the actual query the reports uses, but if it is ran outside of mas nothing appears.

SELECT
"GL_FinancialReportHeaderWrk"."ReportTitle",
"GL_FinancialReportHeaderWrk"."ReportTitle2",
"GL_FinancialReportHeaderWrk"."ReportTitle3",
"GL_FinancialReportHeaderWrk"."ReportTitle4",
"GL_FinancialReportDetailWrk"."AccountDesc",
"GL_FinancialReportDetailWrk"."ValueColumn01",
"GL_FinancialReportDetailWrk"."InNetIncomeBeforeTax",
"GL_FinancialReportDetailWrk"."InNetIncomeFromOp",
"GL_FinancialReportDetailWrk"."InGrossProfit",
"GL_FinancialReportDetailWrk"."AccountGroup",
"GL_FinancialReportDetailWrk"."ReportSequenceNo",
"GL_FinancialReportDetailWrk"."PrintGroup",
"GL_FinancialReportDetailWrk"."ReportPrintSequence",
"GL_FinancialReportDetailWrk"."Account",
"GL_FinancialReportHeaderWrk"."PrintAccountNo",
"GL_FinancialReportDetailWrk"."AccountGroupDesc",
"GL_FinancialReportHeaderWrk"."FormatColumn02",
"GL_FinancialReportHeaderWrk"."DollarSignType",
"GL_FinancialReportHeaderWrk"."PercentSymbol",
"GL_FinancialReportHeaderWrk"."NegativeValueFormat",
"GL_FinancialReportHeaderWrk"."DecimalSeparator",
"GL_FinancialReportHeaderWrk"."ThousandSeparator",
"GL_FinancialReportHeaderWrk"."FormatColumn01",
"GL_FinancialReportDetailWrk"."MainAccountCode",
"GL_FinancialReportHeaderWrk"."SummaryType",
"GL_FinancialReportDetailWrk"."MainAccountDesc",
"GL_FinancialReportDetailWrk"."ValueColumn02",
"GL_FinancialReportHeaderWrk"."FormatColumn03",
"GL_FinancialReportHeaderWrk"."FormatColumn04",
"GL_FinancialReportHeaderWrk"."CenterFootnote",
"GL_FinancialReportHeaderWrk"."HeadingColumn01",
"GL_FinancialReportHeaderWrk"."HeadingColumn02",
"GL_FinancialReportHeaderWrk"."HeadingColumn03",
"GL_FinancialReportHeaderWrk"."HeadingColumn04",
"GL_FinancialReportHeaderWrk"."Heading2Column01",
"GL_FinancialReportHeaderWrk"."Heading2Column02",
"GL_FinancialReportHeaderWrk"."Heading2Column03",
"GL_FinancialReportHeaderWrk"."Heading2Column04",
"GL_FinancialReportHeaderWrk"."Column03Calculated",
"GL_FinancialReportDetailWrk"."ValueColumn03",
"GL_FinancialReportHeaderWrk"."FootnoteOption",
"GL_FinancialReportHeaderWrk"."FirstAmountOnly",
"GL_FinancialReportHeaderWrk"."FootnoteText"

FROM   
"GL_FinancialReportDetailWrk" "GL_FinancialReportDetailWrk",
"GL_FinancialReportHeaderWrk" "GL_FinancialReportHeaderWrk"

WHERE  

("GL_FinancialReportDetailWrk"."ReportSequenceNo"="GL_FinancialReportHeaderWrk"."ReportSequenceNo") AND "GL_FinancialReportDetailWrk"."ReportSequenceNo"='00000000000002'
 

RE: query help

(OP)
The problem is; we have several companies and i need to do consolidated reporting. FRx still mounts each db independently. I am trying to extract the financial data into a consolidated database and have Frx, crystal or some reporting software mount the consolidated db. So, that is why i'm trying to find a gl summary of something. I didn't think it would be that difficult, but jeeeeeeeez. I've never seen a data structure so spread out. I have worked with oracle, teradata, mysql, and ms sql and really didn't think it would be that big of a deal. if i could recreate the GL_FinancialReportDetailWrk table or some other GL summary that would be perfect. any thoughts or am i going about this the wrong way. Sage said their is a way with FRx, but it seemed very confined it what it could do and i am not a big fan of FRx. its clunky.

RE: query help

Here are a few thoughts:

1. You should be posting on the Sage Community. See the link in Dawn's post. It is free and open to the public. Sage employees post as well as any number of end users and reseller.
2. Add the databases you mentioned are fine databases but they are not an integrated accounting system. Once you have taken classes and learned the MAS data structure it makes a lot more sense and is pretty easy to figure out.
3. You can actually extract the data from the works table. It's explained over on the Sage Community. When the report is run another table is created with a time stamp. To extract the data you first preview the report then go find the GL_FinancialReportDetailWrk and rename it for later, then find the GL_FinancialReportDetailWrk with the time stamp. It will look something like this: GL_DailyTransactionRegisterWrkBRL011311372385.M4T
Delete the BRL011311372385 part. You can now extract the data using ODBC. When finished delete the works take with data and rename the empty one to prevent printing problems (you always want to start with an empty works table.)
4. See you over on the Sage Community.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close