query help 1

[deanni]

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?

 

[dfeller]

Have you looked at the File Layouts guide?

Dawn

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[BigLouie]

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.

 

[deanni]

thanks for the link. ill check it out

 

[deanni]

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.

 

[deanni]

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'

 

[dfeller]

Wrk files never contain data. They are just for printing.

For financial reports, you should use FRx.

Dawn

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[deanni]

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.

 

[BigLouie]

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.