Showing posts with label linked. Show all posts
Showing posts with label linked. Show all posts

Wednesday, March 28, 2012

Jump To Report Without Running It Automatically

Hi

I need to be able to click a hyperlink in one report to open a linked report, but without automatically running the linked report.

Scenario.

I have a report showing customer details and on this page there is a link to a price enquiry report. This price enquiry report has two parameters: customer code and product code.

When I click the price enquiry hyperlink in the customer details report I can only attach a value to the customer code parameter. The product code parameter is not known at this stage and would have to be provided by the user after the price enquiry report is opened (but not run). The customer code parameter should already be filled (carried in the link).

When I click the link at the moment I get the error "The 'product' parameter is missing a value".

Any advice much appreciated.

Thanks,

SQLServant

Sometime ago, I made a post about this. It was stated that it's by design and that all parameter values must be passed to the report.

I believe using the hyperlink feature might work though you'd have to manually append all parameter names and values that you -do- want to pass (reportname&param=val&param2=val2).

Jump to a linked report

I have a problem using the jump-to feature in a linked report.
We are deploying all reports to a single folder 'Deployment' (not visible to
the browser role) and then creating links to these reports in folders that
are visible to the browser role. So from a maintenance point of view, there
is only ever one version of the report to update.
So we have report A that jumps-to report B and both of these are deployed to
the â'Deploymentâ' folder which is only visible to developers. We then create
links, AL and BL, to both A and B, in another folder called â'Runtimeâ' folder
which is visible to end users. End users are able to execute report AL fine
but when they try to jump-to BL, report manager looks for BL in the
â'Deploymentâ' folder instead of the â'Runtimeâ' folder, therefore, errors are
thrown because users donâ't have access to the â'Deployment Folderâ'.
How do we force the jump to action on linked reports to remain in the
current folder?
Obviously this seems to work fine if we are not using linked reports.
Hope this makes sense and any advice would be appreciated.
TIAWhat you want to be doing is doing a jump to the linked report, not the base
report. Instead of the name of the report (which assumes it is in the
current folder, i.e. Deployment folder in your case, do this instead:
/Runtime/TheReportYouWant
This will allow you to jump to the link report as you want. Note that it
means you have to deploy to test the link, you won't be able to test from
within Report Designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>I have a problem using the jump-to feature in a linked report.
> We are deploying all reports to a single folder 'Deployment' (not visible
> to
> the browser role) and then creating links to these reports in folders that
> are visible to the browser role. So from a maintenance point of view,
> there
> is only ever one version of the report to update.
> So we have report A that jumps-to report B and both of these are deployed
> to
> the 'Deployment' folder which is only visible to developers. We then
> create
> links, AL and BL, to both A and B, in another folder called 'Runtime'
> folder
> which is visible to end users. End users are able to execute report AL
> fine
> but when they try to jump-to BL, report manager looks for BL in the
> 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> thrown because users don't have access to the 'Deployment Folder'.
> How do we force the jump to action on linked reports to remain in the
> current folder?
> Obviously this seems to work fine if we are not using linked reports.
> Hope this makes sense and any advice would be appreciated.
> TIA
>|||Thanks Bruce.
But to further complicate matters...
If we only want to deploy one version of the main report, how would this
work if we wanted to have multiple links to the reports, i.e. one in
'Runtime' folder and one in 'Runtime2' folder, with different access
permissions on each folder?
Cheers
"Bruce L-C [MVP]" wrote:
> What you want to be doing is doing a jump to the linked report, not the base
> report. Instead of the name of the report (which assumes it is in the
> current folder, i.e. Deployment folder in your case, do this instead:
> /Runtime/TheReportYouWant
> This will allow you to jump to the link report as you want. Note that it
> means you have to deploy to test the link, you won't be able to test from
> within Report Designer.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> >I have a problem using the jump-to feature in a linked report.
> > We are deploying all reports to a single folder 'Deployment' (not visible
> > to
> > the browser role) and then creating links to these reports in folders that
> > are visible to the browser role. So from a maintenance point of view,
> > there
> > is only ever one version of the report to update.
> > So we have report A that jumps-to report B and both of these are deployed
> > to
> > the 'Deployment' folder which is only visible to developers. We then
> > create
> > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > folder
> > which is visible to end users. End users are able to execute report AL
> > fine
> > but when they try to jump-to BL, report manager looks for BL in the
> > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > thrown because users don't have access to the 'Deployment Folder'.
> > How do we force the jump to action on linked reports to remain in the
> > current folder?
> > Obviously this seems to work fine if we are not using linked reports.
> > Hope this makes sense and any advice would be appreciated.
> > TIA
> >
>
>|||Do you realize that instead of all these links that you can set the report
permissions so that based on the role they only see the reports they have
rights to view?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
> Thanks Bruce.
> But to further complicate matters...
> If we only want to deploy one version of the main report, how would this
> work if we wanted to have multiple links to the reports, i.e. one in
> 'Runtime' folder and one in 'Runtime2' folder, with different access
> permissions on each folder?
> Cheers
> "Bruce L-C [MVP]" wrote:
>> What you want to be doing is doing a jump to the linked report, not the
>> base
>> report. Instead of the name of the report (which assumes it is in the
>> current folder, i.e. Deployment folder in your case, do this instead:
>> /Runtime/TheReportYouWant
>> This will allow you to jump to the link report as you want. Note that it
>> means you have to deploy to test the link, you won't be able to test from
>> within Report Designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>> >I have a problem using the jump-to feature in a linked report.
>> > We are deploying all reports to a single folder 'Deployment' (not
>> > visible
>> > to
>> > the browser role) and then creating links to these reports in folders
>> > that
>> > are visible to the browser role. So from a maintenance point of view,
>> > there
>> > is only ever one version of the report to update.
>> > So we have report A that jumps-to report B and both of these are
>> > deployed
>> > to
>> > the 'Deployment' folder which is only visible to developers. We then
>> > create
>> > links, AL and BL, to both A and B, in another folder called 'Runtime'
>> > folder
>> > which is visible to end users. End users are able to execute report AL
>> > fine
>> > but when they try to jump-to BL, report manager looks for BL in the
>> > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors
>> > are
>> > thrown because users don't have access to the 'Deployment Folder'.
>> > How do we force the jump to action on linked reports to remain in the
>> > current folder?
>> > Obviously this seems to work fine if we are not using linked reports.
>> > Hope this makes sense and any advice would be appreciated.
>> > TIA
>> >
>>|||I guess we can but we need to be able to specify different parameters for
each report depending on who is viewing it.
Does that make sense or do you think we are going about things in the wrong
way?
"Bruce L-C [MVP]" wrote:
> Do you realize that instead of all these links that you can set the report
> permissions so that based on the role they only see the reports they have
> rights to view?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
> > Thanks Bruce.
> >
> > But to further complicate matters...
> >
> > If we only want to deploy one version of the main report, how would this
> > work if we wanted to have multiple links to the reports, i.e. one in
> > 'Runtime' folder and one in 'Runtime2' folder, with different access
> > permissions on each folder?
> >
> > Cheers
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What you want to be doing is doing a jump to the linked report, not the
> >> base
> >> report. Instead of the name of the report (which assumes it is in the
> >> current folder, i.e. Deployment folder in your case, do this instead:
> >>
> >> /Runtime/TheReportYouWant
> >>
> >> This will allow you to jump to the link report as you want. Note that it
> >> means you have to deploy to test the link, you won't be able to test from
> >> within Report Designer.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> >> >I have a problem using the jump-to feature in a linked report.
> >> > We are deploying all reports to a single folder 'Deployment' (not
> >> > visible
> >> > to
> >> > the browser role) and then creating links to these reports in folders
> >> > that
> >> > are visible to the browser role. So from a maintenance point of view,
> >> > there
> >> > is only ever one version of the report to update.
> >> > So we have report A that jumps-to report B and both of these are
> >> > deployed
> >> > to
> >> > the 'Deployment' folder which is only visible to developers. We then
> >> > create
> >> > links, AL and BL, to both A and B, in another folder called 'Runtime'
> >> > folder
> >> > which is visible to end users. End users are able to execute report AL
> >> > fine
> >> > but when they try to jump-to BL, report manager looks for BL in the
> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors
> >> > are
> >> > thrown because users don't have access to the 'Deployment Folder'.
> >> > How do we force the jump to action on linked reports to remain in the
> >> > current folder?
> >> > Obviously this seems to work fine if we are not using linked reports.
> >> > Hope this makes sense and any advice would be appreciated.
> >> > TIA
> >> >
> >>
> >>
> >>
>
>|||Is the user going to this or are you integrating into your own app. It
sounds like here you are integrating it into your app.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:8B07B2C4-34CA-461B-929F-6DBEB4B44AAC@.microsoft.com...
>I guess we can but we need to be able to specify different parameters for
> each report depending on who is viewing it.
> Does that make sense or do you think we are going about things in the
> wrong
> way?
> "Bruce L-C [MVP]" wrote:
>> Do you realize that instead of all these links that you can set the
>> report
>> permissions so that based on the role they only see the reports they have
>> rights to view?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
>> > Thanks Bruce.
>> >
>> > But to further complicate matters...
>> >
>> > If we only want to deploy one version of the main report, how would
>> > this
>> > work if we wanted to have multiple links to the reports, i.e. one in
>> > 'Runtime' folder and one in 'Runtime2' folder, with different access
>> > permissions on each folder?
>> >
>> > Cheers
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> What you want to be doing is doing a jump to the linked report, not
>> >> the
>> >> base
>> >> report. Instead of the name of the report (which assumes it is in the
>> >> current folder, i.e. Deployment folder in your case, do this instead:
>> >>
>> >> /Runtime/TheReportYouWant
>> >>
>> >> This will allow you to jump to the link report as you want. Note that
>> >> it
>> >> means you have to deploy to test the link, you won't be able to test
>> >> from
>> >> within Report Designer.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>> >> >I have a problem using the jump-to feature in a linked report.
>> >> > We are deploying all reports to a single folder 'Deployment' (not
>> >> > visible
>> >> > to
>> >> > the browser role) and then creating links to these reports in
>> >> > folders
>> >> > that
>> >> > are visible to the browser role. So from a maintenance point of
>> >> > view,
>> >> > there
>> >> > is only ever one version of the report to update.
>> >> > So we have report A that jumps-to report B and both of these are
>> >> > deployed
>> >> > to
>> >> > the 'Deployment' folder which is only visible to developers. We
>> >> > then
>> >> > create
>> >> > links, AL and BL, to both A and B, in another folder called
>> >> > 'Runtime'
>> >> > folder
>> >> > which is visible to end users. End users are able to execute report
>> >> > AL
>> >> > fine
>> >> > but when they try to jump-to BL, report manager looks for BL in the
>> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore,
>> >> > errors
>> >> > are
>> >> > thrown because users don't have access to the 'Deployment Folder'.
>> >> > How do we force the jump to action on linked reports to remain in
>> >> > the
>> >> > current folder?
>> >> > Obviously this seems to work fine if we are not using linked
>> >> > reports.
>> >> > Hope this makes sense and any advice would be appreciated.
>> >> > TIA
>> >> >
>> >>
>> >>
>> >>
>>|||Both really, but primarily users will access the reports directly through
report manager.
"Bruce L-C [MVP]" wrote:
> Is the user going to this or are you integrating into your own app. It
> sounds like here you are integrating it into your app.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> news:8B07B2C4-34CA-461B-929F-6DBEB4B44AAC@.microsoft.com...
> >I guess we can but we need to be able to specify different parameters for
> > each report depending on who is viewing it.
> >
> > Does that make sense or do you think we are going about things in the
> > wrong
> > way?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Do you realize that instead of all these links that you can set the
> >> report
> >> permissions so that based on the role they only see the reports they have
> >> rights to view?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> >> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
> >> > Thanks Bruce.
> >> >
> >> > But to further complicate matters...
> >> >
> >> > If we only want to deploy one version of the main report, how would
> >> > this
> >> > work if we wanted to have multiple links to the reports, i.e. one in
> >> > 'Runtime' folder and one in 'Runtime2' folder, with different access
> >> > permissions on each folder?
> >> >
> >> > Cheers
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> What you want to be doing is doing a jump to the linked report, not
> >> >> the
> >> >> base
> >> >> report. Instead of the name of the report (which assumes it is in the
> >> >> current folder, i.e. Deployment folder in your case, do this instead:
> >> >>
> >> >> /Runtime/TheReportYouWant
> >> >>
> >> >> This will allow you to jump to the link report as you want. Note that
> >> >> it
> >> >> means you have to deploy to test the link, you won't be able to test
> >> >> from
> >> >> within Report Designer.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> >> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> >> >> >I have a problem using the jump-to feature in a linked report.
> >> >> > We are deploying all reports to a single folder 'Deployment' (not
> >> >> > visible
> >> >> > to
> >> >> > the browser role) and then creating links to these reports in
> >> >> > folders
> >> >> > that
> >> >> > are visible to the browser role. So from a maintenance point of
> >> >> > view,
> >> >> > there
> >> >> > is only ever one version of the report to update.
> >> >> > So we have report A that jumps-to report B and both of these are
> >> >> > deployed
> >> >> > to
> >> >> > the 'Deployment' folder which is only visible to developers. We
> >> >> > then
> >> >> > create
> >> >> > links, AL and BL, to both A and B, in another folder called
> >> >> > 'Runtime'
> >> >> > folder
> >> >> > which is visible to end users. End users are able to execute report
> >> >> > AL
> >> >> > fine
> >> >> > but when they try to jump-to BL, report manager looks for BL in the
> >> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore,
> >> >> > errors
> >> >> > are
> >> >> > thrown because users don't have access to the 'Deployment Folder'.
> >> >> > How do we force the jump to action on linked reports to remain in
> >> >> > the
> >> >> > current folder?
> >> >> > Obviously this seems to work fine if we are not using linked
> >> >> > reports.
> >> >> > Hope this makes sense and any advice would be appreciated.
> >> >> > TIA
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||One thing you can do is use the global variable User!UserID. Then query a
table that holds the report configuration options in a table. Query
parameters do not have to map to report parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:620FB6AB-309D-4B45-85F7-296867EBD074@.microsoft.com...
> Both really, but primarily users will access the reports directly through
> report manager.
> "Bruce L-C [MVP]" wrote:
>> Is the user going to this or are you integrating into your own app. It
>> sounds like here you are integrating it into your app.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> news:8B07B2C4-34CA-461B-929F-6DBEB4B44AAC@.microsoft.com...
>> >I guess we can but we need to be able to specify different parameters
>> >for
>> > each report depending on who is viewing it.
>> >
>> > Does that make sense or do you think we are going about things in the
>> > wrong
>> > way?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Do you realize that instead of all these links that you can set the
>> >> report
>> >> permissions so that based on the role they only see the reports they
>> >> have
>> >> rights to view?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> >> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
>> >> > Thanks Bruce.
>> >> >
>> >> > But to further complicate matters...
>> >> >
>> >> > If we only want to deploy one version of the main report, how would
>> >> > this
>> >> > work if we wanted to have multiple links to the reports, i.e. one in
>> >> > 'Runtime' folder and one in 'Runtime2' folder, with different access
>> >> > permissions on each folder?
>> >> >
>> >> > Cheers
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> What you want to be doing is doing a jump to the linked report, not
>> >> >> the
>> >> >> base
>> >> >> report. Instead of the name of the report (which assumes it is in
>> >> >> the
>> >> >> current folder, i.e. Deployment folder in your case, do this
>> >> >> instead:
>> >> >>
>> >> >> /Runtime/TheReportYouWant
>> >> >>
>> >> >> This will allow you to jump to the link report as you want. Note
>> >> >> that
>> >> >> it
>> >> >> means you have to deploy to test the link, you won't be able to
>> >> >> test
>> >> >> from
>> >> >> within Report Designer.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> >> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>> >> >> >I have a problem using the jump-to feature in a linked report.
>> >> >> > We are deploying all reports to a single folder 'Deployment' (not
>> >> >> > visible
>> >> >> > to
>> >> >> > the browser role) and then creating links to these reports in
>> >> >> > folders
>> >> >> > that
>> >> >> > are visible to the browser role. So from a maintenance point of
>> >> >> > view,
>> >> >> > there
>> >> >> > is only ever one version of the report to update.
>> >> >> > So we have report A that jumps-to report B and both of these are
>> >> >> > deployed
>> >> >> > to
>> >> >> > the 'Deployment' folder which is only visible to developers. We
>> >> >> > then
>> >> >> > create
>> >> >> > links, AL and BL, to both A and B, in another folder called
>> >> >> > 'Runtime'
>> >> >> > folder
>> >> >> > which is visible to end users. End users are able to execute
>> >> >> > report
>> >> >> > AL
>> >> >> > fine
>> >> >> > but when they try to jump-to BL, report manager looks for BL in
>> >> >> > the
>> >> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore,
>> >> >> > errors
>> >> >> > are
>> >> >> > thrown because users don't have access to the 'Deployment
>> >> >> > Folder'.
>> >> >> > How do we force the jump to action on linked reports to remain in
>> >> >> > the
>> >> >> > current folder?
>> >> >> > Obviously this seems to work fine if we are not using linked
>> >> >> > reports.
>> >> >> > Hope this makes sense and any advice would be appreciated.
>> >> >> > TIA
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||I have an identical Management requirement (meaning the higher levels of
management have decided to do this, and told me to figure out how).
Like trmpalmer, I published all me report to a "Production Reports" folder,
hidden from browsers. Like trmpalmer, I have been instructed to create
various folders based on management dictated groups, which will also be
duplicated in ADS to give specific groups access to specific reports
designated for that group/department.
Also like trmpalmer, some of my reports use Jump-To to reach other reports.
I tried relative pathing, i.e. " ./Report_2_Name " but that didn't help.
Linked Reports is ideal for me becasue of the administration issue of
maintaining multiple copies of the same report, versus maintaining a single
report and linking to that master copy. I am really hopng to find a solution
that will allow me to use Linked Reports and Jump-To-Report at the same time.
"trmpalmer" wrote:
> Thanks Bruce.
> But to further complicate matters...
> If we only want to deploy one version of the main report, how would this
> work if we wanted to have multiple links to the reports, i.e. one in
> 'Runtime' folder and one in 'Runtime2' folder, with different access
> permissions on each folder?
> Cheers
> "Bruce L-C [MVP]" wrote:
> > What you want to be doing is doing a jump to the linked report, not the base
> > report. Instead of the name of the report (which assumes it is in the
> > current folder, i.e. Deployment folder in your case, do this instead:
> >
> > /Runtime/TheReportYouWant
> >
> > This will allow you to jump to the link report as you want. Note that it
> > means you have to deploy to test the link, you won't be able to test from
> > within Report Designer.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> > news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> > >I have a problem using the jump-to feature in a linked report.
> > > We are deploying all reports to a single folder 'Deployment' (not visible
> > > to
> > > the browser role) and then creating links to these reports in folders that
> > > are visible to the browser role. So from a maintenance point of view,
> > > there
> > > is only ever one version of the report to update.
> > > So we have report A that jumps-to report B and both of these are deployed
> > > to
> > > the 'Deployment' folder which is only visible to developers. We then
> > > create
> > > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > > folder
> > > which is visible to end users. End users are able to execute report AL
> > > fine
> > > but when they try to jump-to BL, report manager looks for BL in the
> > > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > > thrown because users don't have access to the 'Deployment Folder'.
> > > How do we force the jump to action on linked reports to remain in the
> > > current folder?
> > > Obviously this seems to work fine if we are not using linked reports.
> > > Hope this makes sense and any advice would be appreciated.
> > > TIA
> > >
> >
> >
> >|||I just found a solution, and I wanted to share it here, and I will make a
seperate post.
To accomplish the goal of having a Linked Report perform a Jump-To with a
target report within the same directory as the initial linked report (the
target can also be a linked report), you need some means of "relative
pathing". This can be achieved with the following placed in the Jump to
Report field (click on the fx button or type directly):
=Globals!ReportFolder + "/" + "ReportName"
This will grab the folder the report being run is in. So if you have a
report in runtime2, it would show "/runtime2/ReportName", but would show
"/runtime/ReportName" when run from the runtime folder.
"trmpalmer" wrote:
> Thanks Bruce.
> But to further complicate matters...
> If we only want to deploy one version of the main report, how would this
> work if we wanted to have multiple links to the reports, i.e. one in
> 'Runtime' folder and one in 'Runtime2' folder, with different access
> permissions on each folder?
> Cheers
> "Bruce L-C [MVP]" wrote:
> > What you want to be doing is doing a jump to the linked report, not the base
> > report. Instead of the name of the report (which assumes it is in the
> > current folder, i.e. Deployment folder in your case, do this instead:
> >
> > /Runtime/TheReportYouWant
> >
> > This will allow you to jump to the link report as you want. Note that it
> > means you have to deploy to test the link, you won't be able to test from
> > within Report Designer.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> > news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> > >I have a problem using the jump-to feature in a linked report.
> > > We are deploying all reports to a single folder 'Deployment' (not visible
> > > to
> > > the browser role) and then creating links to these reports in folders that
> > > are visible to the browser role. So from a maintenance point of view,
> > > there
> > > is only ever one version of the report to update.
> > > So we have report A that jumps-to report B and both of these are deployed
> > > to
> > > the 'Deployment' folder which is only visible to developers. We then
> > > create
> > > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > > folder
> > > which is visible to end users. End users are able to execute report AL
> > > fine
> > > but when they try to jump-to BL, report manager looks for BL in the
> > > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > > thrown because users don't have access to the 'Deployment Folder'.
> > > How do we force the jump to action on linked reports to remain in the
> > > current folder?
> > > Obviously this seems to work fine if we are not using linked reports.
> > > Hope this makes sense and any advice would be appreciated.
> > > TIA
> > >
> >
> >
> >|||Thanks a million!!
Just what I was looking for :-)
"Rob 'Spike' Stevens" wrote:
> I just found a solution, and I wanted to share it here, and I will make a
> seperate post.
> To accomplish the goal of having a Linked Report perform a Jump-To with a
> target report within the same directory as the initial linked report (the
> target can also be a linked report), you need some means of "relative
> pathing". This can be achieved with the following placed in the Jump to
> Report field (click on the fx button or type directly):
> =Globals!ReportFolder + "/" + "ReportName"
> This will grab the folder the report being run is in. So if you have a
> report in runtime2, it would show "/runtime2/ReportName", but would show
> "/runtime/ReportName" when run from the runtime folder.
> "trmpalmer" wrote:
> > Thanks Bruce.
> >
> > But to further complicate matters...
> >
> > If we only want to deploy one version of the main report, how would this
> > work if we wanted to have multiple links to the reports, i.e. one in
> > 'Runtime' folder and one in 'Runtime2' folder, with different access
> > permissions on each folder?
> >
> > Cheers
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What you want to be doing is doing a jump to the linked report, not the base
> > > report. Instead of the name of the report (which assumes it is in the
> > > current folder, i.e. Deployment folder in your case, do this instead:
> > >
> > > /Runtime/TheReportYouWant
> > >
> > > This will allow you to jump to the link report as you want. Note that it
> > > means you have to deploy to test the link, you won't be able to test from
> > > within Report Designer.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> > > news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> > > >I have a problem using the jump-to feature in a linked report.
> > > > We are deploying all reports to a single folder 'Deployment' (not visible
> > > > to
> > > > the browser role) and then creating links to these reports in folders that
> > > > are visible to the browser role. So from a maintenance point of view,
> > > > there
> > > > is only ever one version of the report to update.
> > > > So we have report A that jumps-to report B and both of these are deployed
> > > > to
> > > > the 'Deployment' folder which is only visible to developers. We then
> > > > create
> > > > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > > > folder
> > > > which is visible to end users. End users are able to execute report AL
> > > > fine
> > > > but when they try to jump-to BL, report manager looks for BL in the
> > > > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > > > thrown because users don't have access to the 'Deployment Folder'.
> > > > How do we force the jump to action on linked reports to remain in the
> > > > current folder?
> > > > Obviously this seems to work fine if we are not using linked reports.
> > > > Hope this makes sense and any advice would be appreciated.
> > > > TIA
> > > >
> > >
> > >
> > >

Friday, March 9, 2012

Join two views using server aliases

Hello everybody,
I'm working for a hospital and for this job, I created two views using the
server aliases (linked servers).
Both these views are working correctly.
The design of the first view is:
SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
SPECIALTY_DESCRIPTION AS NAME
FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
ORDER BY SPECIALTY_DESCRIPTION
And for the second view is:
SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE AS
SPECIALTY_CODE
FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
ORDER BY NAME
The problem appaers when I try to join both these views with, for example,
this query:
select c.ID
from VW_CONSULTANTS c
left outer join VW_SPECIALTIES s
on c.SPECIALTY_CODE=s.CODE
I have these error messages:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
For me, the problem is because we try to join two views using server aliases
and the sql server is not successfull in creating the temporary table to put
the results of my select...
But I don't know what I have to do to fix the problem.
Can you help me please?
StF"StF" <StF@.discussions.microsoft.com> wrote in message
news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> Hello everybody,
> I'm working for a hospital and for this job, I created two views using the
> server aliases (linked servers).
> Both these views are working correctly.
> The design of the first view is:
> SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> SPECIALTY_DESCRIPTION AS NAME
> FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> ORDER BY SPECIALTY_DESCRIPTION
> And for the second view is:
> SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> AS
> SPECIALTY_CODE
> FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> ORDER BY NAME
> The problem appaers when I try to join both these views with, for example,
> this query:
> select c.ID
> from VW_CONSULTANTS c
> left outer join VW_SPECIALTIES s
> on c.SPECIALTY_CODE=s.CODE
>
I don't know exactly what's happening, but I know how to fix it.
Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
In SQL 2000 this would cause queries against the view to be sorted. But
this behavior was never guaranteed, and it doesn't always happen in 2005.
David|||Thank you very much.
In fact, we use SQL Server 2000 and that's why I used 'TOP 100 PERCENT'.
So, I removed this thing and the 'ORDER BY' and now my select is working.
Thank you again.
StF
"David Browne" wrote:

> "StF" <StF@.discussions.microsoft.com> wrote in message
> news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> I don't know exactly what's happening, but I know how to fix it.
> Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
> In SQL 2000 this would cause queries against the view to be sorted. But
> this behavior was never guaranteed, and it doesn't always happen in 2005.
> David
>
>

Join two views using server aliases

Hello everybody,
I'm working for a hospital and for this job, I created two views using the
server aliases (linked servers).
Both these views are working correctly.
The design of the first view is:
SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
SPECIALTY_DESCRIPTION AS NAME
FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
ORDER BY SPECIALTY_DESCRIPTION
And for the second view is:
SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE AS
SPECIALTY_CODE
FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
ORDER BY NAME
The problem appaers when I try to join both these views with, for example,
this query:
select c.ID
from VW_CONSULTANTS c
left outer join VW_SPECIALTIES s
on c.SPECIALTY_CODE=s.CODE
I have these error messages:
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
Server: Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables,
and subqueries, unless TOP is also specified.
For me, the problem is because we try to join two views using server aliases
and the sql server is not successfull in creating the temporary table to put
the results of my select...
But I don't know what I have to do to fix the problem.
Can you help me please?
StF"StF" <StF@.discussions.microsoft.com> wrote in message
news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> Hello everybody,
> I'm working for a hospital and for this job, I created two views using the
> server aliases (linked servers).
> Both these views are working correctly.
> The design of the first view is:
> SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> SPECIALTY_DESCRIPTION AS NAME
> FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> ORDER BY SPECIALTY_DESCRIPTION
> And for the second view is:
> SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> AS
> SPECIALTY_CODE
> FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> ORDER BY NAME
> The problem appaers when I try to join both these views with, for example,
> this query:
> select c.ID
> from VW_CONSULTANTS c
> left outer join VW_SPECIALTIES s
> on c.SPECIALTY_CODE=s.CODE
>
I don't know exactly what's happening, but I know how to fix it.
Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
In SQL 2000 this would cause queries against the view to be sorted. But
this behavior was never guaranteed, and it doesn't always happen in 2005.
David|||Thank you very much.
In fact, we use SQL Server 2000 and that's why I used 'TOP 100 PERCENT'.
So, I removed this thing and the 'ORDER BY' and now my select is working.
Thank you again.
StF
"David Browne" wrote:
> "StF" <StF@.discussions.microsoft.com> wrote in message
> news:760671EB-8EC8-4464-8972-D570E0FB2F86@.microsoft.com...
> > Hello everybody,
> >
> > I'm working for a hospital and for this job, I created two views using the
> > server aliases (linked servers).
> > Both these views are working correctly.
> >
> > The design of the first view is:
> > SELECT TOP 100 PERCENT UNID AS ID, SPECIALTY_CODE AS CODE,
> > SPECIALTY_DESCRIPTION AS NAME
> > FROM ddsqlGIMS.GIMS.dbo.TBL_SPECIALTY TBL_SPECIALTY_1
> > ORDER BY SPECIALTY_DESCRIPTION
> >
> > And for the second view is:
> > SELECT TOP 100 PERCENT CLINICIAN_ID AS ID, NAME, CLINICIAN_SPEC_CODE
> > AS
> > SPECIALTY_CODE
> > FROM ddsqlGIMS.GIMS.dbo.TBL_CLINICIANS TBL_CLINICIANS_1
> > WHERE (GMC_NO IS NOT NULL) AND (ACTIVE_FLAG = 'Y')
> > ORDER BY NAME
> >
> > The problem appaers when I try to join both these views with, for example,
> > this query:
> > select c.ID
> > from VW_CONSULTANTS c
> > left outer join VW_SPECIALTIES s
> > on c.SPECIALTY_CODE=s.CODE
> >
> I don't know exactly what's happening, but I know how to fix it.
> Don't use TOP 100 PERCENT in a view. It serves absolutely no purpose.
> In SQL 2000 this would cause queries against the view to be sorted. But
> this behavior was never guaranteed, and it doesn't always happen in 2005.
> David
>
>