Jump to URL - Syntax wrong?

Hello,
I realize this topic has been beaten to death but I'm still having trouble
with a report.
I'm trying to open up an asp.net page in a new window. Here is what I'm
using based on examples I've read here so far:
= "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum=" & Fields!Name.Value ','_blank'))"
I do this through right clicking on a textbox and going to its advanced
navigation properties. Now the report builds fine with no errors and once
it's done building instead of giving me a preview of the report it says
something like: "Invalid URL type must use http://, https://, file://, etc.".
Is there some other way of doing this by using LinkTarget or something? I
am hoping my syntax is just wrong here. I have RS 2000 with SP2 installed.
Help!
ThanksAlso, when testing this you might need to deploy it to test it properly.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> Hello,
> I realize this topic has been beaten to death but I'm still having trouble
> with a report.
> I'm trying to open up an asp.net page in a new window. Here is what I'm
> using based on examples I've read here so far:
> => "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> & Fields!Name.Value ','_blank'))"
> I do this through right clicking on a textbox and going to its advanced
> navigation properties. Now the report builds fine with no errors and once
> it's done building instead of giving me a preview of the report it says
> something like: "Invalid URL type must use http://, https://, file://,
> etc.".
>
> Is there some other way of doing this by using LinkTarget or something? I
> am hoping my syntax is just wrong here. I have RS 2000 with SP2
> installed.
> Help!
> Thanks|||Here is one that works.
= "javascript:void(window.open('http://www.google.com','_blank'))"
Looking at yours I would say that you need to do this:
="javascript:void(window.open('http://www.mysite.com/subdir/form.aspx?ctrlnum="
& Fields!Name.Value & "','_blank'))"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> Hello,
> I realize this topic has been beaten to death but I'm still having trouble
> with a report.
> I'm trying to open up an asp.net page in a new window. Here is what I'm
> using based on examples I've read here so far:
> => "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> & Fields!Name.Value ','_blank'))"
> I do this through right clicking on a textbox and going to its advanced
> navigation properties. Now the report builds fine with no errors and once
> it's done building instead of giving me a preview of the report it says
> something like: "Invalid URL type must use http://, https://, file://,
> etc.".
>
> Is there some other way of doing this by using LinkTarget or something? I
> am hoping my syntax is just wrong here. I have RS 2000 with SP2
> installed.
> Help!
> Thanks|||You mean I could have had it right and it would still give me that syntax
error?
"Bruce L-C [MVP]" wrote:
> Also, when testing this you might need to deploy it to test it properly.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John" <John@.discussions.microsoft.com> wrote in message
> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> > Hello,
> >
> > I realize this topic has been beaten to death but I'm still having trouble
> > with a report.
> >
> > I'm trying to open up an asp.net page in a new window. Here is what I'm
> > using based on examples I've read here so far:
> >
> > => > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> > & Fields!Name.Value ','_blank'))"
> >
> > I do this through right clicking on a textbox and going to its advanced
> > navigation properties. Now the report builds fine with no errors and once
> > it's done building instead of giving me a preview of the report it says
> > something like: "Invalid URL type must use http://, https://, file://,
> > etc.".
> >
> >
> > Is there some other way of doing this by using LinkTarget or something? I
> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
> > installed.
> >
> > Help!
> > Thanks
>
>|||What happens to me from development is I get a flash and a ding sound and
nothing happens.
No, I think you have your syntax incorrect. You are trying to assemble a
string. Look at this part: & Fields!Name.Value ','_blank'))"
it should be & Fields!Name.Value & "','_blank'))"
You want to append the two strings together. The value in your feild and the
rest of the string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
> You mean I could have had it right and it would still give me that syntax
> error?
> "Bruce L-C [MVP]" wrote:
>> Also, when testing this you might need to deploy it to test it properly.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
>> > Hello,
>> >
>> > I realize this topic has been beaten to death but I'm still having
>> > trouble
>> > with a report.
>> >
>> > I'm trying to open up an asp.net page in a new window. Here is what
>> > I'm
>> > using based on examples I've read here so far:
>> >
>> > =>> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
>> > & Fields!Name.Value ','_blank'))"
>> >
>> > I do this through right clicking on a textbox and going to its advanced
>> > navigation properties. Now the report builds fine with no errors and
>> > once
>> > it's done building instead of giving me a preview of the report it says
>> > something like: "Invalid URL type must use http://, https://, file://,
>> > etc.".
>> >
>> >
>> > Is there some other way of doing this by using LinkTarget or something?
>> > I
>> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
>> > installed.
>> >
>> > Help!
>> > Thanks
>>|||Great thanks!
I will test this as soon as I can and let you know.
"Bruce L-C [MVP]" wrote:
> What happens to me from development is I get a flash and a ding sound and
> nothing happens.
> No, I think you have your syntax incorrect. You are trying to assemble a
> string. Look at this part: & Fields!Name.Value ','_blank'))"
> it should be & Fields!Name.Value & "','_blank'))"
> You want to append the two strings together. The value in your feild and the
> rest of the string.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John" <John@.discussions.microsoft.com> wrote in message
> news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
> > You mean I could have had it right and it would still give me that syntax
> > error?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Also, when testing this you might need to deploy it to test it properly.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John" <John@.discussions.microsoft.com> wrote in message
> >> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> >> > Hello,
> >> >
> >> > I realize this topic has been beaten to death but I'm still having
> >> > trouble
> >> > with a report.
> >> >
> >> > I'm trying to open up an asp.net page in a new window. Here is what
> >> > I'm
> >> > using based on examples I've read here so far:
> >> >
> >> > => >> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> >> > & Fields!Name.Value ','_blank'))"
> >> >
> >> > I do this through right clicking on a textbox and going to its advanced
> >> > navigation properties. Now the report builds fine with no errors and
> >> > once
> >> > it's done building instead of giving me a preview of the report it says
> >> > something like: "Invalid URL type must use http://, https://, file://,
> >> > etc.".
> >> >
> >> >
> >> > Is there some other way of doing this by using LinkTarget or something?
> >> > I
> >> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
> >> > installed.
> >> >
> >> > Help!
> >> > Thanks
> >>
> >>
> >>
>
>|||I just wanted to say this is exactly what I needed. It still gives me a
"invalid url" error inside VS but it works fine once I deploy it and test
inside IE.
Thanks!
"Bruce L-C [MVP]" wrote:
> Here is one that works.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> Looking at yours I would say that you need to do this:
> => "javascript:void(window.open('http://www.mysite.com/subdir/form.aspx?ctrlnum="
> & Fields!Name.Value & "','_blank'))"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
> > Hello,
> >
> > I realize this topic has been beaten to death but I'm still having trouble
> > with a report.
> >
> > I'm trying to open up an asp.net page in a new window. Here is what I'm
> > using based on examples I've read here so far:
> >
> > => > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
> > & Fields!Name.Value ','_blank'))"
> >
> > I do this through right clicking on a textbox and going to its advanced
> > navigation properties. Now the report builds fine with no errors and once
> > it's done building instead of giving me a preview of the report it says
> > something like: "Invalid URL type must use http://, https://, file://,
> > etc.".
> >
> >
> > Is there some other way of doing this by using LinkTarget or something? I
> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
> > installed.
> >
> > Help!
> > Thanks
>
>|||Make sure you are running at least RS 2000 SP1 on the report server and
report designer for this to work.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"John" <John@.discussions.microsoft.com> wrote in message
news:1B63DA2A-1655-477E-BBCA-7839423DBD52@.microsoft.com...
> Great thanks!
> I will test this as soon as I can and let you know.
>
> "Bruce L-C [MVP]" wrote:
>> What happens to me from development is I get a flash and a ding sound and
>> nothing happens.
>> No, I think you have your syntax incorrect. You are trying to assemble a
>> string. Look at this part: & Fields!Name.Value ','_blank'))"
>> it should be & Fields!Name.Value & "','_blank'))"
>> You want to append the two strings together. The value in your feild and
>> the
>> rest of the string.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
>> > You mean I could have had it right and it would still give me that
>> > syntax
>> > error?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Also, when testing this you might need to deploy it to test it
>> >> properly.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John" <John@.discussions.microsoft.com> wrote in message
>> >> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I realize this topic has been beaten to death but I'm still having
>> >> > trouble
>> >> > with a report.
>> >> >
>> >> > I'm trying to open up an asp.net page in a new window. Here is what
>> >> > I'm
>> >> > using based on examples I've read here so far:
>> >> >
>> >> > =>> >> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
>> >> > & Fields!Name.Value ','_blank'))"
>> >> >
>> >> > I do this through right clicking on a textbox and going to its
>> >> > advanced
>> >> > navigation properties. Now the report builds fine with no errors
>> >> > and
>> >> > once
>> >> > it's done building instead of giving me a preview of the report it
>> >> > says
>> >> > something like: "Invalid URL type must use http://, https://,
>> >> > file://,
>> >> > etc.".
>> >> >
>> >> >
>> >> > Is there some other way of doing this by using LinkTarget or
>> >> > something?
>> >> > I
>> >> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
>> >> > installed.
>> >> >
>> >> > Help!
>> >> > Thanks
>> >>
>> >>
>> >>
>>|||It is common to get different behaviour in the dev environment than in
prod... We always go ahead and deploy and test both places before we decide
that something doesn't work.
--
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
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OYsFzsqqFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Make sure you are running at least RS 2000 SP1 on the report server and
> report designer for this to work.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "John" <John@.discussions.microsoft.com> wrote in message
> news:1B63DA2A-1655-477E-BBCA-7839423DBD52@.microsoft.com...
>> Great thanks!
>> I will test this as soon as I can and let you know.
>>
>> "Bruce L-C [MVP]" wrote:
>> What happens to me from development is I get a flash and a ding sound
>> and
>> nothing happens.
>> No, I think you have your syntax incorrect. You are trying to assemble a
>> string. Look at this part: & Fields!Name.Value ','_blank'))"
>> it should be & Fields!Name.Value & "','_blank'))"
>> You want to append the two strings together. The value in your feild and
>> the
>> rest of the string.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:2C4D09B7-C68E-4BFB-AA86-B2B8D36665B2@.microsoft.com...
>> > You mean I could have had it right and it would still give me that
>> > syntax
>> > error?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Also, when testing this you might need to deploy it to test it
>> >> properly.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "John" <John@.discussions.microsoft.com> wrote in message
>> >> news:02B7C28C-8A74-465A-95B9-C5959F426FB7@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I realize this topic has been beaten to death but I'm still having
>> >> > trouble
>> >> > with a report.
>> >> >
>> >> > I'm trying to open up an asp.net page in a new window. Here is
>> >> > what
>> >> > I'm
>> >> > using based on examples I've read here so far:
>> >> >
>> >> > =>> >> > "javascript:void(window.open('"http://www.mysite.com/subdir/form.aspx?ctrlnum="
>> >> > & Fields!Name.Value ','_blank'))"
>> >> >
>> >> > I do this through right clicking on a textbox and going to its
>> >> > advanced
>> >> > navigation properties. Now the report builds fine with no errors
>> >> > and
>> >> > once
>> >> > it's done building instead of giving me a preview of the report it
>> >> > says
>> >> > something like: "Invalid URL type must use http://, https://,
>> >> > file://,
>> >> > etc.".
>> >> >
>> >> >
>> >> > Is there some other way of doing this by using LinkTarget or
>> >> > something?
>> >> > I
>> >> > am hoping my syntax is just wrong here. I have RS 2000 with SP2
>> >> > installed.
>> >> >
>> >> > Help!
>> >> > Thanks
>> >>
>> >>
>> >>
>>
>

