Using Single parameter value in multiple places of report (SQL)

arjunmanju · 2011-07-16T08:25:16+00:00

Hi all, I want to use a parameter value in multiple places of sql example : parameter name might be language : [value] <br…

arjunmanju

Hi all, 
 
 
I want to use a parameter value in multiple places of sql 
 
 
example : 
 
 
parameter name might be 
 
language : <del class='bbc'>[value]</del> 
 
 
I need the value of above parameter in different places of SQL 
 
 
example SQL: 
 
SELECT count(*),GET_CODE('11',column_name115,<del class='bbc'> ? </del>) 
 
from table1,table2 
 
where GET_CODE('1111',column_name1111 ,<del class='bbc'> ? </del>) = '' 
and GET_CODE('222',column_name85 ,<del class='bbc'> ? </del>) = '' 
 
 
 
 
In the above sql i want the value passed to parameter language must be replaced at all the <del class='bbc'>?</del>

Find more posts tagged with

Comments

Srividya Sharma

Hi

As far as I know, you will need to use 3 question marks in your sql and then 3 dataset parameters pointing to the same variable or report parameter.

Hope this helps.
Srividya Sharma

arjunmanju

Hi Srividya Sharma,

We can do it with different parameters say 3 as you said and referring to same value....but i want only one parameter value to be displayed at multiple places... I dont want to use multiple parameters for it... so can it be done??? please suggest me...
In the same example i want the value of one parameter to be put at multiple locations...

Srividya Sharma

Hi

Is there any reason or use case why you would not want to use 3 dataset parameters?

Srividya Sharma

Hans_vd

Hi,

This article may help: http://enterprisesmartapps.wordpress.com/2011/01/10/re-using-parameters-in-birt-data-set/

Regards
Hans

Srividya Sharma

Hi

Thank you. I learnt something new today.

Srividya Sharma

arjunmanju

Hi Srividya Sharma / Hans_vd

Thanks a lot for your suggestions ,I will definitely work with the solutions you had given, But just need a clarification that using WITH does not effect the rest of the SQL and its functionality right??? Just need a Clarification, I will post my SQL and I just want to know that using WITH is not going to effect other part of SQL

Thanks & Regards

bauwetom

Hi,

Using the WITH statement you create sort of a virtual table which you can then use in your main query. If you do it correctly it won't affect your current sql.
Good luck!

Tom

mwilliams

Another thing you can do to have only one report parameter represented several times within your query is to write your query in the beforeOpen of your dataSet or in the property binding section of the dataSet editor. Then you can just use the actual named report parameter. The script would just look something like this:

this.queryText = "SELECT count(*),GET_CODE('11',column_name115,"
+ params["language"] + " ) from table1,table2 where GET_CODE('1111',column_name1111 ,"
+ params["language"] + " ) = '' and GET_CODE('222',column_name85 ,"
+ params["language"] + " ) = ''"

This would remove the use of '?'s altogether.

arjunmanju

Hi all,

Thank you for your help

Arjun

bauwetom

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="80650" data-time="1311777376" data-date="27 July 2011 - 07:36 AM">
Another thing you can do to have only one report parameter represented several times within your query is to write your query in the beforeOpen of your dataSet or in the property binding section of the dataSet editor. Then you can just use the actual named report parameter. The script would just look something like this: 
 
this.queryText = "SELECT count(*),GET_CODE('11',column_name115," 
+ params["language"] + " ) from table1,table2 where GET_CODE('1111',column_name1111 ," 
+ params["language"] + " ) = '' and GET_CODE('222',column_name85 ," 
+ params["language"] + " ) = ''" 
 
This would remove the use of '?'s altogether. </blockquote>
 
Hi Michael, 
This is also a nice approach, the only thing though is that you circumvent the 'normal' Birt parameter handling with its built-in features like datatype checking and so on. 
We only use the this.queryText for occasions when we don't know the names of the tables we are selecting (for example a tablename based on a parameter: SALES_2011 where 2011 is a parameter) 
 
Regards, 
 
Tom