Problems integrating reporting services into a web app

Hello

I have an existing web app on my web server, and reporting services on my app server. I'm trying to integrate the reports into the web app.

The problem is, when the user clicks 'execute report', the app, predictably, falls over, because the aspx page thinks it's on the same server as reporting services, whereas it's not.

Does anybody know how these requests from the aspx page can be redirected to a different server? The way I see it, if I can't get the integration working through url access, I'll have to use the soap api, which will involve dynamically creating drop downs for the report parameters, etc, which is moving away from utilising the advantages that reporting services brings!

Any thoughts would be greatly appreciated.

Thanks in advance
Dominic

Hi Dominic,

this article will help you embed the report in your web application:
http://msdn2.microsoft.com/en-us/library/ms153563.aspx

regards
Helen|||

Thanks for the reply helen. My problem is that in our architecture the client can't access the report server directly. I've decided to go with the reportviewer control instead.

Cheers

Dominic

Problems in Report Presentation

Hello:
We are running SQL Server 2000 RS. We are just starting to deploy reports
and we are running into a strange problem.
We have given users access to their reports via various Windows security
groups. When an Sysadmin runs a report, it's runs fine and displays
everything in a timely fashion.
When a regular user runs a report, there is a significant delay while the
Report is Being Generated icon spins. Finally, the report displays but some
graphics are not displayed and are replaced by a box with a red X.
This behavior is consistent on any user who is not a sysadmin.
Any suggestions on how to correct this problem would be appreciated.
ThanksBrennan,
Do the images exist on a location where the group to which the user's
belong does not have permission?
I bet if you set the permission on the image to 'Everyone'/Anonymous
you'll get the images in the report.
Andy Potter

Problems Exporting To Excel When Using Report Subscription