Jump to URL - Passing Parameter Issue with special symbols

Hi Microsoft,
I've currently using the Jump to URL Syntax (see below) to open a new window
and pop up a drilldown report in IE.
The main problem i'm having with the syntax below iswhen the account manager
name (ACCTMGR Parameter string) contains a umlaut (german name with a double
o on top of a letter), the drilldown report runs but it doesn't run the
report details (report is blank) when information does exist. I've tried
search for resolutions online and cam up with nothing.
In addition, when a parenthesis and or an ampersand is present in the
parameter, it does not run the drilldown subreport. Your help would be
greatly appreciated.
Can you help?
=iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
& Parameters!rptCurrency.Value & "&Division=" &
Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
"&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
"','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")My guess is you have to URL encode this. Here is a link. You might be able
to use HttpUtility.UrlEncode in your expression. However, if it doesn't then
you will need to use code behind reports.
http://weblogs.asp.net/psteele/pages/7509.aspx
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
message news:2BDFEDDE-C685-4224-91A1-489DF67E757D@.microsoft.com...
> Hi Microsoft,
> I've currently using the Jump to URL Syntax (see below) to open a new
> window
> and pop up a drilldown report in IE.
> The main problem i'm having with the syntax below iswhen the account
> manager
> name (ACCTMGR Parameter string) contains a umlaut (german name with a
> double
> o on top of a letter), the drilldown report runs but it doesn't run the
> report details (report is blank) when information does exist. I've tried
> search for resolutions online and cam up with nothing.
> In addition, when a parenthesis and or an ampersand is present in the
> parameter, it does not run the drilldown subreport. Your help would be
> greatly appreciated.
> Can you help?
> =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
> & Parameters!rptCurrency.Value & "&Division=" &
> Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
> Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
> Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
> Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
> Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
> "&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
> "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")|||Hi Bruce,
Thanks for the quick reply!
I've tried referencing System.Web.HttpUtility.UrlEncode (and also just
HttpUtility.UrlEncode) in the Jump to URL syntax as such:
=iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://myserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Opportunity+Report+Details&rc:Parameters=false&rptCurrency="
& Parameters!rptCurrency.Value & "&Division=" &
Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
"&ACCTMGR=" & System.Web.HttpUtility.UrlEncode(ReportItems("ACCTMGR1").Value)
& "&CAMP=LEFT+BLANK" &
"','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=1200,resizable=yes'))")
But no luck it doesn't work. It actually doesn't recognize the UrlEncode
syntax and I've also added the Reference System.Web as well to ensure
everything was referenced properly.
I'm a little new to VB so if you don't mind, I would greatly appreciate it
if you can be a little more specific on what needs to be completed iwth the
code and show an example of how would i reference it in the Jump to URL
expression below?
Thank you in advance for your help.
"Bruce L-C [MVP]" wrote:
> My guess is you have to URL encode this. Here is a link. You might be able
> to use HttpUtility.UrlEncode in your expression. However, if it doesn't then
> you will need to use code behind reports.
> http://weblogs.asp.net/psteele/pages/7509.aspx
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
> message news:2BDFEDDE-C685-4224-91A1-489DF67E757D@.microsoft.com...
> > Hi Microsoft,
> >
> > I've currently using the Jump to URL Syntax (see below) to open a new
> > window
> > and pop up a drilldown report in IE.
> >
> > The main problem i'm having with the syntax below iswhen the account
> > manager
> > name (ACCTMGR Parameter string) contains a umlaut (german name with a
> > double
> > o on top of a letter), the drilldown report runs but it doesn't run the
> > report details (report is blank) when information does exist. I've tried
> > search for resolutions online and cam up with nothing.
> >
> > In addition, when a parenthesis and or an ampersand is present in the
> > parameter, it does not run the drilldown subreport. Your help would be
> > greatly appreciated.
> >
> > Can you help?
> >
> > =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
> > & Parameters!rptCurrency.Value & "&Division=" &
> > Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
> > Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
> > Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
> > Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
> > Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
> > "&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
> > "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")
>
>|||I suggest creating a report with a single textbox in it. Set your expression
so you can see the result. Next read up on using the code behind reports:
Using Custom Code References in Expressions (Reporting Services)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rptsrvr9/html/13fa8c92-0471-4b2f-b97c-9ebbcb13797f.htm
Your expression will look like this:
=Code.URLEncode("This is a test")URLEncode is a function you have written
that takes a single parameter and returns the encoded string.-- Bruce
Loehle-CongerMVP SQL Server Reporting Services
"RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
message news:AA9EC05C-F668-4E99-AB58-48968CF9E909@.microsoft.com...
> Hi Bruce,
> Thanks for the quick reply!
> I've tried referencing System.Web.HttpUtility.UrlEncode (and also just
> HttpUtility.UrlEncode) in the Jump to URL syntax as such:
> =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://myserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Opportunity+Report+Details&rc:Parameters=false&rptCurrency="
> & Parameters!rptCurrency.Value & "&Division=" &
> Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
> Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
> Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
> Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
> Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
> "&ACCTMGR=" &
> System.Web.HttpUtility.UrlEncode(ReportItems("ACCTMGR1").Value)
> & "&CAMP=LEFT+BLANK" &
> "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=1200,resizable=yes'))")
> But no luck it doesn't work. It actually doesn't recognize the UrlEncode
> syntax and I've also added the Reference System.Web as well to ensure
> everything was referenced properly.
> I'm a little new to VB so if you don't mind, I would greatly appreciate it
> if you can be a little more specific on what needs to be completed iwth
> the
> code and show an example of how would i reference it in the Jump to URL
> expression below?
> Thank you in advance for your help.
> "Bruce L-C [MVP]" wrote:
>> My guess is you have to URL encode this. Here is a link. You might be
>> able
>> to use HttpUtility.UrlEncode in your expression. However, if it doesn't
>> then
>> you will need to use code behind reports.
>> http://weblogs.asp.net/psteele/pages/7509.aspx
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "RS2005Developer" <RS2005Developer@.discussions.microsoft.com> wrote in
>> message news:2BDFEDDE-C685-4224-91A1-489DF67E757D@.microsoft.com...
>> > Hi Microsoft,
>> >
>> > I've currently using the Jump to URL Syntax (see below) to open a new
>> > window
>> > and pop up a drilldown report in IE.
>> >
>> > The main problem i'm having with the syntax below iswhen the account
>> > manager
>> > name (ACCTMGR Parameter string) contains a umlaut (german name with a
>> > double
>> > o on top of a letter), the drilldown report runs but it doesn't run the
>> > report details (report is blank) when information does exist. I've
>> > tried
>> > search for resolutions online and cam up with nothing.
>> >
>> > In addition, when a parenthesis and or an ampersand is present in the
>> > parameter, it does not run the drilldown subreport. Your help would be
>> > greatly appreciated.
>> >
>> > Can you help?
>> >
>> > =iif(sum(Fields!TotalAmount.Value)=0,"","javascript:void(window.open('http://ourreportserveralias/reportserver?%2fDrillDown+Reports%2fMarketing+Campaign+Report+Details&rc:Parameters=false&rptCurrency="
>> > & Parameters!rptCurrency.Value & "&Division=" &
>> > Join(Parameters!Division.Value, "&Division=") & "&DivSales=" &
>> > Join(Parameters!DivSales.Value, "&DivSales=") & "&DeptSales=" &
>> > Join(Parameters!DeptSales.Value, "&DeptSales=") & "&ProductLIne=" &
>> > Join(Parameters!ProductLIne.Value, "&ProductLIne=") & "&startdate=" &
>> > Parameters!startdate.Value & "&enddate=" & Parameters!enddate.Value &
>> > "&ACCTMGR=" & ReportItems("ACCTMGR1").Value & "&CAMP=LEFT+BLANK" &
>> > "','_blank','location=no,toolbar=no,left=100,top=100,height=600,width=800,resizable=yes'))")
>>

