May 8, 2012 at 6:32 am
Hello all,
I have the following query in SSRS...
SELECT
a.JURISDICTIONCODE,
a.NAME,
to_number(REGEXP_REPLACE(REGEXP_REPLACE(b.value,'[a-zA-z]',''),',','')) as "Population"
FROM
DLSGATEWAY.JURISDICTION a,
DLSGATEWAY.SE_DATA b,
DLSGATEWAY.SE_DATA_VALUE_TYPE c
WHERE
b.JURISDICTION_CODE=a.JURISDICTIONCODE
AND b.VALUE_TYPE_ID=c.VALUE_TYPE_ID
AND c.VALUE_TYPE_ID = 51
AND b.YEAR = 2010
and ((b.value BETWEEN(:MinPop)AND(:MaxPop))OR((:MinPop IS NULL)AND(:MaxPop IS NULL)))
order by a.JURISDICTIONCODE
As you can see, I'm using a range population for b.value (or Population). The parameter works fine if BOTH parameter boxes (:MinPop) and (:MaxPop) have a value in them. Great. I want to know if there's a way to write this so that the user has the option to only use ONE of the parameter boxes, either :MinPop or :MaxPop
The above query is PART of this web report Im about to launch. However, if I can figure out the Population part, I can figure out the rest of my range parameters.
If you can't click the link, cutting and pasting it into your browser works.
Your time and consideration are much appreciated!
Mike
Many thanks!
May 8, 2012 at 7:26 am
mjbriggs03 (5/8/2012)
Hello all,I have the following query in SSRS...
SELECT
a.JURISDICTIONCODE,
a.NAME,
to_number(REGEXP_REPLACE(REGEXP_REPLACE(b.value,'[a-zA-z]',''),',','')) as "Population"
FROM
DLSGATEWAY.JURISDICTION a,
DLSGATEWAY.SE_DATA b,
DLSGATEWAY.SE_DATA_VALUE_TYPE c
WHERE
b.JURISDICTION_CODE=a.JURISDICTIONCODE
AND b.VALUE_TYPE_ID=c.VALUE_TYPE_ID
AND c.VALUE_TYPE_ID = 51
AND b.YEAR = 2010
and ((b.value BETWEEN(:MinPop)AND(:MaxPop))OR((:MinPop IS NULL)AND(:MaxPop IS NULL)))
order by a.JURISDICTIONCODE
As you can see, I'm using a range population for b.value (or Population). The parameter works fine if BOTH parameter boxes (:MinPop) and (:MaxPop) have a value in them. Great. I want to know if there's a way to write this so that the user has the option to only use ONE of the parameter boxes, either :MinPop or :MaxPop
The above query is PART of this web report Im about to launch. However, if I can figure out the Population part, I can figure out the rest of my range parameters.
If you can't click the link, cutting and pasting it into your browser works.
Your time and consideration are much appreciated!
Mike
Many thanks!
You should be able to do this in your where clause. Something like
WHERE (@MinPop IS NOT NULL AND @MaxPop IS NOT NULL AND b.value BETWEEN @MinPop AND @MaxPop)
OR
(@MinPop IS NOT NULL AND @MaxPop IS NULL AND b.value >= @MinPop)
OR
(@MinPop IS NULL AND @MaxPop IS NOT NULL AND b.value <= @MaxPop )
Something along these lines.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply