HELP! Report Parameters not working.

I bought a book, seems good, on Reporting Services. Since I am new to this
all, I am on the uphill learning curve. I have a dataset I created and added
parameters. After trying to run the report with the new parameters, I always
get the same error, "The report parameter â'SystemNameâ' has a DefaultValue or
a ValidValue that depends on the report parameter â'SystemNameâ'. Forward
dependencies are not valid." I have read other posts where a solution
provider says that the dataset is not being populated before getting to the
parameter. How do I force the dataset to be populated first? In all the
examples I worked with, I never saw where I had to do something specific
after creating the dataset to initiate the parameter.
Also, after creating a dataset I am able to see the fields without issues.
If I drag/drop one of those fields on my form, I
get:"=First(Fields!FieldName.Value)". In all the examples in the book, I
don't get First(Fields!blah.blah). It is always without parathesis in the
book. I know I am not doing something correctly. Can someone please help me
out.
Thanks much,To address a couple of things:
1) on the parameters:
Are you specifying the source of the parameter is a datasource? I'm
assuming yes, other wise it is simply input by the user and you wouldn't have
problems. In this case, make sure that the datasource you are using actually
returns data. Under the Data tab, open up the datasource for you parameter.
Click on Refresh, and then click the "!" icon to see what data you get. Does
this populate your datasource? Does this give you data to provide for your
parameters in the report?
2) The First(Fields!blah.Value) come out because you probably are just
dragging a field onto the report, and not putting it into a data region (such
as a table or list). The First() thing is saying "give me the first value
returned for this dataset." The reason being, otherwise it doesn't know how
to display the data (i.e. which piece of data do you want to display).
If you create a table, then drag a field to the "details" row of the table,
you should not see the First() thing come up. This is because a table (in
the report) automatically displays a new row for each new row of data
returned in the dataset.
Hope this helps!
"cmcdavid" wrote:
> I bought a book, seems good, on Reporting Services. Since I am new to this
> all, I am on the uphill learning curve. I have a dataset I created and added
> parameters. After trying to run the report with the new parameters, I always
> get the same error, "The report parameter â'SystemNameâ' has a DefaultValue or
> a ValidValue that depends on the report parameter â'SystemNameâ'. Forward
> dependencies are not valid." I have read other posts where a solution
> provider says that the dataset is not being populated before getting to the
> parameter. How do I force the dataset to be populated first? In all the
> examples I worked with, I never saw where I had to do something specific
> after creating the dataset to initiate the parameter.
> Also, after creating a dataset I am able to see the fields without issues.
> If I drag/drop one of those fields on my form, I
> get:"=First(Fields!FieldName.Value)". In all the examples in the book, I
> don't get First(Fields!blah.blah). It is always without parathesis in the
> book. I know I am not doing something correctly. Can someone please help me
> out.
> Thanks much,
>|||David,
Yes, I am wanting the parameter to be filled with a drop-down box on the
report. Yes, I have run this and get back a result. When assigning a field
a parameter, when I run it, it pops up a dialogue box asking for the
parameters. After supplying it with the intial values, it returns what I
want. After running in the report, it doesn't work as I preview it.
Chris
"david boardman" wrote:
> To address a couple of things:
> 1) on the parameters:
> Are you specifying the source of the parameter is a datasource? I'm
> assuming yes, other wise it is simply input by the user and you wouldn't have
> problems. In this case, make sure that the datasource you are using actually
> returns data. Under the Data tab, open up the datasource for you parameter.
> Click on Refresh, and then click the "!" icon to see what data you get. Does
> this populate your datasource? Does this give you data to provide for your
> parameters in the report?
> 2) The First(Fields!blah.Value) come out because you probably are just
> dragging a field onto the report, and not putting it into a data region (such
> as a table or list). The First() thing is saying "give me the first value
> returned for this dataset." The reason being, otherwise it doesn't know how
> to display the data (i.e. which piece of data do you want to display).
> If you create a table, then drag a field to the "details" row of the table,
> you should not see the First() thing come up. This is because a table (in
> the report) automatically displays a new row for each new row of data
> returned in the dataset.
> Hope this helps!
> "cmcdavid" wrote:
> > I bought a book, seems good, on Reporting Services. Since I am new to this
> > all, I am on the uphill learning curve. I have a dataset I created and added
> > parameters. After trying to run the report with the new parameters, I always
> > get the same error, "The report parameter â'SystemNameâ' has a DefaultValue or
> > a ValidValue that depends on the report parameter â'SystemNameâ'. Forward
> > dependencies are not valid." I have read other posts where a solution
> > provider says that the dataset is not being populated before getting to the
> > parameter. How do I force the dataset to be populated first? In all the
> > examples I worked with, I never saw where I had to do something specific
> > after creating the dataset to initiate the parameter.
> >
> > Also, after creating a dataset I am able to see the fields without issues.
> > If I drag/drop one of those fields on my form, I
> > get:"=First(Fields!FieldName.Value)". In all the examples in the book, I
> > don't get First(Fields!blah.blah). It is always without parathesis in the
> > book. I know I am not doing something correctly. Can someone please help me
> > out.
> >
> > Thanks much,
> >
> >