Joining 2 Tables

I am having difficulty with the correct JOIN syntax for retrieving data from 2 SQL tables. The two tables are:

Table tblA
----
ID Name
1916 Al
1917 Bill
1918 Carrie
1919 Ed
1920 Frank
1921 Kristin
1922 Laura
1923 Mike

Table tblB
----
StaffID BossID Level
1917 1916 1
1918 1916 1
1919 1917 1
1920 1917 1
1921 1920 1
1922 1920 1
1919 1916 2
1920 1916 2
1921 1917 2
1922 1917 2
1921 1916 3
1922 1916 3

Desired Result Table for a
selected Table tblA ID of 1917
----------
ID Name
1916 Al
1918 Carrie
1923 Mike

Here's what's going on: Table tblA is a list of people (the ID is unique). Table tblB is a list of reporting relationships among the people in Table tblA. The Level column indicates how far down the org tree they are. So, for example, Ed (1919) reports to Bill (1917) who reports to Al (1916). In table tblB, record 1 shows Bill reporting to Al. Record 3 shows Ed reporting to Bill. And record 7 shows Ed reporting to Al 2 levels up (ie. through Bill).

The result that I'm trying to generate is all of the people that a selected person could possibly report to. So, for example, if I selected Bill (1917) from Table tblA, the only people he could report to would be Al, Carrie and Mike (since everyone else in Table tblA either reports to Bill at some level or is Bill).