Hi,
I'm having a problem with Reporting Services report subscriptions.
I have a set of reports that need to be sent via email on a schedule. I need
to send them in Excel format, but the files sent to my email account are
unreadable in Excel. I am using Excel 2003 and can manually export reports
and open them in Excel without any problems, yet when I use a subscription to
email the Excel files they are not valid files. They have the .xls extension,
but Excel can't open them properly, it just opens them as a load of
unintelligible text.
The same thing happens when I set up a subscription to email a report in XML
format, I could export it manually and it worked fine, yet when I run the
subscription to email the XML report to me I get an invalid XML file.
Has anyone else had this problem or does anyone know how to fix it?
If it helps, the report server is stored on a network server, not my local PC.
The server is running Windows Server 2003 Pro, my PC is running Windows XP
Pro and has Office 2003. Office is not installed on the server, but I doubt
that makes any difference, since I can manually export to Excel without any
problems.
Thanks in advance
TabbyWhen you run them "locally" due you mean from VS.NET on your workstation?
If yes... I found that generating reports "locally" can have totally
different results than running them on the report server.
If this is the case...try loading the report via a browser from the report
server.
If this is not the case... try pdf/html or other output formats to narrow
down the problem. I found problems exporting to excel but not other formats
when report objects are overlapping.
Tabby Cool wrote:
>Hi,
>I'm having a problem with Reporting Services report subscriptions.
>I have a set of reports that need to be sent via email on a schedule. I need
>to send them in Excel format, but the files sent to my email account are
>unreadable in Excel. I am using Excel 2003 and can manually export reports
>and open them in Excel without any problems, yet when I use a subscription to
>email the Excel files they are not valid files. They have the .xls extension,
>but Excel can't open them properly, it just opens them as a load of
>unintelligible text.
>The same thing happens when I set up a subscription to email a report in XML
>format, I could export it manually and it worked fine, yet when I run the
>subscription to email the XML report to me I get an invalid XML file.
>Has anyone else had this problem or does anyone know how to fix it?
>If it helps, the report server is stored on a network server, not my local PC.
>The server is running Windows Server 2003 Pro, my PC is running Windows XP
>Pro and has Office 2003. Office is not installed on the server, but I doubt
>that makes any difference, since I can manually export to Excel without any
>problems.
>Thanks in advance
>Tabby
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||No, I am not running htem from Visual Studio, I am running them direct from
the Report Manager.
When you run reports in Visual Studio, can you actually export them? I
thought you could only preview them, there is no option to export a report
from the preview screen.
I have tried exporting them to both XML and Excel, I can do this perfectly
from the Report Manager, yet when I use a subscription to email the reports
to me in either of these formats the files are unusable.
William N wrote:
>When you run them "locally" due you mean from VS.NET on your workstation?
>If yes... I found that generating reports "locally" can have totally
>different results than running them on the report server.
>If this is the case...try loading the report via a browser from the report
>server.
>If this is not the case... try pdf/html or other output formats to narrow
>down the problem. I found problems exporting to excel but not other formats
>when report objects are overlapping.
>>Hi,
>[quoted text clipped - 23 lines]
>>Tabby|||I have tried exporting to PDF, again, this works fine when performed manually,
but the email attachments from the subscription are not valid PDFs and can't
be opened by Adobe Acrobat.
It seems there is something different with the export procedure when using a
subscription as opposed to exporting manually, I don't understand why it
would work perfectly one way and not the other.|||Seems like the report is not getting the the data?
Are you saving the credentials in your data connection?
Tabby Cool wrote:
>I have tried exporting to PDF, again, this works fine when performed manually,
>but the email attachments from the subscription are not valid PDFs and can't
>be opened by Adobe Acrobat.
>It seems there is something different with the export procedure when using a
>subscription as opposed to exporting manually, I don't understand why it
>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Yes, the credentials are stored in the data source.
William N wrote:
>Seems like the report is not getting the the data?
>Are you saving the credentials in your data connection?
>>I have tried exporting to PDF, again, this works fine when performed manually,
>>but the email attachments from the subscription are not valid PDFs and can't
>[quoted text clipped - 3 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Not sure what else to suggest.
I always try to simplify as much as possible first. Maybe try creating a
report with just a "Hello" text box and see if that works (no data sets or
anything else).
If it doesn't, then it's probably a server/install issue.
Good luck.
Tabby Cool wrote:
>Yes, the credentials are stored in the data source.
>>Seems like the report is not getting the the data?
>[quoted text clipped - 5 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1

Problems exporting reports to Excel

Hello all

I have a problem with an aplication that developed in .Net to view Crystal 9.0 reports, in my dev PC I can export any report to Excel without a problem, but on my client Pc's, click directly on the Export button, the app show the file location window, select Excel file on type file field and click ok, appear an error, it is in spanish but it is the translation ...

"Error in file c:\pathfile\filename.rpt:
Exportation DLL or format file invalid"

Then appear a new message box:

"Unable to export"

Checking on the web I found tow possible solutions, download the last Hot Fix from seagate site or check and replace the U2F*.dll files located in my user's PC Crystal folder. I tried with both but I'm where I started...

Any idea ?

ThanxDid you have latest service pack of CR?
Refer this for more info
http://www.businessobjects.com/support/default.asp

Problems exporting Reports programatically

I am simply trying to use ActiveX to export a Report to another format, programatically, i.e. without the user having to select a format using the Export dialog.

I can set up all the properties in the ExportOptions object that is part of the Report Object itself, but these do not appear to have any effect when I call the the Export() method on the Report object; which simply brings up the Export dialog with default values.

In fact, even after entering different values through the Export dialog, these do not appear to change the properties that are found in the ExportOptions object. These properties are as what they were coded previously, i.e. unchanged

It's as though the IReport::Export() method and the ExportOptions object are totally separate... Can anyone help to see what I am missing?Try passing "False" to Export so that it does not prompt for the values - from memory, that's what I had to do to get it working.

Problems deploying to a different server

I've been playing with Reporting Services on my computer and got it working fine. I can view the reports on my localhost with no problems. However, when I change the TargetServerURL in the report properties to the server that I want the report to go on (yes, the target server has SQL Reporting Services installed on it), I get the following error message:

Server was unable to process request. > Method ReportingService.SetExecutionOptions can not be reflected. > Unknown error - HRESULT 0x80131047

Anyone have any idea of what's going wrong here? Thanks for any help.

Just to add: I do have rights to the server I'm trying to publish to. This is assuming that SQL Reporting Services is distributing the reports under my username. It could well be that it's trying to use the generic ASPNET user that Visual Studio has a habit of using for its web publishing. Anyone know for sure?Problem solved. Silly mistake on my part. I deployed Framework 2.0 beta to that server and by default it set the folders used by Report Services to use ASP.NET 2.0 which my Visual Studio 2003 didn't like, so I set the folders to use ASP.NET 1.1 and all is good.sql

Problems configuring Execution Log Reports

I receive the following error:
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'RSExecutionLog'.
(rsErrorOpeningConnection) Get Online Help
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
My ReportServer DB is separate from my ReportServer IIS boxes, I'm trying to
use Windows NT Integrated Security to access the Execution Log. I'm a Local
Admin of both servers but still receive the error.
Am I dealing with a permissions hop issue? Any suggestions would help.
DavidYes. Use stored credentials in the shared data source for the reports
(another slightly more complex option would be to use Kerberos delegation)
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"David Dubay" <DavidDubay@.discussions.microsoft.com> wrote in message
news:30C3BBF3-5DE7-4F4E-9008-349CA172D6EC@.microsoft.com...
>I receive the following error:
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'RSExecutionLog'.
> (rsErrorOpeningConnection) Get Online Help
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> My ReportServer DB is separate from my ReportServer IIS boxes, I'm trying
> to
> use Windows NT Integrated Security to access the Execution Log. I'm a
> Local
> Admin of both servers but still receive the error.
> Am I dealing with a permissions hop issue? Any suggestions would help.
> David

Problem: Using Custom Code to provide default values for parameter

Hi, I have reports with parameters that use custom code, and when you hide
the parameters in the report manager the reports no longer work.
I thought it was a parameter dependancy problem, but have done the following
test to prove its not:
I have just created a report, with one parameter called prmUserID that gets
its value from my dll. The default non-queried value for this parameter is
set as follows:
=libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True , False,
False )
The report displays a table, and the table references a dataset that uses
the parameter value. This is the string for the dataset:
="Select * from mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
The report works fine when I deploy it to the report
manager. The parameter shows the value returned from my
dll correctly.
However, I changed the parameter properties of my report in report manager
and took the tick out of the prompt user checkbox. The report no longer
works - I'm pretty sure that my prmUserID is just parsing an empty string now
to the dataset. Maybe once you've hidden a parameter the code from my dll is
no longer called?
I also know its not a problem with my dll as I did the same test above but
used custom code directly in the report (ie added to the code tab of the
report properties dialog). This produced the same result - deployed it,
worked fine, hide the parameter and the code no longer seems to run.
I would be grateful if someone at MS could try and replicate this and let me
know if I'm doing something wrong or if its a bug.
My Firm is VERY keen to move forward with Reporting Services, the printing
issue (which I know is being addressed in SP2) and this parameter problem is
the only thing holding us back at the moment.
Thanks
JeanineHave you installed SP1?... I think that hidden parameters are NOT settable
unless you have SP1 installed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for you reply, yes I do have SP1 installed.
"Wayne Snyder" wrote:
> Have you installed SP1?... I think that hidden parameters are NOT settable
> unless you have SP1 installed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||You need to change the report parameter property. Set the PROMPT string to
"" and the prompt value to TRUE.
It will not display the prompt but at least it will be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for that. I tried that and it worked - as long as you don't then go
and edit the report parameters in report manager.
What I would really like to do is have linked reports, one with a boolean
parameter set to true and one with a boolean parameter set to false.
So as soon as I use the report manager to alter the report parameter
properties (even though I've done you've said below), the reports don't work
properly again.
I still think this is a bug of some kind, and am hoping MS come up with a
fix for this.
Jeanine
"Christopher Conner" wrote:
> You need to change the report parameter property. Set the PROMPT string to
> "" and the prompt value to TRUE.
> It will not display the prompt but at least it will be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||Well first off - users shouldn't have rights to modify the report
parameters.
Second, I set those properties in the report designer - not off the report
manager. It would be a pain to update those reports everytime I did a
deployment.
What happens when you do this inside your report designer with your linked
report?
I'll see if I can replicate your issue here, but otherwise I haven't run
into any problems - albeit just a few quirks like the solution I just gave
you to not show parameters but yet allow them to be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> Thanks for that. I tried that and it worked - as long as you don't then
> go
> and edit the report parameters in report manager.
> What I would really like to do is have linked reports, one with a boolean
> parameter set to true and one with a boolean parameter set to false.
> So as soon as I use the report manager to alter the report parameter
> properties (even though I've done you've said below), the reports don't
> work
> properly again.
> I still think this is a bug of some kind, and am hoping MS come up with a
> fix for this.
> Jeanine
>
> "Christopher Conner" wrote:
>> You need to change the report parameter property. Set the PROMPT string
>> to
>> "" and the prompt value to TRUE.
>> It will not display the prompt but at least it will be updatable.
>> =-Chris
>>
>> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
>> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
>> > Hi, I have reports with parameters that use custom code, and when you
>> > hide
>> > the parameters in the report manager the reports no longer work.
>> >
>> > I thought it was a parameter dependancy problem, but have done the
>> > following
>> > test to prove its not:
>> >
>> > I have just created a report, with one parameter called prmUserID that
>> > gets
>> > its value from my dll. The default non-queried value for this
>> > parameter
>> > is
>> > set as follows:
>> >
>> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
>> > False,
>> > False )
>> >
>> > The report displays a table, and the table references a dataset that
>> > uses
>> > the parameter value. This is the string for the dataset:
>> >
>> > ="Select * from mhgroup.docusers where userid='" &
>> > Parameters!prmUserID.Value & "'"
>> >
>> > The report works fine when I deploy it to the report
>> > manager. The parameter shows the value returned from my
>> > dll correctly.
>> >
>> > However, I changed the parameter properties of my report in report
>> > manager
>> > and took the tick out of the prompt user checkbox. The report no
>> > longer
>> > works - I'm pretty sure that my prmUserID is just parsing an empty
>> > string
>> > now
>> > to the dataset. Maybe once you've hidden a parameter the code from my
>> > dll
>> > is
>> > no longer called?
>> >
>> > I also know its not a problem with my dll as I did the same test above
>> > but
>> > used custom code directly in the report (ie added to the code tab of
>> > the
>> > report properties dialog). This produced the same result - deployed
>> > it,
>> > worked fine, hide the parameter and the code no longer seems to run.
>> >
>> > I would be grateful if someone at MS could try and replicate this and
>> > let
>> > me
>> > know if I'm doing something wrong or if its a bug.
>> >
>> > My Firm is VERY keen to move forward with Reporting Services, the
>> > printing
>> > issue (which I know is being addressed in SP2) and this parameter
>> > problem
>> > is
>> > the only thing holding us back at the moment.
>> >
>> > Thanks
>> > Jeanine
>>|||Hi,
1) Users will not be modifying report parameters settings. They will
however be able to use the prms that I set as being visible.
2) What I'm trying to achieve is to have 1 main report and create from this,
several linked reports. By linked reports, I'm referring to the ability to
create a linked report from another report in Report manager. These linked
reports would have different parameter settings. This way I can use the
parameter information to tailor 1 main report, dependant upon the parameter
values. This saves me from having to create multiple reports in the report
designer, that contain basically the same information.
I tried your suggestion, with one of my production reports that has quite a
few parameters, and it worked fine. However to achieve 2) I will need to
create from this a linked report, change the parameter settings - which will
involve things such as changing a boolean prm from true to false etc.
If it is indeed a problem it is very easy to replicate, just create a report
with a parameter and set the default value of the parameter to a string
returned from a function in the custom code of the report. Use this
parameter in the datasource of the report, eg, ="Select * from
mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
Deploy the report, use the report manager to hide the parameter, and the
report should then no longer display the correct information (because the
default value of the parameter was not populated with the custom code value).
Thanks very much.
"Christopher Conner" wrote:
> Well first off - users shouldn't have rights to modify the report
> parameters.
> Second, I set those properties in the report designer - not off the report
> manager. It would be a pain to update those reports everytime I did a
> deployment.
> What happens when you do this inside your report designer with your linked
> report?
> I'll see if I can replicate your issue here, but otherwise I haven't run
> into any problems - albeit just a few quirks like the solution I just gave
> you to not show parameters but yet allow them to be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> > Thanks for that. I tried that and it worked - as long as you don't then
> > go
> > and edit the report parameters in report manager.
> >
> > What I would really like to do is have linked reports, one with a boolean
> > parameter set to true and one with a boolean parameter set to false.
> >
> > So as soon as I use the report manager to alter the report parameter
> > properties (even though I've done you've said below), the reports don't
> > work
> > properly again.
> >
> > I still think this is a bug of some kind, and am hoping MS come up with a
> > fix for this.
> >
> > Jeanine
> >
> >
> > "Christopher Conner" wrote:
> >
> >> You need to change the report parameter property. Set the PROMPT string
> >> to
> >> "" and the prompt value to TRUE.
> >>
> >> It will not display the prompt but at least it will be updatable.
> >>
> >> =-Chris
> >>
> >>
> >>
> >> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> >> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> >> > Hi, I have reports with parameters that use custom code, and when you
> >> > hide
> >> > the parameters in the report manager the reports no longer work.
> >> >
> >> > I thought it was a parameter dependancy problem, but have done the
> >> > following
> >> > test to prove its not:
> >> >
> >> > I have just created a report, with one parameter called prmUserID that
> >> > gets
> >> > its value from my dll. The default non-queried value for this
> >> > parameter
> >> > is
> >> > set as follows:
> >> >
> >> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> >> > False,
> >> > False )
> >> >
> >> > The report displays a table, and the table references a dataset that
> >> > uses
> >> > the parameter value. This is the string for the dataset:
> >> >
> >> > ="Select * from mhgroup.docusers where userid='" &
> >> > Parameters!prmUserID.Value & "'"
> >> >
> >> > The report works fine when I deploy it to the report
> >> > manager. The parameter shows the value returned from my
> >> > dll correctly.
> >> >
> >> > However, I changed the parameter properties of my report in report
> >> > manager
> >> > and took the tick out of the prompt user checkbox. The report no
> >> > longer
> >> > works - I'm pretty sure that my prmUserID is just parsing an empty
> >> > string
> >> > now
> >> > to the dataset. Maybe once you've hidden a parameter the code from my
> >> > dll
> >> > is
> >> > no longer called?
> >> >
> >> > I also know its not a problem with my dll as I did the same test above
> >> > but
> >> > used custom code directly in the report (ie added to the code tab of
> >> > the
> >> > report properties dialog). This produced the same result - deployed
> >> > it,
> >> > worked fine, hide the parameter and the code no longer seems to run.
> >> >
> >> > I would be grateful if someone at MS could try and replicate this and
> >> > let
> >> > me
> >> > know if I'm doing something wrong or if its a bug.
> >> >
> >> > My Firm is VERY keen to move forward with Reporting Services, the
> >> > printing
> >> > issue (which I know is being addressed in SP2) and this parameter
> >> > problem
> >> > is
> >> > the only thing holding us back at the moment.
> >> >
> >> > Thanks
> >> > Jeanine
> >>
> >>
> >>
>
>|||i myself are having a similar problem
i believe its a BUG in report manager,
that changing any of the options for parameters in report manager
(which is what is needed to be done to make a writable hidden
parameter) causes default calculations (even query based) to not work
for parameters that are not pull down boxes.|||Thanks for that. Its nice to know I'm not the only one experiencing this
problem. I'm hoping somone from MS may be able to shed some light on this as
well.
"klumsy@.xtra.co.nz" wrote:
> i myself are having a similar problem
> i believe its a BUG in report manager,
> that changing any of the options for parameters in report manager
> (which is what is needed to be done to make a writable hidden
> parameter) causes default calculations (even query based) to not work
> for parameters that are not pull down boxes.
>|||Please check this related thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
Also check my recent posting in that same thread for a possible workaround
on RS 2000 (specifying hidden parameters through report designer).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> Thanks for that. Its nice to know I'm not the only one experiencing this
> problem. I'm hoping somone from MS may be able to shed some light on this
> as
> well.
>
> "klumsy@.xtra.co.nz" wrote:
>> i myself are having a similar problem
>> i believe its a BUG in report manager,
>> that changing any of the options for parameters in report manager
>> (which is what is needed to be done to make a writable hidden
>> parameter) causes default calculations (even query based) to not work
>> for parameters that are not pull down boxes.
>>|||Thanks Robert. I'll look forward to the release of Yukon.
"Robert Bruckner [MSFT]" wrote:
> Please check this related thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
> Also check my recent posting in that same thread for a possible workaround
> on RS 2000 (specifying hidden parameters through report designer).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> > Thanks for that. Its nice to know I'm not the only one experiencing this
> > problem. I'm hoping somone from MS may be able to shed some light on this
> > as
> > well.
> >
> >
> > "klumsy@.xtra.co.nz" wrote:
> >
> >> i myself are having a similar problem
> >> i believe its a BUG in report manager,
> >> that changing any of the options for parameters in report manager
> >> (which is what is needed to be done to make a writable hidden
> >> parameter) causes default calculations (even query based) to not work
> >> for parameters that are not pull down boxes.
> >>
> >>
>
>

