Parameter All Values and Values

I am new at this parameter thing. I have a paramater that uses a drop down
list and can select multiple values. How can I let the user choose all of
the values without having to click on all the values individually. And also
How can I get the parameter to import the values into the dropdown list
automatically instead of me having to type them all in?
ThanksThe easiest way I have found to solve your problem is to create a new dataset
to select the values for the parameter. If you create a union for the value
(ALL), and order by the values, (ALL) will be at the top of the list - for
example (SELECT DISTINCT(CUSTNMBR)
, RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
LEN(CUSTNMBR))) AS CUSTNAME
FROM RM00101 (NOLOCK)
UNION
SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
ORDER BY CUSTNAME ASC) . In the report parameters, select available values
from query, and chose your new dataset and the appropriate label and value.
If you want, you can set the nonqueried default to (ALL). In the main
dataset, use a where clause to set the field value equal to the parameter, OR
the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
OR
@.CUSTOMER IS NULL).
I hope this helps.
"Benw" wrote:
> I am new at this parameter thing. I have a paramater that uses a drop down
> list and can select multiple values. How can I let the user choose all of
> the values without having to click on all the values individually. And also
> How can I get the parameter to import the values into the dropdown list
> automatically instead of me having to type them all in?
> Thanks|||Yes that helped ALOT!!! Here is my query for the parameter. I still need to
add the ALL function.
SELECT Empid
FROM smServFault
WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
GROUP BY Empid
ORDER BY Empid
"Joe" wrote:
> The easiest way I have found to solve your problem is to create a new dataset
> to select the values for the parameter. If you create a union for the value
> (ALL), and order by the values, (ALL) will be at the top of the list - for
> example (SELECT DISTINCT(CUSTNMBR)
> , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> LEN(CUSTNMBR))) AS CUSTNAME
> FROM RM00101 (NOLOCK)
> UNION
> SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> from query, and chose your new dataset and the appropriate label and value.
> If you want, you can set the nonqueried default to (ALL). In the main
> dataset, use a where clause to set the field value equal to the parameter, OR
> the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> OR
> @.CUSTOMER IS NULL).
> I hope this helps.
> "Benw" wrote:
> > I am new at this parameter thing. I have a paramater that uses a drop down
> > list and can select multiple values. How can I let the user choose all of
> > the values without having to click on all the values individually. And also
> > How can I get the parameter to import the values into the dropdown list
> > automatically instead of me having to type them all in?
> >
> > Thanks|||Try this. I don't think you need the "GROUP BY", and I normally use
DISTINCT, but I don't know your situation.
SELECT Empid AS EMPLOYEE
FROM smServFault
WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
UNION
SELECT '(ALL)' AS EMPLOYEE
GROUP BY EMPLOYEE
ORDER BY EMPLOYEE
"Benw" wrote:
> Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> add the ALL function.
> SELECT Empid
> FROM smServFault
> WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> GROUP BY Empid
> ORDER BY Empid
>
> "Joe" wrote:
> > The easiest way I have found to solve your problem is to create a new dataset
> > to select the values for the parameter. If you create a union for the value
> > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > example (SELECT DISTINCT(CUSTNMBR)
> > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > LEN(CUSTNMBR))) AS CUSTNAME
> > FROM RM00101 (NOLOCK)
> > UNION
> > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > from query, and chose your new dataset and the appropriate label and value.
> > If you want, you can set the nonqueried default to (ALL). In the main
> > dataset, use a where clause to set the field value equal to the parameter, OR
> > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > OR
> > @.CUSTOMER IS NULL).
> >
> > I hope this helps.
> > "Benw" wrote:
> >
> > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > list and can select multiple values. How can I let the user choose all of
> > > the values without having to click on all the values individually. And also
> > > How can I get the parameter to import the values into the dropdown list
> > > automatically instead of me having to type them all in?
> > >
> > > Thanks|||That worked awesome. Thank you so much for the tips. I have one more
question and I will leave you alone. LOL. I want the user to be able to
choose a blank parameter from the EMPLOYEE. It works fine in the report
writer but on the report server it tells me to enter a value. What should I
do?
"Joe" wrote:
> Try this. I don't think you need the "GROUP BY", and I normally use
> DISTINCT, but I don't know your situation.
> SELECT Empid AS EMPLOYEE
> FROM smServFault
> WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> UNION
> SELECT '(ALL)' AS EMPLOYEE
> GROUP BY EMPLOYEE
> ORDER BY EMPLOYEE
>
>
> "Benw" wrote:
> > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > add the ALL function.
> >
> > SELECT Empid
> > FROM smServFault
> > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > GROUP BY Empid
> > ORDER BY Empid
> >
> >
> > "Joe" wrote:
> >
> > > The easiest way I have found to solve your problem is to create a new dataset
> > > to select the values for the parameter. If you create a union for the value
> > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > example (SELECT DISTINCT(CUSTNMBR)
> > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > LEN(CUSTNMBR))) AS CUSTNAME
> > > FROM RM00101 (NOLOCK)
> > > UNION
> > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > from query, and chose your new dataset and the appropriate label and value.
> > > If you want, you can set the nonqueried default to (ALL). In the main
> > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > OR
> > > @.CUSTOMER IS NULL).
> > >
> > > I hope this helps.
> > > "Benw" wrote:
> > >
> > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > list and can select multiple values. How can I let the user choose all of
> > > > the values without having to click on all the values individually. And also
> > > > How can I get the parameter to import the values into the dropdown list
> > > > automatically instead of me having to type them all in?
> > > >
> > > > Thanks|||I'm afraid I'll have to defer that one to someone else. What results would
you expect to get?
"Benw" wrote:
> That worked awesome. Thank you so much for the tips. I have one more
> question and I will leave you alone. LOL. I want the user to be able to
> choose a blank parameter from the EMPLOYEE. It works fine in the report
> writer but on the report server it tells me to enter a value. What should I
> do?
> "Joe" wrote:
> > Try this. I don't think you need the "GROUP BY", and I normally use
> > DISTINCT, but I don't know your situation.
> >
> > SELECT Empid AS EMPLOYEE
> > FROM smServFault
> > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > UNION
> > SELECT '(ALL)' AS EMPLOYEE
> > GROUP BY EMPLOYEE
> > ORDER BY EMPLOYEE
> >
> >
> >
> >
> > "Benw" wrote:
> >
> > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > add the ALL function.
> > >
> > > SELECT Empid
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > GROUP BY Empid
> > > ORDER BY Empid
> > >
> > >
> > > "Joe" wrote:
> > >
> > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > to select the values for the parameter. If you create a union for the value
> > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > FROM RM00101 (NOLOCK)
> > > > UNION
> > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > from query, and chose your new dataset and the appropriate label and value.
> > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > OR
> > > > @.CUSTOMER IS NULL).
> > > >
> > > > I hope this helps.
> > > > "Benw" wrote:
> > > >
> > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > list and can select multiple values. How can I let the user choose all of
> > > > > the values without having to click on all the values individually. And also
> > > > > How can I get the parameter to import the values into the dropdown list
> > > > > automatically instead of me having to type them all in?
> > > > >
> > > > > Thanks|||I want to ba able to choose a blank value so I can get all rows with EMPLOYEE
that is blank. I want to choose blank and get a rows that the EMPLOYYEE is
blank. Like I said its weird, it works in in the report writer but not on
the report server.
"Joe" wrote:
> I'm afraid I'll have to defer that one to someone else. What results would
> you expect to get?
> "Benw" wrote:
> > That worked awesome. Thank you so much for the tips. I have one more
> > question and I will leave you alone. LOL. I want the user to be able to
> > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > writer but on the report server it tells me to enter a value. What should I
> > do?
> >
> > "Joe" wrote:
> >
> > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > DISTINCT, but I don't know your situation.
> > >
> > > SELECT Empid AS EMPLOYEE
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > UNION
> > > SELECT '(ALL)' AS EMPLOYEE
> > > GROUP BY EMPLOYEE
> > > ORDER BY EMPLOYEE
> > >
> > >
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > add the ALL function.
> > > >
> > > > SELECT Empid
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > GROUP BY Empid
> > > > ORDER BY Empid
> > > >
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > to select the values for the parameter. If you create a union for the value
> > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > FROM RM00101 (NOLOCK)
> > > > > UNION
> > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > OR
> > > > > @.CUSTOMER IS NULL).
> > > > >
> > > > > I hope this helps.
> > > > > "Benw" wrote:
> > > > >
> > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > the values without having to click on all the values individually. And also
> > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > automatically instead of me having to type them all in?
> > > > > >
> > > > > > Thanks|||I just noticed something with the "ALL". It wont pull anything. I have to
select the individual Employee for it to pull anything. The ALL wont work.
Thanks, you have been a huge help.
"Joe" wrote:
> I'm afraid I'll have to defer that one to someone else. What results would
> you expect to get?
> "Benw" wrote:
> > That worked awesome. Thank you so much for the tips. I have one more
> > question and I will leave you alone. LOL. I want the user to be able to
> > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > writer but on the report server it tells me to enter a value. What should I
> > do?
> >
> > "Joe" wrote:
> >
> > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > DISTINCT, but I don't know your situation.
> > >
> > > SELECT Empid AS EMPLOYEE
> > > FROM smServFault
> > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > UNION
> > > SELECT '(ALL)' AS EMPLOYEE
> > > GROUP BY EMPLOYEE
> > > ORDER BY EMPLOYEE
> > >
> > >
> > >
> > >
> > > "Benw" wrote:
> > >
> > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > add the ALL function.
> > > >
> > > > SELECT Empid
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > GROUP BY Empid
> > > > ORDER BY Empid
> > > >
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > to select the values for the parameter. If you create a union for the value
> > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > FROM RM00101 (NOLOCK)
> > > > > UNION
> > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > OR
> > > > > @.CUSTOMER IS NULL).
> > > > >
> > > > > I hope this helps.
> > > > > "Benw" wrote:
> > > > >
> > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > the values without having to click on all the values individually. And also
> > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > automatically instead of me having to type them all in?
> > > > > >
> > > > > > Thanks|||It should. Did you add the @.employee=null to your SQL statement?
"Benw" wrote:
> I just noticed something with the "ALL". It wont pull anything. I have to
> select the individual Employee for it to pull anything. The ALL wont work.
> Thanks, you have been a huge help.
> "Joe" wrote:
> > I'm afraid I'll have to defer that one to someone else. What results would
> > you expect to get?
> >
> > "Benw" wrote:
> >
> > > That worked awesome. Thank you so much for the tips. I have one more
> > > question and I will leave you alone. LOL. I want the user to be able to
> > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > writer but on the report server it tells me to enter a value. What should I
> > > do?
> > >
> > > "Joe" wrote:
> > >
> > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > DISTINCT, but I don't know your situation.
> > > >
> > > > SELECT Empid AS EMPLOYEE
> > > > FROM smServFault
> > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > UNION
> > > > SELECT '(ALL)' AS EMPLOYEE
> > > > GROUP BY EMPLOYEE
> > > > ORDER BY EMPLOYEE
> > > >
> > > >
> > > >
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > add the ALL function.
> > > > >
> > > > > SELECT Empid
> > > > > FROM smServFault
> > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > GROUP BY Empid
> > > > > ORDER BY Empid
> > > > >
> > > > >
> > > > > "Joe" wrote:
> > > > >
> > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > FROM RM00101 (NOLOCK)
> > > > > > UNION
> > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > OR
> > > > > > @.CUSTOMER IS NULL).
> > > > > >
> > > > > > I hope this helps.
> > > > > > "Benw" wrote:
> > > > > >
> > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > the values without having to click on all the values individually. And also
> > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > automatically instead of me having to type them all in?
> > > > > > >
> > > > > > > Thanks|||I am so lost now. Here is my EmpId Parameter
SELECT Empid AS EMPLOYEE
FROM smServFault
WHERE (TaskStatus <> 'C')
UNION
SELECT '(ALL)' AS EMPLOYEE
ORDER BY EMPLOYEE
And here is my main query
SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
smServFault.Notes, smServFault.CauseID,
smServFault.ResolutionID, smServCall.ServiceCallCompleted
FROM smServCall INNER JOIN
smServFault ON smServCall.ServiceCallID =smServFault.ServiceCallId
WHERE (smServFault.TaskStatus <> 'C') AND
(smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
AND
(smServFault.StartDate = @.date)
ORDER BY smServFault.StartDate
"Joe" wrote:
> It should. Did you add the @.employee=null to your SQL statement?
> "Benw" wrote:
> > I just noticed something with the "ALL". It wont pull anything. I have to
> > select the individual Employee for it to pull anything. The ALL wont work.
> > Thanks, you have been a huge help.
> >
> > "Joe" wrote:
> >
> > > I'm afraid I'll have to defer that one to someone else. What results would
> > > you expect to get?
> > >
> > > "Benw" wrote:
> > >
> > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > writer but on the report server it tells me to enter a value. What should I
> > > > do?
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > DISTINCT, but I don't know your situation.
> > > > >
> > > > > SELECT Empid AS EMPLOYEE
> > > > > FROM smServFault
> > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > UNION
> > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > GROUP BY EMPLOYEE
> > > > > ORDER BY EMPLOYEE
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Benw" wrote:
> > > > >
> > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > add the ALL function.
> > > > > >
> > > > > > SELECT Empid
> > > > > > FROM smServFault
> > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > GROUP BY Empid
> > > > > > ORDER BY Empid
> > > > > >
> > > > > >
> > > > > > "Joe" wrote:
> > > > > >
> > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > UNION
> > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > OR
> > > > > > > @.CUSTOMER IS NULL).
> > > > > > >
> > > > > > > I hope this helps.
> > > > > > > "Benw" wrote:
> > > > > > >
> > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > automatically instead of me having to type them all in?
> > > > > > > >
> > > > > > > > Thanks|||Try this.
(smServFault.Empid = @.vendor OR @.vendor IS NULL)
"Benw" wrote:
> I am so lost now. Here is my EmpId Parameter
> SELECT Empid AS EMPLOYEE
> FROM smServFault
> WHERE (TaskStatus <> 'C')
> UNION
> SELECT '(ALL)' AS EMPLOYEE
> ORDER BY EMPLOYEE
> And here is my main query
> SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
> smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
> smServFault.Notes, smServFault.CauseID,
> smServFault.ResolutionID, smServCall.ServiceCallCompleted
> FROM smServCall INNER JOIN
> smServFault ON smServCall.ServiceCallID => smServFault.ServiceCallId
> WHERE (smServFault.TaskStatus <> 'C') AND
> (smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
> AND
> (smServFault.StartDate = @.date)
> ORDER BY smServFault.StartDate
> "Joe" wrote:
> > It should. Did you add the @.employee=null to your SQL statement?
> >
> > "Benw" wrote:
> >
> > > I just noticed something with the "ALL". It wont pull anything. I have to
> > > select the individual Employee for it to pull anything. The ALL wont work.
> > > Thanks, you have been a huge help.
> > >
> > > "Joe" wrote:
> > >
> > > > I'm afraid I'll have to defer that one to someone else. What results would
> > > > you expect to get?
> > > >
> > > > "Benw" wrote:
> > > >
> > > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > > writer but on the report server it tells me to enter a value. What should I
> > > > > do?
> > > > >
> > > > > "Joe" wrote:
> > > > >
> > > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > > DISTINCT, but I don't know your situation.
> > > > > >
> > > > > > SELECT Empid AS EMPLOYEE
> > > > > > FROM smServFault
> > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > UNION
> > > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > > GROUP BY EMPLOYEE
> > > > > > ORDER BY EMPLOYEE
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Benw" wrote:
> > > > > >
> > > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > > add the ALL function.
> > > > > > >
> > > > > > > SELECT Empid
> > > > > > > FROM smServFault
> > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > GROUP BY Empid
> > > > > > > ORDER BY Empid
> > > > > > >
> > > > > > >
> > > > > > > "Joe" wrote:
> > > > > > >
> > > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > > UNION
> > > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > > OR
> > > > > > > > @.CUSTOMER IS NULL).
> > > > > > > >
> > > > > > > > I hope this helps.
> > > > > > > > "Benw" wrote:
> > > > > > > >
> > > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > > automatically instead of me having to type them all in?
> > > > > > > > >
> > > > > > > > > Thanks|||is this for the ALL not working. Because it still wont.
"Joe" wrote:
> Try this.
> (smServFault.Empid = @.vendor OR @.vendor IS NULL)
> "Benw" wrote:
> > I am so lost now. Here is my EmpId Parameter
> >
> > SELECT Empid AS EMPLOYEE
> > FROM smServFault
> > WHERE (TaskStatus <> 'C')
> > UNION
> > SELECT '(ALL)' AS EMPLOYEE
> > ORDER BY EMPLOYEE
> >
> > And here is my main query
> >
> > SELECT smServCall.ShiptoId, smServFault.FaultCodeId, smServFault.Empid,
> > smServFault.TaskStatus, smServFault.StartDate, smServFault.WarrStart,
> > smServFault.Notes, smServFault.CauseID,
> > smServFault.ResolutionID, smServCall.ServiceCallCompleted
> > FROM smServCall INNER JOIN
> > smServFault ON smServCall.ServiceCallID => > smServFault.ServiceCallId
> > WHERE (smServFault.TaskStatus <> 'C') AND
> > (smServCall.ServiceCallCompleted = 0) AND (smServFault.Empid IN (@.vendor))
> > AND
> > (smServFault.StartDate = @.date)
> > ORDER BY smServFault.StartDate
> >
> > "Joe" wrote:
> >
> > > It should. Did you add the @.employee=null to your SQL statement?
> > >
> > > "Benw" wrote:
> > >
> > > > I just noticed something with the "ALL". It wont pull anything. I have to
> > > > select the individual Employee for it to pull anything. The ALL wont work.
> > > > Thanks, you have been a huge help.
> > > >
> > > > "Joe" wrote:
> > > >
> > > > > I'm afraid I'll have to defer that one to someone else. What results would
> > > > > you expect to get?
> > > > >
> > > > > "Benw" wrote:
> > > > >
> > > > > > That worked awesome. Thank you so much for the tips. I have one more
> > > > > > question and I will leave you alone. LOL. I want the user to be able to
> > > > > > choose a blank parameter from the EMPLOYEE. It works fine in the report
> > > > > > writer but on the report server it tells me to enter a value. What should I
> > > > > > do?
> > > > > >
> > > > > > "Joe" wrote:
> > > > > >
> > > > > > > Try this. I don't think you need the "GROUP BY", and I normally use
> > > > > > > DISTINCT, but I don't know your situation.
> > > > > > >
> > > > > > > SELECT Empid AS EMPLOYEE
> > > > > > > FROM smServFault
> > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > UNION
> > > > > > > SELECT '(ALL)' AS EMPLOYEE
> > > > > > > GROUP BY EMPLOYEE
> > > > > > > ORDER BY EMPLOYEE
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Benw" wrote:
> > > > > > >
> > > > > > > > Yes that helped ALOT!!! Here is my query for the parameter. I still need to
> > > > > > > > add the ALL function.
> > > > > > > >
> > > > > > > > SELECT Empid
> > > > > > > > FROM smServFault
> > > > > > > > WHERE (Empid <> ' ') AND (TaskStatus <> 'C')
> > > > > > > > GROUP BY Empid
> > > > > > > > ORDER BY Empid
> > > > > > > >
> > > > > > > >
> > > > > > > > "Joe" wrote:
> > > > > > > >
> > > > > > > > > The easiest way I have found to solve your problem is to create a new dataset
> > > > > > > > > to select the values for the parameter. If you create a union for the value
> > > > > > > > > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > > > > > > > > example (SELECT DISTINCT(CUSTNMBR)
> > > > > > > > > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > > > > > > > > LEN(CUSTNMBR))) AS CUSTNAME
> > > > > > > > > FROM RM00101 (NOLOCK)
> > > > > > > > > UNION
> > > > > > > > > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > > > > > > > > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > > > > > > > > from query, and chose your new dataset and the appropriate label and value.
> > > > > > > > > If you want, you can set the nonqueried default to (ALL). In the main
> > > > > > > > > dataset, use a where clause to set the field value equal to the parameter, OR
> > > > > > > > > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > > > > > > > > OR
> > > > > > > > > @.CUSTOMER IS NULL).
> > > > > > > > >
> > > > > > > > > I hope this helps.
> > > > > > > > > "Benw" wrote:
> > > > > > > > >
> > > > > > > > > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > > > > > > > > list and can select multiple values. How can I let the user choose all of
> > > > > > > > > > the values without having to click on all the values individually. And also
> > > > > > > > > > How can I get the parameter to import the values into the dropdown list
> > > > > > > > > > automatically instead of me having to type them all in?
> > > > > > > > > >
> > > > > > > > > > Thanks|||maybe I am missing something here but in RS2005 if you define the parameters
as a multi-value parameter <Select All> is an option isnt it?
"Joe" wrote:
> The easiest way I have found to solve your problem is to create a new dataset
> to select the values for the parameter. If you create a union for the value
> (ALL), and order by the values, (ALL) will be at the top of the list - for
> example (SELECT DISTINCT(CUSTNMBR)
> , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> LEN(CUSTNMBR))) AS CUSTNAME
> FROM RM00101 (NOLOCK)
> UNION
> SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> from query, and chose your new dataset and the appropriate label and value.
> If you want, you can set the nonqueried default to (ALL). In the main
> dataset, use a where clause to set the field value equal to the parameter, OR
> the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> OR
> @.CUSTOMER IS NULL).
> I hope this helps.
> "Benw" wrote:
> > I am new at this parameter thing. I have a paramater that uses a drop down
> > list and can select multiple values. How can I let the user choose all of
> > the values without having to click on all the values individually. And also
> > How can I get the parameter to import the values into the dropdown list
> > automatically instead of me having to type them all in?
> >
> > Thanks|||Yes, your are right, but when I put it on the report server for others to
view, the <Select All> function disappears. I actually figured out what I
was doing wrong.
I fixed it by saying
WHERE field = @.vendor or @.vendor = 'All'
"MJT" wrote:
> maybe I am missing something here but in RS2005 if you define the parameters
> as a multi-value parameter <Select All> is an option isnt it?
> "Joe" wrote:
> > The easiest way I have found to solve your problem is to create a new dataset
> > to select the values for the parameter. If you create a union for the value
> > (ALL), and order by the values, (ALL) will be at the top of the list - for
> > example (SELECT DISTINCT(CUSTNMBR)
> > , RTRIM(LEFT(CUSTNAME, LEN(CUSTNAME)) + ' #' + LEFT(CUSTNMBR,
> > LEN(CUSTNMBR))) AS CUSTNAME
> > FROM RM00101 (NOLOCK)
> > UNION
> > SELECT NULL AS CUSTNMBR, '(ALL)' AS CUSTNAME
> > ORDER BY CUSTNAME ASC) . In the report parameters, select available values
> > from query, and chose your new dataset and the appropriate label and value.
> > If you want, you can set the nonqueried default to (ALL). In the main
> > dataset, use a where clause to set the field value equal to the parameter, OR
> > the parameter equal to NULL - for example (dbo.SOP10100.CUSTNMBR = @.CUSTOMER
> > OR
> > @.CUSTOMER IS NULL).
> >
> > I hope this helps.
> > "Benw" wrote:
> >
> > > I am new at this parameter thing. I have a paramater that uses a drop down
> > > list and can select multiple values. How can I let the user choose all of
> > > the values without having to click on all the values individually. And also
> > > How can I get the parameter to import the values into the dropdown list
> > > automatically instead of me having to type them all in?
> > >
> > > Thanks