Report Parameter: display one value, storing another?

Hey.
 
Does anyone have an idea how to solve my problem: I'm trying to build a report parameter (multi value) so that it displays a value "service name", but stores a different value "serviceid".
 
The reason for this is that service name is the informative field which users know and can make a selection out of. So Report parameter should show the service name and user should be able to select 1-n out of the displayed names.
 
BUT: the service names might include commas (,) which seems to cause problems in storing the values. I'd like to store serviceid values instead for further use.
 
I tried to figure out whether I could affect the stored value in before open -script but it's too late by then.
 
SO: is it possible to present one type of values (string) in report parameter for user to choose from and according to the selection, store different type (integer) in the same report parameter? The data set I'm using for the representable values includes both name and id in the select -statement.
 
My Before Open script ->
First part that works:
this.queryText = this.queryText.replace("ostovoimat", params[RP_Ostovoiman_tila"].value.join("','");
This part doesn't:
this.queryText = this.queryText.replace("palvelut", params[RP_Palvelut"].value.join("','");
in this palvelut -part I should use the palveluid that's an integer..
 
The main data set SQL includes the usage of string arrays as follows:
AND ovs.tila in ('ostovoimat') AND p.palveluid in (palvelut)
-> ostovoimat works fine, palvelut not at the moment..
 
Thanks in advance, Elja
 
Ps. sorry about the little mistake in the topic, it seems I can't correct it after posting the topic.

Find more posts tagged with

Comments

donino

Why don't you simply use one report parameter, with "service name" as displayText, and "serviceid" as value column? This is precisely the purpose of the "displayText" property of a list parameter, and it would solve this trouble with commas.
 
For the second part of the question, if you want to filter a list of integer values you need to remove the quotes around the comma:
 
instead of this:
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("palvelut", params[RP_Palvelut"].value.join("','");</pre>
use this:
<pre class="_prettyXprint">
this.queryText = this.queryText.replace("palvelut", params[RP_Palvelut"].value.join(",");</pre>

Elja

Hey Donino and thanks a lot for your tips.
 
I seem to have forgot to mention that I'm trying to do this to a cascading parameter, but it seems to work the same way..
 
I'll try the things you suggested!
 
- Elja

Elja

Hey, some questions arouse when trying to make the report work (following the tips Donino gave me).
 
1) Cascading Report Parameter: Value and Display can be different fields, but can they be of different data type?
I'd need to show (Display text = char field) to the user and store the Value (another field of integer-type).
When configuring the parameter, the data type has to be determined.
So: If display text and value -fields are of different data types, to which one does the configurable data type refer to?
 
2) Before Open, current script:
this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','")); this.queryText = this.queryText.replace("palvelut", params["RP_Palvelut"].value.join(","));
 
-> RP_Palvelut should be integer (type bigint in postgresql db). Is it enough to drop the '' from .value.join(","));?
Or should the whole this.queryText -sentence be further modified?
 
3) In the SQL-statement:
AND p.palveluid in ('palvelut')
 
-> similar type of code worked with another Report Parameter (of string type).
Does integer type work differently?
 
- Elja

donino

1) The data type refers to the value, and the displayText property is always a char expression. Therefore this should meet your needs: there are many situations where cascading parameters are incredibly useful, but i don't think it is necessary here. 
 
2) If i correctly understood, remove the filter on "ostovoimat": only the filter on "palvelut" is required. Since this field is an integer, you should also remove quotes in SQL:
 
query:
<pre class="_prettyXprint">
AND p.palveluid in (palvelut)</pre>
beforeopen:
<pre class="_prettyXprint">
this.queryText.replace("palvelut", params["RP_Palvelut"].value.join(","));</pre>

Elja

Thanks a lot for your response once again, donino.
 
1) Ostovoimat parameter is a stand alone Report Parameter which I need for acquiring 1-n values from a field (which luckily only has 4 possible values).
 
2) I'm using 1 standalone Report Parameter (Ostovoimat, as described above).
I've also got 2 cascading parameters:
* Cascading report parameter 1: RP_Kunnat (cities in Finnish) -> user selects 1 city with this parameter, which is used in the next cascading parameter as a filter to fetch the services related to the selected city:
* Cascading report parameter 2: RP_Palvelut, which presents a list of values (services). It produces the service name (p.nimi, type character varying(150) in postgredb) and service id (p.palveluid, type bigint in postgredb).
 
RP_Palvelut is linked to Data Set Palvelut, which filters p.nimi and p.palveluid values with the following SQL-command:
 