I've tried (unsuccessfully):

"SELECT DISTINCT tblA.ID, tblA.Name From tblA LEFT OUTER JOIN tblB ON tblB.StaffID = tblA.ID WHERE tblB.BossID <> " & selID

where selID is the ID of the person of interest (1917 in the example above).

Please help me find the right syntax to get the desired resulting table.I am unable to find a reference to 1923 (Mike) in table b. Do you want to know who 1917 supervises or who 1917 is supervised by ? What is the relationship between bossid and staffid - how does 1917 relate to 1918 and 1923 ?|||Thanks for your response, rnealejr.

Table tblB is a list of reporting relationships among the people listed in tblA. Mike doesn't report to anyone, and he doesn't have anyone reporting to him. That's why he doesn't appear in tblB. But since he doesn't report to Bill at any level, the user may want to establish a new reporting relationship having Bill report to Mike. That's why Mike should be included in the result table.

The end user will be looking at a personnel record from tblA (eg, Bill) wanting to assign a new "boss" to that person. I'm trying ot give him a combobox list (the Result Table) of possible bosses. Frank, for example, shouldn't be in that list of Bill's possible bosses because Bill is Frank's boss (4th record, tblB). Kristin can't be Bill's boss either, since Kristin reports to Bill through Frank (5th and 9th records, tblB).

