[Logo] SpagoWorld Forums
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Members]  Member Listing  
[Groups] Back to home page  [Moderation Log] Moderation Log   [Register] Register /  [Login] Login 
How to implement in SpagoBI Server a BIRT report parameter that allows multiple values [SOLVED]  XML
Forum Index » Technical Help - SpagoBI Server - REPORTING
Author Message
msnoeckx.spw



Joined: 26/05/2014 14:01:33
Messages: 9
Offline

Hi Annoul4,

Thanks for the quick reply!
I've tried this as you said, but it's not really working here.
Probably I'm still doing something wrong.
Do you maybe have an example of this ?

Thanks in advance!
Mike
annoul4.spw

SpagoBI supporter

Joined: 30/05/2012 14:31:16
Messages: 183
Offline

If your parameter is for example some project ids, your dataset query would be something like:



Click on the dataset and open the tab script and select beforeOpen.
There you would type:



That is you need to write the rest of the query that uses the parameter and 'params["projectId"].value' will get the parameter values.

Can you see any error in the logs?

This message was edited 1 time. Last update was at 26/09/2014 13:43:55


------------
Anna T.
msnoeckx.spw



Joined: 26/05/2014 14:01:33
Messages: 9
Offline

I did the following:

SQL:

SELECT
table.ms_pr, table.ms_sev, ...
FROM
...
WHERE
_SEV_CODE_


In the beforeOpen script:

this.queryText = this.queryText.replace("_SEV_CODE_", " table.ms_sev in ( "+params['rpSeverity'].value+" ) ");

the parameter rpSeverity is set as Textbox.

I get the error:

Caused by: java.sql.SQLException: ERROR: column "a" does not exist

So I also can't add any fields from the dataset into the report because it has a faulty/empty dataset.
annoul4.spw

SpagoBI supporter

Joined: 30/05/2012 14:31:16
Messages: 183
Offline

Is your dataset query correct? Maybe you have a typo.
Have you tried to execute the query from a tool to your database to check that it is correct?

------------
Anna T.
msnoeckx.spw



Joined: 26/05/2014 14:01:33
Messages: 9
Offline

Hi Annoul,

Yes, if I copy/paste the dataset in my sql tool it works correct.
I had the report & sql ready and running, but after replacing it with the variable etc, I get errors.


annoul4.spw wrote:Is your dataset query correct? Maybe you have a typo.
Have you tried to execute the query from a tool to your database to check that it is correct?

annoul4.spw

SpagoBI supporter

Joined: 30/05/2012 14:31:16
Messages: 183
Offline

To me this error looks like you are calling a column from the table that does not exist.
Can you copy/pase the whole query?

Once the query is correct then you ll be able to add fields to the report.

------------
Anna T.
msnoeckx.spw



Joined: 26/05/2014 14:01:33
Messages: 9
Offline

