Skip to Content
avatar image
Former Member

possible design flaw with multiple values in the filter

Hello,

I really need some help resolving this issue.

We have a report which accepts 4 parameters. The report has 2 sub reports. Based on parameters, we hide the main report and generate sub reports 2 &3. The report uses SQL command to generate reports. Each sub report is linked to main report using fields which are 1:1 mapping. When I try to run the report by passing single value in the filter, it generates fine. But, when I try to pass multiple values in the filter criteria, for e.g invoicenumber in (1,2), the visual studio add-in crashes indicating that it cannot accept multiple filter criteria with the same parameter values. Do I need to change any thing or have a formula inside the sub report to allow multiple filter criteria?

The aim is to generate a single file where the sub reports 1,2  are generated for a invoicenumber 1 followed by sub reports 1, 2 generated for invoicenumber 2 with the main report hidden.

Sorry, let me know if the question is unclear.

Thanks,

Dileep

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 29, 2015 at 08:56 PM

    Thanks Don for the response. I guess we can try using the range parameters, but we need to change a lot of code to address all types of reports.

    On further research, here is a new observation.

    If you change the sub reports to on demand, it runs fine. Once you revert back and rerun the report, the report crashes when trying to retrieve second set of records from sub reports. Did anyone see this behaviour.

    Thanks,

    Dileep

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Don Williams

      Hey Don,

      Just figured out the issue. The issue is with page orientation in one of the group of the subreport is different to other groups in sub report. If i changed everything to landscape, it worked. I will raise a ticket to crystal support to fix the tool /client to show exact error

      Thanks  much!!!

      Dileep

  • Oct 28, 2015 at 05:20 AM

    Hi Dileep,

    Are the prompts setup to accept 'multiple values' or do you type them in as a comma separated string?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 28, 2015 at 05:21 AM

    Hi Dileep,

    There are few things to check :

    Is your main report / sub report parameter accepts multi value ?

    You said the report source is SQL, how you are handling in this parameter in where clause

    How you are entering multi value in the parameter (Eg. 1,2,3..) values side by side with a delimiter or one below the other ?

    Can you right click on sub report and save as a separate report and try to run with multi value.  If that pops out an error try to look at SQL.

    Also while defining parameters at Add Command (SQL ) you will see an option / check box 'Allow Multi Values' have to checked this option ?

    Thanks,

    Sastry

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 28, 2015 at 11:23 AM

    Thanks guys for the inputs. I think I made it complex by explaining unnecessary stuff . I will try to make it clear.

    The main report has 4 parameters. These are just boolean values that are used to hide the reports dynamically. The main report is mapped to a table which accepts a filter condition that is being sent via selection formula. So, if I right click the main report and click on show sql, it says select * from invoice where productid = '123' where 123 is set by the filter condition. Basically, the code we have sends this data. Now, the two sub reports are nothing but two other tables which are linked to main report by the productid field . For example product id from main report exists both in sub report tables and are linked to main report.  So, when I try to preview report, the 4 parameters are not used as filter criteria. But, the filter is used by the main report table which in turn populates the data in the sub reports since they are joined by a parameter field from the main report. Now, instead of sending one product id, the intention is to send multiple product ids . e.g the main report query will look like select * from invoice where productid = 123 or productid = 234. What I expect to happen is that the output will be generated for one product id followed by another productid. Instead , the visual studio add in crashes each time when I pass on multiple values in the selection formula filter. So, I believe it was not able to handle multiple filters.

    Thanks for helping this newbie.

    Dileep

    Add comment
    10|10000 characters needed characters exceeded

    • What SDK are you using?

      I suspect you need to change your logic, you can create Range parameters which allow CR to prompt for those values or will allow you to set those values.

      Bottom line is if you can do this in CR Designer then should be able to in code using the exact same work flow. But since the values are set in the Command Parameter you need to update the SQL to accept range values also.

      Don