SELECT DISTINCT 
p.nimi, p.palveluid 
FROM 
kuntahallinta.kunta k, 
kuntahallinta.palvelu p, 
kuntahallinta.palvelujarjestaja pj, 
kuntahallinta.palvelusisalto ps, 
kuntahallinta.ostovoimasisalto ovs 
WHERE 
p.palvelujarjestajaid = pj.palvelujarjestajaid AND 
pj.kuntaid = k.kuntaid AND 
ovs.palvelusisaltoid = ps.palvelusisaltoid AND 
ps.palveluid = p.palveluid AND 
ovs.toistuvuus <> 'EI_TOISTOA' AND 
k.nimi = 'Espoo' 
ORDER BY p.nimi ASC;
 
-> the select distinct fields are used in the report parameter, p.nimi as the display text and p.palveluid as the value. I set the k.nimi (city) to a city name 'Espoo' for the moment. I'm going to change it to k.nimi = ? as soon as I get this script to work (which I hope will then use dynamically the first chosen cascading parameter city as filter for the second value).
 
Now, I made the changes you introduced, that are necessary to make the integer work, but I still got an error (attached text file).
 
I tried to configure both value types "Integer" and "Decimal" (suggested) for RP_Palvelut, but the same error appeared. Could this be still a field type thing?
As palveluid- field type is defined as bigint in postgresql database, but birt report parameter doesn't include bigint? Or is it ok to select INT as the matching type in Birt Report Parameter?

donino

Ok understood. Yes, INT type should be fine here. This exception means at least one of the two parameters involved in beforeOpen script is null. Comment the script temporarily, and drag & drop these two parameters from the outline view onto the top of the report body: it will create 2 dynamic texts showing their respective values. Run the report, thus you will be able to find out what's going on.
 
Furthermore you should set a default value to each of these parameters, especially RP_Palvelut: this would help birt to preview data and analyze a resultset sample. And this way the script will never fail due to a "null" exception, it will make easier to find out where you made a mistake
 
I hope it helps

Elja

Thanks again, Donino.
 
It seems I'm (rather slowly) advancing to get this script work. Big thanks to you! 
 
Now, maybe the type isn't the issue after all.
I checked your new tips with "RP_Palvelut" -Report parameter. The parameter seems to show the 3 correct palveluid -values that are produced by the data set "Palvelut" -> [357, 838, 839]. 
They were shown on the top of the report along with the other "RP_Kunnat" -report parameters "city"-value.
 
I configured the fist value "357" as the default value of "RP_Palvelut". For some reason the Error in "Before Open" script still seems to be the same: referring to null value.
 
I'll attach some screen shots + files: 1. the report setup shown in birt, 2. the report parameter RP_Palvelut with the before open script, 3. the actual report showing the cascading report parameters on the top (below the title).
AND 4. a different Birt Error when I removed the "RP_Ostovoimat" part from the "Before Open" and "ostovoimat" array from the SQL-script:
* AND ovs.tila in ('ostovoimat')
* this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','"));
 
Once again thank you Donino for helping me out with this.
 
I guess the resolution lies in the before open -script, as the report parameter seems to have the correct int values. Maybe I'm not able to store 2 different this.queryText -sentences in row..
 
- Elja
 
Ps. Another related question: How is it possible to store a multi line script in before open (as many of the samples show)? I've been able to store only 1-line script, commands separated by ; -character.
 
Attachments:

donino

Ok, so Palvelut parameter is fine. However as a second parameter to be checked i was thinking "RP_Ostovoiman_tilat" rather than "RP_kunnat", because in the script failing it is one one of the two parameters being replaced.
 
EDIT: i think i got it. In the rptdesign you attached, the parameter is named "RP_Ostovoiman_tila", whereas in the script there is a "t" at the end. This is why it returns null. You should make use of "drag & drop" feature from the palette to include parameters in a script

Elja

Hey again.
I couldn't find an attached rptdesign. BUT I think I might have used "RP_Ostovoiman_tila" -name earlier. Now it's been "RP_Ostovoiman_tilat" for a while. The reason for change is that it's supposed to be multivalue (t in the end is usually plural in Finnish) AND I've deleted the parameters for numerous times as I've tried many different approaches to solve this problem.
 
But: I edited my response above and maybe my latest finding didn't stick out: I removed the working "RP_Ostovoiman_tilat" parameter -line from Before Open and the error produced was different (number 4 attachment above). Not the usual null-error anymore..
 