SELECT
ictmanager.im_request.ms_project_request as Project_req_nbr,
ictmanager_im_task_conf_dom.code as Task_conf_dom_code,
ictmanager_im_task_app_dom.code as Task_app_dom_code,
ictmanager.msp_projects.PROJ_CREATION_DATE as Project_req_creation_date,
ictmanager_im_appli_req.name as Req_app_name,
ictmanager.im_severity.code as Req_severity_code,
ictmanager.im_request.short_name as Req_short_name,
ictmanager.im_history.user_id as Req_created_by_user_ID,
fenics_user_req_a.USER_NAME as Req_assigned_to_user_name,
ictmanager.msp_projects.PROJ_NAME as Project_req_name,
ictmanager.im_request_type.code as Req_type_code,
ictmanager.msp_projects.PROJ_INFO_FINISH_DATE as Project_req_close_expected_date,
ictmanager.im_request.it_added_value as Req_state,
ictmanager.im_request.direct_customer_impact as Req_direct_customer_impact,
ictmanager.im_request.planned_man_days as Req_planned_man_days,
ictmanager_im_req_state.code as Req_state_code,
ictmanager.im_request.it_added_value+ictmanager.im_request.direct_customer_impact as Added_value,
case when ictmanager.im_request_type.code in ('Project', 'Board Project') then 'Project' else 'Request' end as Req_Type_OpenSummary
FROM
ictmanager.im_severity INNER JOIN ictmanager.im_request ON (ictmanager.im_request.severity=ictmanager.im_severity.oid)
LEFT OUTER JOIN ictmanager.im_request_type ON (ictmanager.im_request_type.oid=ictmanager.im_request.request_type)
INNER JOIN ictmanager.im_translation im_translation_req_type ON (im_translation_req_type.translated_oid=ictmanager.im_request_type.oid AND im_translation_req_type.language like 'en')
LEFT OUTER JOIN ictmanager.im_task ON (ictmanager.im_task.request=ictmanager.im_request.oid)
LEFT OUTER JOIN ictmanager.im_configuration ictmanager_im_confi_task ON (ictmanager_im_confi_task.oid=ictmanager.im_task.configuration)
LEFT OUTER JOIN ictmanager.im_domain ictmanager_im_task_conf_dom ON (ictmanager_im_task_conf_dom.oid=ictmanager_im_confi_task.domain)
LEFT OUTER JOIN ictmanager.im_application ictmanager_im_appli_task ON (ictmanager_im_appli_task.oid=ictmanager.im_task.application)
LEFT OUTER JOIN ictmanager.im_domain ictmanager_im_task_app_dom ON (ictmanager_im_task_app_dom.oid=ictmanager_im_appli_task.domain)
INNER JOIN ictmanager.msp_projects ON (ictmanager.msp_projects.proj_id=ictmanager.im_request.ms_project_request)
LEFT OUTER JOIN ictmanager.im_application ictmanager_im_appli_req ON (ictmanager.im_request.application=ictmanager_im_appli_req.oid)
LEFT OUTER JOIN ictmanager.im_domain ictmanager_im_dom_app ON (ictmanager_im_appli_req.domain=ictmanager_im_dom_app.oid)
LEFT OUTER JOIN fenics.fenics_user fenics_user_req_a ON (fenics_user_req_a.oid=ictmanager.im_request.assignee)
LEFT OUTER JOIN ictmanager.im_request_action ON (ictmanager.im_request.oid=ictmanager.im_request_action.request)
LEFT OUTER JOIN ictmanager.im_configuration ictmanager_im_confi_req ON (ictmanager.im_request.configuration=ictmanager_im_confi_req.oid)
LEFT OUTER JOIN ictmanager.im_domain ictmanager_im_dom_con ON (ictmanager_im_confi_req.domain=ictmanager_im_dom_con.oid)
LEFT OUTER JOIN ictmanager.im_task_state ictmanager_im_req_state ON (ictmanager_im_req_state.oid=ictmanager.im_request.state)
INNER JOIN ictmanager.im_history ON (ictmanager.im_history.linked_oid=ictmanager.im_request.oid and ictmanager.im_history.modify_date = (select min(test.modify_date) from ictmanager.im_history test where test.linked_oid = ictmanager.im_history.linked_oid))

WHERE
_SEV_CODE_


Script beforeOpen:
this.queryText = this.queryText.replace("_SEV_CODE_", " ictmanager.im_severity.code in ( "+params['rpSeverity'].value+" ) ");

And report parameter rpSeverity is a textbox.

annoul4.spw wrote:To me this error looks like you are calling a column from the table that does not exist.
Can you copy/pase the whole query?

Once the query is correct then you ll be able to add fields to the report.
annoul4.spw

SpagoBI supporter

Joined: 30/05/2012 14:31:16
Messages: 183
Offline



should be:




------------
Anna T.
msnoeckx.spw



Joined: 26/05/2014 14:01:33
Messages: 9
Offline

Hi,

I don't think that's the problem.
I've tried by removing the 'as' for this one and also removing the 'as' for all other fields in the query, but I still get the error.
If I replace the variable _SEV_CODE_ by ictmanager.im_severity.code in ('X','Y') it gives me results for X & Y.
It's just from the moment I add the variable, I get an empty dataset (including error "column b doesn't exist".
So, I can't add any objects to a table, because I don't have any fields to drag into it
Ans when I leave the variable out of it, drag the fields in a table and then add the variable I, again get an empty resultSet.


