MAS200/Crystal question 1

[acent]

Greetings y'all!

The bigwigs at my company finally decided to stop dragging their knuckles and start walking upright by starting to challange the reporting features of MAS! This is a good thing, except for the fact that I have now been asked to start generate these reports from a database that I know basically nothing about, and they don't want to spend any money training me....

My first task to so get a report of the difference between the PO date, and the date that the items were recieved, averaged by month. I have years of experience with MS Access reports, though this will be my first undertaking with Crystal.

I believe we are using MAS200 3.71. Any help locating the appropriate tables to use, and how to generate this report is greatly appreciated.

Thanks,
Andrew

"If you say you can, or you say you can't, you're right!"
-- Henry Ford

 

[Blorf]

Hi.

If you have Access Skills, why not do the reports in Access? I do lots of reporting with Access - Granted, does not bolt on to a menu in Mas, but works for me nicely.

The Tables are pretty clear in definition, so if you are a d/b person, you won't really have any problem.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[acent]

Thanks for the reply Blorf. The tables do seem somewhat clear. I found the PO5_RecieptEntryHeader file and thought that was where recieving was kept, however, it seems that is just a file for reciept batches, and not the actual recieving.

Rather than go though every file, I was hoping someone might point me in the right direction for connecting the dots.

Thanks,
Andrew

"If you say you can, or you say you can't, you're right!"
-- Henry Ford

 

[Blorf]

I see. Well, Monday morning, I will provide the tables you need. It's pretty clear once you see it. I don't have the data with me at home.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[BigLouie]

Find the TRSG, Technical Reference and Support Guide. It is a CD that all users receive with their install. It will list all tables and fields. Basicaly in MAS you have data entry, history and summary tables.

 

[GKChesterton]

I am in the same position as Acent, although not sure my bigwigs have got knuckles fully clear of ground surface.

I have the TRSG now (I think Big Louie helped me get pointed there). But the file descriptions consist of PDFs, accessed through a poorly-designed-yet-illegible menu, jimmied to not print, and ill-suited in the end for query-building.

I got around the print issue (printed them to PDF using PrimoPDF, then printed from the copy). Whoopee doo -- more personal satisfaction than usable information.

I am now applying for "BOSS" online help in the hope the info portals are more robust.

IS THIS HOPE VALID?

Any chance of getting to a chart similar to the Access relationships table?

Or of discovering the real location of extended descriptions that go more than thirty characters?

Because if not, I will join the many others here in advocating a complete replacement for MAS 90 in our operation.

[purple]If we knew what it was we were doing, it would not be called
[blue]research[/blue] database[white].[/white]development, would it? [tab]-- Albert Einstein[/purple]​

 

[BigLouie]

If you go into the TRSG you will find the file layouts. Just select the module and then file and print out. I do it all the time. It will give you the field names. The real key is understanding the files. There are master files such as Customer Master and Inventory Master. History files such as ARN and ARO the invoice history files. Invoice files generally have header and detail files. There are summary files such as Item, Warehouse Sales History where have totals by month and year. There is no chart similar to the Access Relationships Table for MAS. Your best bet is to attend the Data Files class .

If you can tell me what you are trying to report on I'll let you know what tables to use.

 

[GKChesterton]

The particular need is to be able to print reports that have the ExtendedDescriptions, both for Inv Master Items and SO line items.

The table [blue]SO_05SOExtdItemDescription[/blue] is promising, but the field will contain something like:

[blue]power supply,12vdc,200mA,Stancor,STA-300R[/blue][red]~;~[/red]

Now how to get to the whole entry?

By the way, I see your advice elsewhere to use SQL-Specific Pass-Through Queries from Access. Will try to apply that. THANKS!

[purple]If we knew what it was we were doing, it would not be called
[blue]research[/blue] database[white].[/white]development, would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Also, I am still keen to learn if this is a valid hope:

I am now applying for "BOSS" online help in the hope the info portals are more robust [than the TRSG].

[purple]If we knew what it was we were doing, it would not be called
research [blue]database[white].[/white]development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[BigLouie]

The SO_05 is the sales order line history extended description file. It has two file types in it. Header which tells you number of characters and the text which has the text split into segments of 50 characters each. The end of each line has a ~;~. The real trick is to combine the files by segment and strip out ~;~. This is detailed in the manual. This is how to use the IM_03 but the file layout is exactly the same with the SO_05 so you can just subtitute one for the other.

1 On the Insert menu, click Subreport.
2 At the Report Name field, type the title of the subreport, such as
Extended Description.
3 Click Report Expert.
4 Click Database.
5 Expand the ODBC folder and expand the SOTAMAS90 (ODBC) or
MAS_REPORTS (SQL) directory.
6 Log in to the database.
7 Click IM_03ItemEndedDescription to select the table and click
Add.
8 Click Close.
9 Click the Fields tab.
10 Click Add. This adds IM_03ItemEndedDescription.ItemNumber
to the report fields (the ItemNumber field is selected by default in Database fields).
11 Click OK.
12 Click the Link tab.
13 In the Available Fields list box, locate the ItemNumber field in the
IM_22 file, and click the > button to add then click OK.
14 Click OK; the cursor is now loaded.
15 Drag the subreport into the appropriate details section where the
data is to print, and click to drop it in.
16 Right click the subreport and then click Edit Subreport.
17 Delete the ItemNumber field.
18 On the Insert menu, click Group.
19 Select the IM_03ItemEndedDescription.ItemNumber field (this
field normally appears by default) and click OK.
20 On the Insert menu, click SQL Expression Field.
21 Click the New button and type a title at the New Name field.
22 Click OK to launch the SQL Expression Editor.
23 Type the following formula:
{fn REPLACE(IM_03ItemEndedDescription."CommentText",'~;~',{fn
CHAR(13)})}
24 Insert the SQL expression above into the Details section.
25 Suppress all other sections except Details.
26 On the Format menu, click Section.
27 Click Details.
28 Next to Suppress (No Drill Down), click the x+2 Formula Editor
button.
29 Enter the formula {IM_03_ItemEndedDescr.SEQNumber}="000".
30 Save and then close the Formula Editor.
31 Click OK.
32 Click the Design tab.
33 Right click the subreport and click Change Border.
34 In the Line style, change Left, Right, Top, and Bottom to None (this
removes the box/outline on your extended description), and click
OK.
35 Close and save the report/label.

 

[BigLouie]

The on-line help is great for problem solving. The information from the TRSG is there but in same format.