Also, there is no reporting relationship between 1917 and either 1918 or 1923. That's why 1918 and 1923 should be included in the Result Table for 1917. As it happens, 1916 is already Bill's boss (record 1, tblB), so 1916 is also a candidate boss for Bill.

Here's how the example organization chart would look:

1916 Al
|
--1917 Bill
| |
| --1919 Ed
| |
| --1920 Frank
| |
| --1921 Kristin
| |
| --1922 Laura
|
--1918 Carrie

1923 Mike

I hope this is clearer. Thanks again for taking a look at my question.|||Try the following:

select id, name from tblA where id not in (select distinct staffid from tblB where bossid = 1917) and id <> 1917|||Thanks, rnealejr !

That did the trick!

Join syntax?

I'm not sure if this is a join problem or even if it can be done.
The english part of the query will be requesting cagegoires="Cat2"
and return the matching records in table1, plus return all the other
categories that belong to the set in table 2.
should return two records..
1 document1 Cat1 Cat2 Cat3
2 document2 Cat1 Cat2
table 1
ID int DOC char(20)
1 document1
2 document2
table 2
ID int Category char(4)
1 Cat1
1 Cat2
1 Cat3
2 Cat1
2 Cat2
Thanks,
Don
table 1
1 do
On Mon, 29 Aug 2005 13:56:36 -0700, DonSQL2222 wrote:

