0

I have a Java application the invokes Crystal Reports through its Java libraries. One of the reports has the following clause in its record selector: 

and ({DriverMotionView.GROUPID} = {?GroupID1Parameter}
or {DriverMotionView.GROUPID} = {?GroupID2Parameter}
or {DriverMotionView.GROUPID} = {?GroupID3Parameter}
or {DriverMotionView.GROUPID} = {?GroupID4Parameter}
or {DriverMotionView.GROUPID} = {?GroupID5Parameter}
or {DriverMotionView.GROUPID} = {?GroupID6Parameter}
or {DriverMotionView.GROUPID} = {?GroupID7Parameter}
or {DriverMotionView.GROUPID} = {?GroupID8Parameter}
or {DriverMotionView.GROUPID} = {?GroupID9Parameter}
or {DriverMotionView.GROUPID} = {?GroupID10Parameter}
)

Each GroupIDnParameter is an integer groupID, created by selecting a group names from a tree structure in the UI. The UI converts these to groupIDs and passes them into the report. Now this record selector works but limits the number of selected groups to 10.

I would like to remove this limit. To check the syntax of a possible solution, I first tried the following 

and {DriverMotionView.GROUPID} in [-1, 1618, 1608, 1610, -1]

This does indeed select the records for groupIDs 1618, 1608 and 1610.

So then I tried things like 

and {DriverMotionView.GROUPID} in {?GroupIDListParameter}

where the GroupIDListParameter is passed in as the string [-1, 1618, 1608, 1610, -1]. This results in a run time Crystal Reports error.

I tried 

and {DriverMotionView.GROUPID} in [{?GroupIDListParameter}]

but that clause is syntactically incorrect and results in a "array must be subscripted" error on a syntax check.

There is likely another approach to solving this problem, but I was not able to find it in the on-line help, reading the User's Guide or even Google searches.

Any suggestions would be appreciated.

Andy
  • 99
  • 10

3 Answers3

0

You said that the {?GroupIDListParameter} is passed in as a string which includes the brackets ([ ]) around the values. Did you type that in/do it with code? Whatever you are doing to get the brackets there, I would remove them and then try this around just the comma separated string of numebrs that are put in {?GroupIDListParameter}:

{DriverMotionView.GROUPID} in split({?GroupIDListParmeter},",")
Gary
  • 37
  • 1
  • 9
  • The {?GroupIDListParameter} is generated with code although I can type it in manually when previewing the report. I know it is being passed into Crystal Reports because I log the generated string and occasionally the CR error message contains the string. – Andy May 09 '13 at 17:43
  • Furthermore, almost any use of a function in the clause prevents the clause from being passed to the DB server. This dramatically increases the query time from a few seconds to over 50 seconds not to mention the time on the client side filtering the additional records returned by the DB server I think I have stumbled on the reason why the original report was written the way t was. I just may have to resort to doing the very ugly thing and increase the number of discrete parameters from 10 to say 100. – Andy May 09 '13 at 18:58
0

You need to set your {?GroupIDListParameter} to be a multi-valued parameter (a property of the parameter itself). This will treat it as an array so you can do the record selection with a simple {DriverMotionView.GROUPID} in {?GroupIDListParameter} like you had already tried.

There is no need to delimit the values or use square-brackets.

