I have perfmon data collected in a sql database and now i would like to
display the data captured in the form of report.. But I dont really know how
to go about it ? Is there a pack or set of reports that are out there that I
could use and just plug it to the perfmon collection database ?
Thankshi there
you may want to check out this link - scroll down the page - hopefully
something may help you.
http://sqlblog.com/blogs/andrew_kelly/
Jewel
"Hassan" wrote:
> I have perfmon data collected in a sql database and now i would like to
> display the data captured in the form of report.. But I dont really know how
> to go about it ? Is there a pack or set of reports that are out there that I
> could use and just plug it to the perfmon collection database ?
> Thanks
>
Showing posts with label report. Show all posts
Showing posts with label report. Show all posts
Wednesday, March 28, 2012
Pre-defined lists of values as report parameter input
Has anyone come up with a solution for the following scenario yet?
We have many reports which have input parameters that are lists of values.
As an example the user might want to report on 30 of 100 available product
codes, or 12 of 50,000 account numbers.
What might be an idea would be to store the lists of values that userâ's want
to report on, in a general purpose Lists table. One column would be a List
Name and the other a delimited list of values. This List table could then be
used as the input to a Drop Down box for the relevant report parameter.
Has anybody tried something similar, or does anyone have any other
suggestions?
(Weâ're aware that multi-select list boxes will be available in SSRS 2005,
but that would still be a bit tiresome for a user that needs to select a few
values from a long list).
Regards,
John MarshHi John,
I have done similar thing like that.
i have a lookup parameter report which is a webpage with listbox and the
value is populated by calling a param report. The param report contain 2
fileds and it like name- value pair. The report is called programttically and
the result is returned in xml format so that it can be bind to a dataset and
to tht listbox or dropdown box.
So this act as a lists of values as report parameter input..
Regards
Bava
"John Marsh" wrote:
> Has anyone come up with a solution for the following scenario yet?
> We have many reports which have input parameters that are lists of values.
> As an example the user might want to report on 30 of 100 available product
> codes, or 12 of 50,000 account numbers.
> What might be an idea would be to store the lists of values that userâ's want
> to report on, in a general purpose Lists table. One column would be a List
> Name and the other a delimited list of values. This List table could then be
> used as the input to a Drop Down box for the relevant report parameter.
> Has anybody tried something similar, or does anyone have any other
> suggestions?
> (Weâ're aware that multi-select list boxes will be available in SSRS 2005,
> but that would still be a bit tiresome for a user that needs to select a few
> values from a long list).
> Regards,
> John Marsh
>
We have many reports which have input parameters that are lists of values.
As an example the user might want to report on 30 of 100 available product
codes, or 12 of 50,000 account numbers.
What might be an idea would be to store the lists of values that userâ's want
to report on, in a general purpose Lists table. One column would be a List
Name and the other a delimited list of values. This List table could then be
used as the input to a Drop Down box for the relevant report parameter.
Has anybody tried something similar, or does anyone have any other
suggestions?
(Weâ're aware that multi-select list boxes will be available in SSRS 2005,
but that would still be a bit tiresome for a user that needs to select a few
values from a long list).
Regards,
John MarshHi John,
I have done similar thing like that.
i have a lookup parameter report which is a webpage with listbox and the
value is populated by calling a param report. The param report contain 2
fileds and it like name- value pair. The report is called programttically and
the result is returned in xml format so that it can be bind to a dataset and
to tht listbox or dropdown box.
So this act as a lists of values as report parameter input..
Regards
Bava
"John Marsh" wrote:
> Has anyone come up with a solution for the following scenario yet?
> We have many reports which have input parameters that are lists of values.
> As an example the user might want to report on 30 of 100 available product
> codes, or 12 of 50,000 account numbers.
> What might be an idea would be to store the lists of values that userâ's want
> to report on, in a general purpose Lists table. One column would be a List
> Name and the other a delimited list of values. This List table could then be
> used as the input to a Drop Down box for the relevant report parameter.
> Has anybody tried something similar, or does anyone have any other
> suggestions?
> (Weâ're aware that multi-select list boxes will be available in SSRS 2005,
> but that would still be a bit tiresome for a user that needs to select a few
> values from a long list).
> Regards,
> John Marsh
>
Wednesday, March 21, 2012
POSTing report parameters
We are having an issue POSTing report parameters to reporting services. One
of our report parameters can be very long (hundreds or even thousands of
characters) and is far to long to pass on a URL. Our solution has been to
POST the parameter, which does work, but not quite as we want it to. We only
need to provide a single parameter to the reprt as all the others have
default values.
The report generates and displays results as expected, however the parameter
selection and report navigation section is not visible on the report that is
rendered.
If we POST the parameters, is it possible to get the parameter selection and
report navigation sections to show? and, if so, how? The report is being
called from a custom app which passes the parameter to the report.
This is how we call our report:
http://sidev/ReportServer?/tbi/development/Report200_GIS
The POST data is:
XMLFarmIDs=<TbI>....</TbI>I've done this in the past by putting a hidden element in the form I'm
posting equivalent to the HTML Viewer command.
In my post form, I'll put this:
<input type=hidden name="rc:Parameters" value="false">
Andy Potter
of our report parameters can be very long (hundreds or even thousands of
characters) and is far to long to pass on a URL. Our solution has been to
POST the parameter, which does work, but not quite as we want it to. We only
need to provide a single parameter to the reprt as all the others have
default values.
The report generates and displays results as expected, however the parameter
selection and report navigation section is not visible on the report that is
rendered.
If we POST the parameters, is it possible to get the parameter selection and
report navigation sections to show? and, if so, how? The report is being
called from a custom app which passes the parameter to the report.
This is how we call our report:
http://sidev/ReportServer?/tbi/development/Report200_GIS
The POST data is:
XMLFarmIDs=<TbI>....</TbI>I've done this in the past by putting a hidden element in the form I'm
posting equivalent to the HTML Viewer command.
In my post form, I'll put this:
<input type=hidden name="rc:Parameters" value="false">
Andy Potter
PostBack while selecting a parameter
Hi,
I'm working on a report having 2 date parameters(which uses calendar control) and a dropdownlist. But on selecting each of these parameters, the page refreshes. For eg On selecting a date from the calendar control results in a postback. The same is the case with the dropdownlist. Could you please help to resolve this issue? We need the postback to happen only on clicking the 'View Report' button.
Also, is there any way to customize the 'View Report' button. It always appears in the right hand side. Can we set the position of this button so that it appears just below the paging button?
Thanks in advance,
Sonu.
1. No, it's not possible to avoid postbacks when you enter parameters one by one.
2. There is no way to customize the position of the button but you can change the style of the button in your report manager by using the ReportingServices.css file in the following folder (probably):
C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting Services\ReportManager\Styles
Please refer to more details in the following link:
http://msdn2.microsoft.com/en-us/library/ms345247.aspx
Shyam
sqlTuesday, March 20, 2012
Post method in ASP stops working
I am having an issue on a windows 2003 box where I run a report to a new
window, and I do a request.form to get all my parameters. The first time I
run a report to a new window I am prompted for the windows user name and
password. Once I enter in the login info my report runs fine. Then when I try
to run another report to a new window the request.form doesn't work to pull
things in from my form. Everything is blank, so I get error messages. Another
report will not run until I shutdown IE restart IE log back into my website
and run the report.
My redirect string is
http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
"&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
Can anyone tell me why the post method or Request.form will only work once
and then I have to restart IE. Again I am opening the report in a new window.
--
Thanks,
CraigI fixed this by going into IIS and going into the properties on the Reports
and ReportServer Virtual directories and under Directory security enabling
anonymous access.
"Craig" wrote:
> I am having an issue on a windows 2003 box where I run a report to a new
> window, and I do a request.form to get all my parameters. The first time I
> run a report to a new window I am prompted for the windows user name and
> password. Once I enter in the login info my report runs fine. Then when I try
> to run another report to a new window the request.form doesn't work to pull
> things in from my form. Everything is blank, so I get error messages. Another
> report will not run until I shutdown IE restart IE log back into my website
> and run the report.
> My redirect string is
> http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
> "&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
> Can anyone tell me why the post method or Request.form will only work once
> and then I have to restart IE. Again I am opening the report in a new window.
> --
> Thanks,
> Craig
window, and I do a request.form to get all my parameters. The first time I
run a report to a new window I am prompted for the windows user name and
password. Once I enter in the login info my report runs fine. Then when I try
to run another report to a new window the request.form doesn't work to pull
things in from my form. Everything is blank, so I get error messages. Another
report will not run until I shutdown IE restart IE log back into my website
and run the report.
My redirect string is
http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
"&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
Can anyone tell me why the post method or Request.form will only work once
and then I have to restart IE. Again I am opening the report in a new window.
--
Thanks,
CraigI fixed this by going into IIS and going into the properties on the Reports
and ReportServer Virtual directories and under Directory security enabling
anonymous access.
"Craig" wrote:
> I am having an issue on a windows 2003 box where I run a report to a new
> window, and I do a request.form to get all my parameters. The first time I
> run a report to a new window I am prompted for the windows user name and
> password. Once I enter in the login info my report runs fine. Then when I try
> to run another report to a new window the request.form doesn't work to pull
> things in from my form. Everything is blank, so I get error messages. Another
> report will not run until I shutdown IE restart IE log back into my website
> and run the report.
> My redirect string is
> http://Session('sServer')/ReportServer?/Session("sProjectCode")/sReport &
> "&rs:Command=render&rs:Format=HTML4.0&rc:Paramaters=False"
> Can anyone tell me why the post method or Request.form will only work once
> and then I have to restart IE. Again I am opening the report in a new window.
> --
> Thanks,
> Craig
Possibly similar issue with launching Report Builder from client PC's
Hi,
I got a working installation of reporting services 2005 up and running, Im able to create models and launch the Report Builder on the machine on which the server is installed, however if I try and run it from another machine on the network I get an error and Im able to view the following exception.
Following errors were detected during this operation.
* [04 Jul 2005 10:39:34 +01:00] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Failed while downloading http://testserver/ReportServer/ReportBuilder/ReportBuilder.application
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifest(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ManifestType manifestType, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirect(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifest(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
Inner Exception
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
Anyone able to help ?The error means that the client does not have the Whidbey CLR installed. You will need to install the Whidbey CLR on each client machine where you want to use report builder.
-Lukasz|||Hi,
I was facing similar problem while accessing Report Manager installed on different system on the network, I was able to get Report Manager menu but can not see contents on Report Server. It was displayed Blank without any exception.
Report Server is installed on http://testsrv/Reports$sql2005.
I changed configuration of Reporting Service on Server , Report Server Configuration Manager > Windows Service Identity> Service account from "Local System" to "Local Services".
I can access Report Manager with contents.
Meanwhile I noticed that Report Builder is not launching on the system which is a different system from the Report Server. As per my understanding, Report Builder is a "End user tool" and will be launched without any upgradation of the system (Different system from server). I am getting prompt for "Open/Save document".
Where as Report Builder is working fine on Server itself.
While opening document, I can see XML contents to access Report Builder.
I am pasting part of that XML file in orange colour,
<?xml version="1.0" encoding="utf-8" ?>
- <asmv1:assembly xsi:schemaLocation="urn:schemas-microsoft-com:asm.v1 assembly.adaptive.xsd" manifestVersion="1.0" xmlns:dsig="http://www.w3.org/2000/09/xmldsig#" xmlns="urn:schemas-microsoft-com:asm.v2" xmlns:asmv1="urn:schemas-microsoft-com:asm.v1" xmlns:asmv2="urn:schemas-microsoft-com:asm.v2" xmlns:xrml="urn:mpeg:mpeg21:2003:01-REL-R-NS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<assemblyIdentity name="ReportBuilder.app" version="9.0.1116.8" publicKeyToken="49ef3e7f44a9c98c" language="neutral" processorArchitecture="msil" xmlns="urn:schemas-microsoft-com:asm.v1" />
<description asmv2:publisher="Microsoft" asmv2:product="Report Builder" xmlns="urn:schemas-microsoft-com:asm.v1" />
<deployment install="false" trustURLParameters="true" />
- <dependency>
- <dependentAssembly dependencyType="install" allowDelayedBinding="true" codebase="ReportBuilder.exe.manifest" size="7148">
<assemblyIdentity name="ReportBuilder.exe" version="9.0.1116.8" publicKeyToken="49ef3e7f44a9c98c" language="neutral" processorArchitecture="msil" type="win32" />
- <hash>
- <dsig:Transforms>
<dsig:Transform Algorithm="urn:schemas-microsoft-com:HashTransforms.Identity" />
</dsig:Transforms>
<dsig:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" />
<dsig:DigestValue>aAi7VtRySyyvYXiAs4FuFXSiqLA=</dsig:DigestValue>
</hash>
</dependentAssembly>
Anyone knows how to fix this, It is required to upgrade my system, what are the upgrades require on the system to access Report Builder?
The reason is that the Report Builder leverages the ClickOnce technology that is new in the .Net Framework 2.0.
Regarding your other issue - the Report Manager shows the top menu with a blank contents when your login does not have permissions on the root of the report server. You will need a role that has Read Properties (Browser roles has this) assigned to your login on the root of the report server namespace. Changing the account from Local System to Local Service should not affect this.
-Lukasz|||Thanks Lukasz, I have installed .NET Framework 2.0 on Client machine and able to launch Report Builder successfully.
I created a new user with sufficient permission and access to HOME directory on Report Server and able to access Reports/Data Sources/Models using Client Machine.
Thanks again!|||
I have what may be a similar problem. I try to click on the "Report Builder" button from a client machine and nothing happens, however I have tried things mentioned in the suggestions above and it did not seem to help. From a client PC, I logged in as myself and am not able to run "Report Builder", but when I login as a local admin I am able to run "Report Builder".
Our server has SQL Server 2005 Enterprise Edition and Visual Studio 2005 installed. I am able to run "Report Builder" from my PC where I had Visual Studio 2005 installed, but not SQL Server 2005 installed. I have local admin priviledges to my PC.
From a client PC, I logged in as myself and am not able to run "Report Builder". When I click on it nothing happens. I gave my Windows login "Content Manger" permissions under the SSRS Home -> Properties tab, and I also added my login and gave myself "System Administrator" and "System User" Roles under the SSRS ->Site Settings->Configure site-wide security option. This seems to be configured correctly, as "Report Builder" launches when I am on my PC. However, it does not launch on the client PC when logged in under my login.
Further, when I login as a local admin on the client PC, I am able to run "Report Builder", so I think the .net framework 2.0 is correctly installed and working properly. However, it is our policy not to give the user of the client PC local admin rights. My windows login does not have local admin rights to this particular PC.
Q1. Is local admin rights required on the client PC where "Report Builder" is ran? If so, is it only required for the first time it is loaded? It seems like it should not be required, however this would explain the symptoms detailed above.
Q2. Is there an error log that may help debug what this problem is? It is strange to me, that nothing happens, but no error is displayed on the http://.../reportserver/Pages page
Q3. When logged in as local admin, report builder installed to something like:
C:\Documents and Settings\Administrator.8ZR5551\Local Settings\Apps\2.0\VVJ0004J.B0N\H3KE16BP.Y07\repo..lder_89845dcd8080cc91_0009.0000_none_7ecce75e919dcccd
I am not sure if I can copy this install to another directory which the client has permission to run from. Is this a valid thing to do? I am guessing probably not since I tried this and it did not seem to work.I tried this and it did not seem to work..
Q4. I saw this posting talking about the need to run under PartialTrust mode, instead of FullTrust mode. So I reconfigured the RSWebApplication.config file to do that, and it still does not work.:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=280387&SiteID=1 which refers to the following link.
http://msdn2.microsoft.com/en-us/library/ms345245.aspx
I changed the C:\Program Files\MSSQL2005\MSSQL.3\Reporting Services\ReportManager\ RSWebApplication.config
From
<Configuration><UI> …
<ReportBuilderTrustLevel> FullTrust </ReportBuilderTrustLevel>
</UI>…</Configuration>
To
<Configuration><UI> …
<ReportBuilderTrustLevel>PartialTrust</ReportBuilderTrustLevel>
</UI>…</Configuration>
Stopped and restarted report server through reporting services configuration (not sure if I needed to do this) The problem still occurred, however.
|||
Well scratch that idea on local admin rights. Our IT department gave login admin to my windows login (My computer->Manage->System Tools: Local Users and Groups->Administrators) and it still did not work. When I click on "Report Builder" it still does not do anything. I also tried to use it directly at the following URL's and it still did not work, same result of not displaying anything:
http://.../reportserver/ReportBuilder/ReportBuilder.application
http://.../reportserver/ReportBuilder/ReportBuilderLocalIntranet.application
Q5. Is there any Internet Explorer settings that would cause this behavior? I tried disabling popup blocker, but this did not seem to help.
Q6. In the above posting, giving permission on the server HOME directory was required. What directory does this refer to? Does this mean each user of Report Builder needs Windows Directory level security to some directories like C:\Program Files\MSSQL2005\MSSQL.3\Reporting Services\ReportManager\ or others? This does not seem to make sense, but I was unsure which HOME directory was mentioned in the previous posting by Nilesh Trivedi.
.
|||Well this is almost a year old but I am facing the exact same problem as Chad Buher. Clicking on the Report Builder button does nothing, no error, nothing. Can this be caused by some network policy setting?
Monday, March 12, 2012
Possibly similar issue with launching Report Builder from client PC's
Hi,
I got a working installation of reporting services 2005 up and running, Im able to create models and launch the Report Builder on the machine on which the server is installed, however if I try and run it from another machine on the network I get an error and Im able to view the following exception.
Following errors were detected during this operation.
* [04 Jul 2005 10:39:34 +01:00] System.Deployment.Application.DeploymentDownloadException (Unknown subtype)
- Failed while downloading http://testserver/ReportServer/ReportBuilder/ReportBuilder.application
- Source: System.Deployment
- Stack trace:
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
at System.Deployment.Application.SystemNetDownloader.DownloadAllFiles()
at System.Deployment.Application.FileDownloader.Download(SubscriptionState subState)
at System.Deployment.Application.DownloadManager.DownloadManifest(Uri& sourceUri, String targetPath, IDownloadNotification notification, DownloadOptions options, ManifestType manifestType, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifestDirect(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, IDownloadNotification notification, DownloadOptions options, ServerInformation& serverInformation)
at System.Deployment.Application.DownloadManager.DownloadDeploymentManifest(SubscriptionStore subStore, Uri& sourceUri, TempFile& tempFile, IDownloadNotification notification, DownloadOptions options)
at System.Deployment.Application.ApplicationActivator.PerformDeploymentActivation(Uri activationUri, Boolean isShortcut)
at System.Deployment.Application.ApplicationActivator.ActivateDeploymentWorker(Object state)
Inner Exception
System.Net.WebException
- The remote server returned an error: (401) Unauthorized.
- Source: System
- Stack trace:
at System.Net.HttpWebRequest.GetResponse()
at System.Deployment.Application.SystemNetDownloader.DownloadSingleFile(DownloadQueueItem next)
Anyone able to help ?The error means that the client does not have the Whidbey CLR installed. You will need to install the Whidbey CLR on each client machine where you want to use report builder.
-Lukasz|||Hi,
I was facing similar problem while accessing Report Manager installed on different system on the network, I was able to get Report Manager menu but can not see contents on Report Server. It was displayed Blank without any exception.
Report Server is installed on http://testsrv/Reports$sql2005.
I changed configuration of Reporting Service on Server , Report Server Configuration Manager > Windows Service Identity> Service account from "Local System" to "Local Services".
I can access Report Manager with contents.
Meanwhile I noticed that Report Builder is not launching on the system which is a different system from the Report Server. As per my understanding, Report Builder is a "End user tool" and will be launched without any upgradation of the system (Different system from server). I am getting prompt for "Open/Save document".
Where as Report Builder is working fine on Server itself.
While opening document, I can see XML contents to access Report Builder.
I am pasting part of that XML file in orange colour,
<?xml version="1.0" encoding="utf-8" ?>
- <asmv1:assembly xsi:schemaLocation="urn:schemas-microsoft-com:asm.v1 assembly.adaptive.xsd" manifestVersion="1.0" xmlns:dsig="http://www.w3.org/2000/09/xmldsig#" xmlns="urn:schemas-microsoft-com:asm.v2" xmlns:asmv1="urn:schemas-microsoft-com:asm.v1" xmlns:asmv2="urn:schemas-microsoft-com:asm.v2" xmlns:xrml="urn:mpeg:mpeg21:2003:01-REL-R-NS" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<assemblyIdentity name="ReportBuilder.app" version="9.0.1116.8" publicKeyToken="49ef3e7f44a9c98c" language="neutral" processorArchitecture="msil" xmlns="urn:schemas-microsoft-com:asm.v1" />
<description asmv2:publisher="Microsoft" asmv2:product="Report Builder" xmlns="urn:schemas-microsoft-com:asm.v1" />
<deployment install="false" trustURLParameters="true" />
- <dependency>
- <dependentAssembly dependencyType="install" allowDelayedBinding="true" codebase="ReportBuilder.exe.manifest" size="7148">
<assemblyIdentity name="ReportBuilder.exe" version="9.0.1116.8" publicKeyToken="49ef3e7f44a9c98c" language="neutral" processorArchitecture="msil" type="win32" />
- <hash>
- <dsig:Transforms>
<dsig:Transform Algorithm="urn:schemas-microsoft-com:HashTransforms.Identity" />
</dsig:Transforms>
<dsig:DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" />
<dsig:DigestValue>aAi7VtRySyyvYXiAs4FuFXSiqLA=</dsig:DigestValue>
</hash>
</dependentAssembly>
Anyone knows how to fix this, It is required to upgrade my system, what are the upgrades require on the system to access Report Builder?
The reason is that the Report Builder leverages the ClickOnce technology that is new in the .Net Framework 2.0.
Regarding your other issue - the Report Manager shows the top menu with a blank contents when your login does not have permissions on the root of the report server. You will need a role that has Read Properties (Browser roles has this) assigned to your login on the root of the report server namespace. Changing the account from Local System to Local Service should not affect this.
-Lukasz|||Thanks Lukasz, I have installed .NET Framework 2.0 on Client machine and able to launch Report Builder successfully.
I created a new user with sufficient permission and access to HOME directory on Report Server and able to access Reports/Data Sources/Models using Client Machine.
Thanks again!|||
I have what may be a similar problem. I try to click on the "Report Builder" button from a client machine and nothing happens, however I have tried things mentioned in the suggestions above and it did not seem to help. From a client PC, I logged in as myself and am not able to run "Report Builder", but when I login as a local admin I am able to run "Report Builder".
Our server has SQL Server 2005 Enterprise Edition and Visual Studio 2005 installed. I am able to run "Report Builder" from my PC where I had Visual Studio 2005 installed, but not SQL Server 2005 installed. I have local admin priviledges to my PC.
From a client PC, I logged in as myself and am not able to run "Report Builder". When I click on it nothing happens. I gave my Windows login "Content Manger" permissions under the SSRS Home -> Properties tab, and I also added my login and gave myself "System Administrator" and "System User" Roles under the SSRS ->Site Settings->Configure site-wide security option. This seems to be configured correctly, as "Report Builder" launches when I am on my PC. However, it does not launch on the client PC when logged in under my login.
Further, when I login as a local admin on the client PC, I am able to run "Report Builder", so I think the .net framework 2.0 is correctly installed and working properly. However, it is our policy not to give the user of the client PC local admin rights. My windows login does not have local admin rights to this particular PC.
Q1. Is local admin rights required on the client PC where "Report Builder" is ran? If so, is it only required for the first time it is loaded? It seems like it should not be required, however this would explain the symptoms detailed above.
Q2. Is there an error log that may help debug what this problem is? It is strange to me, that nothing happens, but no error is displayed on the http://.../reportserver/Pages page
Q3. When logged in as local admin, report builder installed to something like:
C:\Documents and Settings\Administrator.8ZR5551\Local Settings\Apps\2.0\VVJ0004J.B0N\H3KE16BP.Y07\repo..lder_89845dcd8080cc91_0009.0000_none_7ecce75e919dcccd
I am not sure if I can copy this install to another directory which the client has permission to run from. Is this a valid thing to do? I am guessing probably not since I tried this and it did not seem to work.I tried this and it did not seem to work..
Q4. I saw this posting talking about the need to run under PartialTrust mode, instead of FullTrust mode. So I reconfigured the RSWebApplication.config file to do that, and it still does not work.:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=280387&SiteID=1 which refers to the following link.
http://msdn2.microsoft.com/en-us/library/ms345245.aspx
I changed the C:\Program Files\MSSQL2005\MSSQL.3\Reporting Services\ReportManager\ RSWebApplication.config
From
<Configuration><UI> …
<ReportBuilderTrustLevel> FullTrust </ReportBuilderTrustLevel>
</UI>…</Configuration>
To
<Configuration><UI> …
<ReportBuilderTrustLevel>PartialTrust</ReportBuilderTrustLevel>
</UI>…</Configuration>
Stopped and restarted report server through reporting services configuration (not sure if I needed to do this) The problem still occurred, however.
|||
Well scratch that idea on local admin rights. Our IT department gave login admin to my windows login (My computer->Manage->System Tools: Local Users and Groups->Administrators) and it still did not work. When I click on "Report Builder" it still does not do anything. I also tried to use it directly at the following URL's and it still did not work, same result of not displaying anything:
http://.../reportserver/ReportBuilder/ReportBuilder.application
http://.../reportserver/ReportBuilder/ReportBuilderLocalIntranet.application
Q5. Is there any Internet Explorer settings that would cause this behavior? I tried disabling popup blocker, but this did not seem to help.
Q6. In the above posting, giving permission on the server HOME directory was required. What directory does this refer to? Does this mean each user of Report Builder needs Windows Directory level security to some directories like C:\Program Files\MSSQL2005\MSSQL.3\Reporting Services\ReportManager\ or others? This does not seem to make sense, but I was unsure which HOME directory was mentioned in the previous posting by Nilesh Trivedi.
.
|||Well this is almost a year old but I am facing the exact same problem as Chad Buher. Clicking on the Report Builder button does nothing, no error, nothing. Can this be caused by some network policy setting?
Possible to use Calendar control with date parameter?
SQL 2005 on XP Pro.
I want to call up a Calendar Control when a user enters a report parameter which is a date. Is this possible? I am using the out-of-the-box SQL Srvr Reporting Services, not a custom implementation.
I want to call up a Calendar Control when a user enters a report parameter which is a date. Is this possible? I am using the out-of-the-box SQL Srvr Reporting Services, not a custom implementation.
Thanks in advanceThis is supported. A calendar control will be displayed for date parameters that do not have a valid values list.|||
Calender control comes automatically once you define a report parameter as datetime.
Amarnath
possible to spoof report parameters?
Sorry if this has been asked before, but the searches I tried didn't turn anything up.
My question is, is it possible for a user to spoof report parameters in order to get a report to run for values that would normally be unavailable to that user?
For example, let's say we have a report that has a drop-down parameter containing a list of account numbers that the user is allowed to see data for. (The query that populates the dropdown itself uses the reporting services login to appropriately filter the list of accounts.) Would it be theoretically possible to write a custom http client that injected its own value for the account # parameter and have the report run for an account that was not intended for that user? Or is this somehow prevented in some way (perhaps the viewstate?).
Note, I am not the potential hacker, I would like to know if I need to add some other security mechanism to all of our reports.
By the way, we are using RS 2005.
ThanksUse stored procedures to prevent SQL injection attacks.|||I'm sorry, I shouldn't have used the word "inject" in my original question. I am not speaking about a traditional SQL injection attack, I'm only talking about spoofing an http client to post arbirtrary http-parameter values in runing the report. Thanks for the reply tho.
possible to spoof report parameters?
Sorry if this has been asked before, but the searches I tried didn't turn anything up.
My question is, is it possible for a user to spoof report parameters in order to get a report to run for values that would normally be unavailable to that user?
For example, let's say we have a report that has a drop-down parameter containing a list of account numbers that the user is allowed to see data for. (The query that populates the dropdown itself uses the reporting services login to appropriately filter the list of accounts.) Would it be theoretically possible to write a custom http client that injected its own value for the account # parameter and have the report run for an account that was not intended for that user? Or is this somehow prevented in some way (perhaps the viewstate?).
Note, I am not the potential hacker, I would like to know if I need to add some other security mechanism to all of our reports.
By the way, we are using RS 2005.
ThanksUse stored procedures to prevent SQL injection attacks.|||I'm sorry, I shouldn't have used the word "inject" in my original question. I am not speaking about a traditional SQL injection attack, I'm only talking about spoofing an http client to post arbirtrary http-parameter values in runing the report. Thanks for the reply tho.
Possible to set http://localhost/Reports default start report?
SQL 2005
Is it possible to set a default start report? I have a report which contains hyperlinks to all the other reports, I'd like to just load this one by default when a user hits: http://machine/Reports
Is it possible to set a default start report? I have a report which contains hyperlinks to all the other reports, I'd like to just load this one by default when a user hits: http://machine/Reports
Perhaps I'm barking up the wrong tree and need to set this in IIS? I want users to still be able to make their way back to the /Reports folder lists also.
- Thanks in advanceI would rather set a new virtual directory with pointing / redirecting to the new folder than messing with the old settings. But with that there would be not point in returning to the main folder structure, unless you put it as a hyperlink inyour report.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
Friday, March 9, 2012
Possible to reuse the same SQL dataset in many reports?
Is it possible to create a SQL query and hook this up to more than one
report? I have several reports that are supposed to be standardized to be
deployed to different clients. We want to brand the reports with each
client's name. We can get the name from the SQL Server database that the
reports are using.
Is it possible to just create this dataset "somewhere" in my report project,
and have all reports get the data returned from this dataset? Or do I have
to add this dataset to all my reports?
Sincerely,
Kaisa M. Lindahlyou can do this by using a stored procedure
"Kaisa M. Lindahl" wrote:
> Is it possible to create a SQL query and hook this up to more than one
> report? I have several reports that are supposed to be standardized to be
> deployed to different clients. We want to brand the reports with each
> client's name. We can get the name from the SQL Server database that the
> reports are using.
> Is it possible to just create this dataset "somewhere" in my report project,
> and have all reports get the data returned from this dataset? Or do I have
> to add this dataset to all my reports?
> Sincerely,
> Kaisa M. Lindahl
>
>|||I found that the best way is to put all or most of your query into a stored
procedure or in-line table function on the server so it is easily accessible
from other reports.
"Kaisa M. Lindahl" wrote:
> Is it possible to create a SQL query and hook this up to more than one
> report? I have several reports that are supposed to be standardized to be
> deployed to different clients. We want to brand the reports with each
> client's name. We can get the name from the SQL Server database that the
> reports are using.
> Is it possible to just create this dataset "somewhere" in my report project,
> and have all reports get the data returned from this dataset? Or do I have
> to add this dataset to all my reports?
> Sincerely,
> Kaisa M. Lindahl
>
>|||DavidK or Antoon or someone else:
Can you please explain how to do it? I've looked at articles on the internet
and the "Hitchhiker's" book, and I can't figure out how to use Stored
Procedures properly.
I've managed to create a stored procedure. Unfortunately, this has to be a
sp with dynamic sql. It takes 2 parameters. When I'm testing it, it works
OK, but it doesn't return any fields. So none of the results show up in the
report at all.
Any clues, please? A short explanation or links to resources on the web is
OK. :)
Sincerely,
Kaisa
"DavidK" <DavidK@.discussions.microsoft.com> wrote in message
news:649C3B02-DA26-42EA-8F4E-FD3EB1D15361@.microsoft.com...
>I found that the best way is to put all or most of your query into a stored
> procedure or in-line table function on the server so it is easily
> accessible
> from other reports.
> "Kaisa M. Lindahl" wrote:
>> Is it possible to create a SQL query and hook this up to more than one
>> report? I have several reports that are supposed to be standardized to be
>> deployed to different clients. We want to brand the reports with each
>> client's name. We can get the name from the SQL Server database that the
>> reports are using.
>> Is it possible to just create this dataset "somewhere" in my report
>> project,
>> and have all reports get the data returned from this dataset? Or do I
>> have
>> to add this dataset to all my reports?
>> Sincerely,
>> Kaisa M. Lindahl
>>|||Although u create a Stored Procedure , u need to have a dataset in all
your reports which point to the stored procedure.
In short for every report there shall be a dataset|||"Vishu" <vishwasaj@.gmail.com> wrote in message
news:1121329763.408325.84730@.g43g2000cwa.googlegroups.com...
> Although u create a Stored Procedure , u need to have a dataset in all
> your reports which point to the stored procedure.
> In short for every report there shall be a dataset
Thanks. :)
I recreated the Stored Procedure without parameters. The SP needs to be
added manually to each database anyway, so I will change it to fit with the
database in the stored procedure, not have each report send the parameters.
Adding just one line of code as a dataset + adding a text box to the report
was a lot easier than expected.
In the end I wrote a blog post about it:
http://blogs.spipp.net/kaisa/archive/2005/07/12/1154.aspx
Kaisa M. Lindahl
report? I have several reports that are supposed to be standardized to be
deployed to different clients. We want to brand the reports with each
client's name. We can get the name from the SQL Server database that the
reports are using.
Is it possible to just create this dataset "somewhere" in my report project,
and have all reports get the data returned from this dataset? Or do I have
to add this dataset to all my reports?
Sincerely,
Kaisa M. Lindahlyou can do this by using a stored procedure
"Kaisa M. Lindahl" wrote:
> Is it possible to create a SQL query and hook this up to more than one
> report? I have several reports that are supposed to be standardized to be
> deployed to different clients. We want to brand the reports with each
> client's name. We can get the name from the SQL Server database that the
> reports are using.
> Is it possible to just create this dataset "somewhere" in my report project,
> and have all reports get the data returned from this dataset? Or do I have
> to add this dataset to all my reports?
> Sincerely,
> Kaisa M. Lindahl
>
>|||I found that the best way is to put all or most of your query into a stored
procedure or in-line table function on the server so it is easily accessible
from other reports.
"Kaisa M. Lindahl" wrote:
> Is it possible to create a SQL query and hook this up to more than one
> report? I have several reports that are supposed to be standardized to be
> deployed to different clients. We want to brand the reports with each
> client's name. We can get the name from the SQL Server database that the
> reports are using.
> Is it possible to just create this dataset "somewhere" in my report project,
> and have all reports get the data returned from this dataset? Or do I have
> to add this dataset to all my reports?
> Sincerely,
> Kaisa M. Lindahl
>
>|||DavidK or Antoon or someone else:
Can you please explain how to do it? I've looked at articles on the internet
and the "Hitchhiker's" book, and I can't figure out how to use Stored
Procedures properly.
I've managed to create a stored procedure. Unfortunately, this has to be a
sp with dynamic sql. It takes 2 parameters. When I'm testing it, it works
OK, but it doesn't return any fields. So none of the results show up in the
report at all.
Any clues, please? A short explanation or links to resources on the web is
OK. :)
Sincerely,
Kaisa
"DavidK" <DavidK@.discussions.microsoft.com> wrote in message
news:649C3B02-DA26-42EA-8F4E-FD3EB1D15361@.microsoft.com...
>I found that the best way is to put all or most of your query into a stored
> procedure or in-line table function on the server so it is easily
> accessible
> from other reports.
> "Kaisa M. Lindahl" wrote:
>> Is it possible to create a SQL query and hook this up to more than one
>> report? I have several reports that are supposed to be standardized to be
>> deployed to different clients. We want to brand the reports with each
>> client's name. We can get the name from the SQL Server database that the
>> reports are using.
>> Is it possible to just create this dataset "somewhere" in my report
>> project,
>> and have all reports get the data returned from this dataset? Or do I
>> have
>> to add this dataset to all my reports?
>> Sincerely,
>> Kaisa M. Lindahl
>>|||Although u create a Stored Procedure , u need to have a dataset in all
your reports which point to the stored procedure.
In short for every report there shall be a dataset|||"Vishu" <vishwasaj@.gmail.com> wrote in message
news:1121329763.408325.84730@.g43g2000cwa.googlegroups.com...
> Although u create a Stored Procedure , u need to have a dataset in all
> your reports which point to the stored procedure.
> In short for every report there shall be a dataset
Thanks. :)
I recreated the Stored Procedure without parameters. The SP needs to be
added manually to each database anyway, so I will change it to fit with the
database in the stored procedure, not have each report send the parameters.
Adding just one line of code as a dataset + adding a text box to the report
was a lot easier than expected.
In the end I wrote a blog post about it:
http://blogs.spipp.net/kaisa/archive/2005/07/12/1154.aspx
Kaisa M. Lindahl
Possible to render direct to PDF instead of viewing the report on a webpage?
Is it possible to to render direct to PDF when viewing the report url?
Hello,
Try adding the part in red to your URL:
http://ServerName/Reportserver/Pages/Report.aspx?ItemPath=%2fReportPath%2fReportName&rs:Command=Render&rs:Format=PDF
Hope this helps.
Jarret
|||Will check that out in the beginning of the next week.
Thanks!!!
|||
Jarret wrote:
Hello,
Try adding the part in red to your URL:
http://ServerName/Reportserver/Pages/Report.aspx?ItemPath=%2fReportPath%2fReportName&rs:Command=Render&rs:Format=PDF
Hope this helps.
Jarret
Jarret is right, however it is better not to render from http://servername/reportserver/?/ReportPath/ReportName&rs:Format=PDF
The server will automatically redirect you to the ./Pages/Report.aspx part, but that is really an implementation detail and could change in the future.
|||Hi
I′ve tested to do it like this and it works if parameters needed for the report are passed in the the url-string.
The problem is if you would like the user to write in a parameter (eg. orderno) to the report, this solution won′t work.
It seems like it have to go through the standard way, via the HTML-page if orderno needs to be set.
Any Ideas how to solve this?
|||private void CreatePDF()
{
try
{
Microsoft.Reporting.WebForms.Warning[] warn;
string[] streamids;
string mimeType = "application/pdf";
string encoding = string.Empty;
string extension = string.Empty;
byte[] byteViewer;
byteViewer = ReportViewerMain.ServerReport.Render("PDF", null, out mimeType, out encoding, out extension, out streamids, out warn);
Response.Buffer = true;
Response.Clear();
Response.ContentType = "application/pdf";
Response.AddHeader("content-disposition", "inline; filename=myReport.pdf");
Response.BinaryWrite(byteViewer);
Response.Flush();
Response.End();
streamids = null;
byteViewer = null;
}
catch (Exception ex)
{
ExceptionManager.Sys(ex);
}
}
|||Hi there
Should I put this code in custome code? Any references needed?
Possible to reference the value of a textbox in page header or footer?
Is it possible to reference the value of a textbox in a report page header
or footer? I've tried to create a textbox in my report page footer, and make
it show the PageNumber. My textbox is called txtPageNum.
In my report body, I've created another textbox. I've tried to set this
textbox's value to =ReportItems!txtPageNum.value, but that didn't work out.
This is the error message:
"The value expression for the textbox 'textbox2' refers to the report item
'txtPageNum'. Report item expressions can only refer to other report items
within the same grouping scope or a containing grouping scope."
So, is it even possible to reference it, or should I just give up on the
whole idea?
Kaisa M. LindahlSorry, meant that to be a post. Yes, in theory you can reference a
textbox field from a Header or Footer. However, for page numbers, I'd
reference the Report Services global variables. There are two of them
that apply here.
For example:
="Page " & Globals.PageNumber & " of " & Globals.TotalPages
This will show "Page 1 of 2", etc.
MikeN|||Funny, as each time I try to use Globals!PageNumber in a text box in the
body, I get this error message:
The value expression for the textbox 'textbox20' refers to the global
variable PageNumber or TotalPages. These global variables can be used only
in the page header and page footer.
I'm very interested in how you suggest I can use the globals in the report
body section.
I'm also rather interested in how to reference a textbox field in the header
or footer, even if it's in theory. I've tried referencing it with
ReportItems!myTextBox.value, but no good. Suggestions on syntax are very
welcome.
Kaisa M. Lindahl
"MikeN" <mnorman07@.yahoo.com> wrote in message
news:1113574896.932765.202500@.o13g2000cwo.googlegroups.com...
> Sorry, meant that to be a post. Yes, in theory you can reference a
> textbox field from a Header or Footer. However, for page numbers, I'd
> reference the Report Services global variables. There are two of them
> that apply here.
> For example:
> ="Page " & Globals.PageNumber & " of " & Globals.TotalPages
> This will show "Page 1 of 2", etc.
> MikeN
>
or footer? I've tried to create a textbox in my report page footer, and make
it show the PageNumber. My textbox is called txtPageNum.
In my report body, I've created another textbox. I've tried to set this
textbox's value to =ReportItems!txtPageNum.value, but that didn't work out.
This is the error message:
"The value expression for the textbox 'textbox2' refers to the report item
'txtPageNum'. Report item expressions can only refer to other report items
within the same grouping scope or a containing grouping scope."
So, is it even possible to reference it, or should I just give up on the
whole idea?
Kaisa M. LindahlSorry, meant that to be a post. Yes, in theory you can reference a
textbox field from a Header or Footer. However, for page numbers, I'd
reference the Report Services global variables. There are two of them
that apply here.
For example:
="Page " & Globals.PageNumber & " of " & Globals.TotalPages
This will show "Page 1 of 2", etc.
MikeN|||Funny, as each time I try to use Globals!PageNumber in a text box in the
body, I get this error message:
The value expression for the textbox 'textbox20' refers to the global
variable PageNumber or TotalPages. These global variables can be used only
in the page header and page footer.
I'm very interested in how you suggest I can use the globals in the report
body section.
I'm also rather interested in how to reference a textbox field in the header
or footer, even if it's in theory. I've tried referencing it with
ReportItems!myTextBox.value, but no good. Suggestions on syntax are very
welcome.
Kaisa M. Lindahl
"MikeN" <mnorman07@.yahoo.com> wrote in message
news:1113574896.932765.202500@.o13g2000cwo.googlegroups.com...
> Sorry, meant that to be a post. Yes, in theory you can reference a
> textbox field from a Header or Footer. However, for page numbers, I'd
> reference the Report Services global variables. There are two of them
> that apply here.
> For example:
> ="Page " & Globals.PageNumber & " of " & Globals.TotalPages
> This will show "Page 1 of 2", etc.
> MikeN
>
Possible to override browser language setting?
this snippet:
The browser language setting is the primary factor in determining which
language resource is used by the report server for that client connection.
For example, a user who accesses a Japanese report server using a French
version of Internet Explorer will work with French versions of Report
Manager, HTML Viewer, and the report server folder namespace. In addition,
all errors, warnings, and informational messages will be returned in French.
is from this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/drp_deploying_v1_0vaa.asp
Is it possible to override this for reporting services? I have a user
locale setting in a DB that I would like to use instead of the browser or OS
settings. I can deliver the reports in the proper locale by passing a
parameter, but the settings for the web service still behave according to the
browser settings, as the snippet above describes. In fact, I have a date
format clash that I am not sure quite how to resolve.
Thanks in advance, any help will be very much appreciated.I can solve the date clash by formatting the datetime parameters according
to the browser's setting, but this does not really solve my problem because I
don't want things like selection of report export formats to be in the
browser's language, I want to set the culture information (from a user
profile in a DB).
Thank you again, I am looking forward to your feedback|||regarding my post on whether the locale used for things like the export
options can be set manually rather than pulled from the browser...
I think this will be an easy question to answer for someone on the
development team or simply someone more familiar with microsoft
internationalization than myself.
Thank you very much, in advance.
skillet
The browser language setting is the primary factor in determining which
language resource is used by the report server for that client connection.
For example, a user who accesses a Japanese report server using a French
version of Internet Explorer will work with French versions of Report
Manager, HTML Viewer, and the report server folder namespace. In addition,
all errors, warnings, and informational messages will be returned in French.
is from this article:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/drp_deploying_v1_0vaa.asp
Is it possible to override this for reporting services? I have a user
locale setting in a DB that I would like to use instead of the browser or OS
settings. I can deliver the reports in the proper locale by passing a
parameter, but the settings for the web service still behave according to the
browser settings, as the snippet above describes. In fact, I have a date
format clash that I am not sure quite how to resolve.
Thanks in advance, any help will be very much appreciated.I can solve the date clash by formatting the datetime parameters according
to the browser's setting, but this does not really solve my problem because I
don't want things like selection of report export formats to be in the
browser's language, I want to set the culture information (from a user
profile in a DB).
Thank you again, I am looking forward to your feedback|||regarding my post on whether the locale used for things like the export
options can be set manually rather than pulled from the browser...
I think this will be an easy question to answer for someone on the
development team or simply someone more familiar with microsoft
internationalization than myself.
Thank you very much, in advance.
skillet
Possible to make "Detail View" default in Report Mgr?
Is there a config file where this can be modified such that the views in http://SomeWebServer/Reports will be Details view by default?Still wondering about this. I reviewed the CSS one day for the site, but didn't see this in there.
Possible to make "Detail View" default in Report Mgr?
Is there a config file where this can be modified such that the views in http://SomeWebServer/Reports will be Details view by default?Still wondering about this. I reviewed the CSS one day for the site, but didn't see this in there.
possible to link 2 stored procedures to produce only 1 recordset?
Hello everybody!
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
KumaHow about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp|||How about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp
Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...
Otherwise it won't...
Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)
CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)
AS
SELECT
a + b + c AS fld1,
d + e + f AS fld2
FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd
SP1 would return:
PersonID, Fld1, Fld2, Fld3
SP2:
PersonID, Fld4, Fld5, Fld6
SP3:
PersonID, Fld7, Fld8, Fld9
Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.
SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.
Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.
like this:
Results for PersonID: XYZ123
SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7
If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.
SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?
thx
Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS
CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd
SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)
RETURN-PatP|||thx a lot.
hoped for something even simpler, but i can live with that.
Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.
glad i got the syntax for the INSERT statement!
ty again
Kuma|||Just hope the sproc is executed at the same time then...
I would suggest a table variable if you don't like temp tables...
My question to you then, is how BIG is the result set?
Because if it's not then temp is not a problem...
still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.
-PatP|||thx for the info in the last two posts.
table variable sounds very good. i'll try that.
didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...
:)
Kuma
*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!
-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.
-PatP|||rdjabarov: yes pls u should say why :)
ty|||rdjabarov: yes pls u should say why :)
tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.
-PatP
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
KumaHow about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp|||How about this:
create a new "wrapper" procedure. that does the something like:
create #temp (with a buch of fields)
insert into #temp exec proc1
insert into #temp exec proc2
select *
from #temp
Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...
Otherwise it won't...
Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)
CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)
AS
SELECT
a + b + c AS fld1,
d + e + f AS fld2
FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd
SP1 would return:
PersonID, Fld1, Fld2, Fld3
SP2:
PersonID, Fld4, Fld5, Fld6
SP3:
PersonID, Fld7, Fld8, Fld9
Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.
SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.
Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.
like this:
Results for PersonID: XYZ123
SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7
If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.
SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?
thx
Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS
CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd
CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd
SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)
RETURN-PatP|||thx a lot.
hoped for something even simpler, but i can live with that.
Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.
glad i got the syntax for the INSERT statement!
ty again
Kuma|||Just hope the sproc is executed at the same time then...
I would suggest a table variable if you don't like temp tables...
My question to you then, is how BIG is the result set?
Because if it's not then temp is not a problem...
still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.
-PatP|||thx for the info in the last two posts.
table variable sounds very good. i'll try that.
didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...
:)
Kuma
*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!
-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.
-PatP|||rdjabarov: yes pls u should say why :)
ty|||rdjabarov: yes pls u should say why :)
tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.
-PatP
Possible to Limit the Export Formats
Is it possible to limit the export formats from the Report Viewer control? Specifically the options that appear in the dropdown box. I'm using it in an ASP.NET environment (RTM) and remote mode of the ReportViewer control.
In remote mode, we can edit the rsreportserver.config file on the Report Server to limit the available export formats but that will enforce for all the reporting applications running on that machine.
Can we restrict it for one of the reporting application deployed on the reporting Services and not for all?
Unfortunately the list is not customizable. You can, however, hide the dropdown entirely and add your own to the form that includes only the formats you want. You can programmatically access to all available rendering extensions through ReportViewer.LocalReport.Render().
In remote mode, we can edit the rsreportserver.config file on the Report Server to limit the available export formats but that will enforce for all the reporting applications running on that machine.
Can we restrict it for one of the reporting application deployed on the reporting Services and not for all?
Unfortunately the list is not customizable. You can, however, hide the dropdown entirely and add your own to the form that includes only the formats you want. You can programmatically access to all available rendering extensions through ReportViewer.LocalReport.Render().
Possible to know if I'm on the first page or not?
I want to do a visibility check. If I'm looking at the first page of a
multipage report, I want an item to be hidden. If I'm looking on all pages
except page 1, I want the item to be visible. Is this possible? How? I've
tried creating a page footer, and added a textbox which shows the page
number with Globals!PageNumber. Unfortunately, I can't figure out to
reference this textbox. I've tried =IIF(MyTextBox.value > 1, True, False),
but then I get an error message saying that "MyTextBox" is not declared.
Please give me some advice.
Kaisa M. Lindahlwell dear in the hidden property of the textbox(field) which u want to
hide on first page n visible on all other pages write the following
expression
=iif(globals!pagenumber=1,trrue,false)
REMEMBER write this expression in the HIDDEN property of the textbox
which u want to hide
hope this ll resolve the problem
take care|||You can't use Globals!PageNumber in the body section of a report.
It can only be used in header and footer.Which is why I created my textbox
in the footer, and found out I can't reference the value of it.
Anyway, it's the whole report header I want to hide on this first page. But
the "Hidden"-expression is just the same for all items.
Kaisa M. Lindahl
"** Spirits **" <muhammadhammad78@.gmail.com> wrote in message
news:1112248349.597510.309570@.g14g2000cwa.googlegroups.com...
> well dear in the hidden property of the textbox(field) which u want to
> hide on first page n visible on all other pages write the following
> expression
> =iif(globals!pagenumber=1,trrue,false)
> REMEMBER write this expression in the HIDDEN property of the textbox
> which u want to hide
> hope this ll resolve the problem
> take care
>
multipage report, I want an item to be hidden. If I'm looking on all pages
except page 1, I want the item to be visible. Is this possible? How? I've
tried creating a page footer, and added a textbox which shows the page
number with Globals!PageNumber. Unfortunately, I can't figure out to
reference this textbox. I've tried =IIF(MyTextBox.value > 1, True, False),
but then I get an error message saying that "MyTextBox" is not declared.
Please give me some advice.
Kaisa M. Lindahlwell dear in the hidden property of the textbox(field) which u want to
hide on first page n visible on all other pages write the following
expression
=iif(globals!pagenumber=1,trrue,false)
REMEMBER write this expression in the HIDDEN property of the textbox
which u want to hide
hope this ll resolve the problem
take care|||You can't use Globals!PageNumber in the body section of a report.
It can only be used in header and footer.Which is why I created my textbox
in the footer, and found out I can't reference the value of it.
Anyway, it's the whole report header I want to hide on this first page. But
the "Hidden"-expression is just the same for all items.
Kaisa M. Lindahl
"** Spirits **" <muhammadhammad78@.gmail.com> wrote in message
news:1112248349.597510.309570@.g14g2000cwa.googlegroups.com...
> well dear in the hidden property of the textbox(field) which u want to
> hide on first page n visible on all other pages write the following
> expression
> =iif(globals!pagenumber=1,trrue,false)
> REMEMBER write this expression in the HIDDEN property of the textbox
> which u want to hide
> hope this ll resolve the problem
> take care
>
Subscribe to:
Posts (Atom)