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.
>> >
>

Report Viewer Help

I need help in Reportviewer report. When i Run my report it pulls up all the information in the database but I want to show data by Month on each page. Does anyone know how to do this?

For example.

Jan 2007 , TOOLS, INFO, NUMBER

Feb 2007, TOOLS, INFO, NUMBER

JAN 2006, TOLLS, INFO, NUMBER

so this is from database now wat i want to do is i want to have each page with diffrent Year so page 1 will have JAn 2007 and FEB 2007 and the next page will have Jan 2006 then i want to sort it by Month on thos each page so i can export data for only that month. I need someone's help. I am not familar with report viewer

Anyone who knows how to do can you please respond. Thanks

You need help on the actual creation of the report. Try this link that has tutorials on creating reports. I think it will have what you are looking for:

http://msdn2.microsoft.com/en-us/library/ms170246.aspx

Report pulls only first record

Hello,

First I'd like to thank Michel Smit for providing the instructions on how to create/recreate a report using VWD then converting it to C# Express. It was a tremendous help.

My report is generating, however, it is only pulling the first record in the database (there are four records in all). My report uses a stored procedure shown below:

ALTER PROCEDURE dbo.StoredProcedure1

AS

SET NOCOUNT ON;

SELECT inventory.*, Feeding.*

FROM Feeding CROSS JOIN inventory

Is there something wrong with this stored procedure? Or is there some other step I need to include. I was experiencing the "A data source instance has not been supplied for the data source" error before and I corrected that issue when I discover the report was looking at the wrong data source. However, on the information I found to the correct the problem it said that when the correct source is located, the relevant components should be created on the design surface which they are not, could this be contributing to my issue?

walkswan

if you want to pull all records regardless of whether or not they have related records in another table, then you must research the use of LEFT JOIN and RIGHT JOIN.

Hope that helps. Without seeing your data i'm just guessing that only 1 of your records has a related record in the joined table. (i believe cross join, like inner join, only shows records that have related records in BOTH joined tables.)

|||

Thank you, unfortunately I can't get it to work at the moment. I ran three more types of queries based on the suggestion and they all returned the information I wanted but when I run the report I still only get the first record. I did double check and I have three records in the second table with id's that match records in the first table. Any other ideas?

walkswan