annoul4.spw wrote:

should be:



annoul4.spw

SpagoBI supporter

Joined: 30/05/2012 14:31:16
Messages: 183
Offline

1 thing:
I use double quotes when getting the param value. I dont know if it makes any difference.
For ex.


2. Have you tried adding a data report item with the value of the param to check that the param value you are getting is ok?

------------
Anna T.
jageerdar.spw



Joined: 27/04/2015 06:59:54
Messages: 8
Offline

Hi,

This is a very old thread, but i am facing same issue and not able to resolve, can you help me, here is my query



and here is my dataset beforeOpen ()



In SpagoBI i have created an LOV and Analytical driver, simple string with combobox and in BIRT also i have a parameter GroupName simple string static, but for some reason i keep getting:

Data (id = 417):
+ An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: G_Speed
Cannot set the string value ('Demo-AE-G','Demo-DRTC-G','Demo-DRT-G','Demo-SMC-G','Demo-SMQ-G') to parameter 3.
org.eclipse.birt.report.data.oda.jdbc.JDBCException: Cannot set preparedStatement parameter string value.
SQL error #1:The index 3 is out of range.
;
com.microsoft.sqlserver.jdbc.SQLServerException: The index 3 is out of range.


Thanks in Advance.
annoul4.spw

SpagoBI supporter

Joined: 30/05/2012 14:31:16
Messages: 183
Offline

This seems a little strange to me because it seems to be complaining about a 3rd parameter but I can only see 2 parameters in your query.
Is the query you are posting the correct one?

------------
Anna T.
jageerdar.spw



Joined: 27/04/2015 06:59:54
Messages: 8
Offline

Hi,

Yes the query is correct, i managed to resolve the issue, I noticed that I had created a parameter in BIRT and marked it as required, this parameter is the one which was supposed to get the value from SpagoBI, it would then pass the parameters correctly, i also changed the parameter line of the query with



and beforeOpen() code to



This seems to do the trick for me. I will deploy one more report with this method and see if this is consistent.

Thanks
sidalilo.spw



Joined: 01/09/2015 14:31:54
Messages: 4
Offline

hi
i have the problem of multi values :
HI,
I work on the SpagoBI Server 4.0 and I make templates by SpagoBI Studio and I have a problem with selection by multi value SpagoBI server
when I activate the option of multi values I receive a empty report and bearing I add the script to the studio template SpagoBI
EX:
category_Param var = "" + params ["sm"] value.join (".", "") + "";
if (category_Param.match ("(All)") == null) {
this.queryText this.queryText + = "where sm IN (" + category_Param + ")"
// IN ("+ category_Param +") "
}
NB: this script works in the studio but not in the SpagoBI spago server.

if you solved the problem thank you to send me the solution
thank you very much first for your attention and response.
very cordially.
ALI.




jageerdar.spw wrote:Hi,

Yes the query is correct, i managed to resolve the issue, I noticed that I had created a parameter in BIRT and marked it as required, this parameter is the one which was supposed to get the value from SpagoBI, it would then pass the parameters correctly, i also changed the parameter line of the query with



and beforeOpen() code to



This seems to do the trick for me. I will deploy one more report with this method and see if this is consistent.

Thanks

This message was edited 1 time. Last update was at 01/09/2015 15:28:16

sd.spw

Newby

Joined: 14/12/2015 10:55:15
Messages: 10
Offline

If you keep the parameters as normal String you can write the query in this way:


The Below step is in studio or server ? If it is server please explain the steps


Select …
From …
Where …. And _PROJECT_ID_

Now click on the data set, and open the tab Script.
In the main panel select Script: beforeOpen in the combo box and write this code

this.queryText = this.queryText.replace(_PROJECT_ID_, “ projectId in ( “+params["projectId"].value+” ) ”);

In this way you are substituting the placeholder _PROJECT_ID_ in the query with the string “ projectId in ( “+params["projectId"].value+” ) ”

In this way the query to execute will be something like

Select …
From …
Where …. And projectId in ( [values in the parameter projectId] )
 
Forum Index » Technical Help - SpagoBI Server - REPORTING
Go to:   
Powered by JForum 2.1.9 © JForum Team