Ryan
  • 7,212
  • 1
  • 17
  • 30
  • That was my original approach. Unless I have missed something, parameters passed in via the Java APIs are either discrete quantities like Boolean, Number, String, Currency and variations of Date/Times or ranges thereof. There is a ParameterFieldRangeValue class as opposed to the ParameterFieldDiscreteValue class that is being used, but a range is not general enough. That is why tried to resort to a String parameter and use variations of the suggestion above. I have unable to find the JavaDocs or any other form of documentation for the for these APIs, so I may well have missed something. – Andy May 09 '13 at 18:45
  • A quick Googling turns up the [parameterField class documentation](https://help.sap.com/javadocs/cr/xi/jrc/en/com/crystaldecisions/sdk/occa/report/data/ParameterField.html) and an [example](http://scn.sap.com/docs/DOC-6916) – Ryan May 09 '13 at 20:11
  • Thanks for that. It just confirmed what I suspected. There apparently is no way to pass an array parameter to the report via the Java APIs. – Andy May 10 '13 at 16:22
  • No, but you can use a Values collection which can be assigned to a single parameter. That's essentially the same thing, as far as I can tell. If you open the .rpt in the example, the parameter {?pfield1} is set to `Allow multiple values` which is what you're looking to work with here. – Ryan May 10 '13 at 16:56
  • This may work. I can get the correct SQL generated in the report designer. I just have to figure out how to tweak the Java. Stay tuned – Andy May 13 '13 at 15:38
  • This works. For the record here is the solution, but its not obvious from the documentation. The correct entry in the record selection is `code`{DriverMotionView.GROUPID} in {?GroupIDListParameter} `code` – Andy May 13 '13 at 16:57
  • Now the question is, "What object is valid for the parameter newValue in public void setCurrentValues(java.lang.String sReportName, java.lang.String sFieldName, java.lang.Object[] newValue)? – Andy May 14 '13 at 03:11
  • The answer to this question in the context of the above is simple. newValue should be an Integer array of Integer objects. How you would know this from the JavaDocs is not obvious. – Andy May 15 '13 at 23:55
0

Here's the answer. I replaced

and ({DriverMotionView.GROUPID} = {?GroupID1Parameter}
or {DriverMotionView.GROUPID} = {?GroupID2Parameter}
or {DriverMotionView.GROUPID} = {?GroupID3Parameter}
or {DriverMotionView.GROUPID} = {?GroupID4Parameter}
or {DriverMotionView.GROUPID} = {?GroupID5Parameter}
or {DriverMotionView.GROUPID} = {?GroupID6Parameter}
or {DriverMotionView.GROUPID} = {?GroupID7Parameter}
or {DriverMotionView.GROUPID} = {?GroupID8Parameter}
or {DriverMotionView.GROUPID} = {?GroupID9Parameter}
or {DriverMotionView.GROUPID} = {?GroupID10Parameter}
)

with

and ({DriverMotionView.GROUPID} in {?GroupIDArrayParameter})

Where the GroupIDArrayParameter is declared as a number parameter allowing discrete and multiple values. Custom values are allowed but I don't think that matters. What is key is that the type of the GroupIDArrayParameter (number) match the type of the DriverMotionView.GROUPID which is a number as well.

The code that sets the GroupIDArrayParameter is essentially as follows:

 Fields fields = new Fields ();
 ... //Set other parameters here
 setDiscreteArrayParameter(fields, "GroupIDArrayParameter", "",  
                           getIntGroupIDs(groups));

 ...

 // Convert String groupIDs to Integers
 private Integer[] getIntGroupIDs(String[] s) {
     Integer[] result = new Integer[s.length];
     for (int i = 0; i < s.length; i++) {
        result[i] = Integer.parseInt(s[i]);
     }
     return result;
}

...

private void setDiscreteArrayParameter(Fields fields, String paramName, 
                           String reportName, Object[] parameterValues) {
    logger.debug("DescreteParameter - Name: " + paramName);
    // Create parameter field
    ParameterField parmeterField = new ParameterField();
    // Set report name
    parmeterField.setReportName(reportName);
    // Set parameter name
    parmeterField.setName(paramName);
    // Create value     
    Values values = new Values();
    for (int i = 0; i < parameterValues.length; i++) {
        ParameterFieldDiscreteValue discreteValue = new ParameterFieldDiscreteValue();
        discreteValue.setValue(parameterValues[i]);
        values.add(discreteValue);
     }
 parmeterField.setCurrentValues(values);
     fields.add(parmeterField);
}

There may even be cleaner ways to do this, but I have only so much time to investigate.

Enjoy

Andy
  • 99
  • 10