| Using parameters in WHERE clause.. (probably a stupid question) [message #220175] |
Wed, 21 February 2007 13:09  |
Eclipse User |
|
|
|
Originally posted by: ryan.seldonplan.com
Hi All,
This is probably a really stupid question, but if I don't ask I'll never
work it out. Google and eclipse.org don't turn up any examples, neither
does a title search for SQL on the last few thousand posts from this
group...
I've created a dataset in BIRT 2.1.2 in Eclipse, which will be used as the
source of a graph.
The SQL is:
SELECT
count(distinct ID) as total,
d.description
FROM licences L
INNER JOIN departments d ON d.id=l.dim_department
GROUP BY
d.description
That works fine.
The chart will eventually be embedded in a webpage, accessed via the
appropriate URL.
What I'd like to do is add pass a parameter by the url to the report, which
would then be put into the sql's where clause.
I've tried defining a "report parameter" called "department" and making my
SQL look like this:
SELECT
count(distinct ID) as total,
d.description
FROM licences L
INNER JOIN departments d ON d.id=l.dim_department
WHERE
d.dim_department=params["department"]
GROUP BY
d.description
However, that doesn't work.
Can anyone explain to me what I'm doing wrong?
Thanks in advance!
--
Ryan
|
|
|
| Re: Using parameters in WHERE clause.. (probably a stupid question) [message #220194 is a reply to message #220175] |
Wed, 21 February 2007 13:20  |
| Eclipse User |
|
|
|
Originally posted by: martin.tauber.t-online.de
Ryan,
try this:
add a "Report"-parameter to your report
enter the query using a ? as a placeholder for the parameter
SELECT
count(distinct ID) as total,
d.description
FROM licences L
INNER JOIN departments d ON d.id=l.dim_department
WHERE
d.dim_department = ?
GROUP BY
d.description
click on the "parameters" in the "edit data set"-dialog
add a "dataset"-parameter and link it to the "Report"-parameter
this should work ... If you are using String values I experianced that
the value of the parameter must be quoted.
Regards
Martin
Ryan Barrett schrieb:
> Hi All,
>
> This is probably a really stupid question, but if I don't ask I'll never
> work it out. Google and eclipse.org don't turn up any examples, neither
> does a title search for SQL on the last few thousand posts from this
> group...
>
> I've created a dataset in BIRT 2.1.2 in Eclipse, which will be used as the
> source of a graph.
>
> The SQL is:
>
> SELECT
> count(distinct ID) as total,
> d.description
> FROM licences L
> INNER JOIN departments d ON d.id=l.dim_department
> GROUP BY
> d.description
>
> That works fine.
>
> The chart will eventually be embedded in a webpage, accessed via the
> appropriate URL.
>
> What I'd like to do is add pass a parameter by the url to the report, which
> would then be put into the sql's where clause.
>
> I've tried defining a "report parameter" called "department" and making my
> SQL look like this:
>
> SELECT
> count(distinct ID) as total,
> d.description
> FROM licences L
> INNER JOIN departments d ON d.id=l.dim_department
> WHERE
> d.dim_department=params["department"]
> GROUP BY
> d.description
>
> However, that doesn't work.
>
> Can anyone explain to me what I'm doing wrong?
>
> Thanks in advance!
>
|
|
|
| Re: Using parameters in WHERE clause.. (probably a stupid question) [message #220210 is a reply to message #220194] |
Wed, 21 February 2007 13:45 |
| Eclipse User |
|
|
|
Originally posted by: ryan.seldonplan.com
Hi Martin,
Thanks, that works perfectly!
--
Ryan
"Martin Tauber" <martin.tauber@t-online.de> wrote in message
news:erhgu5$2i9$1@utils.eclipse.org...
> Ryan,
>
> try this:
>
> add a "Report"-parameter to your report
>
> enter the query using a ? as a placeholder for the parameter
>
> SELECT
> count(distinct ID) as total,
> d.description
> FROM licences L
> INNER JOIN departments d ON d.id=l.dim_department
> WHERE
> d.dim_department = ?
> GROUP BY
> d.description
>
> click on the "parameters" in the "edit data set"-dialog
>
> add a "dataset"-parameter and link it to the "Report"-parameter
>
>
> this should work ... If you are using String values I experianced that the
> value of the parameter must be quoted.
>
> Regards
> Martin
>
> Ryan Barrett schrieb:
>> Hi All,
>>
>> This is probably a really stupid question, but if I don't ask I'll never
>> work it out. Google and eclipse.org don't turn up any examples, neither
>> does a title search for SQL on the last few thousand posts from this
>> group...
>>
>> I've created a dataset in BIRT 2.1.2 in Eclipse, which will be used as
>> the source of a graph.
>>
>> The SQL is:
>>
>> SELECT
>> count(distinct ID) as total,
>> d.description
>> FROM licences L
>> INNER JOIN departments d ON d.id=l.dim_department
>> GROUP BY
>> d.description
>>
>> That works fine.
>>
>> The chart will eventually be embedded in a webpage, accessed via the
>> appropriate URL.
>>
>> What I'd like to do is add pass a parameter by the url to the report,
>> which would then be put into the sql's where clause.
>>
>> I've tried defining a "report parameter" called "department" and making
>> my SQL look like this:
>>
>> SELECT
>> count(distinct ID) as total,
>> d.description
>> FROM licences L
>> INNER JOIN departments d ON d.id=l.dim_department
>> WHERE
>> d.dim_department=params["department"]
>> GROUP BY
>> d.description
>>
>> However, that doesn't work.
>>
>> Can anyone explain to me what I'm doing wrong?
>>
>> Thanks in advance!
>>
|
|
|
Powered by
FUDForum. Page generated in 0.02968 seconds
] 
Search
Help
Register
Login
Home
