Drill-Down Crystal Report From Macola Data

[ZulumanatSPI]

Ok, this problem can probably fall under several different categories, but since I'm specifically using Macola, I will post it here.
I use Crystal Decisions to print various reports based on the BTrieve data in Macola. One thing I have not been able to do, however, is drill-down a Bill Of Material (BOM)and print all item numbers associated with a parent item. I can generate a single level BOM in Crystal, but cannot figure out the drill-down. I have now resorted to trying to teach myself Visual Basic because I believe it can be achieved that way...But then there's a whole bunch of other stuff I gotta learn in the proccess......Any ideas????
(What I'm really trying to achieve is an Indented Bill Of Material via a Crystal Report.)

 

[dgillz]

Zuluman-

I am not sure this can be done via Crystal.

I did this once in an environment where the boms where a maximum of 2 levels deep. I had to use a subreport, with the component of the main report linked to the parent of the subreport, which called the next level. My results were that I had the top level and next level from the main report and a third level from the subreport.

Since a subreport cannot call another subreport, this is as far as it can go. I have had version 8.5 for about 5 days so if 85 addresses this great. If not, Crystal Decisions - if your listening, we need this!

I also looked into heirarchical grouping (a new feature in version 8.5) but that got me a where used report, not a multi level BOM.

Given all that, I would use pervasive ODBC and link tables in MS Access and have a query that recurses itself as many times as need be, then write the report in Access. The application could read the maximum of Low Level Code from the IMITMIDX file to determine the number of times it needs to recurse.

If you have any questions, please let me know. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[ZulumanatSPI]

dgillz,
Thanks for your response. I too was able to do a subreport and drill down one level, but go no further. As you said, no subreport of a subreport...I'm surprised Crystal has not done that yet. I believe our lowest level code is 6 or 7 right now...So there would be some drilling down to do.
As far as the MS Access goes, although my abilities are limited, I can try to play with that for a while. Incidently, I was not able to find a table named IMITMIDX, but I know the Low Level Code can be found in IMITMFIL. However, I'm not sure my recurssion rate would be based on Low Level Code.... It's complicated because any one of my "Master Parent Items" may have 0, 1, 2, or any number of Sub Assembly Component Item Numbers that may themselves act as a parent item to 1 or more sub assembly component itmes...and so forth and so on.
We had a programmer wirte a program that could do the drill-down...I believe he wrote the program in Visual Basic and passed the info onto a Crystal report. But I want to learn it for myself so I can write as many different programs as I want using drill-down into a BOM.
If in the end I cannot accomplish my goal, maybe we can talk about you writing the program for us.....With proper compensation of course

Thanks again......

 

[dgillz]

Zuluman,

My apologies...I was assumming you are using macola Version 7.5x. If it is in IMITMFIL then you are on version 6.x

Trust me, hang your hat on it, the low level code IS the indicator of how many times you need to recurse. (remember the top level is level zero). Low level code is a standard convention of how this is done thoughout the ERP world and has nothing to do with Macola.

I still have version 6 installed on my system at home. If you run into any problems, let em know and I will try to help.

By the way where are you located? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[ZulumanatSPI]

dgillz,
You are absolutely correct.....We are using Macola V6.72b...In good old DOS. And I'm gonna trust you on that Low Level Code recurssion. Actually, it never occurred to me to think of it in those terms. But if that is the case, and since Low Level Code 11 is the lowest level (as far as I can tell), wouldn't one just set it to 11 recurssions and cover all bases?
We are located in Ronkonkoma, New York....On Long Island....Very close to Islip airport...But not close enough to Florida for me

 

[dgillz]

11 levels is correct for Macola and that would cover all bases. If you are interested in some help with the report, email me at dgilsdorf@altavista.com

The history of a low level code is interesting. Going back to the 70s when an MRP or capcity regeneration, or a cost roll-up was run, it could literally take 3 or 4 days to execute.

The low level code reset program would go through the entire BOM file and determine for each item the absolute lowest level it EVER apprears in, and store that value in the low level code field of the item master.

Then when one of the prgrams I mentioned was run, the program code would use the LL Code to see how deep to look for requirements of each component, and to look no deeper. If it were not for the LL code, the program could go looking through dozens of levels of BOM for no reason (imagine you are building 747s here and not plastic parcheesee game board pieces). It was a time savings device, that was needed a lot more then than now. Like I said, virtually every ERP package has this.

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[dgillz]

Zuluman-

I ran across a solution to this right before Xmas but its gonna hurt. Use aliases of the BMPRDSTR file, attached from component level 0 to parent level 1, component level 1 to Parent level 2, etc. Do this for as many levels as needed.

Create details b,c,d, etc., one for each level of the report. place level 0 in details a, level 1 (data drawn from alias 1) in details b, level 2 in details c, etc. Format each of these sections to suppress a blank section to get rid of white space.

Thats the report. The bad news is that if you want, say item descriptions on the report, you have to have as many instances of the IMITMFIL as you do levels of the BOM - one for the top parent level, and pne for each level of components. The same is true of IMINVLOC data such as Qty on hand, Qty on Order, etc. In short this could easily baloon to a report requiring 20 or more tables.

If you have never worked with aliases let me know, its very straight forward.

Let me know your thoughts. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[ZulumanatSPI]

dgillz,
I've been playing for a few minutes with this approach and I can see where you're coming from. I know how to work with the aliases. But it's working with the multiple detail sections that a killer. For instance....
Let's say I do a single level bill of material with 1 detail section. No problem. Now, let's say one of the component items is itself a parent. I want to alias that component item to be a parent item on an aliased table...Got that. Now I want to print its bill of material into Detail b....But I want this Detail b to start on a new page...not fall directly under Detail a and not show until all the Detail a's are shown. Then I want Detail c to be a single level BOM for a parent item of Detail b, but start on a new page and not show until all Detail b's are listed. Is this a do-able thing?