Customize parameters area

We want to include some explanatory text inside the parametersArea but can
find no way to do that. Is it possible to customize the parametersArea, or
to replace the default with a customized control of our own?No it is not possible on the parameter area, otherway round is to create your
own custom code, like anyother application and include RS 2005 web services
Amarnath
"Richard Rickard" wrote:
> We want to include some explanatory text inside the parametersArea but can
> find no way to do that. Is it possible to customize the parametersArea, or
> to replace the default with a customized control of our own?

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

CustomData on SQL Server connection string?

Analysis Services has a CustomData connection string property. You can put anything on it, then inside an MDX query you can use the CustomData() function to retrieve whatever was on the connection string. This is helpful for passing in a security token.

Is there anything equivalent for SQL Server?

I also posted this here, but got no response so thought I'd check in the security forum.

I'm not sure that there is a property which is exactly suited for passing custom user data, but there is a property which you might modify without affecting pretty much anything else.

The property is "Application Name" (SSPROP_INIT_APPNAME).

|||

I see where you're going with that, but is there a SQL function to detect the application name from the connection string of the currently connected person?

|||

Hi,

What you can do to get the application name provided in the property is to run a statement like this:

select program_name from master..sysprocesses where spid=@.@.spid

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

custom code

Is it possible to use aggregate functions inside custom code?Hi,
This is not directly suppotred by RS. There is an other way to do this. See
http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx for
instructions how to.
Jan Pieter Posthuma
"loginov" wrote:
> Is it possible to use aggregate functions inside custom code?
>
>|||Thanks, but this doesn't work for me :)
I try to implement nested datasets like
'=Max(IIF
(Cint(Fields("id").value)=Parameters!id.Value,Fields("title").value,""),
"dataset2")'
within "dataset1" and hide this inside my function. Another problem - RS
doesn't support nested aggregate functions :(
I have no words.
BTW do you know how to get PageNumber in the Body section?
"Jan Pieter Posthuma" <JanPieterPosthuma@.discussions.microsoft.com>
ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ:
news:9037C02C-9EA3-4808-AD98-C7B986430F85@.microsoft.com...
> Hi,
> This is not directly suppotred by RS. There is an other way to do this.
> See
> http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx for
> instructions how to.
> Jan Pieter Posthuma
> "loginov" wrote:
>> Is it possible to use aggregate functions inside custom code?
>>

Cursors inside SQL Functions - Very long execution time

I have a report that requires the use of cursors within a function because we
don't have control over the design of the database. So, the datasource for
the report is as follows:
1. Report calls a Stored Proc
2. Stored Proc runs this statement: Select... dbo.Function()...
3. Inside the Function(), I loop through a cursor.
Now when I run this Stored Proc directly in Query Analyzer, it takes 3
seconds.
But, when the report is run through SQL Reporting Services, it takes 108
seconds. I ran a SQL Profiler and noticed that Reporting Services was using
.Net Provider and the provider is way too slow for running cursors
- 2000ms for .Net Provider vs 0ms for Query Analyzer.
Is there anything I can do other than finding a way out of using cursors?Assuming that you call a stored procedure from RS it makes absolutely no
difference as far as executing the procedure. Stored procedures execute on
the server. It is not like the dotnet provider is opening a cursor, it is
not, it calls the stored procedure and gets back the results. What is most
likely happening is one of two things. Sometime there can be a delay due to
security authentication. More likely, however, is that you are returning a
lot of records and the rendering is what is taking the time. What are you
rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
than HTML. Are you using a filter? Filters bring over all the data and then
filters it. So, the key questions are, what are you rendering to and how
many records is the stored procedure returning.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
>I have a report that requires the use of cursors within a function because
>we
> don't have control over the design of the database. So, the datasource for
> the report is as follows:
> 1. Report calls a Stored Proc
> 2. Stored Proc runs this statement: Select... dbo.Function()...
> 3. Inside the Function(), I loop through a cursor.
> Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> seconds.
> But, when the report is run through SQL Reporting Services, it takes 108
> seconds. I ran a SQL Profiler and noticed that Reporting Services was
> using
> .Net Provider and the provider is way too slow for running cursors
> - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> Is there anything I can do other than finding a way out of using cursors?|||The number of records returned for the parameters I specified is only 69. And
I am rendering the report using the Reporting Server interface.
I may have solved this issue. I ran the Index Tuning Wizard on the query
used by the cursor and resulted in a 95% improvement. So that leads me to
think that when the cursor is run through Reporting Server, it did not use
the best execution plan. I'll do some more testing and see if that's it.
"Bruce L-C [MVP]" wrote:
> Assuming that you call a stored procedure from RS it makes absolutely no
> difference as far as executing the procedure. Stored procedures execute on
> the server. It is not like the dotnet provider is opening a cursor, it is
> not, it calls the stored procedure and gets back the results. What is most
> likely happening is one of two things. Sometime there can be a delay due to
> security authentication. More likely, however, is that you are returning a
> lot of records and the rendering is what is taking the time. What are you
> rendering to? Html, pdf, excel? PDF and Excel take much longer to render to
> than HTML. Are you using a filter? Filters bring over all the data and then
> filters it. So, the key questions are, what are you rendering to and how
> many records is the stored procedure returning.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sanjay Gandhi" <SanjayGandhi@.discussions.microsoft.com> wrote in message
> news:36286553-9F41-488F-BB24-4DC46C680168@.microsoft.com...
> >I have a report that requires the use of cursors within a function because
> >we
> > don't have control over the design of the database. So, the datasource for
> > the report is as follows:
> > 1. Report calls a Stored Proc
> > 2. Stored Proc runs this statement: Select... dbo.Function()...
> > 3. Inside the Function(), I loop through a cursor.
> >
> > Now when I run this Stored Proc directly in Query Analyzer, it takes 3
> > seconds.
> >
> > But, when the report is run through SQL Reporting Services, it takes 108
> > seconds. I ran a SQL Profiler and noticed that Reporting Services was
> > using
> > .Net Provider and the provider is way too slow for running cursors
> > - 2000ms for .Net Provider vs 0ms for Query Analyzer.
> >
> > Is there anything I can do other than finding a way out of using cursors?
>
>