List-Box Multiple Values Parameter - "Select All" issues

mhall0906
edited February 11, 2022 in Analytics #1
<p>Hello.</p><p> </p><p>I am having trouble with a report parameter. </p><p> </p><p>I have it set up as a dynamic list box, which allows multiple values to be selected, and I have added in a default value of "ALL" so that the user has an option to select all values. Additionally, the SQL statement will select all values by default (confirmed in SSMS). </p><p> </p><p>In the beforeOpen script of my data set I have included the following script:</p><pre class="_prettyXprint">param=params["report_param"].value;queryString = this.queryText;if (param != 'ALL'){queryString = queryString + " AND field IN ('" + params["report_param"].toString().replace(/,/g,"','") + "')";this.queryText = queryString;}</pre><p>This script works for selecting multiple values, however it does not work when I choose the default value of "ALL". I do not have this parameter linked to my data set in any other way, that I am aware of. Any advice or direction would be greatly appreciated! If you need more information, I'd be happy to provide.</p><p> </p><p>Thanks.</p>

Comments

  • <p>That is because multi-value parameters are arrays. I would write my code like this.</p><pre class="_prettyXprint">if (params["report_param"].value[0] != 'ALL'){this.queryText = this.queryText+ " AND field IN ('" + params["report_param"].value.join(",") + "')";}</pre>
  • <blockquote class="ipsBlockquote" data-author="mblock" data-cid="128348" data-time="1401860081"><div><p> </p><p>That is because multi-value parameters are arrays. I would write my code like this.</p><pre class="_prettyXprint">if (params["report_param"].value[0] != 'ALL')</pre></div></blockquote><p> </p><p>Awesome! This worked. Thanks for the speedy support. </p>
  • <p>Hi Can anyone help me on resolving my issue.</p>
    <p>I can see the report successfully when I select multiple values. But I do not have any idea on how to create an option "All" in the listbox values. Pls suggest me how to modify my sql query and tell me all the necessary modifications. So that I can select all values by clicking that. Awaiting for your valuable reply.  Below u can find my SQL query,</p>
    <p> </p>
    <p>select ta.c_bpartner_id as account_id,cbp.name as customer,cbpg.name as bpgroup_name,<br>
    pastdue1_30 as _30days,pastdue31_60 as _60days,pastdue61_90 as _90days,pastdue91_plus as _90plus<br>
    from adempiere.t_aging ta<br>
    inner join adempiere.c_bpartner cbp on cbp.c_bpartner_id=ta.c_bpartner_id<br>
    inner join adempiere.c_bp_group cbpg on cbpg.c_bp_group_id = cbp.c_bp_group_id<br>
    where cbp.iscustomer = 'Y' and pastdueamt > 0 and<br>
    ad_pinstance_id in (select max(ta.ad_pinstance_id) from adempiere.t_aging ta inner join adempiere.c_bpartner cbp on cbp.c_bpartner_id=ta.c_bpartner_id<br>
    inner join adempiere.c_bp_group cbpg on cbpg.c_bp_group_id = cbp.c_bp_group_id group by cbp.name )<br>
    order by cbp.name asc</p>
  • <p>Hi Abdur,</p>
    <p> </p>
    <p> </p>
    <p><span style="font-size:14px;">take a look on this example  </span><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/files/file/593-default-value-all-with-multi-select-parsmeter/'>http://developer.actuate.com/.../default-value-all-with-multi-select-parsmeter/</a></p&gt;
    Warning No formatter is installed for the format ipb
  • <p>Hi <strong><span style="color:#000000;"><span style="font-family:Arial, sans-serif;">Guillaume L,</span></span></strong></p>
    <p> </p>
    <p><strong><span style="color:#000000;"><span style="font-family:Arial, sans-serif;"> I thank you for </span></span></strong>your valuable assistance. It worked fine for me. Now I have little modifications, like for example, I have two parameters select_customer and select_todate. I need multiselect option for select _customer parameter and I need to pass a date(single value not multiselect) for select_todate parameter.</p>
    <p>My query looks like this,</p>
    <p> </p>
    <p>select * from customer where date between 1950-01-01 and ? ;</p>
    <p> </p>
    <p>I did not add customer paramtere in where clause becuase of multiselect. I added the following script in dataset.</p>
    <p> </p>
    <p>var param_customer = reportContext.getParameterValue("select_customer");<br>
    this.queryText+=" where customer_name IN ("+param_customer+")";</p>
    <p> </p>
    <p>when i run the report it throws error...Pls suggest me. Awaiting for ur help.</p>
    <p> </p>
    <p>Thanks,</p>
    <p>Abdur Rahmaan</p>
  • <p>Hi,</p>
    <p> </p>
    <p>What version of BIRT are you using ?</p>
    <p>Can you upload your design ?</p>
    Warning No formatter is installed for the format ipb
  • <p>You already have a where clause hence the error change your code to:</p>
    <pre class="_prettyXprint">
    this.queryText+=" and customer_name IN ("+param_customer+")";</pre>
  • xplorerdev
    edited October 24, 2016 #9
    <p>Hi,</p>
    <p> </p>
    <p>Would like to revisit this topic.</p>
    <p> </p>
    <p>I have two datasets:</p>
    <p>1) <strong>QuarterAll </strong>= Selects all QURT_KEY, <em>QURT_NAM</em> from the database.</p>
    <p>2) <strong>DataAll </strong>= Selects the data I want.</p>
    <p> </p>
    <p> </p>
    <p>I have created a report parameter, <strong>pQuarter</strong>, with following settings:</p>
    <p>DataType: Integer</p>
    <p>DisplayType: List Box</p>
    <p>Selection list values: Dynamic (Allow Multiple Values has been checked)</p>
    <p>Data set: QuarterAll</p>
    <p>Select value column: QURT_KEY</p>
    <p>Select display text: QURT_NAM</p>
    <p>Default value: Blank.</p>
    <p> </p>
    <p>Then, in the DataAll query, I have put a clause: <span style="color:#800000;"><em>AND D1.[QURT_KEY] IN (999)</em></span></p>
    <p> </p>
    <p>Then, I have added the following code in the beforeOpen script for the dataset, DataAll:</p>
    <p><span style="color:#800000;"><em>this.queryText=this.queryText.replace("999",params["pQuarter"].value.join(","));</em></span></p>
    <p> </p>
    <p>When I select multiple quarters and run the report, <span style="color:#006400;"><strong>it runs fine. Part 1 achieved. :)</strong></span></p>
    <p> </p>
    <p>Now, I want to add an 'ALL' option in the Quarter dropdown. I have added a default value ALL to the parameter and my beforeOpen script for the dataset, DataAll, looks like the following:</p>
    <div>
    <div><em><span style="color:#800000;">if (params["pQuarter"].value[0] != 'ALL')</span></em></div>
    <div><em><span style="color:#800000;">{this.queryText = this.queryText+ " AND <schema_name>.<table_name>.QURT_NAM IN ('" + params["pQuarter"].value.join(",") + "')";}</span></em></div>
    </div>
    <div> </div>
    <div>When I select ALL, then the report gives a blank output. But if I select any other values, I get the following error:</div>
    <div><span style="color:#ff0000;"><em>SQL error #1:Incorrect syntax near the keyword AND. ; com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword and. Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.</em></span></div>
    <p> </p>
    <p><u><span style="font-size:14px;"><strong>I want to now get the ALL thing to work.</strong></span></u></p>
    <p> </p>
    <p>Best Regards.</p>
  • <p>Can you share your whole query. Is the where clause at the end? Is there an order by? group by?</p>
  • xplorerdev
    edited October 24, 2016 #11
    <p>Hi Mica,</p>
    <p> </p>
    <p>Thanks for the reply. Yes there are Group By's. Following is my <strong>DataAll </strong>query:</p>
    <div>
    <div> </div>
    <div><span style="font-size:10px;">SELECT </span></div>
    <div><span style="font-size:10px;">D1.[CALN_YER],</span></div>
    <div><span style="font-size:10px;">D1.[QURT_KEY],</span></div>
    <div><span style="font-size:10px;">D1.[QURT_NAM],</span></div>
    <div><span style="font-size:10px;">D1.[MONT_KEY],</span></div>
    <div><span style="font-size:10px;">D1.[MONT_YER],</span></div>
    <div><span style="font-size:10px;">D3.[AGE_BND_PK],</span></div>
    <div><span style="font-size:10px;">D3.[AGE_BAND],</span></div>
    <div><span style="font-size:10px;">D4.[EDCT_LEVL],</span></div>
    <div><span style="font-size:10px;">D4.[EDCT_LEVL_PK],</span></div>
    <div><span style="font-size:10px;">D5.[GEND],</span></div>
    <div><span style="font-size:10px;">D5.[GEND_PK],</span></div>
    <div><span style="font-size:10px;">D6.[RESD_PERD_BND],</span></div>
    <div><span style="font-size:10px;">D6.[RESD_PERD_BND_PK],</span></div>
    <div><span style="font-size:10px;">D7.[HOM_OWNR_PK],</span></div>
    <div><span style="font-size:10px;">D7.[HOM_OWNR],</span></div>
    <div><span style="font-size:10px;">D8.[MART_STAT],</span></div>
    <div><span style="font-size:10px;">D8.[MART_STAT_PK],</span></div>
    <div><span style="font-size:10px;">D9.[EMPL_TYPE],</span></div>
    <div><span style="font-size:10px;">D9.[EMPL_TYPE_PK],</span></div>
    <div><span style="font-size:10px;">SUM(CASE WHEN F1.ACCN_IN_FORC > 0 THEN F1.CARD_IN_FORC ELSE 0 END) AS "CARDS IN FORCE",</span></div>
    <div><span style="font-size:10px;">SUM(CASE WHEN F1.ACCN_IN_FORC > 0 THEN F1.EXPC_NET_RECV ELSE 0 END) AS "EOP ENR",</span></div>
    <div><span style="font-size:10px;">SUM(CASE WHEN F1.ACCN_IN_FORC > 0 THEN F1.NO_OF_ALL_ACCN ELSE 0 END) AS "# ACCOUNTS",</span></div>
    <div><span style="font-size:10px;">SUM(CASE WHEN F1.ACCN_IN_FORC > 0 AND F1.VINT_FK = D1.MONT_KEY THEN F1.NO_OF_ALL_ACCN</span></div>
    <div><span style="font-size:10px;">ELSE 0 END) AS "# NEW ACCOUNTS"</span></div>
    <div><span style="font-size:10px;">FROM</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_AGG_ACCN_TRAN_SNAP_M_CC F1,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_STC_MIS_TIM_CC D1,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_SNF_CUST_PERS_CC D2,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_STC_AGE_BND_CC D3,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_EDCT_LEVL_CC D4,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_GEND_CC D5,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_STC_RESD_PERD_BND_CC D6,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_HOM_OWNR_CC D7,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_MART_STAT_CC D8,</span></div>
    <div><span style="font-size:10px;">MY_SCHEMA.T_DIM_EMPL_TYPE_CC D9</span></div>
    <div> </div>
    <div><span style="font-size:10px;">WHERE F1.MIS_DAT_FK = D1.DAY_PK</span></div>
    <div><span style="font-size:10px;">AND F1.CUST_PERS_FK = D2.CUST_PERS_CK</span></div>
    <div><span style="font-size:10px;">AND D2.AGE_BND_FK = D3.AGE_BND_PK</span></div>
    <div><span style="font-size:10px;">AND D2.EDCT_LEVL_FK = D4.EDCT_LEVL_PK </span></div>
    <div><span style="font-size:10px;">AND D2.GEND_FK = D5.GEND_PK </span></div>
    <div><span style="font-size:10px;">AND D2.RESD_PERD_BND_FK = D6.RESD_PERD_BND_PK </span></div>
    <div><span style="font-size:10px;">AND D2.HOM_OWNR_FK = D7.HOM_OWNR_PK </span></div>
    <div><span style="font-size:10px;">AND D2.MART_STAT_FK = D8.MART_STAT_PK </span></div>
    <div><span style="font-size:10px;">AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK</span></div>
    <div><strong><span style="font-size:12px;">AND D1.[QURT_NAM] IN ('999')</span></strong></div>
    <div> </div>
    <div><span style="font-size:10px;">GROUP BY</span></div>
    <div><span style="font-size:10px;">[CALN_YER],</span></div>
    <div><span style="font-size:10px;">[QURT_KEY],</span></div>
    <div><span style="font-size:10px;">[QURT_NAM],</span></div>
    <div><span style="font-size:10px;">[MONT_KEY],</span></div>
    <div><span style="font-size:10px;">[MONT_YER],</span></div>
    <div><span style="font-size:10px;">[AGE_BND_PK],</span></div>
    <div><span style="font-size:10px;">[AGE_BAND],</span></div>
    <div><span style="font-size:10px;">[EDCT_LEVL],</span></div>
    <div><span style="font-size:10px;">[EDCT_LEVL_PK],</span></div>
    <div><span style="font-size:10px;">[GEND],</span></div>
    <div><span style="font-size:10px;">[GEND_PK],</span></div>
    <div><span style="font-size:10px;">[RESD_PERD_BND],</span></div>
    <div><span style="font-size:10px;">[RESD_PERD_BND_PK],</span></div>
    <div><span style="font-size:10px;">[HOM_OWNR_PK],</span></div>
    <div><span style="font-size:10px;">[HOM_OWNR],</span></div>
    <div><span style="font-size:10px;">[MART_STAT],</span></div>
    <div><span style="font-size:10px;">[MART_STAT_PK],</span></div>
    <div><span style="font-size:10px;">[EMPL_TYPE],</span></div>
    <div><span style="font-size:10px;">[EMPL_TYPE_PK]</span></div>
    </div>
    <p> </p>
    <p>..and my QuarterAll query looks like the following:</p>
    <div><span style="font-size:10px;">select [QURT_KEY], [QURT_NAM]</span></div>
    <div><span style="font-size:10px;">from MY_SCHEMA.T_DIM_STC_MIS_TIM_CC</span></div>
    <div> </div>
    <p>Best Regards</p>
  • micajblock
    edited October 24, 2016 #12
    <p>So of course your code will not work as it puts the additional where clause after the group by. First get rid of:</p>
    <pre class="_prettyXprint">
    AND D1.[QURT_NAM] IN ('999')</pre>
    <p>Then try something like this:</p>
    <div>
    <pre class="_prettyXprint">
    if (params["pQuarter"].value[0] != 'ALL') {
    this.queryText = this.queryText.replace("AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK", "AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK AND D1.QURT_NAM IN ('" + params["pQuarter"].value.join(",") + "')");
    }</pre>
    </div>
  • <p>Excellent! Did what you suggested and now the <strong>All </strong>works like a charm :) Thanks a lot :) But it's not over yet :)</p>
    <p> </p>
    <p>I select <strong>All </strong>- <u>working fine.</u></p>
    <p>I select Q1 2014 <strong>or </strong>Q2 2014 manually - <u>working fine.</u></p>
    <p>But if I select both i.e. Q1 2014 <strong>and </strong>Q2 2014 - <u>NO data. Empty report.</u></p>
    <p> </p>
    <p>#1) Is this a known behavior to you?</p>
    <div> </div>
    <div>#2) Also, I could not comprehend the relevance of the part: <span style="color:rgb(0,136,0);">AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK"</span><span style="color:rgb(102,102,0);">,</span><span> </span><span style="color:rgb(0,136,0);">"AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK </span>in the above script. When I remove this part, the All still works but the single selection does not work. And multiple selection throws an error. :(</div>
    <div> </div>
    <div>Signing off for today. Looking forward to carry on this discussion tomorrow.</div>
    <div> </div>
    <div>PS: I am in IST :)</div>
    <div> </div>
    <div>Best Regards</div>
  • <p>That is because quarter name is a string. The purpose of the code is to find the end of the where clause and add the new where clause at the end. The only thing needed id to add quotes to the join function. Note the what is in side the join function is:double quote, single quote, comma, single quote, double quote.</p>
    <pre class="_prettyXprint">
    if (params["pQuarter"].value[0] != 'ALL') {
    this.queryText = this.queryText.replace("AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK", "AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK AND D1.QURT_NAM IN ('" + params["pQuarter"].value.join("','") + "')");
    }</pre>
  • <p>Ohh Yes. I missed out on that single quotes thing in case of string. Sorry.</p>
    <p> </p>
    <p><span style="color:#008000;"><strong>I have done that now and everything works fine.</strong></span> Thanks a lot for your assistance Mica. Your youtube video was very helpful. :)</p>
    <p> </p>
    <p>Best Regards</p>
  • <p><u><strong>Adding to the above solution, if anyone wants to add more parameters, following should be the steps:</strong></u></p>
    <p> </p>
    <p>Create a new dataset for the 2nd dimension. I did the following:</p>
    <div><span style="color:#8b4513;"><em>select EMPL_TYPE_PK, EMPL_TYPE </em></span><em>from ARAWSH_MRT.T_DIM_EMPL_TYPE_CC</em></div>
    <div> </div>
    <div>Then, create a new parameter, <em><strong>pEmplType </strong></em>in a similar manner as done for pQuarter.</div>
    <div> </div>
    <div>Now, in the main dataset, modify the beforeOpen script as follows:</div>
    <div>
    <div> </div>
    <div>
    <div><span style="color:#008000;">//The purpose of the code is to find the end of the where clause and add the new where clause at the end.</span></div>
    <div><em><span style="color:#0000cd;">if (params["pQuarter"].value[0] != 'ALL') {</span></em></div>
    <div><em><span style="color:#0000cd;">this.queryText = this.queryText.replace</span></em></div>
    <div><em><span style="color:#0000cd;">("AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK", </span></em></div>
    <div><em><span style="color:#0000cd;">"AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK AND D1.QURT_NAM IN ('" + params["pQuarter"].value.join("','") + "')");</span></em></div>
    <div><em><span style="color:#0000cd;">}</span></em></div>
    <div> </div>
    <div><em><span style="color:#0000cd;">if (params["pEmplType"].value[0] != 'ALL') {</span></em></div>
    <div><em><span style="color:#0000cd;">this.queryText = this.queryText.replace</span></em></div>
    <div><em><span style="color:#0000cd;">("AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK", </span></em></div>
    <div><em><span style="color:#0000cd;">"AND D2.EMPL_TYPE_FK = D9.EMPL_TYPE_PK AND D9.EMPL_TYPE IN ('" + params["pEmplType"].value.join("','") + "')");</span></em></div>
    <div><em><span style="color:#0000cd;">}</span></em></div>
    </div>
    <div> </div>
    <div>It works like a charm. Thanks a lot Mica for your timely support and guidance.  :)</div>
    </div>
    <p> </p>
    <p>Best Regards</p>
  • shweta14
    edited June 7, 2017 #17
    <p>Hi,<br>
     <br>
    Would like to revisit this topic. I have been trying to make this multiple values in a single parameter thing work and facing some issues with it. In my case instead of a separate DataSet the parameter values are coming from a JSP page in the report url: (as shown below)<br><br><em>?reportDesign=tcDetail.rptdesign&rpAccountID=tc01&rpComponentType=<strong>T,P</strong></em><br><br>
    Have defined a report parameter rpComponentType with the following details:<br><br><em>DataType: String<br><br>
    DisplayType: List Box</em><br><br><em>Selection list values: Dynamic (Allow Multiple Values has been checked)<br><br>
    Data set: Have selected the same Dataset(defined for the report)<br><br>
    Select value column: COMPONENT_TYPE<br><br>
    Select display text: COMPONENT_TYPE<br><br>
    Default value: none</em><br><br><br>
    Have modified the Dataset sql:<br><br><em>select id, descr from tcTable where ext_Id = ? and component_Type in ('999');</em><br><br>
    And added the below code in beforeOpen method of Dataset:<br><br><em>this.queryText = this.queryText.replace("999", params["rpComponentType"].value.join("','"));</em><br><br><br>
    With all these changes in place, my report is displaying the data only if one option is passed from JSP (T or P). If I select both, then the report gives the below error:<br><br><em>org.eclipse.birt.report.engine.api.impl.ParameterValidationException: Required parameter rpComponentType is not set.</em><br><br><br>
    If I choose only one option - T or P, it shows corresponding data correctly (one at a time). The SQL uses IN instead of =, so not sure why is it only working with one parameter value.<br>
     <br>
    Also if for any option there is no corresponding data in db, then the sql when executed in database returns blank (as expected it doesn't error). But the report gives the same error as above: <em>org.eclipse.birt.report.engine.api.impl.ParameterValidationException: Required parameter rpComponentType is not set</em>.<br>
     <br>
    Have been struggling with this from past couple of days and would appreciate any suggestion on how to make this work. I would be happy to provide more details if needed.<br>
     <br>
    My BIRT Version: 4.4.1<br><br><br>
     <br><strong>Update:</strong> I even tried the following, but again it works with only one parameter value T or P, not with both (getting same error as in above case):</p>
    <p>
    Modified the Dataset sql:<br><em>select id, descr from tcTable where ext_Id = ? and component_Type in (?);</em><br><br>
    And added the below code in beforeOpen method of Dataset:<br><br><em>this.queryText = this.queryText.replace("component_Type in (?)", "component_Type in ('" + params["rpComponentType"].value.join("','") + "')");</em></p>
    <p> </p>
    <p>Made no updates to report parameter. I noticed in every example on various forums and youtube videos, a secondary dataset is used to populate the parameter values. Is it necessary to populate the report parameter with a secondary dataset? Will it not work with values coming from JSP? Do I need to do settings differently for my scenario?</p>
    <p> </p>
    <p> </p>
    <p>Thanks,</p>
    <p>Shweta</p>