>I'm not sure if this is a join problem or even if it can be done.
>The english part of the query will be requesting cagegoires="Cat2"
>and return the matching records in table1, plus return all the other
>categories that belong to the set in table 2.
>should return two records..
>1 document1 Cat1 Cat2 Cat3
>2 document2 Cat1 Cat2
(snip)
Hi DonSQL2222,
To get the basic information, but not in the format you specified above:
SELECT t1.ID, t1.DOC, t2.Category
FROM table1 AS t1
INNER JOIN table2 AS t2
ON t2.ID = t1.ID
ORDER BY t1.ID, t2.Category
Modifying the format to your desired form should be done at the front
end. Presentation is not a task for the database in a tiered
architecture.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Join syntax help

I am trying to figure out some sql syntax, and I could use some help. This
is my first atempt at joins, so bear with me.

I have a table (A) which looks like the following

ID Data Source
--------
1 abcdef 100
2 abcdef 100
3 abcdef 200
4 abcdef 200
5 abcdef 200

A second table (B) which looks like the following

Key ID
--------
Key1 1
Key1 2
Key1 3
Key1 4
Key2 1
Key2 2

Essentially, A is a table of items, and B is a table of where those items
have been used (Key1 is like an invoice which has items 1-4 on it, Key2 is a
second invoice with 1 and 2.) Source, in table A, is like the item
supplier.

