sql server reporting services: Report with default of all -- sql server reporting services

You're in the

sql server reporting services

group:

Report with default of all

chad
11/14/2006 11:53:52 AM

sql server reporting services:

Have a report with three datasets. The first dataset pulls the
department table and uses this as one of the parameters.
select DEPT from dept

The second dataset pulls the division table and uses that as one of the
parameters.
select DIV from div

The third dataset calls a stored procedure that passes the startdate,
enddate, division and department for the report. With the startdate and
enddate also being parameters.
exec wo_sstm @startdate, @enddate, @division, @department

How can I set the parameters for @division and @department to utilize a
default of all for each.

Re: Report with default of all

Chris Conner
11/14/2006 3:22:31 PM

for your first dataset for dept:

select 0 as DeptID, '<All>' as DEPT FROM dept
UNION ALL
select DeptID, DEPT from dept

Then your value will be DeptID and label will be DEPT in Parameters

In your second dataset, same thing:

select 0 AS DivisionID, '<All>' as DIV from div
UNION ALL
select DivisionID, DIV from div

Then in your stored procedure wo_sstm you will process 0 as the indicator
for all.

=-Chris

[quoted text, click to view]

Re: Report with default of all

chad
11/15/2006 6:42:33 AM

Chris,
Getting invalid column name 'DeptID'. How does this work with no DeptID
column?

[quoted text, click to view]

Re: Report with default of all

Chris Conner
11/15/2006 10:31:10 AM

No - DeptID is something I made up - Do you have a column that uniquely
identifies your departments? I assumed you would understand that DeptID in
this case was an ID that uniquely identifies that department.

=-Chris

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn