Clarity

  • 1.  Error - Invalid Type for parameter used in IN clause

    Posted Aug 11, 2017 07:17 PM

    I have an interesting error when running my JasperSoft Server Advanced Report. This report is in production and I am making an upgrade by adding additional subreports.  This upgrade has not happened yet as I have had to refresh my memory on Advanced Reporting (THANK YOU COMMUNITY!). 

     

    I have downloaded the Jasper JRXML from production and have it in my TEST/DEV environment and loaded it up into CA PPM; with inputs linked (again, Thank you Community!). Now I receive the following error when I try to run the report:

     

    Error – Invalid type java.math.Big.Decimal for parameter projectKey_1 used in IN clause: The value must be an array or a collection.

     

    I have not changed any of the parameters (names or types) nor have I updated the SQL Query for this report (or any of its currently attached subreports).

     

    The parameter is defined as:

     

                    Name:  projectKey_1

                    Class: java.util.collection

                    Netsted Type Name: java.math.BigDecimal

     

    In the Where clause of the query it is used in the following context:

     

                    AND $X{IN, i.investment_key, projectKey_1}

     

    The input control is defined as:   /ca_ppm/input_controls/common/projectKey_1

     

    What is the error really trying to tell me? As I said, I haven’t changed anything related to the parameter and it does run in the production environment.

     

    Thank you for your help in resolving this.

     

     

    Michael Tognetti



  • 2.  Re: Error - Invalid Type for parameter used in IN clause

    Posted Aug 15, 2017 05:13 PM

    I can see that we have had 100 views on this question.  Is there more information I could add to make it clearer what I am experiencing in order to get a resolution?  Are their additional tags?

     

    Thank you.

     

    Michael



  • 3.  Re: Error - Invalid Type for parameter used in IN clause
    Best Answer

    Broadcom Employee
    Posted Aug 16, 2017 01:13 AM

    The project key datatype you have set in your sub report is incorrect, can you please check the datatype for the out of box report for the same parameter and then deploy again. Also you can test the report in Jaspersoft studio before even deploying in PPM.

     

    More information on how to create Jaspersoft reports is here Create and Publish Jaspersoft Studio Reports - CA PPM - 15.2 - CA Technologies Documentation 

     

    Regards
    Suman Pramanik



  • 4.  Re: Error - Invalid Type for parameter used in IN clause

    Posted Aug 17, 2017 12:09 PM

    Suman,

     

    Thank you for reminding me that I should go back to the root and see what was/is happening there and how the intem(s) in question are defined.  I had gone back one level, but only to the last instance where this particular report was updated by my organization.  What I should have done was that, and as you suggested, go back to the root report which we cloned.  

     

    I have now done the right thing and reviewed the original "canned" Project Storyboard report which is the basis for our reporting.  However, nothing is different from what I outlined above that I have found in my current iteration or the base which I took it from.  That is, the project key datatype is defined as: 

     

                    Name:  projectKey_1

                    Class: java.util.collection

                    Netsted Type Name: java.math.BigDecimal

     

    Why is it that these two versions run and mine is returned with the error?  I truly want to understand in order to fix this report instance and to overcome this error if I see it again in the future.

     

    You also mention that I should be able to run the report in Jaspersoft Studio.  I agree, but there is something that is blocking my attempts to do so, although it has worked with simpler reports that I was building while taking the Advanced Reporting course.  It has to do with my setup I believe and in this particular case about the templates.  I need to work that outbut I till need to be a separate issue.

     

    Again, thank you for the guidance so far, and if you have further ideas and thoughts to pass along I would like to see them and to bring this to a resolution.

     

    Michael.



  • 5.  Re: Error - Invalid Type for parameter used in IN clause

    Broadcom Employee
    Posted Aug 17, 2017 12:17 PM

    HI Michael,

     

    I am running out of any further ideas, it somehow looks like the design of the report has issue and that can only be checked if the report can be edited in the Studio. However I understand your challenge and if you can share the exact steps and I can test the same and see if I can find out something.

     

    Regards

    Suman Pramanik



  • 6.  Re: Error - Invalid Type for parameter used in IN clause

    Posted Aug 17, 2017 05:32 PM

    I found the answer an Suman's reference to review the original was the key (I marked it as the answer).  This had a lot to do with what I was "overlooking."   In this report instance, I had added a subreport.  It was the subreport that was the overall issue. 

     

    I do need the subreport, and I will be putting it back in.  However, I am going to review the instruction reference (also in Suman's post) more thoroughly as I accomplish this.

     

    Thank you Suman!

     

     

    Michael.



  • 7.  Re: Error - Invalid Type for parameter used in IN clause

    Broadcom Employee
    Posted Aug 18, 2017 06:59 AM

    Thanks Michael. Glat it helped you.



  • 8.  Re: Error - Invalid Type for parameter used in IN clause

    Posted May 30, 2018 10:25 AM

    Hi, I also encounter this issue in Reports having sub Report, I need to convert one input param to Multi Select and its used in Sub Report as well. When i convert that Param to util.collection it and update the SQL to take $X(IN) it is giving me error: Parameter type not supported in query : __ Class java.util.Collection --- Net.sf.jersperreports.engine.design.JRDDESIGNQuery@52ecd9f5.

     

    Any leads