optional parameter

I'm attempting to use an optional parameter to build my report query. My base query is as follows: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select
o.orders_id,
o.orders_number,
o.date_purchased,
o.customers_name,
o.customers_street_address,
o.customers_city,
o.customers_state,
o.customers_postcode,
o.customers_telephone,
o.customers_email_address,
op.products_id,
op.products_name,
opa.products_options_values_id,
opa.products_options_values,
ot.value
from orders o
inner join orders_products op on o.orders_id = op.orders_id
left join orders_products_attributes opa on op.orders_products_id = opa.orders_products_id
inner join orders_total ot on o.orders_id = ot.orders_id
where o.orders_status = 5
and ot.class = 'ot_total'
and o.date_purchased >= ?
and o.date_purchased <= ?
</pre>
 
Then, on the data set's 'beforeOpen' script, i have this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (params["productIdParam"] != null && params["productIdParam"] != 0)
{
this.queryText += " and op.products_id = " + params["productIdParam"];
this.queryText += " order by opa.products_options_values_id, o.date_purchased;";
}
else
{
this.queryText += " order by op.products_id, opa.products_options_values_id, o.date_purchased;";
}
</pre>
 
When I run this query, if I do not supply a product id, I get nothing back; contrarily, if I do enter a product id, the report runs just as expected. I cannot see anything that's wrong with what I've done, but obviously, something is. I've attached my report design file. Any help on this would be greatly appreciated, thanks. 
 
Oh, also, I cannot get logging to work. I am using BIRT Report Designer (i.e. stand-alone - I think this is what the RCP refers to?), version 2.6.1. My goal would be to enable SQL logging so that I can see what BIRT is using to query the database; I would think this would help me debug these types of issues on my own. Again, any help with this would be great. Lastly, I have removed database connection details from the uploaded report, so just be aware of that.

Find more posts tagged with

Comments

mwilliams

Have you tried checking for an empty string as well? You might also store your query text into a variable and display this variable in your report, so you can see what is happening to your query when the value is null.

DispatchMG

So, I tried creating a report variable and then on the onFetch script of the data set, I attempt this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
vars["queryString"] = this.queryText;
</pre>
 
However, when I output that on the footer of my master page, it gives me nothing. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
<div>
Query String:
<value-of>vars["queryString"].value</value-of>
</div>
</pre>
 
I can't really seem to find any other way to set the variable than what I've attempted above. Is this something you would be able to do and perhaps provide me with a sample so that I can ape your methodology?

mwilliams

In your initialize script, put:

QT = "";

In your beforeOpen script (at the end), put:

QT = this.queryText;

In a dynamic textbox after a report item that is bound to your dataset, put:

QT;

This will show you what your query text is in your report.

DispatchMG

That got me my query text, thank you! I've not yet been able to do much debugging or troubleshooting, but the parameter is indeed coming in as null. I check for that with the following code: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
if (params["productIdParam"] != null && params["productIdParam"] != 0 && params["productIdParam"] != "null")
{
this.queryText += " and op.products_id = " + params["productIdParam"];
this.queryText += " order by opa.products_options_values_id, o.date_purchased;";
}
else
{
this.queryText += " order by op.products_id, opa.products_options_values_id, o.date_purchased;";
}

QT = this.queryText;
</pre>
 
However, even with that check in place, the query text keeps coming up as: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
QUERY:
select o.orders_id, o.orders_number, o.date_purchased, o.customers_name, o.customers_street_address, o.customers_city, o.customers_state, o.customers_postcode, o.customers_telephone, o.customers_email_address, op.products_id, op.products_name, opa.products_options_values_id, opa.products_options_values, ot.value from orders o inner join orders_products op on o.orders_id = op.orders_id left join orders_products_attributes opa on op.orders_products_id = opa.orders_products_id inner join orders_total ot on o.orders_id = ot.orders_id where o.orders_status = 5 and ot.class = 'ot_total' and o.date_purchased >= ? and o.date_purchased <= ? and op.products_id = null order by opa.products_options_values_id, o.date_purchased;
</pre>
 
Where the product id is still null, passing my checking and messing up the expected results. Hmm, I'll keep digging into this one. Thank you for your help!

mwilliams

You're welcome. If you can reproduce your issue with the sample database and attach the report so I can run it and test on it, two of us could look for the issue.