Problem: I can''t get Reports from http://myserver/ReportServer

Hello all,

I'm using SQL Server 2005 SP 2 with Reporting services and MS dynamic CRM 3.0.

During the installation of CRM, a lot of Reports are deployed to the ReportServer.

I can connect to the ReportServer over http://myserver/ReportServer, can walk through the folders and select the wanted Report, but I only get the header of the Report without the Action Icons and Data!

If I use http://myserver/Reports, the selected Reports works very well.

Has anybody an idea what is happen?

Hi,

I fixed the problem myself!

Thanks for the lot of answers

Ok, here is what I have done:

I renewed the virtual Directory for the ReportServer in the ReportingService configuration tool.

Problem: I can''t get Reports from http://myserver/ReportServer

Hello all,

I'm using SQL Server 2005 SP 2 with Reporting services and MS dynamic CRM 3.0.

During the installation of CRM, a lot of Reports are deployed to the ReportServer.

I can connect to the ReportServer over http://myserver/ReportServer, can walk through the folders and select the wanted Report, but I only get the header of the Report without the Action Icons and Data!

If I use http://myserver/Reports, the selected Reports works very well.

Has anybody an idea what is happen?

Hi,

I fixed the problem myself!

Thanks for the lot of answers

Ok, here is what I have done:

