parameter properties in report manager - "Has Default"

ok I've designed a report template that uses cascading paramters based
on several sp's. The user selects a value for the first parameter and
that value is passed to the sp that populates the second parameter
list. The value selected for the second param is used to populate the
third param list etc...
I want the first parameter to be required and the other 3 to be
optional - the report should still run if they are not selected. RS
BOL told me to go into the Report manager, select "Properties"
then"Parameters" and then select the "Has Default" check box for the
three optional paramters. This in turn enables the "Null" checkbox, so
I checked that box so null will be the default value passed if these
parameters are not selected.
By all accounts this should allow the report to run with only the first
parameter selected, but when run from Report Manager it still keeps
prompting the user to enter values for the 3 other optional parameters.
Any ideas? help?Never mind! found a workaround...

Parameter Prompt with Wildcard

I'd like to create a report parameter to allow the user to enter only a
partial field using wildcard(s) in SRS. The list is lengthy and the user
does not always know the full name to enter.
Is this possible?
Thanks,
KarenVery much it can be done. Just you need to keep in mind about the single
quotes you are using for string paramters. eg '%userrequest' which you will
be passing the parameters to the "where" clause.
Amarnath
"Moving rpts from Access to Rptg Services" wrote:
> I'd like to create a report parameter to allow the user to enter only a
> partial field using wildcard(s) in SRS. The list is lengthy and the user
> does not always know the full name to enter.
> Is this possible?
> Thanks,
> Karen|||Thank you for our reply! in the where clause I currently have this:
WHERE (dbo.UP_Agents.AgentName = @.AgentName)
is this where I would add the wildcard? Or would I add in a separate
string? (sorry, i'm learning this as I go)
Thank you,
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen|||AHA, this worked:
WHERE (dbo.UP_Agents.AgentName LIKE '%' + @.AgentName + '%')
Thanks for getting me started.
Karen
"Amarnath" wrote:
> Very much it can be done. Just you need to keep in mind about the single
> quotes you are using for string paramters. eg '%userrequest' which you will
> be passing the parameters to the "where" clause.
> Amarnath
> "Moving rpts from Access to Rptg Services" wrote:
> > I'd like to create a report parameter to allow the user to enter only a
> > partial field using wildcard(s) in SRS. The list is lengthy and the user
> > does not always know the full name to enter.
> >
> > Is this possible?
> >
> > Thanks,
> >
> > Karen

parameter problem with report

I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name

The data source for this report is an oracle db

SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND

(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")

GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,

sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||

Can you mark the helpful response as an answer?

Parameter Madness

I have a set of parameterized reports. For each, I default some querries and
do not default others so that when each report comes up, the user must
specify filtering information. Everything works great in VS. But. Not under
report manager.
Defaulted querry parameters lose their defaults. Not defaulted, not from
querry paramters are suddenly defaulted to the first value. It's all
bass-ackward! Did I miss something?
--
Thanks,
CGWAND... the parameters I gave no prompt for so they wouldn't show... THEY
SHOW!!! Arrrrgh!
"CGW" wrote:
> I have a set of parameterized reports. For each, I default some querries and
> do not default others so that when each report comes up, the user must
> specify filtering information. Everything works great in VS. But. Not under
> report manager.
> Defaulted querry parameters lose their defaults. Not defaulted, not from
> querry paramters are suddenly defaulted to the first value. It's all
> bass-ackward! Did I miss something?
> --
> Thanks,
> CGW

parameter issue in with member

Not sure where the problem is The @.bucket-size is an integer value being entered by the user.

with

set [end] as strtomember("[REL TURN HRS].[TRN HRS].&[" + @.bucket_size + "]")

member measures.bucket1 as

iif( isempty(sum([REL TURN HRS].[TRN HRS].&[0] :

[end],[Measures].[FACT CUT RELEASE Count])),0,sum([REL TURN HRS].[TRN HRS].&[0]:

[end],[Measures].[FACT CUT RELEASE Count]))

SELECT NON EMPTY { measures.bucket1} ON COLUMNS,

NON EMPTY {[CUSTOMER JOB].[Cust-Title-Issue-Job].[JOB_NUMBER].ALLMEMBERS } ON ROWS

FROM [DW INSIGHT]

WHERE ([CUSTOMER JOB].[JOB_NUMBER].&[A403EZ0])

some indication of what the problem is might be usefull. Any error messages?|||

here is the error i get

Parser: The query contains the bucket_size parameter, which is not declared. (msmgdsrv)

|||Have you created the parameter throught the Report->Report Parameters window? Also your question states @.bucket-size and your query uses @.bucket_size, is this a typo?

Parameter error

Why do I keep getting this error:

"A number is required here"

I am trying to prompt the user of the report for the a Billing ID, which will refresh the contents of the report.

I am using exactly the same technique and method which is illustrated in the manual:

{VW_BillingItemsConsolidated.BillingAddressID}={?billingID}

Where {VW_BillingItemsConsolidated.BillingAddressID} is the table field and {?billingID} is the forumla I have created.

RegardsNow I have changed the "Parameter Field" "Value Type" to take numerical data the error is no longer showing. Still though, when I enter a number the information on the report doesn't change to reflect that customer. Where am I going wrong?|||I just keeo getting "False" in the field which is inserted onto the report.|||What is the datatype of VW_BillingItemsConsolidated.BillingAddressID?
It should work correctly

Parameter Date Format!

Hi,

How to control the Datetime Parameter's format, such as I need the user input Date and Time, but default, the user can only select date, but can not input the time.

How to solve it?

Thank you.

Jeffers,

You could give your users two textbox parameter for date and time entry, combine to 2 strings ( "mm/dd/yyyy" + "00:00:00" ) and use a cdate function convert to the actual datetime field.

Ham

parameter date format - change to dd/mm/yyyy

Hi All,

I'm using report with a date parameter (user enters a date) and all transactions before that date are displayed. I don't know how to set parameter date format on the displayed report to dd/mm/yyyy.

If it's not a parameter I usually use sql " convert(varchar(10),datefield,103)" but don't know how to use this with parameter.

Thanks

Sonny

Pretty much the same whether for a parameter or for a field expression:

Code Snippet

=Format(Fields!last_edit.Value,"d/M/yyyy")

=Format(Parameters!RPDate.value,"d/M/yyyy")

that should do it.... watch the case-sensitivity; a lower case "m" gives you minutes, not months...

One thing, though: if the user enters the date param directly, you might want to do a cast, something like this just in case there is a data type issue somewhere (of course you might have to do this for whatever code actually *uses* the param as well, I don't know, so maybe it's a dumb thought)

Code Snippet

=Format(CDATE(Parameters!RPDate.value),"d/M/yyyy")

>L<

|||

Hi Lisa,

thanks for the answer. this works with date picker but if I enter the date manually it does not. maybe i have to convert date as you said.

basicall current report paramater is such that you must enter date as YYYYMMDD format. I guess this is default SQL server date format.

I'm on RS patch 0 , never been able to use date picker.

Sonny

|||

WHen you enter the date manually, what does it look like (I'm not asking what the result of the expression is -- I'm asking what you actually enter)?

It's not so much that it has to match the "default sql server date format" as it has to match *some* default, which could be the locale on the server, I'm really not sure.

It is not a big deal to write some code to make sure that your manually-entered date is properly parsed into the format that you want. HOWEVER the reason I just suggested the cast, rather than parsing code, is that you run into the problem of other people maybe entering the manually-entered date differently than you do and possibly parsing out for many different cases.

Since, as you say, the server does have an internal idea of what a date should look like, I find it's often best to write something that does a cast and lets the server figure it out if it can. The worst that will happen is that you'll see #ERROR instead of the expected result, but often SQL Server is pretty smart about figuring out dates, and some of the .NET classes at work in RS (which probably aren't even in SQL Server at the point they are doing this work) may be smart about it also.

If the cast doesn't work, we will proceed further <s>.

>L<

Parameter Array with dynamic SQL

I'm building an interface that will allow the user to dynamically make from
1-5 selections from a control. The @.catList parameter is a comma-delimited
string of paramater values.
When I run it and make one selection from the control, it returns a row.
However, when I make more than one selection, no rows are returned.
Should I be using "sp_executesql" or the "exec" statements in the query?
ALTER PROCEDURE sp_GetJobsByFilter
@.catList nvarchar(100),
@.Travel varchar(50),
@.Tax varchar(50)
AS
SELECT J.Category, J.Travel, J.Tax
FROM JB_Post J
INNER JOIN JB_Cats C on J.Category = C.Category WHERE C.Category IN
(@.catList) AND J.Travel = @.Travel AND J.Tax = @.Tax
RETURN
--
seandevHere are links to a couple of articles by SQL Server MVP Erland Sommarskog
you might find interesting:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"sdev" <sdev@.discussions.microsoft.com> wrote in message
news:43B76434-6EC9-41D3-90D9-1ABE20A33D92@.microsoft.com...
> I'm building an interface that will allow the user to dynamically make
> from
> 1-5 selections from a control. The @.catList parameter is a
> comma-delimited
> string of paramater values.
> When I run it and make one selection from the control, it returns a row.
> However, when I make more than one selection, no rows are returned.
> Should I be using "sp_executesql" or the "exec" statements in the query?
>
> ALTER PROCEDURE sp_GetJobsByFilter
> @.catList nvarchar(100),
> @.Travel varchar(50),
> @.Tax varchar(50)
> AS
> SELECT J.Category, J.Travel, J.Tax
> FROM JB_Post J
> INNER JOIN JB_Cats C on J.Category = C.Category WHERE C.Category IN
> (@.catList) AND J.Travel = @.Travel AND J.Tax = @.Tax
>
> RETURN
> --
> seandev

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

Parameter - Default Value - ALL?

H
I need to have a report parameter that Allows the user to select o
Company name but that has a default value as ALL (i.e. will retur
data for ALL companys
How can I do this
Thanks
DarraDarran,
There are lots of ways to do this but this is how I do it:
Stored Procedure to fill dropdown:
SELECT
"<ALL>" AS CompanyName
, NULL AS CompanyID
UNION
SELECT
CompanyName
, CompanyID
FROM
CompanyTable
--
In RS make sure your parameter "Allows Nulls"
--
Now set the parameter Default to "=Nothing"
--
In your data-select stored procedure use this logic:
...
@.CompanyID INT
...
SELECT (fields)
FROM (data table)
WHERE CompanyID = @.CompanyID OR @.CompanyID IS NULL
--
If the user selects "<ALL>" the stored procedure will ignore the parameter
and select everything.
Hope this helps.
Fred
"Darran" wrote:
> Hi
> I need to have a report parameter that Allows the user to select on
> Company name but that has a default value as ALL (i.e. will return
> data for ALL companys)
> How can I do this?
> Thanks!
> Darran
>sql

Pains of a lazy user

I've only been using SSRS for a short period, but I've already found myself
on the verge of throwing my computer out of the office window (would be less
spectacular than it sounds - the window's are toughened glass and invariably
shut).
My beef is with the way that the parameter list is deleted from the dataset
when the stored procedure is changed. Why does this happen? Surely I'm not
the only person who having been building a report based on a procedure which
already exists suddenly decides to base it on new version with the same
parameter list of around a dozen parameters? And then, half an hour later,
will change my mind and go back to the original? Meaning (including the
original entering of parameters) I've had to enter 36 parameters and select
their report parameter from a list!
I realise this may sound lazy, but I really can't be bothered doing that.
Please - someone tell me there's a way to assign parameters without having
to use the dataset dialogue box or a way to persist the parameters over to
the new stored procedure.
Thanks in advance.Create a copy of your report (ctrl-c, ctrl-v). View Menu-> code . This
brings up the xml source of the rdl. Search for the name of the stored
procedure and replace it everywhere you find it. Save all.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
news:0ADA9C44-5CED-4831-A585-347896F0ECAB@.microsoft.com...
> I've only been using SSRS for a short period, but I've already found
> myself
> on the verge of throwing my computer out of the office window (would be
> less
> spectacular than it sounds - the window's are toughened glass and
> invariably
> shut).
> My beef is with the way that the parameter list is deleted from the
> dataset
> when the stored procedure is changed. Why does this happen? Surely I'm not
> the only person who having been building a report based on a procedure
> which
> already exists suddenly decides to base it on new version with the same
> parameter list of around a dozen parameters? And then, half an hour later,
> will change my mind and go back to the original? Meaning (including the
> original entering of parameters) I've had to enter 36 parameters and
> select
> their report parameter from a list!
> I realise this may sound lazy, but I really can't be bothered doing that.
> Please - someone tell me there's a way to assign parameters without having
> to use the dataset dialogue box or a way to persist the parameters over to
> the new stored procedure.
> Thanks in advance.|||Yup - that would work.
To be honest I haven't spent very much time looking at the rdl xml, I guess
it's better to get to grips with it.
I still find it hard to believe that this default behaviour was chosen as
the best and that it hasn't been a thorn in a lot of user's sides. It's a LOT
easier to delete unwanted parameters than enter new ones. Editting the rdl
directly seems like bypassing SSRS rather than using it.
Am I the only person to have found this an issue?
(BTW - Thanks, Bruce - I'll make use of that tip. Trusty old 'find and
replace' ;o)
"Bruce L-C [MVP]" wrote:
> Create a copy of your report (ctrl-c, ctrl-v). View Menu-> code . This
> brings up the xml source of the rdl. Search for the name of the stored
> procedure and replace it everywhere you find it. Save all.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
> news:0ADA9C44-5CED-4831-A585-347896F0ECAB@.microsoft.com...
> > I've only been using SSRS for a short period, but I've already found
> > myself
> > on the verge of throwing my computer out of the office window (would be
> > less
> > spectacular than it sounds - the window's are toughened glass and
> > invariably
> > shut).
> >
> > My beef is with the way that the parameter list is deleted from the
> > dataset
> > when the stored procedure is changed. Why does this happen? Surely I'm not
> > the only person who having been building a report based on a procedure
> > which
> > already exists suddenly decides to base it on new version with the same
> > parameter list of around a dozen parameters? And then, half an hour later,
> > will change my mind and go back to the original? Meaning (including the
> > original entering of parameters) I've had to enter 36 parameters and
> > select
> > their report parameter from a list!
> >
> > I realise this may sound lazy, but I really can't be bothered doing that.
> >
> > Please - someone tell me there's a way to assign parameters without having
> > to use the dataset dialogue box or a way to persist the parameters over to
> > the new stored procedure.
> >
> > Thanks in advance.
>
>|||Well, it is a case of Report Designer trying to help too much. It
automatically creates report parameters for every detected query parameter
of the stored procedure.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
news:61713364-3459-43AE-881B-CD075E6334FD@.microsoft.com...
> Yup - that would work.
> To be honest I haven't spent very much time looking at the rdl xml, I
> guess
> it's better to get to grips with it.
> I still find it hard to believe that this default behaviour was chosen as
> the best and that it hasn't been a thorn in a lot of user's sides. It's a
> LOT
> easier to delete unwanted parameters than enter new ones. Editting the rdl
> directly seems like bypassing SSRS rather than using it.
> Am I the only person to have found this an issue?
> (BTW - Thanks, Bruce - I'll make use of that tip. Trusty old 'find and
> replace' ;o)
> "Bruce L-C [MVP]" wrote:
>> Create a copy of your report (ctrl-c, ctrl-v). View Menu-> code . This
>> brings up the xml source of the rdl. Search for the name of the stored
>> procedure and replace it everywhere you find it. Save all.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "rocket salad" <rocketsalad@.discussions.microsoft.com> wrote in message
>> news:0ADA9C44-5CED-4831-A585-347896F0ECAB@.microsoft.com...
>> > I've only been using SSRS for a short period, but I've already found
>> > myself
>> > on the verge of throwing my computer out of the office window (would be
>> > less
>> > spectacular than it sounds - the window's are toughened glass and
>> > invariably
>> > shut).
>> >
>> > My beef is with the way that the parameter list is deleted from the
>> > dataset
>> > when the stored procedure is changed. Why does this happen? Surely I'm
>> > not
>> > the only person who having been building a report based on a procedure
>> > which
>> > already exists suddenly decides to base it on new version with the same
>> > parameter list of around a dozen parameters? And then, half an hour
>> > later,
>> > will change my mind and go back to the original? Meaning (including the
>> > original entering of parameters) I've had to enter 36 parameters and
>> > select
>> > their report parameter from a list!
>> >
>> > I realise this may sound lazy, but I really can't be bothered doing
>> > that.
>> >
>> > Please - someone tell me there's a way to assign parameters without
>> > having
>> > to use the dataset dialogue box or a way to persist the parameters over
>> > to
>> > the new stored procedure.
>> >
>> > Thanks in advance.
>>

Pagination question - retrieve only records for each page

I want to provide paged access to data. Rather than displaying the
entire contents of a report or query to an end user, I want to show
only a subset of records per web page, with controls for moving from
page to page. I have a large result set and only want to selectively
retrieve only those records that should be shown for the particular
page. I know how to do this in ASP.NET but I'm new to Report Server.
Can anyone point me in the right direction?
ThanksThis is default Reporting Services behavior.
RS will give you the functions to move from page to page.
You have to design the report to use parameters, and then add formatting to
the report to make it break over several pages.
If you have the SQL Server 2005 setup available, install the Report Designer
on your workstation and play around with it. (Or download a trial version of
SS 2005) There are lots of good articles to get you going. A good place to
start is with William Pearson's articles about Reporting Services at
http://www.databasejournal.com/article.php/1459531 (scroll down to find
them).
Kaisa M. Lindahl Lervik
"fparc" <fparc@.aol.com> wrote in message
news:1165461353.254431.13270@.j72g2000cwa.googlegroups.com...
>I want to provide paged access to data. Rather than displaying the
> entire contents of a report or query to an end user, I want to show
> only a subset of records per web page, with controls for moving from
> page to page. I have a large result set and only want to selectively
> retrieve only those records that should be shown for the particular
> page. I know how to do this in ASP.NET but I'm new to Report Server.
> Can anyone point me in the right direction?
> Thanks
>|||Thanks for the link. What a great resource. I was thinking of
pagination in terms of record retrieval instead of page breaks. I want
to query 50 records at a time so I don't tax the database. After
looking at it a bit I think I will try to use the TimeStamp field as
the index. So I put a text box that holds the Last timestamp of the
group of TOP 50. I make the textbox a 'Jump to URL' and pass the last
timestamp on the end of the URL to open the new report page. One
problem; I can't get the parameterize URL to work in Report Server. I'm
using the wrox Professional SQL Server 2005 Reporting Services book but
the URL format they give is wrong(a pre-release book no doubt). So now
I need to find a good simple test to see if I can get this working
first. Know of any good tutorials on passing params in URL for Report
Server?|||I got the parameterize URL to work in Report Server. I was wrong about
the wrox Professional SQL Server 2005 Reporting Services book being
wrong. I was using "http://server/SQLReports/Pages/Folder.aspx?"
instead of "http://server/ReportServer?/folder/file" in my Jump URL.
Now I have 12 parameters including the TimeStamp the user can choose
from. By choosing the Allow Blank Value property from the Report
Parameters Dialog Box, the user can fill in all or none of the
parameter text boxes for various searches - except the Timestamp which
defaults to =Now.
My SQL pulls TOP 50 records. When the user clicks on the 'Jump to URL'
text box the timestamp param of the 50th record is passed in the URL to
pull the next 50.
Now I have a type text column filled with XML in which there are 2
things we need to pull - an email and/or an event_code. My next step is
to change the table column from text to the XML type to speed up the
searches on that column. Here's what I've found so far.
http://www.developer.com/db/article.php/3531196
http://www.15seconds.com/issue/050803.htm
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx
http://www.ftponline.com/vsm/2005_06/magazine/features/rjennings/

PAGEIO

Please Help.
A user of mine was deleting records in a table of about
40 million records it took almost an hour.
On Process/info activitiy I saw PAGEIOLATCH_EX. Any idea
what it means please.
Regards
Olutimi OloruntobaAssuming a SCSI RAID disk subsystem one hour for 40 million records is not
unexpected. The PAGEIOLATCH_EX is a fancy way of saying it is waiting for a
physical IO (disk read/write) to complete. Again, this is expected. If you
want to lower the impact of a large delete, break it up into smaller chunks.
--
Geoff N. Hiten
SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"Olutimi" <olutimi@.hotmail.com> wrote in message
news:0b6001c36d6b$88a5c780$a601280a@.phx.gbl...
> Please Help.
> A user of mine was deleting records in a table of about
> 40 million records it took almost an hour.
> On Process/info activitiy I saw PAGEIOLATCH_EX. Any idea
> what it means please.
> Regards
>
> Olutimi Oloruntoba
>

page totals in crystal report

hello everybody,
i am a new user to crystel report. i am using the crystal
report with vb. i have a report with some groups and
sub-groups. i want to print pagewise totals on each page.
pl show me the way.
thanks
shrikant
shrikant_smart@.rediffmail.comIn Crystal Reports 8.5, click Insert, Field Object. This will bring up a list of field objects. Expand 'Special Fields' and you can see your options of 'Page Number', 'Total Page Count', 'Page N of M', etc. Just drag and drop onto your report and position where you want it.|||I don't know this is the proper way or not. any way this will display page viz totals.

Create Formula named tot1

code:

WhilePrintingRecords;
NumberVar total;
total := 0

formula 2 : tot2

WhilePrintingRecords;
NumberVar total;
total := total + {amount};

formula 3 : tot3

WhilePrintingRecords;
NumberVar total;

Use the same variable name.

place tot1 is page header section. This will reset the total value to 0 for each and every page.

Place tot2 in details section. This will add the values.

Format both as suppressed.

Place the tot3 in Page Footer section. This will display page viz total.

page size setting

Hello!
If somebody knows how to set page size (A4 etc.) on development, because
when I trying to print my report (after deployee) from user's computer the
settings are differents and not taken from default printer.
Thanks.
GennadyReport... Report Properties... Layout tab... set your page width, height,
margins ( left, right, top, bottom )...
"Gennady" wrote:
> Hello!
> If somebody knows how to set page size (A4 etc.) on development, because
> when I trying to print my report (after deployee) from user's computer the
> settings are differents and not taken from default printer.
> Thanks.
> Gennady
>
>|||Thank You but I tryed it before and that doesn't help more.
If the customer computer set "letter" report is printing in "letter" format
in spite of layout settings.
If You're famouse with the Active report it has "printer setings" on
development page, I'd like something similar.
Reguards.
Gennady.
"mike" <mike@.discussions.microsoft.com> wrote in message
news:2C3533AC-434F-4114-AC55-F9926A64B6AB@.microsoft.com...
> Report... Report Properties... Layout tab... set your page width, height,
> margins ( left, right, top, bottom )...
> "Gennady" wrote:
> > Hello!
> >
> > If somebody knows how to set page size (A4 etc.) on development, because
> > when I trying to print my report (after deployee) from user's computer
the
> > settings are differents and not taken from default printer.
> >
> > Thanks.
> >
> > Gennady
> >
> >
> >

page setup parameters

I have a report (.rdl) that I created in Visual Studio. Is there any way to
send the page setup parameters along with the report so that the user isn't
required to fiddle with the page setup? For example, I have a report that
prints in landscape on legal paper, I want them to be able to click print and
have it output with the landscape/legal setup as opposed to having to set
that up in a print dialog box. I can see the margin setup, page width and
Height in VS, but these don't affect the paper or orientation settings.
Any ideas?I have same issue on Margine setting once i put it in production, the report
layout are changed and it is not what i want.
Can you solve it'
"beeyule" wrote:
> I have a report (.rdl) that I created in Visual Studio. Is there any way to
> send the page setup parameters along with the report so that the user isn't
> required to fiddle with the page setup? For example, I have a report that
> prints in landscape on legal paper, I want them to be able to click print and
> have it output with the landscape/legal setup as opposed to having to set
> that up in a print dialog box. I can see the margin setup, page width and
> Height in VS, but these don't affect the paper or orientation settings.
> Any ideas?|||If you set the Width to be the Height and the Height to be the Width this
will get you the Landscape option.
Ie. for Letter Width=11in and Height=8.5in.
Choosing the paper is a bit different since it depends on the printer for
the local computer. However to get this to happen you can set the default
printer and paper from the windows printers/faxes option. This will then
default in to that box when they click print.
Hope this helps,
Craig
"beeyule" <beeyule@.discussions.microsoft.com> wrote in message
news:2B86CFB0-44BF-4566-B942-9AAD497BD73B@.microsoft.com...
>I have a report (.rdl) that I created in Visual Studio. Is there any way
>to
> send the page setup parameters along with the report so that the user
> isn't
> required to fiddle with the page setup? For example, I have a report that
> prints in landscape on legal paper, I want them to be able to click print
> and
> have it output with the landscape/legal setup as opposed to having to set
> that up in a print dialog box. I can see the margin setup, page width and
> Height in VS, but these don't affect the paper or orientation settings.
> Any ideas?