Report/Subreport Shared Variable 1

[biobrian]

I am relatively new to Crystal Reports, so the report I am trying to do might be a little over my head or not possible. Currently using Crystal reports Ver. 8, with an ODBC connection. I currently have one main report with two tables linked giving me part numbers that are on order, and quantities on order by a date range selection. It looks like this:

Part Number Quantity
Widgets 100
Etc.

Most of the part numbers have components to them, (3-4 levels of indented bills of material). What I would ultimately like to do is “drill down” and get the component part numbers and quantity per bill. I have created a sub-report in the main report, using a table that has the part number, component part number, and quantity per bill.

In the main report (details section) I have the formula for shared variables:
WHILEPRINTINGRECORDS;
SHARED STRINGVAR part:={table.field}

In the sub-report (report header) I have the formula for the shared variable:
WHILEPRINTINGRECORDS;
SHARED STRINGVAR part

After running the report, the sub-report gave me all the “level 1” component part numbers and quantities used per bill. This is where I am stuck. Some of these part numbers have other components to them. I need to “drill down” to the next level, and perhaps even the next level, and “flatten out” the bill of materials. Any help would be appreciated.

 

[lbass]

You haven't explained the purpose of the shared variable. The main report should be grouped on part, with the subreport in the group header or footer section and linked to the main report on part number. Have you done this?

I think you could put the subreport in the group footer section and then hide it so that it becomes visible on drilldown of the group header.

-LB

 

[biobrian]

The main report is grouped by part. I used the shared variable because of the problem of the subreport picking up only the last field in details as a value, when linking the part numbers. I think I have a problem that I will not be able to overcome. The main report is driven by two joined tables (Inventory masterfile and sales order detail file). The part number I am linking in the inventory masterfile and the part number in the subreport(bill of material file) are not linkable(different string length).

thanks for your help. looks like I will have to go about this the way I am doing it now, a lot of data entry

 

[lbass]

This is probably still doable. Are both part numbers of string datatype? It is possible to do a formula limiting one of the part numbers and then link the formula to the corresponding part number. If you provide more particulars, you might get this to work fairly easily.

I still don't understand how you were hoping to use the shared variable to address the problem you were having.

-LB

 

[biobrian]

As I said before, I am relatively new to using crystal reports, and from browsing some of the topics here, it seemed you can pass values from the sub-report to the main report or vice versa using shared variables. This looked like it might work for me, although seeing some of the data I am getting, I need to take a different approach.

First I am trying to get a report to print out all the levels of the bill of materials for a select few part numbers correctly before linking or adding a subreport to the main report.

I currently have the main part number and revision grouped with the "1st" level of components in that group. One problem is there are different revisions for each main part number:

Part 1
Rev 000
Component
Component
Component

Part 2
Rev 000
Component
Component
Component

Part 2
Rev 001
Component
Component
Component

Any suggestions on how to only select the "latest revision level". For Part 1 I would want Rev 000 but for Part 2 I would want Rev 001

 

[lbass]

You are now asking a different question. If you have a group on part, go to report->edit selection formula->GROUP and enter:

{table.revisionno} = maximum({table.revisionno}, {table.part})

...substituting your correct table and field names. This assumes that the more recent revision number is higher.

If you want to address the subreport linking issue, you need to report back on the data type of the part numbers in the main report and in the subreport, and show samples of each that represent all possible variations, if the length can differ, etc.

-LB

 

[biobrian]

Thanks for the help on the maximum formula. I was trying that out, just had a little trouble on the format.

On the main report I have a grouping of the part numbers(string, length of 15) and a number(number), driven from sales and inventory tables.

P/N Qty
FinPart 1 50
FinPart 2 100
FinPart 3 150

On the second report(subreport) I now have a grouping part numbers(string, length of 20), and thanks to you, showing only the latest revision, with the first level of components(string, length of 20) driven from a bill of materials table.

FinPart1
Rev000
Component
Component
Component
FinPart2
Rev001
Component
Component
Component

I need to get the quantities in the main report into the subreports matching part numbers.

 

[synapsevampire]

Try posting specifics:

Where and how are the quantites in the main report generated?

Where is the subreport? Rather than call it a second report, state that the subreport is in the <whatever> section.

To pass quantities from the main report to the subreport for a part, try the following:

shared numbervar TheCount;
TheCount:=sum({table.qty},{table.part})

Reference this formula in the subreport AFTER this new formula has executed in the main report as follows:

shared numbervar TheCount

-k

 

[biobrian]

The main report is a report I am using currently to find items on order minus items on hand. So the quantities of the main report are generated by a formula, taking the order quantity and subtracting quantity shipped and quantity on hand.

I am wanted to get the subreport to work on its own before starting the process of linking/shared variables that seem to be the answer.

Looking at it closer I am thinking the report with quantities should be the subreport now, passing the quantities to the bill of material report.

 

[lbass]

You still have to figure out the linking first. Please show us samples of your part number fields and how they can vary--your sample above makes it seem like they always match. If one is 20 in length, how will that match with the one that is 15 in length? Do the first 15 characters of the 20-length field match the 15-character field? Or is the last 15 characters or what?

-LB

 

[biobrian]

The part number fields are the same in both reports in respect to the actual part number. The bill of material part number is exactly the same as the sales order part number. They are just on different tables. I have tried to link them in the same report but it does not work.
Why they are different lengths, I am not sure. I got the string length information from File Layouts in TRSG Site map.

The first 15 characters are the same for both part number fields.

 

[lbass]

In the subreport, create a formula {@partno}:

left({table.partno},15)

Then in the main report, go to edit->subreport links and choose {table.partno} from the list for the main report and then link it with the subreport formula {@partno} instead of the subreport database field. The subreport should be placed in the part number group header or footer in the main report.

-LB