I renewed the virtual Directory for the ReportServer in the ReportingService configuration tool.

Problem with WHERE clause

I'm migrating some reports to reporting services, some of the reports use a
bunch of conditional logic to produce the desired results, I'm trying to
eliminate as much of that as possible... Here's my query... At the end of th
e
post is the original sp... My problem is when I use the "@.fdate" parameter
the query is not producing any results, if I use the sp with the same params
I get results so I know something is wrong, I'm just not seeing it, wonderin
g
if someone can help...
Thx... Dan
DECLARE --Report Parameters
@.loc char(06),
@.fdate datetime,
@.dtype char(01),
@.brand char(02)
--SET @.fdate = '07/09/2005'
SET @.dtype = 'E'
SET @.brand = 'MC'
SELECT
ordet.ORDD_ORD_NO AS ordno,
ordet.ORDD_UNIT_NO AS unitno,
ordet.PROD_NO AS prodno,
ord.ord_cus_grp AS cusgrp,
ord.ord_cus_cd AS cuscd,
ordet.ORDD_FLOOR_START_DT AS floorst,
ordet.ORDD_SICKBAY_DT AS sickbay,
ordet.ORDD_DISPATCH_DT AS dispatch,
ordet.ORDD_FLOORED_DT AS floored,
ordet.ORDD_FLOORED_CMNT AS fcomment,
ordet.ORDD_SCHEDULED_LOCATION AS location,
ordet.ORDD_EST_OFFLINE_DT AS eoffline,
ISNULL(ordet.ORDD_BASE_PRICE, 0.00) AS base,
ISNULL(ordet.ORDD_CHASSIS_PRICE, 0.00) AS chassis,
ISNULL(ordet.ORDD_OPTION_PRICE, 0.00) AS options,
cus.cus_name AS cusname,
mod.model_desc AS model,
mm.mm_corp_cd AS brand,
ord.ord_cus_ord AS salescomment
FROM dbo.HR_ORDDET ordet (nolock)
INNER JOIN dbo.ord ord (nolock) on ordet.ORDD_ORD_CO = ord.ord_company and
ordet.ORDD_ORD_NO = ord.ord_no
INNER JOIN dbo.model_master mm (nolock) on ordet.ORDD_UNIT_NO = mm.sku_cd
LEFT OUTER JOIN dbo.customer cus (nolock) on cus.cus_cd = ord.ord_cus_cd
AND cus.cus_grp_cd = ord.ord_cus_grp
LEFT OUTER JOIN dbo.model mod (nolock) on substring(ordet.ORDD_UNIT_NO,5,4)
= mod.model_code
WHERE
((@.dtype IS NULL) OR (@.dtype IS NOT NULL AND @.dtype = 'E' AND
ordet.ORDD_EST_OFFLINE_DT IS NOT NULL) OR (@.dtype IS NOT NULL AND @.dtype =
'F' AND ordet.ORDD_FLOOR_START_DT IS NOT NULL)) AND
((@.loc IS NULL) OR (@.loc IS NOT NULL AND ordet.ORDD_SCHEDULED_LOCATION =
@.loc)) AND
( ordet.ORDD_INVOICED_DT IS NULL ) AND
((@.fdate IS NULL) OR (@.fdate IS NOT NULL AND @.dtype IS NOT NULL AND @.dtype
= 'E' AND ordet.ORDD_EST_OFFLINE_DT >= @.fdate) OR (@.fdate IS NOT NULL AND
@.dtype IS NOT NULL AND @.dtype = 'F' AND ordet.ORDD_FLOOR_START_DT >= @.fdate)
)
AND
((@.brand IS NULL) OR (@.brand IS NOT NULL AND @.brand IN ('HR','BC','SC') AND
mm.mm_corp_cd = @.brand) OR (@.brand IS NOT NULL AND @.brand = 'MC' AND
SUBSTRING(ordet.ORDD_UNIT_NO,5,4) > '1500' AND mm.mm_corp_cd = @.brand) OR
(@.brand IS NOT NULL AND @.brand = 'MK' AND SUBSTRING(ordet.ORDD_UNIT_NO,5,4)
<
'1500' AND mm.mm_corp_cd = 'MC'))
ORDER BY
location,
cusname,
model
/*
Begin original procedure...
*/
CREATE procedure dbo.msp_web_floored_units (@.loc char(06) = null,
@.fdate datetime = null,
@.dtype char(01) = null,
@.brand char(02) = null)
as
create table #floor
(ordno int,
unitno char(20),
prodno int,
cusgrp varchar(06),
cuscd varchar(10),
floorst datetime,
sickbay datetime,
dispatch datetime,
floored datetime,
fcomment char(25),
location char(06),
eoffline datetime,
base money,
chassis money,
options money,
cusname varchar(30),
model varchar(40),
brand char(02),
salescomment varchar(20))
if @.loc is not null
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) on dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) on dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@.dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null))
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null
and dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION = @.loc
end
else
begin
insert #floor
SELECT dbo.HR_ORDDET.ORDD_ORD_NO,
dbo.HR_ORDDET.ORDD_UNIT_NO,
dbo.HR_ORDDET.PROD_NO,
dbo.ord.ord_cus_grp,
dbo.ord.ord_cus_cd,
dbo.HR_ORDDET.ORDD_FLOOR_START_DT,
dbo.HR_ORDDET.ORDD_SICKBAY_DT,
dbo.HR_ORDDET.ORDD_DISPATCH_DT,
dbo.HR_ORDDET.ORDD_FLOORED_DT ,
dbo.HR_ORDDET.ORDD_FLOORED_CMNT,
dbo.HR_ORDDET.ORDD_SCHEDULED_LOCATION,
dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT,
IsNull(dbo.HR_ORDDET.ORDD_BASE_PRICE, 0.00) AS ORDD_BASE_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_CHASSIS_PRICE, 0.00) AS
ORDD_CHASSIS_PRICE,
IsNull(dbo.HR_ORDDET.ORDD_OPTION_PRICE, 0.00) AS ORDD_OPTION_PRICE,
null,
null,
dbo.model_master.mm_corp_cd,
dbo.ord.ord_cus_ord
FROM dbo.HR_ORDDET (nolock)
INNER JOIN dbo.ord (nolock) ON dbo.HR_ORDDET.ORDD_ORD_CO =
dbo.ord.ord_company and dbo.HR_ORDDET.ORDD_ORD_NO = dbo.ord.ord_no
INNER JOIN dbo.model_master (nolock) ON dbo.HR_ORDDET.ORDD_UNIT_NO =
dbo.model_master.sku_cd
WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not
null) or
(@.dtype = 'F' and dbo.HR_ORDDET.ORDD_FLOOR_START_DT is not null) )
and dbo.HR_ORDDET.ORDD_INVOICED_DT is null
end
if @.fdate is not null
begin
if @.dtype = 'E'
begin
delete #floor
where eoffline >= @.fdate
end
else if @.dtype = 'F'
begin
delete #floor
where floorst >= @.fdate
end
end
update #floor
set cusname = b.cus_name
from #floor a (nolock)
inner join customer b (nolock)
on a.cuscd = b.cus_cd
and a.cusgrp = b.cus_grp_cd
update #floor
set model = b.model_desc
from #floor a (nolock)
inner join model b (nolock)
on substring(a.unitno,5,4) = b.model_code
if @.brand is null
begin
select *
from #floor
order by location, cusname, model
end
else if @.brand in ('HR','BC','SC')
begin
select *
from #floor
where brand = @.brand
order by location, cusname, model
end
else if @.brand = 'MC'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) > '1500'
order by location, cusname, model
end
else if @.brand = 'MK'
begin
select *
from #floor
where brand = 'MC'
and substring(unitno,5,4) < '1500'
order by location, cusname, model
end
GOMaybe I'm missing something, but it looks like the query returns rows
where ORDD_EST_OFFLINE_DT >= @.fdate, and the procedure
returns rows where NOT (ORDD_EST_OFFLINE_DT >= @.fdate),
because it deletes the ones where ORDD_EST_OFFLINE_DT >= @.fdate.
If I have this right, then there's no reason to expect the query and
the procedure to return the same thing. There are probably no
rows where ORDD_EST_OFFLINE_DT >= @.fdate.
See comments inline.
SK
Alien2_51 wrote:

>I'm migrating some reports to reporting services, some of the reports use a
>bunch of conditional logic to produce the desired results, I'm trying to
>eliminate as much of that as possible... Here's my query... At the end of t
he
>post is the original sp... My problem is when I use the "@.fdate" parameter
>the query is not producing any results, if I use the sp with the same param
s
>I get results so I know something is wrong, I'm just not seeing it, wonderi
ng
>if someone can help...
>Thx... Dan
>
>DECLARE --Report Parameters
> @.loc char(06),
> @.fdate datetime,
> @.dtype char(01),
> @.brand char(02)
>--SET @.fdate = '07/09/2005'
>SET @.dtype = 'E'
> SELECT
>
[snip]

> WHERE
> (@.dtype = 'E' AND ordet.ORDD_EST_OFFLINE_DT IS NOT NULL) AND
> (@.dtype = 'E' AND ordet.ORDD_EST_OFFLINE_DT >= @.fdate)
>
[snip AND other stuff]
So the query returns rows where ORDD_EST_OFFLINE_DT >= @.fdate

>CREATE procedure dbo.msp_web_floored_units (@.loc char(06) = null,
> @.fdate datetime = null,
> @.dtype char(01) = null,
> @.brand char(02) = null)
>as
>
>create table #floor
>...
>if @.loc is not null
> begin
> insert #floor
> SELECT ...
> WHERE ( (@.dtype = 'E' and dbo.HR_ORDDET.ORDD_EST_OFFLINE_DT is not nul
l)
>
>if @.fdate is not null
> begin
> if @.dtype = 'E'
> begin
> delete #floor
> where eoffline >= @.fdate
> end
> end
>
>
>
The procedure deletes rows where ORDD_EST_OFFLINE_DT >= @.fdate

>
>|||Simple as that... Works perfect...
Thanks Steve!!
"Steve Kass" wrote:

> Maybe I'm missing something, but it looks like the query returns rows
> where ORDD_EST_OFFLINE_DT >= @.fdate, and the procedure
> returns rows where NOT (ORDD_EST_OFFLINE_DT >= @.fdate),
> because it deletes the ones where ORDD_EST_OFFLINE_DT >= @.fdate.
> If I have this right, then there's no reason to expect the query and
> the procedure to return the same thing. There are probably no
> rows where ORDD_EST_OFFLINE_DT >= @.fdate.
> See comments inline.
> SK
> Alien2_51 wrote:
>
> [snip]
>
> [snip AND other stuff]
> So the query returns rows where ORDD_EST_OFFLINE_DT >= @.fdate
>
> The procedure deletes rows where ORDD_EST_OFFLINE_DT >= @.fdate
>
>

Problem with VS.Net when using SQL Reporting Services

I'm experiencing an odd problem with Visual Studio 1.1 when
using/developing reports, etc with the SQL Reporting Services.
After I build/deploy SRS reports to my Reporting Service Visual Studio
seems to lose its ability to display intellisense and format
new/modified VB.NET code per the original tools/option settings. I
have to shut-down Visual Studio (not just the session) and start it up
again if I want to have the editting options and intellisense work (at
all). I've looked at the tools/options settings and everything appears
to be as it should be (VS simply is ignoring the edit/intellisense)
So... I'm wondering if anyone else has experienced this and/or is
there something that I can do about keeping the settings?
TIA
GlennGlenn,
I was suffer from same problem but workaround like following:
1)Remove your MS Report project from the solution1. Leave web/business logic
etc project as they are.
2)Create solution2 and put the MS Report project in. Open one more instance
of VStudio so that you can access all projects same time. (I like the
multi-instance feature of VStudio)
Anyway, I hope someone in MS can look into the issue.
Cheers.
Joe
"Glenn Owens" wrote:
> I'm experiencing an odd problem with Visual Studio 1.1 when
> using/developing reports, etc with the SQL Reporting Services.
> After I build/deploy SRS reports to my Reporting Service Visual Studio
> seems to lose its ability to display intellisense and format
> new/modified VB.NET code per the original tools/option settings. I
> have to shut-down Visual Studio (not just the session) and start it up
> again if I want to have the editting options and intellisense work (at
> all). I've looked at the tools/options settings and everything appears
> to be as it should be (VS simply is ignoring the edit/intellisense)
> So... I'm wondering if anyone else has experienced this and/or is
> there something that I can do about keeping the settings?
> TIA
> Glenn
>|||Joe, thanks for the suggestion. It confirmed my suspicion that I
needed to separate the logic projects from the report projects.
Once done everything is working again!
There are definitely some gottchas in the SRS 1.0.
Regards,
Glenn
Joe <Joe@.discussions.microsoft.com> wrote in message news:<9CFA1EEE-51A3-4A1E-9ECD-DA33664ABC0E@.microsoft.com>...
> Glenn,
> I was suffer from same problem but workaround like following:
> 1)Remove your MS Report project from the solution1. Leave web/business logic
> etc project as they are.
> 2)Create solution2 and put the MS Report project in. Open one more instance
> of VStudio so that you can access all projects same time. (I like the
> multi-instance feature of VStudio)
> Anyway, I hope someone in MS can look into the issue.
> Cheers.
> Joe
> "Glenn Owens" wrote:
> > I'm experiencing an odd problem with Visual Studio 1.1 when
> > using/developing reports, etc with the SQL Reporting Services.
> >
> > After I build/deploy SRS reports to my Reporting Service Visual Studio
> > seems to lose its ability to display intellisense and format
> > new/modified VB.NET code per the original tools/option settings. I
> > have to shut-down Visual Studio (not just the session) and start it up
> > again if I want to have the editting options and intellisense work (at
> > all). I've looked at the tools/options settings and everything appears
> > to be as it should be (VS simply is ignoring the edit/intellisense)
> >
> > So... I'm wondering if anyone else has experienced this and/or is
> > there something that I can do about keeping the settings?
> >
> > TIA
> > Glenn
> >

