cancel
Showing results for 
Search instead for 
Did you mean: 

Select Expert doesn't work with multiple value parameter

Former Member
0 Kudos

Using Crystal XI.

I have defined a parameter {?Employee} as a string, then used the Select Expert to create the selection:

EmpCode IS ONE OF {?Employee}. This creates the formula syntax 'EmpCode in [{?Employee}]' and saves OK. So far, so good.

However when I use a VB program to set the Employee parameter to a number of employee codes (e.g. "1232","6624","4396") it doesn't work. I suspect it treated my 3 employee codes as a single string and therefore failed to match anything. I tried setting the ?Employee parameter to multiple values but then the parameter doesn't appear in the Select expert options when using 'is one of'.

Surely the Select Expert should be able to handle this? Anyone managed to get this to work?

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos

Moved to Legacy SDK Developer forum.

I assume you are using the RDC ( craxdrt.dll ) in your VB project? If not need more info, version of VB and which engine you are using?

Thank you

Don

Former Member
0 Kudos

Hi Don,

Yes I'm using craxdrt.dll in VB6 and the database I'm reading the data from is SQL Server 2003. Does this help?

I've got a bit more info now too. If I read the 'RecordSelectionFormula' from the Crystal report into my VB program, replace the string {?Employee} in it with some employee codes e.g. ["1111","2222","3333"] then it magically works at main report level. How strange is that?! Unfortunately I also have subreports in my report and when I tried the same approach for those I got the error 'Basic syntax is not allowed in record selection formula' when I tried to overwrite the Recordselectionformula.

Former Member
0 Kudos

Sorry the backslash characters should not be in my example above. I don't know how they got there. Bod

0 Kudos

Hello,

Try modifying the Record Selection formula manually in the Designer to verify you are formatting it correctly. Then do the same in code.

Thanks again

Don

Former Member
0 Kudos

Hi Don,

Good thinking, but I've already done that. In fact I started by generating a formula in the designer so I could see the correct syntax before I started coding the program. I also display the final string immediately before replacement so that I can check it is correctly formed.

I think I actually have 2 distinct problems here. The first is that I can't pass a set of values into a Crystal report via a parameter when using 'is one of' in the selection string. I think this is because Crystal takes the whole parameter as a single value and then fails to match, but if I set the parameter to allow multiple values it then doesn't appear in the selection list when using 'is one of' in the Select Expert, so I can't pass anything at all.

To get around this I manually altered the RecordSelectionFormula string in the report to replace the vaiable name with the values I wanted, but then I encountered the second problem. It seems to work Ok for a simple report, but if the report has a few subreports it spontaneously fails to replace the selection formula string at some (still unpredictable) point. I'm using a routine to set all selection strings so they are consistent, and I display them immediately before replacement to check they are correct. The error produced is 'Basic syntax is not allowed in Group or Record selection formula'. Once a selection formula fails, all subsequent attempts to replace selection formulae in other subreports also fails. I tried this code:

SavedString = CRRep.RecordSelectionFormula

...

<compose my new formula string>

...

On Error goto ErrorTrap

CRRep.RecordSelectionFormula = MyFormulaString

Exit Sub

...

ErrorTrap:

On Error goto 0

CRRep.RecordSelectionFormula = SavedString '**This line then produces a runtime error

...so even replacing the formula with the reports own saved formula throws an error - so I think something must be getting screwed up. The only way to get a selection formula to replace correctly again is to reload the program.

Have you any other ideas? I'm running out of things to try.

Thanks, Bod

0 Kudos

Hi Bod,

What is the actual code you are using? VB I believe requires a chr(32) ( or something like that ) to put an end of string character at the end of a string value.

If that's not it then it may be a timing issue, when you actually set the record selection formula and when you set the log on info and anything else that may affect the selection formula before and after.

try it this way if you are not already doing so:

Open report

Set logon info of main report and .Location if required.

Set logon info of subreports

Set record selection formula of main report

Set parameters for subreports if main report parameters that affect the subreport manually.

If you can use the SaveAs method after you set the new info then open the report in the designer and see what has happened to the record selection and subreport parameters.

If that doesn't work then you may want to purchase a case and work with a Support Engineer so you can pass source code and samples back and forth.

Thanks again

Don

Former Member
0 Kudos

Hi Don,

Thanks for your help and ideas. VB does terminate strings somehow automatically, but I think that is a red herring as I've passed lots of strings to other Crystal reports without problems, and indeed sometimes strings pass correvtly in this report too.

The sequence in which I do things is as follows:-

Open Report

For Each CRTable In CRRep.Database.Tables *set the database to look at in main report*

SetTableLocation

