Linking to subreport from one parameter to another

[StevenK]

Bit of a long-winded one this one, please bear with me.

Suppose I have two tables :
STOCK (with field STOCKCODE)
LOCATIONSTOCK (with fields STOCKCODE, STOCKLOCATION, STOCKQTY)
Entries in this table might exists as follows :
STOCK :
STOCKCODE = STOCKITEM1
STOCKCODE = STOCKITEM2
LOCATIONSTOCK :
STOCKCODE = STOCKITEM1, STOCKLOCATION = LOCATION1, STOCKQTY = 100
STOCKCODE = STOCKITEM1, STOCKLOCATION = LOCATION2, STOCKQTY = 200
STOCKCODE = STOCKITEM1, STOCKLOCATION = LOCATION3, STOCKQTY = 150
STOCKCODE = STOCKITEM1, STOCKLOCATION = NEWLOCATION4, STOCKQTY = 280
STOCKCODE = STOCKITEM1, STOCKLOCATION = NEWLOCATION5, STOCKQTY = 340
STOCKCODE = STOCKITEM2, STOCKLOCATION = LOCATION1, STOCKQTY = 490
STOCKCODE = STOCKITEM2, STOCKLOCATION = LOCATION2, STOCKQTY = 350

My main report basically lists the detail against a selected STOCKCODE from the STOCK table (ie. Stock Code, Description, etc.)
Against my main report I have a parameter set up (call it StockCodeParam) which the user can enter by hand and the report selection on the main report is such that it lists the entry from STOCK where STOCKCODE = StockCodeParam - this way I get the data regarding a particular stock item code.

Within my main report I have a subreport which will list the associated entries from the LOCATIONSTOCK table to list the quantities of the stock item at the various locations.
I can create a link from the main report to the subreport making a match from mainreport.STOCK.STOCKCODE to subreport.LOCATIONSTOCK.STOCKCODE. So far, so good.

My problem now lies that I want to restrict the data in the subreport to only list stock levels against STOCKLOCATION's that start with an entered three-character string (in this case maybe 'LOC' or 'NEW').

I've added an additional parameter within the scope of the main report (call it LocationStartParam - intending the user to enter the first three characters) such that I can display it on my report to indicate the StockCodeParam / LocationStartParam selected for usage.

I want to use this LocationStartParam to create a link through to my subreport such that the subreport only details those entries where subreport.LOCATIONSTOCK.STOCKLOCATION starts with mainreport.LocationStartParam - I can't seem to set this up.
I can't create a link from a field within my subreport table with the criteria that the data-field 'starts with' rather than 'is equal to'. - How is this done ?

For instance if the user enters the parameters of :
StockCodeParam = STOCKITEM1
LocationStartParam = NEW
then the report would look something like :

STOCKITEM1 - Stock Item 1 Description
(Location starts with 'NEW')
STOCKLOCATION STOCKQTY
~~~~~~~~~~~~~ ~~~~~~~~
NEWLOCATION4 280
NEWLOCATION5 340

How can I do this (in the easiest possible way) ?

I've tried adding a parameter in the subreport such that my data in the subreport starts with this new parameter - in the hope that I could create a link from my one parameter in the mainreport to the parameter coming into play in the subreport - but I can't use the parameter in the subreport as a link up to the mainreport - the only selection from the subreport (when creating a link from mainreport to subreport) seems to be against data-fields in the subreport.

I hope readers have beared with it thus far and can shed some light on how I might achieve my desired results.

Any advice / pointers would be appreciated (as always).
Thanks in advance
Steve

 

[crystalreporting]

Ummmmmm - two answers;

1). You can reference main report parameters in subreports, but you first have to 'make them available' to the subreport. Try adding the parameter field in your main report to the subreport links - it doesn't have to (and probably wont) make sense, so link it to any old field that you can. Now, when you edit the subreport, you will the main report parameter is available (edit the record selection formula), and you can use it just like any other field.

2). Why a subreport? I would think that an ODBC connection with a left outer join would give you the results you are looking for.

Good luck - Peter.

 

[StevenK]

We've actually created a link from mainreport parameter to subreport parameter.
I didn't see that there was a link to a subreport's parameter available for selection - we're now in business (well at least on this score).
Thanks again.
Steve

 

[SurfingGecko]

You may want to have a look at the following White paper on subreports. It might help you out some

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_subreports.pdf.asp

Cheers,

SurfingGecko

http://www.surfinggeckosoftware.com