on 03-24-2010 1:06 AM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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
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
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
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
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
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
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.