dynamic queries

Hello All,
I are just starting to use reporting server (2003) and I have hit a
problem. The reports I have created so far have had 3 or 4 sets of possible
parameters, and I have created a different report based on each set. i.e.
agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
The problem now is the next report has far, far too many parameter to
possibly create a report for each permutation. I can't figure out how to
dynamically create the where clause based on which parameters the report
gets passed. Just to clarify, the entire where clause needs to be dynamic,
not just the value.
I need something like this
SELECT blah
FROM table
WHERE IIF(Params!series <> null, "series=Params!series", "")
but this doesn't work.
any help is greatly appreciated,
CraigThe best option for this is to make use of Dynamic queries where you
build your queries dynamically inside the stored proc. You can pass the
superset of all parameters to the SP and check inside the SP. Refer
example below
Inputs to SP;
in_Name
in_Age
in_Sex
in_AcctNum
var str = 'Select * from ACCT_MASTER where'
if (in_name != null)
str = str + 'Name = ' + in_Name
etc.. etc...
finally execute the sp as
exec(str)
This is one of the way of satisfying your requirement|||YOu can also do
="SELECT blah FROM table "
& IIF(Parameters!Series.Value is nothing, ""," Where series='" &
Paramters!Series.Value & "'")
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Craig L" wrote:
> Hello All,
> I are just starting to use reporting server (2003) and I have hit a
> problem. The reports I have created so far have had 3 or 4 sets of possible
> parameters, and I have created a different report based on each set. i.e.
> agedTrialBalanceByCustomerNumber, agedTrialBalanceByAcccountNumber,
> agedTrialBalanceBySeries, and agedTrialBalanceByCustomerNumberAndSeries.
> The problem now is the next report has far, far too many parameter to
> possibly create a report for each permutation. I can't figure out how to
> dynamically create the where clause based on which parameters the report
> gets passed. Just to clarify, the entire where clause needs to be dynamic,
> not just the value.
> I need something like this
> SELECT blah
> FROM table
> WHERE IIF(Params!series <> null, "series=Params!series", "")
> but this doesn't work.
> any help is greatly appreciated,
> Craig
>
>