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?

Paramater in Case statement

When I enter the following query in the MSSQL 2005 Server Management Studio,
I get results, but when i enter the same query (Minus the top 2 lines) in RS,
i get an a error:
Title: Microsoft Visual Database Tools
Error: The Parameter is incorrect.
Any ideas what i'm doing wrong? This query is just a simplified example
using the northwind db.
DECLARE @.Region AS NVARCHAR(15)
SELECT @.Region = '1'
SELECT *
FROM Customers
WHERE Region = CASE
WHEN @.Region = '1'
THEN 'SP'
END
--
Lucas DargisHi Lucas,
> DECLARE @.Region AS NVARCHAR(15)
> SELECT @.Region = '1'
You are using a variable (@.Region) in your query and if you leave off the
top 2 lines, you are not declaring the variable and setting its value. The
statement needs this.
HTH!
Kind regards - Fred|||thanks Fred.
I left off the top two lines because in RS you specify the parameters in the
'Report > Report Parameters' window.
i found that this is just a bug in the design view of RS. when i Previewed
the report, it worked just fine.
thanks
--
Lucas Dargis
"Fred Block" wrote:
> Hi Lucas,
> > DECLARE @.Region AS NVARCHAR(15)
> > SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>
>|||This is not true. The way it works, if you have not declared it then RS
knows that it is a query parameter and automatically creates a report
parameter for it. I used the below code against adventureworks and it works:
SELECT *
FROM sales.Customer
WHERE TerritoryID =CASE
WHEN @.Region = '2'
THEN 2
END
Use the generic query designer (the button to switch to generic mode is one
of the buttons to the right of the ...).
Execute the query, you should be prompted for a value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Lucas,
>> DECLARE @.Region AS NVARCHAR(15)
>> SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>|||Bruce,
that is what i said... just not as well.
i just gave him the 'check' since i couldn't give it to myself.
--
Lucas Dargis
"Bruce L-C [MVP]" wrote:
> This is not true. The way it works, if you have not declared it then RS
> knows that it is a query parameter and automatically creates a report
> parameter for it. I used the below code against adventureworks and it works:
> SELECT *
> FROM sales.Customer
> WHERE TerritoryID => CASE
> WHEN @.Region = '2'
> THEN 2
> END
> Use the generic query designer (the button to switch to generic mode is one
> of the buttons to the right of the ...).
> Execute the query, you should be prompted for a value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
> news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> > Hi Lucas,
> >
> >> DECLARE @.Region AS NVARCHAR(15)
> >> SELECT @.Region = '1'
> >
> > You are using a variable (@.Region) in your query and if you leave off the
> > top 2 lines, you are not declaring the variable and setting its value. The
> > statement needs this.
> >
> > HTH!
> >
> > Kind regards - Fred
> >
>
>

Paragrahs in textboxes

Hi,

Can anyone advise me how to force paragraph breaks in a textbox in Reporting Services 2000? I've tried Shift + Enter and in layout view it forces a paragraph break, but as soon as the report is rendered, the breaks disappear.

Thanks.

Hello Matt,

Try creating an expression of your paragraph, then when you want to add a break, add a Chr(10).

In this example, the first and second sentences will be on different lines.

="This is my first sentence." + chr(10) + "This is my second sentence."

Hope this helps.

Jarret

|||

Hi Jarret,

Perfect! Thank you very much.

Matt

|||

Yup I found the chr(10) very helpful.

I did notice however that when viewing a report, in the report viewer before printing, that the ch(10) has no effect. All of the text runs together as one long string of text.

When I print the report all is fine with the chr(10) doing its job of providing a line break. It also looks ok in the design tool when building the report. It is just that the report viewer does not respect this .

Does anyone have a solution where by the report viewed in the report viewer does the same thing with the text as the the printed version.

Regards

Matteo

|||

Looks like the suggestion from this post works: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=734028&SiteID=1

I've used "Environment.Newline" and it seems to work for the report viewer.

Paragrahs in textboxes

Hi,

Can anyone advise me how to force paragraph breaks in a textbox in Reporting Services 2000? I've tried Shift + Enter and in layout view it forces a paragraph break, but as soon as the report is rendered, the breaks disappear.

Thanks.

Hello Matt,

Try creating an expression of your paragraph, then when you want to add a break, add a Chr(10).

In this example, the first and second sentences will be on different lines.

="This is my first sentence." + chr(10) + "This is my second sentence."

Hope this helps.

Jarret

|||

Hi Jarret,

Perfect! Thank you very much.

Matt

|||

Yup I found the chr(10) very helpful.

I did notice however that when viewing a report, in the report viewer before printing, that the ch(10) has no effect. All of the text runs together as one long string of text.

When I print the report all is fine with the chr(10) doing its job of providing a line break. It also looks ok in the design tool when building the report. It is just that the report viewer does not respect this .

Does anyone have a solution where by the report viewed in the report viewer does the same thing with the text as the the printed version.

Regards

Matteo

|||

Looks like the suggestion from this post works: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=734028&SiteID=1

I've used "Environment.Newline" and it seems to work for the report viewer.