Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Report working like a seach engine.
alecsia
<p>Good evening!</p>
<p>I need your help again!</p>
<p> </p>
<p>I have one DataSet where I have two parameters, which should be optional. User can choose either the first one, or the second, or both. But in the case when one of the parameters is null – the report doesn’t work, for the query is built upon them both, the table simply doesn’t exist.</p>
<p> </p>
<p>Generally, I need to build a report, which must work like a search engine: the result should be correspondent to the search criteria, it must provide so-called “sorted†data, where user can get different results based on different parameters.</p>
<p> </p>
<p>Is there a way to achieve this goal??? </p>
Find more posts tagged with
Comments
micajblock
<p>Take a look at this DevShare. I think it will point you in the right direction. Instead of checking for 'All', check for null.</p>
<p> </p>
<p><a data-ipb='nomediaparse' href='
http://developer.actuate.com/community/forum/index.php?/files/file/1068-simple-example-of-select-all-parameter/'>http://developer.actuate.com/community/forum/index.php?/files/file/1068-simple-example-of-select-all-parameter/</a></p>
;
alecsia
<p>Thank you a lot! I used the second report, the updated one, it was easier to implement since I use DataBase Data Source and don’t use list parameters, only Text Box.</p>
<p> </p>
<p>But I still have some issues with null parameters. I have something like this:</p>
<pre class="_prettyXprint">
SELECT DISTINCT
DECODE_UNI (DOC.T_TERM_RESPONSE,DOC.C_TERM_RESPONSE) AS TERM_RESPONSE,
DECODE_UNI (DOC.T_CONSIDER_RES,DOC.C_CONSIDER_RES) AS RES_RESULT
FROM
DOC
WHERE
DOC.C_TERM_RESPONSE = ?
AND DOC.C_CONSIDER_RES = ?</pre>
<p>And when both of them are not required and one is chosen to be null, the query doesn’t return anything, obviously… I managed to get results by creating three separate tables: the first set parameter is for the first table, the second – for the second, and the third table is based on both set parameters. It works the way it should, but it is easy when I have only two parameters, but I have reports with at least 5-7 and I will simply be creating unnecessary tables and playing with their visibility…</p>
<p> </p>
<p>It seems I need to make the DataSet to ignore that one parameter which is not chosen… To get results based on the chosen only, without interference from the null one… I am not sure, is this possible in the one DataSet?</p>
micajblock
<p>Do not use a parameterized query use the beforeOpen event to add the where clause in script. Only add them when the value is not null.</p>
alecsia
<p>I have tried this: I used “1=1†in the WHERE of my Query, and my script looks like this:</p>
<pre class="_prettyXprint _lang-">
if ( params["TERM"].value != null)
this.queryText = this.queryText.replace("1=1","DOC.C_TERM_RESPONSE = '" + params["TERM"].value + "'");
if ( params["RES"].value != null)
this.queryText = this.queryText.replace("1=1","DOC.C_CONSIDER_RES = '" + params["RES"].value + "'");
</pre>
<p>I am not sure if this is a correct variant, though, because I use two parameters, probably, there is a different way of scripting for several parameters.</p>
<p>Anyway, it works when I pass values, but when I choose the “null value†– I get all results of my query. As if I pass the “all†value, but I don’t need this, I need a parameter to appear non-existing, empty and not to influence results…</p>
<p> </p>
<p>I tried to use a filter too, as in the sample report, but it gives the same result of “all†value.</p>
micajblock
<p>What version of BIRT are you using?</p>
alecsia
<p>I am using Indigo Eclipse, Version: 3.7.2. </p>
micajblock
<p>I do not have that version but your logic is wrong. Try something like this:</p>
<div>
<pre class="_prettyXprint">
var where="";
if ( params["TERM"].value != null) {
where = "DOC.C_TERM_RESPONSE = '" + params["TERM"].value + "'";
}
if ( params["RES"].value != null) {
if (where=="") {
where = "DOC.C_CONSIDER_RES = '" + params["RES"].value + "'";
}
else {where = where + " AND DOC.C_CONSIDER_RES = '" + params["RES"].value + "'"}
}
if (where !="") {this.queryText = this.queryText.replace("1=1",where)}</pre>
</div>
alecsia
<p>This is exactly what I wanted!!! I appreciate your help, thank you a lot!!!</p>