HELP! Passing parameters

Still need help passing a criteria parameter query from a SQL Function (i.e. @.StartDate) to a report header in Access Data Project where header = 'Transactions As Of [StartDate].

If anyone knows anywhere I can get help on this, I would really appreciate it. Thanks.

Could you explain this a bit more in detail ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

HELP! Default Date Parameters Expression in Reporting Services

How do I add a default date parameter to get this:

@.StartDate: Last Sunday

@.EndDate : Yesterday

So if I was to run this today I want the reports StartDate As Sunday, November 11, 2007

and

EndDate >>> Yesterday Thursday, November 15, 2007

Hi,

From your description, it seems that you want to add some parameters to filter the report, right?

If so, I suggest that you should associate a Query Parameter with a Report Parameter. Assume that you handle your querying works in a stored procedure, you can use getdate() method to get the current date, datediff() and dateadd() method to calculate the date. See the following code snippet (Just the idea, not the executable code.):

declare @.InputDay,@.StartDay,@.EndDay

-- @.EndDay=getdate()-1
case @.InputDay='Sun'
-- @.StartDay=getdate()-7
case @.InputDay='Mon'
-- @.StartDay=getdate()-1
case @.InputDay='Tue'
-- @.StartDay=getdate()-2
case @.InputDay='Wed'
-- @.StartDay=getdate()-3
case @.InputDay='Thu'
-- @.StartDay=getdate()-4
case @.InputDay='Fri'
-- @.StartDay=getdate()-5
case @.InputDay='Sat'
-- @.StartDay=getdate()-6

Thanks.

HELP! - Hitting 260 character limit on parameters in URL - how to avoid?

