How to make a parameter not required?

<p>Hello,</p>
<p> </p>
<p>I have several parameters for my report that a user selects before it runs. 4 of these are not required and I need the report to run when the user leaves them blank. I deselected the checkmark next to "is required" when I created the parameters, but the report is blank when it runs. </p>
<p> </p>
<p>What am I doing wrong? I am very new to BIRT, any help is appreciated

</p>

Find more posts tagged with

Comments

sureshpulapalli

<p>Are you using that parameters in report dataset? If possible, share the report design will have a look.</p>

raineyt

<p>I saw in this thread: <a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/31203-optional-parameter/ '>http://developer.actuate.com/community/forum/index.php?/topic/31203-optional-parameter/ </a></p>
<p> </p>
<p>that I needed to include a beforeOpen script for the data set, so I put the following into the script:</p>
<p> </p>
<div>if (params["Bldg Parameter"].value != "All"){</div>
<div>this.queryText = this.queryText + " where ae_p_phs_e.bldg = " + params["Bldg Parameter"].value;</div>
<div>}</div>
<div> </div>
<div>and the report works just fine using this for 1 optional parameter, but I need the report to have 4 optional parameters. </div>
<div> </div>
<div>Is there a way to expand this script to include all 4?</div>
<div> </div>
<div> </div>
<div> </div>
<div> </div>
<div>This is what my query looks like just in case: </div>
<div> </div>
<div>
<div>select ae_p_phs_e.proposal, ae_p_phs_e.sort_code, ae_p_phs_e.description,</div>
<div> ae_p_phs_e.long_desc, ae_p_phs_e.bldg, ae_p_phs_e.location_code, </div>
<div> ae_p_phs_e.category, ae_p_phs_e.status_code, ae_p_phs_e.asset_tag,</div>
<div> ae_p_phs_e_notes.notes, ae_p_phs_e.ent_clerk, ae_p_phs_e.ent_date,</div>
<div> ae_p_phs_e_notes.edit_date, ae_p_phs_e_notes.edit_clerk,</div>
<div> ae_p_phs_e.shop, ae_p_phs_e.craft_code,</div>
<div> CAST(FLOOR(CAST((ae_p_phs_e.ent_date) AS float))AS datetime) as 'DateCreated'</div>
<div> </div>
<div>from ae_p_phs_e </div>
<div> </div>
<div> full outer join ae_p_phs_e_notes</div>
<div> <span> </span> on ae_p_phs_e_notes.proposal = ae_p_phs_e.proposal and</div>
<div> ae_p_phs_e_notes.sort_code = ae_p_phs_e.sort_code</div>
<div> </div>
<div>where ae_p_phs_e.ent_clerk in ('SMCKIM','CTAKACH','BBRAY','ALAMBRIGHT','EPROPHITT',</div>
<div>'BSUTTER','TRABURN','MDESOUZA','DLACHANCE','MIKE','JKROOM','DSPEARMAN')</div>
<div> </div>
</div>

Clement Wong

<p>Yes, absolutely, you can.</p>
<p> </p>
<p>You would just append additional AND clauses to your WHERE clause in the <em>beforeOpen </em>event based on your parameters.</p>
<pre class="_prettyXprint">
if (params["Parameter2"].value != "All"){
this.queryText = this.queryText + " AND yourtable.columnName2 = " + params[Parameter2"].value;
}

// If string and you need to add single quote between the value, for example....
//
if (params["Parameter3"].value != "All"){
this.queryText = this.queryText + " AND yourtable.columnName3 = '" + params["Parameter3"].value + "'";
}

</pre>

raineyt

<p>Thanks for your help! </p>
<p> </p>
<p>Where do I need to add single quote?</p>
<p> </p>
<p>This is what I tried for the 4 parameters and it is not working:</p>
<p> </p>
<p> </p>
<p> </p>
<div>if (params["Bldg Parameter"].value != "All"){</div>
<div>this.queryText = this.queryText + " where ae_p_phs_e.bldg = " + params["Bldg Parameter"].value;</div>
<div>}</div>
<div> </div>
<div>if (params["Shop Parameter"].value != "All"){</div>
<div> this.queryText = this.queryText + " AND ae_p_phs_e.shop = " + params["Shop Parameter"].value;</div>
<div>}</div>
<div> </div>
<div>if (params["Room Parameter"].value != "All"){</div>
<div> this.queryText = this.queryText + " AND ae_p_phs_e.location_code = '" + params["Room Parameter"].value + "'";</div>
<div>}</div>
<div> </div>
<div>if (params["Work Code Parameter"].value != "All"){</div>
<div> this.queryText = this.queryText + " AND ae_p_phs_e.craft_code = '" + params["Work Code Parameter"].value + "'";</div>
<div>}</div>

Clement Wong

<p>If your parameter value is a string that is normally single quoted in your SQL statement, then you'll need a single quote to build your SELECT...WHERE... statement. You didn't specify what data types are defined in your SQL table for each of those parameter columns.</p>
<p> </p>
<p>The code is not working because of that, or you may not be building the SQL statement with the correct WHERE...AND....AND... syntax.</p>
<p> </p>
<p>It's best to add debug statements to output this.queryText after each concatenation to make sure it's what you are expected. Take a look at this post from Jesse that describes the methods to debug in OS and commercial BIRT (<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/36910-parameter-select-all-beforeopen-script/?p=137780'>http://developer.actuate.com/community/forum/index.php?/topic/36910-parameter-select-all-beforeopen-script/?p=137780</a>). </p>
<p> </p>
<p>It's also best to iteratively add each of those IF statements to make sure each are working before adding all 4 IF statements in.</p>
<p> </p>
<p>Also, I see above in post #3 that the base SQL statement ends in a WHERE clause? So if Bldg Parameter is not 'All', then instead of WHERE, it should be an AND.</p>

raineyt

<p>I finally got it working! Thank you so much. </p>
<p> </p>
<p>It was not working because all my parameters were strings, just as you said. </p>