[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 
SpagoBI 4.1 - Multi Value Selection is not working  XML
Forum Index » Technical Help - SpagoBI Server - REPORTING
Author Message
zsoltputnoky.spw

SpagoBI friend

Joined: 03/12/2012 16:13:48
Messages: 94
Offline

Hello Forum,

I try to do the following query in SpagoBI using a BIRT report.

SELECT * FROM [view] WHERE [variable] IN (?)

When I run the query in the sql consol and replace the ? with the proper values, the result is okay.

In SpagoBI 4.1, I set the Analytical Driver Details to accept multivalue. I try to run the report, selecting the same values as I selected during the test. My report comes back empty! If I do the switch back and allow one value, the report works.

Have you experienced the same?

Thanks alot and Merry Xmas to all of you,

Zsolt
paolo.grassi.spw

Newby

Joined: 29/07/2013 12:05:46
Messages: 18
Offline

If I'm not wrong SpagoBI uses the ';' to separate the values, so the IN clause will not work properly.
In order to do that I suggest you to use string replacement (works well for me) such as:

(INSTR(?,CONCAT(d."SID",';'))>0 or LENGTH(?) = 1)

Pay attention that you have to link the parameters twice for each condition.
jepse.spw

Newby

Joined: 14/01/2011 16:16:37
Messages: 22
Offline

Hi there,

i ran into the same problem. By logging the sql query i figured out that the escape sequence from spago to birt is wrong. It surrounds every value with: ‘\’ value \’‘ Example


So you see, the escaping is the problem. It should be just




Is that a bug, or can I modify the escape sequence somewhere? I guess it’s a bug because the first code example has also a third parameter market2 in the middle ('\'foo\',\'\',\bar\'') which I didn’t choose, also the escape sequence would than be wrong.
jepse.spw

Newby

Joined: 14/01/2011 16:16:37
Messages: 22
Offline

so i figured it's correct with mysql to surround a String with \'. Than the multivalue problem is that birt surround the replaced parameter with '[parameter]1..n' again. Then we have : '\'[parameter1]\',\'[parameter2]\'' which obviously returns nothing because it’s interpreted as a complete string.

Any idea?
jepse.spw

Newby

Joined: 14/01/2011 16:16:37
Messages: 22
Offline

I have a workaround:

I don't use the parameter functionality ? Instead i placed my own Parameter i.e. $MULTI1 and modify it in the datasets beforeopen secion:

this.queryText = this.queryText.replace('$MULTI1', params['Paramname']);

Than it works!

Isthat the wanted usage?

This message was edited 1 time. Last update was at 31/01/2014 09:56:58

nishant.bahikar.spw.spw



Joined: 20/06/2017 09:47:52
Messages: 4
Offline

Hello all,

Was going through the same issue then later found out that problem was that
multivalue was passed as a sing varchar string along with comma.

so i modified my dataset and it worked

Older dataset -
[
select * from tablename where column in (?)
]
-- a, b

New dataset -
[

select * from tablename where column in
(select
regexp_split_to_table
(
( select replace ( (?::varchar),'"','') as a ), E',') as C )

]

-- ?::varchar -- it should be of type varchar
-- replace " with blank as when we select radio button or list it adds up " by itself ( not required for manual input parameter )
-- split values in multiple rows seperated by ,
-- mydatbase was postgres

Thanks ,
Nishant Bahikar


 
Forum Index » Technical Help - SpagoBI Server - REPORTING
Go to:   
Powered by JForum 2.1.9 © JForum Team