Parameter Problem

Hello:
RS Newbie here.
I have created a report with a parameter that has an ALL value that should
display all records when that particular parameter is selected.
Here is the code for my dataset:
SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
dbo.BuilderWO.Address,
dbo.BuilderWO.City, dbo.BuilderWO.State,
dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
dbo.BuilderWO.WalkDate,
dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
dbo.Employees.LName, dbo.Status.Status_Status
FROM dbo.Status RIGHT OUTER JOIN
dbo.BuilderWO ON dbo.Status.Status_StatusID = dbo.BuilderWO.Status LEFT OUTER JOIN
dbo.Locations ON dbo.BuilderWO.LocationID = dbo.Locations.LocationID LEFT OUTER JOIN
dbo.Employees ON dbo.BuilderWO.TSHRep1 = dbo.Employees.EmployeeID
WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID = @.location)
Here is the code for the parameter:
SELECT LocationID, Location AS Community FROM Locations WHERE Community = 1
UNION SELECT
-1, 'ALL'
My problem is that when I select ALL, no records are returned. The report
runs but just returns the Page Header items. If I select any other
location, the report runs fine.
It seems like I'm missing something. Any advice would be appreciated.
ThanksWHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID =@.location or @.location = -1)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> Hello:
> RS Newbie here.
> I have created a report with a parameter that has an ALL value that should
> display all records when that particular parameter is selected.
> Here is the code for my dataset:
> SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> dbo.BuilderWO.Address,
> dbo.BuilderWO.City, dbo.BuilderWO.State,
> dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> dbo.BuilderWO.WalkDate,
> dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> dbo.Employees.LName, dbo.Status.Status_Status
> FROM dbo.Status RIGHT OUTER JOIN
> dbo.BuilderWO ON dbo.Status.Status_StatusID => dbo.BuilderWO.Status LEFT OUTER JOIN
> dbo.Locations ON dbo.BuilderWO.LocationID => dbo.Locations.LocationID LEFT OUTER JOIN
> dbo.Employees ON dbo.BuilderWO.TSHRep1 => dbo.Employees.EmployeeID
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location)
>
> Here is the code for the parameter:
> SELECT LocationID, Location AS Community FROM Locations WHERE Community => 1
> UNION SELECT
> -1, 'ALL'
> My problem is that when I select ALL, no records are returned. The report
> runs but just returns the Page Header items. If I select any other
> location, the report runs fine.
> It seems like I'm missing something. Any advice would be appreciated.
> Thanks
>
>|||Great!
Thanks that worked.
"Bruce L-C [MVP]" wrote:
> WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => @.location or @.location = -1)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Brennan" <Brennan@.discussions.microsoft.com> wrote in message
> news:2D2E4216-C840-4DBE-828E-1CB494C08272@.microsoft.com...
> > Hello:
> >
> > RS Newbie here.
> >
> > I have created a report with a parameter that has an ALL value that should
> > display all records when that particular parameter is selected.
> >
> > Here is the code for my dataset:
> >
> > SELECT dbo.Locations.Location, dbo.BuilderWO.LotNum,
> > dbo.BuilderWO.WONum, dbo.BuilderWO.BFName, dbo.BuilderWO.BLName,
> > dbo.BuilderWO.Address,
> > dbo.BuilderWO.City, dbo.BuilderWO.State,
> > dbo.BuilderWO.Zip, dbo.BuilderWO.Phone, dbo.BuilderWO.CloseDate,
> > dbo.BuilderWO.WalkDate,
> > dbo.BuilderWO.ItemCount, dbo.BuilderWO.AnticCompDate,
> > dbo.Employees.LName, dbo.Status.Status_Status
> > FROM dbo.Status RIGHT OUTER JOIN
> > dbo.BuilderWO ON dbo.Status.Status_StatusID => > dbo.BuilderWO.Status LEFT OUTER JOIN
> > dbo.Locations ON dbo.BuilderWO.LocationID => > dbo.Locations.LocationID LEFT OUTER JOIN
> > dbo.Employees ON dbo.BuilderWO.TSHRep1 => > dbo.Employees.EmployeeID
> > WHERE (dbo.Locations.Community = 1) AND (dbo.Locations.LocationID => > @.location)
> >
> >
> > Here is the code for the parameter:
> >
> > SELECT LocationID, Location AS Community FROM Locations WHERE Community => > 1
> > UNION SELECT
> > -1, 'ALL'
> >
> > My problem is that when I select ALL, no records are returned. The report
> > runs but just returns the Page Header items. If I select any other
> > location, the report runs fine.
> >
> > It seems like I'm missing something. Any advice would be appreciated.
> >
> > Thanks
> >
> >
> >
> >
>
>