Would you have an idea what it could indicate?
PLUS: how the heck can I create a multi line script in Before Open? I'm only able to write one line. Could it be because of the browser I'm using (IE 8)?
 
- Elja

donino

You attached Asiakkaan_Kokonaisuus_TEST.rptdesign in another topic, this is where i found a difference between parameter names. Anyway this new error is a more common SQL issue. Did you preview the dataset without error in Eclipse designer? Usually when a parameter is an integer it is a better practice to set a valid value in the query statement, even if it will be replaced in beforeOpen. Thus we can preview data and check if the SQL statement is correct for birt, and if it is not the designer should give you more informations about the SQL error.
 
For example in this case, i would have used something like:
<pre class="_prettyXprint">
AND p.palveluid in (357,838)</pre>
and in beforeOpen:
<pre class="_prettyXprint">
this.queryText.replace("357,838", params["RP_Palvelut"].value.join(","));</pre>

Elja

You hawkeye...

Yep, I've been around this same report a while and there've been quite a few issues with these dynamic report parameters.
 
So, the name of the Report parameter has been changed a bit and it's now "RP_Ostovoiman_tilat".
 
I've run all the Data Sets SQL -statments directly in the db via pgAdmin III.
* Data set "Palvelut" (yesterdays comment 1:51 PM) I run with fixed city (Espoo) where as I'll normally use RP_Kunnat and city = ? in the SQL.
* Data set "Asiakkaan_kokonaisuus" is the main data set. I'll attach the corresponding SQL to the end of this reply just in case it helps with solving the issue. I've run it also via pgAdmin with a few alterations:
city = * -> city = 'Espoo'
and I've removed the rules
AND p.palveluid in (palvelut) AND ovs.tila in ('ostovoimat')
while running the script. It's been working fine. 
 
The thing you said about setting a valid value in Data Sets SQL -> it causes a problem.
 
The service.name (p.nimi) and service.serviceid (p.palveluid) info is filtered in "Palvelut" Data Set.
 