Hi,
Got some reports with a lot of parameters to deal with, a custom web front
end and using the ReportViewer component to render the reports. A typical
URL generated by the app is:
http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
Problem is i'm getting the following error message displayed:
Reporting Services Error
----
--
a.. The path of the item '/Reports/My Report,' is not valid. The full path
must be less than 260 characters long, must start with slash; other
restrictions apply. Check the documentation for complete set of
restrictions. (rsInvalidItemPath) Get Online Help
----
--
How do I get around this? I understand I need to convert the params in some
way & pass them into the report, cannot find out howto atm.
Any help appreciated
SiDo you have a Folder called Reports in the home directory? Is the 'My
Report' report located in that folder? If you navigate to
//localhost/reportserver you can then click on the folder and the report and
see what the url should look like.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Si" <no@.spam.thanks> wrote in message
news:uhscKCHgEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Got some reports with a lot of parameters to deal with, a custom web front
> end and using the ReportViewer component to render the reports. A typical
> URL generated by the app is:
>
http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
> Problem is i'm getting the following error message displayed:
> Reporting Services Error
> ----
--
> --
> a.. The path of the item '/Reports/My Report,' is not valid. The full
path
> must be less than 260 characters long, must start with slash; other
> restrictions apply. Check the documentation for complete set of
> restrictions. (rsInvalidItemPath) Get Online Help
> ----
--
> --
>
> How do I get around this? I understand I need to convert the params in
some
> way & pass them into the report, cannot find out howto atm.
> Any help appreciated
> Si
>|||Hi Daniel,
Yup the reports work fine if I have less params to play with i.e. if I
just use DateFrom & DateTo it's ok, just when I use all the parameters
in the report I get this message.
Just seems to be the length of the params that causes the problem,
seems like I need to use SetReportParameters to programatically pass
the params into the report but as i'm using the ReportViewer component
(programmatic access), there is no Render method for me to use to pass
these params into!
Si
On Thu, 12 Aug 2004 10:14:55 -0700, "Daniel Reib [MSFT]"
<danreib@.online.microsoft.com> wrote:
>Do you have a Folder called Reports in the home directory? Is the 'My
>Report' report located in that folder? If you navigate to
>//localhost/reportserver you can then click on the folder and the report and
>see what the url should look like.
>--
>-Daniel
>This posting is provided "AS IS" with no warranties, and confers no rights.
>
>"Si" <no@.spam.thanks> wrote in message
>news:uhscKCHgEHA.636@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Got some reports with a lot of parameters to deal with, a custom web front
>> end and using the ReportViewer component to render the reports. A typical
>> URL generated by the app is:
>>
>http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
>> Problem is i'm getting the following error message displayed:
>> Reporting Services Error
>> ----
>--
>> --
>> a.. The path of the item '/Reports/My Report,' is not valid. The full
>path
>> must be less than 260 characters long, must start with slash; other
>> restrictions apply. Check the documentation for complete set of
>> restrictions. (rsInvalidItemPath) Get Online Help
>> ----|||Found the problem,
You were correct in the URL was suspect but it was with the parameters
rather than the path to the report. I noticed that there was an extra
ampersand at the end of the paramstring and also the date format
included a timestamp. Removing those two entries seems to have fixed
the problem
Thanks for the heads up. At least I now know i'm not going mad!
Si
On Fri, 13 Aug 2004 08:15:04 +0100, Si <no@.spam.thanks> wrote:
>Hi Daniel,
>Yup the reports work fine if I have less params to play with i.e. if I
>just use DateFrom & DateTo it's ok, just when I use all the parameters
>in the report I get this message.
>Just seems to be the length of the params that causes the problem,
>seems like I need to use SetReportParameters to programatically pass
>the params into the report but as i'm using the ReportViewer component
>(programmatic access), there is no Render method for me to use to pass
>these params into!
>Si
>On Thu, 12 Aug 2004 10:14:55 -0700, "Daniel Reib [MSFT]"
><danreib@.online.microsoft.com> wrote:
>>Do you have a Folder called Reports in the home directory? Is the 'My
>>Report' report located in that folder? If you navigate to
>>//localhost/reportserver you can then click on the folder and the report and
>>see what the url should look like.
>>--
>>-Daniel
>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>"Si" <no@.spam.thanks> wrote in message
>>news:uhscKCHgEHA.636@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Got some reports with a lot of parameters to deal with, a custom web front
>> end and using the ReportViewer component to render the reports. A typical
>> URL generated by the app is:
>>
>>http://localhost/reportserver?/Reports/My%20Report&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false&DateFrom=12/08/2004%2013:59:44&DateTo=12/08/2004%2013:59:44&ClientRefFrom=_All_&ClientRefTo=_All_&SurnameFrom=_All_&SurnameTo=_All_&AreaFrom=_All_&AreaTo=_All_&RegionFrom=_All_&RegionTo=_All_
>> Problem is i'm getting the following error message displayed:
>> Reporting Services Error
>> ----
>>--
>> --
>> a.. The path of the item '/Reports/My Report,' is not valid. The full
>>path
>> must be less than 260 characters long, must start with slash; other
>> restrictions apply. Check the documentation for complete set of
>> restrictions. (rsInvalidItemPath) Get Online Help
>> ----

Help!

