Passing a string of numbers to a sub report

[Slimsteve]

Hi,

I am using cr9 and having a small problem with a sub report. Basically on the main report i have got the following.

DocNO Custid Amount
1 1 10
2 2 20
3 4 15

Now there is a subreport which is linked by docno, which is fine as it returns a sub report for each docno. But I would like to do is pass all the docnos in one go to the sub report and use that string to select information in the sub report i.e. in the sub repot a select criteria like docno in [1,2,3], but as yet I have not been able to get this to work.

I have tried creating string while reading the records in the main report i.e. "1,2,3" and passing that to the sub report but it does not work.

Any ideas or suggestions would be greatly appreciated.

Slim

 

[gortuk]

I was just about to post a similar question - I'll put it here because I think it's the same issue. In my case I've got as far as creating an array (let's call it {DocNoArray}), and a formula to display the array {DisplayDocNoArray}.

The subreport select statement includes the line "{Table.DocNo } in {DisplayDocNoArray}". When the subreport displays, it does contain only the items indicated in {DisplayDocNoArray}. However, the SQL query does not utilize the contents of {DocNoArray} at all, so the query is inefficient and takes a LONG time to return the data.

Does anyone know a way to efficiently use the contents of an array in the select statement for a subreport?

 

[lbass]

Slimsteve,

You could unlink the subreport and copy the record selection formula from the main report into the record selection for the subreport. Or, you could create a parameter in both main report and subreport and add the parameter to the record selection formula in each, as in:

{table.customerID} = {?custID}

And then link the subreport to the main report on {?custID}. In the subreport links screen, after adding {?custID} for the main report, in the bottom left corner, use the drop down to select {?custID} for the subreport (do not use {?PM-?custID}).

gortuk - Either approach will pass to the SQL statement.

-LB

 

[spedi]

I have used a different approach to solve this problem. What I would do is, create a a comma separated string and pass it to the sub report. The Sub report will be based of a stored procedure with a paramter that needs to be passed.

I link the main report's formula (that has comma separated values) to the parameter of sub report which is based of stored procedure.

The above method works like a charm, we have more than 30 reports based off above method. Not only it is easy to implement but fast due to stored procedure.

Hope this helps...
Srinath