I would like to get a list of every invoice (Key) that has used a part (ID)
from a particular Source.

So, for example, I would like to query for source 100 and get back (Key1,
Key2) or query for source 200 and get back only Key1.

To this end, I tried

"SELECT DISTINCT B.Key FROM B JOIN A ON (B.ID = A.ID) WHERE (A.Source =
100)"

But I got an empty recordset, so something is amiss.

Any help is greatly appreciated.

Thanks,

-dPlease post some code that will actually reproduce the problem. Your query
worked for me and here's the proof:

/* (My assumptions about your tables and keys) */
CREATE TABLE A (id INTEGER PRIMARY KEY, data VARCHAR(10), source INTEGER NOT
NULL)
CREATE TABLE B ([key] VARCHAR(10), id INTEGER NOT NULL REFERENCES A (id),
PRIMARY KEY ([key],id))

INSERT INTO A (id, data, source)
SELECT 1, 'abcdef', 100 UNION ALL
SELECT 2, 'abcdef', 100 UNION ALL
SELECT 3, 'abcdef', 200 UNION ALL
SELECT 4, 'abcdef', 200 UNION ALL
SELECT 5, 'abcdef', 200

INSERT INTO B ([key],id)
SELECT 'Key1', 1 UNION ALL
SELECT 'Key1', 2 UNION ALL
SELECT 'Key1', 3 UNION ALL
SELECT 'Key1', 4 UNION ALL
SELECT 'Key2', 1 UNION ALL
SELECT 'Key2', 2

SELECT DISTINCT B.[key]
FROM B JOIN A
ON B.id = A.id
WHERE A.source = 100

Result:

key
----
Key1
Key2

(2 row(s) affected)

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Vd6dnQPnvP1QhX7cRVn-2g@.giganews.com...
> Please post some code that will actually reproduce the problem. Your query
> worked for me and here's the proof:

Goodness, sorry to waste your time, and thanks for the help nonetheless. It
seems I was querying for nonexistent data.

-d