Next CRTable

For Each CRSubrep In CRRep

OpenSubreport

For Each CRTable In CRSubrep.Database.Tables *set the database to look at in subreport tables*

SetTableLocation

Next CRTable

Next CRSubrep

For X = 1 To CRRep.ParameterFields.Count

If ParameterName = "{?Someotherparam}" Then AddCurrentValue ... *For all other parameters just set to a single value**

If ParameterName = "{?Employee}" Then *For the employee parameter I need a set of values*

<build string EmployeeList with values required>

CRRep.RecordSelectionFormula = EmployeeList *This is the line that either works or fails*

End if

Next X

For Each Subreport *Repeat the logic above for each of the subreports*

Open Subreport

If ParameterName = "{?Employee}" Then

<build string EmployeeList with values required>

CRSubrep.RecordSelectionFormula = EmployeeList *This is the line that either works or fails*

End If

Next Subreport

ReadRecords and display the report

I think this is broadly similar to what you suggest.

I also tried saving the report after modifying the parameters as you suggested and it was as I would expect - where the RecordSelectionFormula rewrite worked, the values were correctly installed in the formula - Where it threw the RTE the variable name was still present.

I'd open a support case to progress this if I thought it might provide a solution, but I suspect at the end the answer I got might be - "Yes there is a problem with setting RecordSelectionFormula in subreports, but we are not fixing it in Crystal XI". That would just be a waste of money.

Regards

Bod

Edited by: TheBod on Mar 29, 2010 5:48 PM

0 Kudos

Hi Bod,

Thanks for the details. You are correct, we won't fix anything for XI now. You can upgrade to XI R2 ( 11.5 ) for free:

Go to this link: http://www.sdn.sap.com/irj/boc and download the trial version of CR XI R2 and use your XI keycode, then apply the patches which you can get to by clicking on the BusinessObjects tab above, then Downloads.

Direct link to the Trial version: http://www.sap.com/solutions/sapbusinessobjects/sme/freetrials/index.epx

And then test again, it may be resolved. Unfortunately R2 RDC is end of life and no more fix requests can be submitted as of wednesday so if this was a problem in R2 we can't get it fixed now.

And because the RDC is not Dynamic Parameter ( List of Values (LOV) ) aware there is no option to set values for LOV's at runtime either.

Another possibility is rather than using the RecordSelectionFormula to manually add values if you use the Dynamic Parameter API's and set multiple values then CR should pass those to the subreport for you and then you don't have to at runtime. Use the Object Browser and search on ParameterValueInfo and see if this works for you.

There are other options once you upgrade to .NET and CR assemblies.

Thanks agian

Don

Former Member
0 Kudos

Hi Don,

Sorry I've not replied to your previous post - Easter got in the way!

OK so you are saying that CR XI isn't LOV aware so I can't pass in a list of values as a parameter, and that R2 is no better in that respect. I'd kinda concluded that already, but it is nice to have it confirmed rather than wondering if I'd missed something. I am however interested in your last para regarding Dynamic Parameter APIs and I'll give this a try, except you have lost me. I don't understand what this is, or how I use it. I found the reference to ParameterValueInfo and can create a variable of this type in my program but I don't understand what to do with this in order to get it into my report. Any chance you could step me through the process in outline please?

Thanks

Bod

0 Kudos

Hi Bod,

Sorry paid cases got in the way.

If you link your subreport parameters to the main report they should show up in the parameter collection, don't set them specifically off the subreport object, use the main report object.

Also, the reason CR removes them from the list is because Cr can't determine what the Syntax should be so it filters the data on the second pass. Meaning Cr is bringing all of the data down from the server.

When using multiple values you can format the record selection fileter to look like:

myfield in [1,2,3,4,5,6]

For basic SQL Syntax CR will change it to myfield = 1 and myfield = 2 and myfield = 3.... and eventually you run out of string length and it fails.

Still best to up grade to .NET and use a record set to filter outside of CR.

Try this link for samples to add parameter filtering:

http://wiki.sdn.sap.com/wiki/display/BOBJ/BusinessObjectsHome

Thank you

Don

Former Member
0 Kudos

Hi Don,

Thanks for your last reply. I had a look at the samples in your link and didn't realise that you could add parameter value multiple times when using the = condition in the record selection formula. I thought the conditions would be 'AND'ed and so return no records but it seems they are 'OR'ed so effectively this allows me to check for multiple values and solved my problem.

I appreciate the resulting SQL query may not be pretty and that I may run out of string length with large numbers of parameter values, but so far the report seems to work as I hoped it would.

Thanks again for taking the time to help.

Regards

Bod

Answers (0)