Hi
I have beening trying to build a report with 2 parameters. When I run it
under VS.net preview, it all went well, but after I deploy it to the
server, I got
the message as following: **Reporting Services Error--The path of the item ''
is not valid. The full path must be less than 260 characters long, must start
with slash; other restrictions apply. Check the documentation for complete
set of restrictions. (rsInvalidItemPath) Get Online Help **
what went wrong, please help.
the URL for the report as following:
http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
TIAIT sounds like the URL of the report is too long, even though the URL you
post isn't that long...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"wd1153" <wd1153@.discussions.microsoft.com> wrote in message
news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
> Hi
> I have beening trying to build a report with 2 parameters. When I run it
> under VS.net preview, it all went well, but after I deploy it to the
> server, I got
> the message as following: **Reporting Services Error--The path of the item
> ''
> is not valid. The full path must be less than 260 characters long, must
> start
> with slash; other restrictions apply. Check the documentation for complete
> set of restrictions. (rsInvalidItemPath) Get Online Help **
> what went wrong, please help.
> the URL for the report as following:
> http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
> TIA
>
>|||how might be the solution? thanks
"Wayne Snyder" wrote:
> IT sounds like the URL of the report is too long, even though the URL you
> post isn't that long...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "wd1153" <wd1153@.discussions.microsoft.com> wrote in message
> news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
> > Hi
> >
> > I have beening trying to build a report with 2 parameters. When I run it
> > under VS.net preview, it all went well, but after I deploy it to the
> > server, I got
> > the message as following: **Reporting Services Error--The path of the item
> > ''
> > is not valid. The full path must be less than 260 characters long, must
> > start
> > with slash; other restrictions apply. Check the documentation for complete
> > set of restrictions. (rsInvalidItemPath) Get Online Help **
> > what went wrong, please help.
> >
> > the URL for the report as following:
> >
> > http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
> >
> > TIA
> >
> >
> >
>
>|||Maybe the parameter values are too long?
"wd1153" wrote:
> how might be the solution? thanks
> "Wayne Snyder" wrote:
> > IT sounds like the URL of the report is too long, even though the URL you
> > post isn't that long...
> >
> > --
> > Wayne Snyder, MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > www.mariner-usa.com
> > (Please respond only to the newsgroups.)
> >
> > I support the Professional Association of SQL Server (PASS) and it's
> > community of SQL Server professionals.
> > www.sqlpass.org
> >
> > "wd1153" <wd1153@.discussions.microsoft.com> wrote in message
> > news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
> > > Hi
> > >
> > > I have beening trying to build a report with 2 parameters. When I run it
> > > under VS.net preview, it all went well, but after I deploy it to the
> > > server, I got
> > > the message as following: **Reporting Services Error--The path of the item
> > > ''
> > > is not valid. The full path must be less than 260 characters long, must
> > > start
> > > with slash; other restrictions apply. Check the documentation for complete
> > > set of restrictions. (rsInvalidItemPath) Get Online Help **
> > > what went wrong, please help.
> > >
> > > the URL for the report as following:
> > >
> > > http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
> > >
> > > TIA
> > >
> > >
> > >
> >
> >
> >|||I don't think the length of it is an issue. I have many that are longer that
what you post below. How are you trying to run the report? From Report
Manager? Or from you own app, using URL integration?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"wd1153" <wd1153@.discussions.microsoft.com> wrote in message
news:C1FDEE83-1A0B-4259-8BF6-9802039A66D1@.microsoft.com...
> how might be the solution? thanks
> "Wayne Snyder" wrote:
>> IT sounds like the URL of the report is too long, even though the URL you
>> post isn't that long...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "wd1153" <wd1153@.discussions.microsoft.com> wrote in message
>> news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
>> > Hi
>> >
>> > I have beening trying to build a report with 2 parameters. When I run
>> > it
>> > under VS.net preview, it all went well, but after I deploy it to the
>> > server, I got
>> > the message as following: **Reporting Services Error--The path of the
>> > item
>> > ''
>> > is not valid. The full path must be less than 260 characters long, must
>> > start
>> > with slash; other restrictions apply. Check the documentation for
>> > complete
>> > set of restrictions. (rsInvalidItemPath) Get Online Help **
>> > what went wrong, please help.
>> >
>> > the URL for the report as following:
>> >
>> > http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
>> >
>> > TIA
>> >
>> >
>> >
>>|||Thank for replay
I am going to run this at report manager. The report itself should not be a
problem, right? because it runs fine in Visual Studio?
"Bruce L-C [MVP]" wrote:
> I don't think the length of it is an issue. I have many that are longer that
> what you post below. How are you trying to run the report? From Report
> Manager? Or from you own app, using URL integration?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "wd1153" <wd1153@.discussions.microsoft.com> wrote in message
> news:C1FDEE83-1A0B-4259-8BF6-9802039A66D1@.microsoft.com...
> > how might be the solution? thanks
> >
> > "Wayne Snyder" wrote:
> >
> >> IT sounds like the URL of the report is too long, even though the URL you
> >> post isn't that long...
> >>
> >> --
> >> Wayne Snyder, MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> www.mariner-usa.com
> >> (Please respond only to the newsgroups.)
> >>
> >> I support the Professional Association of SQL Server (PASS) and it's
> >> community of SQL Server professionals.
> >> www.sqlpass.org
> >>
> >> "wd1153" <wd1153@.discussions.microsoft.com> wrote in message
> >> news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
> >> > Hi
> >> >
> >> > I have beening trying to build a report with 2 parameters. When I run
> >> > it
> >> > under VS.net preview, it all went well, but after I deploy it to the
> >> > server, I got
> >> > the message as following: **Reporting Services Error--The path of the
> >> > item
> >> > ''
> >> > is not valid. The full path must be less than 260 characters long, must
> >> > start
> >> > with slash; other restrictions apply. Check the documentation for
> >> > complete
> >> > set of restrictions. (rsInvalidItemPath) Get Online Help **
> >> > what went wrong, please help.
> >> >
> >> > the URL for the report as following:
> >> >
> >> > http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
> >> >
> >> > TIA
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Thanks for replay. But the parameters are only a couple of integers, and it
runs fine in VS .net.
"Mary Bray [SQL Server MVP]" wrote:
> Maybe the parameter values are too long?
> "wd1153" wrote:
> > how might be the solution? thanks
> >
> > "Wayne Snyder" wrote:
> >
> > > IT sounds like the URL of the report is too long, even though the URL you
> > > post isn't that long...
> > >
> > > --
> > > Wayne Snyder, MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > > www.mariner-usa.com
> > > (Please respond only to the newsgroups.)
> > >
> > > I support the Professional Association of SQL Server (PASS) and it's
> > > community of SQL Server professionals.
> > > www.sqlpass.org
> > >
> > > "wd1153" <wd1153@.discussions.microsoft.com> wrote in message
> > > news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
> > > > Hi
> > > >
> > > > I have beening trying to build a report with 2 parameters. When I run it
> > > > under VS.net preview, it all went well, but after I deploy it to the
> > > > server, I got
> > > > the message as following: **Reporting Services Error--The path of the item
> > > > ''
> > > > is not valid. The full path must be less than 260 characters long, must
> > > > start
> > > > with slash; other restrictions apply. Check the documentation for complete
> > > > set of restrictions. (rsInvalidItemPath) Get Online Help **
> > > > what went wrong, please help.
> > > >
> > > > the URL for the report as following:
> > > >
> > > > http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
> > > >
> > > > TIA
> > > >
> > > >
> > > >
> > >
> > >
> > >|||I know the URL which you show is not too long, but the error seemed to
indicate it...
I wonder if there is an image or other item in the report, which may have a
URL which is longer than 260 chars... I do think that the way images are
referenced is different in the dev env than in prod... check to see if
there are any long object references...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"wd1153" <wd1153@.discussions.microsoft.com> wrote in message
news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
> Hi
> I have beening trying to build a report with 2 parameters. When I run it
> under VS.net preview, it all went well, but after I deploy it to the
> server, I got
> the message as following: **Reporting Services Error--The path of the item
> ''
> is not valid. The full path must be less than 260 characters long, must
> start
> with slash; other restrictions apply. Check the documentation for complete
> set of restrictions. (rsInvalidItemPath) Get Online Help **
> what went wrong, please help.
> the URL for the report as following:
> http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
> TIA
>
>|||Good idea. This will show up from Report Manager. Right now he has only
tried it from his web app, not from Report Manager.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:OINwq$QZFHA.3864@.TK2MSFTNGP10.phx.gbl...
>I know the URL which you show is not too long, but the error seemed to
>indicate it...
> I wonder if there is an image or other item in the report, which may have
> a URL which is longer than 260 chars... I do think that the way images are
> referenced is different in the dev env than in prod... check to see if
> there are any long object references...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "wd1153" <wd1153@.discussions.microsoft.com> wrote in message
> news:72710497-4078-44FE-94D7-307B737B15EF@.microsoft.com...
>> Hi
>> I have beening trying to build a report with 2 parameters. When I run it
>> under VS.net preview, it all went well, but after I deploy it to the
>> server, I got
>> the message as following: **Reporting Services Error--The path of the
>> item ''
>> is not valid. The full path must be less than 260 characters long, must
>> start
>> with slash; other restrictions apply. Check the documentation for
>> complete
>> set of restrictions. (rsInvalidItemPath) Get Online Help **
>> what went wrong, please help.
>> the URL for the report as following:
>> http://localhost/ReportServer?%2fMyReports%2fORSchedule&rs:Command=Render
>> TIA
>>
>