The report paramter usage shortly:
1) User selects a city (kunta). City values and selection is stored in Cascading parameter RP_Kunnat (which uses "Kunta" data set for city info)
2) User selects city's services via Cascading parameter RP_Palvelut: The chosen city info is used as a parameter in Data Set "Palvelut" for filtering the shown services according to the chosen city.
-> And hear is the problem: in the installment I'm trying to achieve, the "palveluid" values change according to chosen city, so I really cant have a set values to work with.
3) User selects Service bill state (stand alone Report parameter "RP_Ostovoiman_tilat", this is the third report parameter and it doesn't link to city or service. RP_Ostovoiman_tilat uses Data Set "Ostovoiman_tila" to acquire the 4 possible states to choose from.
 
So, out of these 3, the RP_Palvelut has been the problem child. Do you think the problem lies with dynamic array which I'm creating? It just seems to work fine with chars, It'd be awesome to be able to use with integers too. 
 
I tried before to use the name of the service, but I bumped into errors once again. Maybe the names containing "," charactes messed the script up somehow. That's why I stared working with serviceid as it is simple integer (usually only 3 digits long).
 
- Elja

donino

In the SQL statement you attached there is a wrong additional "AND" between palveluid and tila. It might not be the only issue but it doesn't help

:
<pre class="_prettyXprint">
AND p.palveluid in (palvelut) AND AND ovs.tila in ('ostovoimat')</pre>

Elja

Hey. Yep, thanks for that too. I noticed it but forgot to remove from the script I sent as an attachment.
 
What do you think of this part of the error-message:
"org.postgresql.util.PSQLException: ERROR: syntax error at or near ".357" 
Position: 547"?
 
The palveluid values were: [357, 838, 839].
 
Why is there a dot "." before value 357 in the error message? Is that part of the problem?
 
- Elja

donino

hum. yep you are right, i missed this .357 seems to be the first value of the parameter :blink: . I wonder if there is a trouble related to the localization of the decimal symbol, or something like that. If you create a dynamic text in the report with the expression:
<pre class="_prettyXprint">
params["RP_Palvelut"].value.join(",")</pre>
does it at least display the expected comma-separated string?
 
And what if you select one single value (only 357)? Does the query work like that?

Elja

hello again (and sorry for the absense, I was on vacation).
Unfortunately the problem still exists..
 
I added a dynamic text with expression
params["RP_Palvelut"].value.join(",")
-> but it doesn't show on the report.
 
As I select one value out of the services in "RP_Palvelut", only the 2 parameters I dragged and dropped show below the report title.
 
The heading-row shows but no data..
 
 
- Elja

Elja

Hey.
Something happened and the dynamic text
params["RP_Palvelut"].value.join(",")
at the top of the report shows correctly (

donino

Hi Elja, 
 
Most of beforeOpen scripts are multiple lines and work finely, i don't see why you face troubles with this in your context but it might be a clue for the query issue. Could you try the following:
<ul><li>Store your final query in a global variable "query" such below (in this case it does not need to be declared before)</li>
</ul><pre class="_prettyXprint">
try{
this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','" ));
this.queryText = this.queryText.replace("palvelut", params["RP_Palvelut"].value.join("," ));
query=this.queryText;
}catch(e){
query="Exception:"+e.message;
}</pre>
<ul><li> At the end of your report body (or at least after the element using the involved dataset), add a temporary dynamic text displaying the query. Just enter as expression:</li>
</ul><pre class="_prettyXprint">
query;</pre>
<ul><li>If the query is correctly displayed at the end of the report, copy it and try to run it directly in your postgresdb client. Let us know if this generated query is correct. </li>
<li>If the query is not displayed at the end, then something wrong happened in the script, however the dynamic text should give some clues because we caught possible error messages.</li>
<li>if the dynamic text is not displayed at all, it means the engine stopped the generation of the report before creating the dynamic text when it encountered a problem with postgres, In this case you would need to log the queryText at the end of beforeOpen script. There are a couple of topics describing how to log from a script. A better option would be to run the report using the "debug" perspective but it is more tricky. </li>
</ul>I hope it will help.

Elja

Hey again donino.
 
By the way, I've managed to get the report out now, even though the main data set still gives the null join -error.
I made a slight change (though I still wonder whether that made any difference) to the before open -script:this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','" ));this.queryText = this.queryText.replace("palveluidt", params["RP_Palvelut"].value.join("," ));
* I changed the name "palvelut" to "palveluidt" because the name palvelut was already in use as a data set name.
 
About the global variable:
 
I'm also not sure if I managed to place the script you gave in correct location. I searched the Birt documentation for global variables and learned it can be set in initialization of the report.
 
1) So I inserted in the report - design - property - Initialize:
try{this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','" )); this.queryText = this.queryText.replace("palvelut", params["RP_Palvelut"].value.join("," )); query=this.queryText;}catch(e){query="Exception:"+e.message;}
 
-> again I had to modify the script to be expressed on 1 line, cause I don't know how to write several line scripts in property value.
 
2) I added the dynamic text "query;" to the end of the report.
 
The report opened normally, but unfortunately the dynamic query -text at the end produced the following error:
 
"The report opened normally
Exception:Cannot call method "replace" of undefined"
 
Thanks for your help, Elja

donino

Oh i see, you make use of a property field to set the script... This is not the right way, see the screen below:

Elja

Nice.
 
Thanks a lot for straightening that for me. That's the place I've been searching for.
* Now, I transferred the script to beforeopen:
 
try{ 
 this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','" )); 
 this.queryText = this.queryText.replace("palveluidt", params["RP_Palvelut"].value.join("," )); 
 query=this.queryText; 
}catch(e){query="Exception:"+e.message; 
}
 
* And the result seems to be the main data sets (Asiakkaan_kokonaisuus) SQL-query, the whole result down below but the most interesting right here: AND ovs.tila in ('MYONNETTY','AVOIN','LUONNOS','SULJETTU') AND 
p.palveluid in (357,838,839)
 
-> so the correct id.s do appear here too (in p.palveluid array).
 
I also opened up the main data sets SQL-query and with the latest changes the error message is now different:

donino

Now the error seems to be a common postgres SQL issue. That sounds much better, but you forgot to mention the most important point: if you copy this generated query and run it on your database through a postgres client, does it work? If so, i don't see what could be the problem :wacko:

Elja

Hey and sorry about that.
 
I ran the query in pgadmin 3. After making a slight change to the script it worked fine. The part "WHERE kk.nimi = 'Espoo'..." was originally kk.nimi = ? (replacing the variable with a city name).
 
Query:
 
SELECT h.sukunimi ||' '|| h.etunimet AS Asiakas, ov.tunniste AS OV_tunniste, ovs.myontajannimi AS Myontaja, p.nimi AS 
Palvelu, p.palveluid, ps.nimi AS Palvelusisalto, ovs.arvoperyksikko, ovs.yksikoitamyonnetty AS MyonnetytYksikot, 
pt.yksikoitakaytetty AS KaytetytYksikot, ovs.tila AS Sisallon_Tila FROM Kuntahallinta.kunta kk, Kuntahallinta.kunta_asiakas ka, 
Kuntahallinta.asiakas asi, Kuntahallinta.henkilo h, Kuntahallinta.ostovoima ov, Kuntahallinta.ostovoimasisalto ovs, 
Kuntahallinta.palvelu p, Kuntahallinta.palvelusisalto ps, Kuntahallinta.palvelutapahtuma pt WHERE kk.nimi = 'Espoo' AND kk.kuntaid = 
ka.kuntaid AND ka.asiakasid = asi.asiakasid AND asi.henkiloid = h.henkiloid AND asi.asiakasid = ov.asiakasid AND 
ov.ostovoimaid = ovs.ostovoimaid AND ovs.tila <> '' AND ovs.tila in ('MYONNETTY','AVOIN','LUONNOS','SULJETTU') AND 
p.palveluid in (357,838,839) AND ovs.palvelusisaltoid = ps.palvelusisaltoid AND ps.palveluid = p.palveluid AND asi.asiakasid = 
pt.asiakasid AND pt.hyvitys = FALSE AND ((date(ovs.voimassaoloalku) BETWEEN '01.11.2014' AND '30.11.2014') OR 
(date(ovs.voimassaololoppu) BETWEEN '01.11.2014' AND '30.11.2014') OR ((date(ovs.voimassaoloalku) < '01.11.2014') AND 
(date(ovs.voimassaololoppu) > '30.11.2014'))) AND (date(pt.alku) BETWEEN '01.11.2014' AND '30.11.2014') ORDER BY 
Asiakas, OV_tunniste;
 
- Elja

donino

Great, glad to here it! So after fixing this parameter in the dataset your report is working finely now?

Elja

Unfortunately not..
 
the p.palveluid is defined differenty in this recent query: "AND p.palveluid in (357,838,839)". I'm trying to get the values to work with cascading report parameter "RP_Palvelut".
 
As this part of the query is always true, it doesn't matter what's chosen in the User Interface into RP_Palvelut: the resulting report is the same.
 
The basic problem still exists: how to get the value/values chosen with parameter RP_Palvelut to work with "Asiakkaan_kokonaisuus" data set (with no warnings)..
 
Well, the report IS ACTUALLY WORKING now, but it still gives the "join of null" warning,when I close the main data sets edit mode.
The thing that might have helped is that I renamed the variable "palvelut" to "palveluidt" inside the SQL -script (one of the data set's also called "palvelut").
 
- Elja

Elja

Hello again.
 
I'm still suffering from the problem with report parameter usage, in particular transferring int -type values as arrays into the report.
 
Before: I managed to pass 2 multi value Report parameter values into the report, using before open script:
this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','" )); 
this.queryText = this.queryText.replace("palveluidt", params["RP_Palvelut"].value.join("," ));
-> and added another int type multi value report parameter:
this.queryText = this.queryText.replace("asiakasidt", params["RP_Asiakkaat"].value.join("," ));
 
The string type is fine, but using either / both of the int type parameters (RP_Palvelut / RP_Asiakkaat), Birt still gives the error:
***
" + this.queryText = this.queryText.replace("ostovoimat", params["RP_Ostovoiman_tilat"].value.join("','" )); 
this.queryText = this.queryText.replace("palveluidt", params["RP_Palvelut"].value.join("," )); 
this.queryText = this.queryText.replace("asiakasidt", params["RP_Asiakkaat"].value.join("," )); + "

A BIRT exception occurred. See next exception for more information. 
TypeError: Cannot call method "join" of null (<inline>#1)
***
when valuating the main data sets SQL statement.
 
Last time I managed to create the report, but now the main Data Set shows no data fields to select into the report. They all disappear if I have the following part inserted in the SQL:
"AND p.palveluid in (palveluidt) AND asi.asiakasid in (asiakasidt)"
When I remove this from the SQL, data fields appear below the Main Data Set as expected.
 
Is there a problem with using int -type values with report parameters? I tried to pass a single int type inside SQL, like "asiakasid = ?", but I didn't manage that one either.
 
Could someone still help me with this problem? Should I continue with this problem under another topic (for example

<a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?/topic/35730-report-parameter-multiple-value-problem/' title="View result">Report Parameter - Multiple value problem</a>
, or create a new one?)
 
- Elja