Problem with using report parameters in SQL query function, SSRS 2

Using Visual Studio to build reports for Reporting Services 2000: is it
allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
select function(@.variable) from table where ..
I get "syntax error or access violation" when trying.
Regards, JouniI think that question would be more appropriate in the sqlserver.programming
group. With that said...
Have you tried running that query in Query Analyzer?
yeah...I don't think that Sql statement will ever run.
from the Books Online for CREATE FUNCTION (transact SQL):
"@.parameter_name
Is a parameter in the user-defined function. One or more parameters can be
declared.
[...]
Specify a parameter name by using an at sign (@.) as the first character. The
parameter name must comply with the rules for identifiers. Parameters are
local to the function; the same parameter names can be used in other
functions. Parameters can take the place only of constants; they cannot be
used instead of table names, column names, or the names of other database
objects. "
Soooo...assuming your user-defined function is a scalar-value function that
returns field names and is owned by "dbo" schema, try using dynamic sql,
something like this:
DECLARE @.sql varchar(8000)
SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
SET @.sql= @.sql + ' FROM MyTable '
SET @.sql= @.sql + ' WHERE ... '
EXEC @.sql
Give it a shot, and if I am way off...reply and let me know. Hope this help
you.
--
Regards,
Thiago Silva
"Jouni" wrote:
> Using Visual Studio to build reports for Reporting Services 2000: is it
> allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> select function(@.variable) from table where ..
> I get "syntax error or access violation" when trying.
> Regards, Jouni|||Hi Thiago,
haven't had time to test this. Actually I already switched to another
approach: defining the whole query as a db procedure and just calling that:
exec jounis_procedure @.report_parameter
I noticed that Microsoft people have chosen this approach themselves: in
"Report Pack for SPS" all the reports use procedure calls.
Regards,
Jouni
"HC" wrote:
> I think that question would be more appropriate in the sqlserver.programming
> group. With that said...
> Have you tried running that query in Query Analyzer?
> yeah...I don't think that Sql statement will ever run.
> from the Books Online for CREATE FUNCTION (transact SQL):
> "@.parameter_name
> Is a parameter in the user-defined function. One or more parameters can be
> declared.
> [...]
> Specify a parameter name by using an at sign (@.) as the first character. The
> parameter name must comply with the rules for identifiers. Parameters are
> local to the function; the same parameter names can be used in other
> functions. Parameters can take the place only of constants; they cannot be
> used instead of table names, column names, or the names of other database
> objects. "
> Soooo...assuming your user-defined function is a scalar-value function that
> returns field names and is owned by "dbo" schema, try using dynamic sql,
> something like this:
> DECLARE @.sql varchar(8000)
> SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
> SET @.sql= @.sql + ' FROM MyTable '
> SET @.sql= @.sql + ' WHERE ... '
> EXEC @.sql
> Give it a shot, and if I am way off...reply and let me know. Hope this help
> you.
> --
> Regards,
> Thiago Silva
> "Jouni" wrote:
> > Using Visual Studio to build reports for Reporting Services 2000: is it
> > allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> >
> > select function(@.variable) from table where ..
> >
> > I get "syntax error or access violation" when trying.
> >
> > Regards, Jouni