Help with wildcard numeric search

I have constructed this stored procedure using union to let me find with
lots of flexibility. However one of the Parameters is a Numeric and I only
want to test if it's gtreater than zero. When I do an IF statement I get a
syntax error. I'm new to stored procs, this sort of thing would fly anywhere
else. Can someone help. The parameter I am trying to match on if greater
than zero is @.SuiteAncillaryID. If zero I want the complete result set so I
will need an else.
CREATE PROCEDURE [dbo].[pr_tblPersonAddress_Find_Limited]
@.Complex_Name varchar(60) = '%',
@.Building_Name varchar(60) = '%',
@.Location_Descriptor varchar(60) = '%',
@.House_Number_1 varchar(6) = '%',
@.Street_name varchar(45) = '%',
@.Locality_Name varchar(46) = '%',
@.Surname varchar(50) = '%',
@.FirstName varchar(50) = '%',
@.Position varchar(50) = '%',
@.TradingName varchar(255) = '%',
@.CompanyName varchar(255) = '%',
@.ACN char(20) = '%',
@.ABN char(20) = '%',
@.SiteName varchar(255) = '%',
@.SiteAncillaryID int =0,
@.ErrorCode int OUTPUT
AS
SET NOCOUNT ON
SELECT POIC From
(Select POIC
From tblpoic POICP
JOIN tblPerson PERSON
on POICP.PersonID=PERSON.PersonID
Where
PERSON.[Surname] like '%' + @.Surname + '%' AND
PERSON.[FirstName] like '%' + @.FirstName + '%' AND
PERSON.[Position] like '%' + @.Position + '%'
Union
Select POIC
From tblpoic POICO
JOIN tblOrganisation ORGANISATION
on POICO.OrgID=ORGANISATION.OrgID
Where
ORGANISATION.[TradingName] like '%' + @.TradingName + '%' AND
ORGANISATION.[CompanyName] like '%' + @.CompanyName + '%' AND
ORGANISATION.[ACN] like '%' + @.ACN + '%' AND
ORGANISATION.[ABN] like '%' + @.ABN + '%'
UNION
IF @.SiteAncillaryID>0
Begin
Select POIC
From tblpoic POICS
JOIN tblSite SITE
on POICS.SiteID=SITE.SiteID
WHERE
SITE.[SiteName]>@.SiteName
and SITE.[SiteName]=@.siteAncillaryID
Union
END
Select POIC
From tblpoic POICA
JOIN tbladdress ADDRESS
ON POICA.AddressID = ADDRESS.AddressID
where
ADDRESS.[Complex_Name] like '%' + @.Complex_Name + '%' AND
ADDRESS.[Building_Name] like '%' + @.Building_Name + '%' AND
ADDRESS.[Location_Descriptor] like '%' + @.Location_Descriptor + '%' AND
ADDRESS.[House_Number_1] like '%' + @.House_Number_1 + '%' AND
ADDRESS.[Street_name] like '%' + @.Street_name + '%' AND
ADDRESS.[Locality_Name] like '%' + @.Locality_Name + '%' ) as TEMP
Order by POIC
-- Get the Error Code for the statement just executed.
SELECT @.ErrorCode=@.@.ERROR
-- Get the IDENTITY value for the row just inserted.
GOJust include it in the where clause - if it's <= 0 then you will get an empt
y
resultset for that part of the union.
UNION
Select POIC
From tblpoic POICS
JOIN tblSite SITE
on POICS.SiteID=SITE.SiteID
WHERE
SITE.[SiteName]>@.SiteName
and SITE.[SiteName]=@.siteAncillaryID
and @.SiteAncillaryID>0|||If statements are Transact-SQL, not SQL. A Stored Proc is written in
Transact-SQL language, which is an MS SQL Server priprietary Programming
language for control flow and Variable declaration, whch understands standar
d
SQL..
Standard SQL is the embedded statements that "talk" to the query processor,
the Selects, Updates, Inserts, and Deletes... The T=SQL constructions, (If,
While, Begin End, Declare @.Variable, etc.) can be used only Outside of SQL
Statements not inside of one.
btw, the SQL equivilent (closest equivilent) to IF is Case statement.
Check it out in Books On Line.
"Shoeman" wrote:

> I have constructed this stored procedure using union to let me find with
> lots of flexibility. However one of the Parameters is a Numeric and I only
> want to test if it's gtreater than zero. When I do an IF statement I get a
> syntax error. I'm new to stored procs, this sort of thing would fly anywhe
re
> else. Can someone help. The parameter I am trying to match on if greater
> than zero is @.SuiteAncillaryID. If zero I want the complete result set so
I
> will need an else.
>
> CREATE PROCEDURE [dbo].[pr_tblPersonAddress_Find_Limited]
> @.Complex_Name varchar(60) = '%',
> @.Building_Name varchar(60) = '%',
> @.Location_Descriptor varchar(60) = '%',
> @.House_Number_1 varchar(6) = '%',
> @.Street_name varchar(45) = '%',
> @.Locality_Name varchar(46) = '%',
> @.Surname varchar(50) = '%',
> @.FirstName varchar(50) = '%',
> @.Position varchar(50) = '%',
> @.TradingName varchar(255) = '%',
> @.CompanyName varchar(255) = '%',
> @.ACN char(20) = '%',
> @.ABN char(20) = '%',
> @.SiteName varchar(255) = '%',
> @.SiteAncillaryID int =0,
> @.ErrorCode int OUTPUT
>
> AS
> SET NOCOUNT ON
> SELECT POIC From
> (Select POIC
> From tblpoic POICP
> JOIN tblPerson PERSON
> on POICP.PersonID=PERSON.PersonID
> Where
> PERSON.[Surname] like '%' + @.Surname + '%' AND
> PERSON.[FirstName] like '%' + @.FirstName + '%' AND
> PERSON.[Position] like '%' + @.Position + '%'
> Union
> Select POIC
> From tblpoic POICO
> JOIN tblOrganisation ORGANISATION
> on POICO.OrgID=ORGANISATION.OrgID
> Where
> ORGANISATION.[TradingName] like '%' + @.TradingName + '%' AND
> ORGANISATION.[CompanyName] like '%' + @.CompanyName + '%' AND
> ORGANISATION.[ACN] like '%' + @.ACN + '%' AND
> ORGANISATION.[ABN] like '%' + @.ABN + '%'
> UNION
> IF @.SiteAncillaryID>0
> Begin
> Select POIC
> From tblpoic POICS
> JOIN tblSite SITE
> on POICS.SiteID=SITE.SiteID
> WHERE
> SITE.[SiteName]>@.SiteName
>
> and SITE.[SiteName]=@.siteAncillaryID
> Union
> END
>
>
> Select POIC
> From tblpoic POICA
> JOIN tbladdress ADDRESS
> ON POICA.AddressID = ADDRESS.AddressID
> where
> ADDRESS.[Complex_Name] like '%' + @.Complex_Name + '%' AND
> ADDRESS.[Building_Name] like '%' + @.Building_Name + '%' AND
> ADDRESS.[Location_Descriptor] like '%' + @.Location_Descriptor + '%' AN
D
> ADDRESS.[House_Number_1] like '%' + @.House_Number_1 + '%' AND
> ADDRESS.[Street_name] like '%' + @.Street_name + '%' AND
> ADDRESS.[Locality_Name] like '%' + @.Locality_Name + '%' ) as TEMP
> Order by POIC
>
> -- Get the Error Code for the statement just executed.
> SELECT @.ErrorCode=@.@.ERROR
> -- Get the IDENTITY value for the row just inserted.
> GO
>
>|||> Standard SQL is the embedded statements that "talk" to the query processor,
> the Selects, Updates, Inserts, and Deletes... The T=SQL constructions, (If
,
> While, Begin End, Declare @.Variable, etc.) can be used only Outside of SQ
L
> Statements not inside of one.
So you think that t-sql is use to provide control of flow for sql?
Brings up a few interesting questions - like what is sql?
Actually t-sql is the version of sql implemented on sql server which
includes differences and extensions to any ansi standard.
All control of flow, variable declaration, select statements are t-sql.
p.s. A case statement is usually what people want when they try to use an if
in a select statement but I don't think it is in this case.|||Nigel,
As I'm sure you understand, I'm Just trying to explain why "If" cannot be
used inside a "SQL statement". There is a distinction between those
Statements which Select, Insert, update or delete data, and the control flow
statements which they are embedded in...
"If" is a T-SQL Control-FLow construct, and is not useable within a "SQL"
Statement (a Select/Inert/Update/Delete). Although I cannot find
specifically where this distinction is made in the defintitions, or what the
exact words are to describe it, it is exactly pertinent to the issue the
individual was having...
"Nigel Rivett" wrote:

> So you think that t-sql is use to provide control of flow for sql?
> Brings up a few interesting questions - like what is sql?
> Actually t-sql is the version of sql implemented on sql server which
> includes differences and extensions to any ansi standard.
> All control of flow, variable declaration, select statements are t-sql.
> p.s. A case statement is usually what people want when they try to use an
if
> in a select statement but I don't think it is in this case.|||I'll agree that if is a control of flow statement.
It is also a t-sql statement just like select.
The problem is your assertion that select is an sql statement rather than a
t-sql statement. The distinction is made in defining control of flow
statements.
If you are looking for something that says if is t-sql and select sql then
you won't find it because it's not correct.
You can say that select is part of the ansi standard sql definition and is
also implemented in t-sql.
"CBretana" wrote:
> Nigel,
> As I'm sure you understand, I'm Just trying to explain why "If" cannot
be
> used inside a "SQL statement". There is a distinction between those
> Statements which Select, Insert, update or delete data, and the control fl
ow
> statements which they are embedded in...
> "If" is a T-SQL Control-FLow construct, and is not useable within a "SQL"
> Statement (a Select/Inert/Update/Delete). Although I cannot find
> specifically where this distinction is made in the defintitions, or what t
he
> exact words are to describe it, it is exactly pertinent to the issue the
> individual was having...
>
> "Nigel Rivett" wrote:
>|||Yes, I agree... My semantic mistake was using the Acronym "SQL" to refer to
just those statements which modify or retrieve Data... I'm not sure there is
a phrase or acronym which makes this distinction, but "SQL Statements"
seemed a viable choice, given the issue the user was experiencing...
But you are correct, Thanks.
"Nigel Rivett" wrote:
> I'll agree that if is a control of flow statement.
> It is also a t-sql statement just like select.
> The problem is your assertion that select is an sql statement rather than
a
> t-sql statement. The distinction is made in defining control of flow
> statements.
> If you are looking for something that says if is t-sql and select sql then
> you won't find it because it's not correct.
> You can say that select is part of the ansi standard sql definition and is
> also implemented in t-sql.
> "CBretana" wrote:
>|||Since you're arguing semantics, it would be more accurate to say that the IF
statement is not part of the DML language elements in mickeysoft's T-SQL
implementation and thus cannot be used in a DML statement like Select,
Insert, Update or Delete.
Thomas
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:3385D49B-2CBB-4EC3-B8F1-3CB31E95E39E@.microsoft.com...
> Yes, I agree... My semantic mistake was using the Acronym "SQL" to refer
> to
> just those statements which modify or retrieve Data... I'm not sure there
> is
> a phrase or acronym which makes this distinction, but "SQL Statements"
> seemed a viable choice, given the issue the user was experiencing...
> But you are correct, Thanks.
>
> "Nigel Rivett" wrote:
>