Report with default of all

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.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
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> 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.
>|||Chris,
Getting invalid column name 'DeptID'. How does this work with no DeptID
column?
Chris Conner wrote:
> 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
> "chad" <chad.heilig@.gmail.com> wrote in message
> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
> > 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.
> >|||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
"chad" <chad.heilig@.gmail.com> wrote in message
news:1163601753.813127.152770@.h54g2000cwb.googlegroups.com...
> Chris,
> Getting invalid column name 'DeptID'. How does this work with no DeptID
> column?
>
> Chris Conner wrote:
>> 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
>> "chad" <chad.heilig@.gmail.com> wrote in message
>> news:1163534032.594157.292550@.m73g2000cwd.googlegroups.com...
>> > 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.
>> >
>