Customise Report Parameters in Report Manager

Is it possible to add custom controls in for parameters in report manager?

I have a parameter that requires the user to click a button and select a bunch of things in another form. How can i do this in reporting services?

I also would like to change the layout of the parameters toolbar, ie. make text boxes smaller widths, show/hide parameters based on other parameter values.

I know i could write my own web page that does this and hide the parameters toolbar when they run the report, but this would mean that we lose other functionality that report manager has, eg. scheduling.

Does anyone know if customising the report manager will become available in a later version?

You're correct that you can't customize Report Manager like this in RS 2005. You're also correct that if you wrote your own app using the Reporting controls in VS 2005, you'd have a lot of flexibility.

As for whether you'll be able to do this customization of RM in a later version, I doubt it, but there is "talk" of shipping add'l VS Reporting controls for things like scheduling and security, so you could build your own RM implementation.

Customise Report Parameters in Report Manager

Is it possible to add custom controls in for parameters in report manager?

I have a parameter that requires the user to click a button and select a bunch of things in another form. How can i do this in reporting services?

I also would like to change the layout of the parameters toolbar, ie. make text boxes smaller widths, show/hide parameters based on other parameter values.

I know i could write my own web page that does this and hide the parameters toolbar when they run the report, but this would mean that we lose other functionality that report manager has, eg. scheduling.

Does anyone know if customising the report manager will become available in a later version?

You're correct that you can't customize Report Manager like this in RS 2005. You're also correct that if you wrote your own app using the Reporting controls in VS 2005, you'd have a lot of flexibility.

As for whether you'll be able to do this customization of RM in a later version, I doubt it, but there is "talk" of shipping add'l VS Reporting controls for things like scheduling and security, so you could build your own RM implementation.

Custom Auditing

We are having to implement restrictive security permissions on SQL, some of
which requires that we audit and log any access of any kind for specific
tables and columns. What would be the best way to do this? We can use
triggers to log any changes to specific tables and columns, but we can't use
those when a user is simply retrieving sensitive and encrypted data from a
table. We could lock down access permissions to those tables so that only
authorized users can execute stored procedures, and have code to log such
access in the procs themselves, but we need to ensure that even if the dbo
or sa were to access that data, that it would be logged.
TIA
Michael MacGregor
Database ArchitectMichael,
If you want to log accesses, even of the dbo or sa, then you pretty much
will have to use Profiler. You can put the traces into tables for online
auditing, either directly or by periodically importing the trace files.
There are also probably some third-party auditing tools.
RLF
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OyGkG4tyHHA.3536@.TK2MSFTNGP06.phx.gbl...
> We are having to implement restrictive security permissions on SQL, some
> of which requires that we audit and log any access of any kind for
> specific tables and columns. What would be the best way to do this? We can
> use triggers to log any changes to specific tables and columns, but we
> can't use those when a user is simply retrieving sensitive and encrypted
> data from a table. We could lock down access permissions to those tables
> so that only authorized users can execute stored procedures, and have code
> to log such access in the procs themselves, but we need to ensure that
> even if the dbo or sa were to access that data, that it would be logged.
> TIA
> Michael MacGregor
> Database Architect
>|||Thanks again Russell.
MTM

Custom Assembly

When I execute my report on my web server, I can see the data that requires a
query to the custom assembly. I have got "#Error".
When I execute my report in the Preview window my date is present.
My Assembly is reference to the C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\MyAssembly.dll is defined on My
Reports
I put my custom Assembly in the forlder C:\Program Files\Microsoft SQL
Server\80\Tools\Report Designer and in C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer.
I added the <CodeGroup> to C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\rssrvpolicy.config :
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\MyAssembly.dll"
/>
</CodeGroup>
Can you help me ?You should throw your assembly into C:\Program Files\Microsoft
SQL\Server\MSSQL\Reporting Services\ReportServer\bin as well. Try that.
Also, are you referencing shared or nonshared methods on the assembly?
Carolyn|||My assembly is allready into
C:\Program Files\Microsoft SQL\Server\MSSQL\Reporting
Services\ReportServer\bin
and into C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\bin
The method on the assembly is shared.
When I execute my report on the preview window, all is good.
When I execute my report on my web site, I have got "#Error"
"Carolyn Vo" wrote:
> You should throw your assembly into C:\Program Files\Microsoft
> SQL\Server\MSSQL\Reporting Services\ReportServer\bin as well. Try that.
> Also, are you referencing shared or nonshared methods on the assembly?
> Carolyn|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_rdl_8mue.asp
"Karine" <Karine@.discussions.microsoft.com> wrote in message
news:FFBDC97F-523F-42E7-BA80-C777457F8980@.microsoft.com...
> My assembly is allready into
> C:\Program Files\Microsoft SQL\Server\MSSQL\Reporting
> Services\ReportServer\bin
> and into C:\Program Files\Microsoft SQL Server\80\Tools\Report
> Designer\bin
> The method on the assembly is shared.
> When I execute my report on the preview window, all is good.
> When I execute my report on my web site, I have got "#Error"
>
> "Carolyn Vo" wrote:
>> You should throw your assembly into C:\Program Files\Microsoft
>> SQL\Server\MSSQL\Reporting Services\ReportServer\bin as well. Try that.
>> Also, are you referencing shared or nonshared methods on the assembly?
>> Carolyn|||I read this doc and I try to do what is writen on it ... but i only have
#error.
I hope that i do something bad but i don't know what.
- My assembly is define on y report.
- I copy my dll on my WebServer : C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin
- I try to modify my modify the file : rssrvpolicy.config : I add :
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyCustomAssemblyCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\MyAssembly.dll"
/>
</CodeGroup>
I don't know if i put this code on the right place.
Can you help me more ?
"Yoshi" wrote:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_rdl_8mue.asp
>
>
> "Karine" <Karine@.discussions.microsoft.com> wrote in message
> news:FFBDC97F-523F-42E7-BA80-C777457F8980@.microsoft.com...
> > My assembly is allready into
> > C:\Program Files\Microsoft SQL\Server\MSSQL\Reporting
> > Services\ReportServer\bin
> > and into C:\Program Files\Microsoft SQL Server\80\Tools\Report
> > Designer\bin
> >
> > The method on the assembly is shared.
> > When I execute my report on the preview window, all is good.
> > When I execute my report on my web site, I have got "#Error"
> >
> >
> > "Carolyn Vo" wrote:
> >
> >> You should throw your assembly into C:\Program Files\Microsoft
> >> SQL\Server\MSSQL\Reporting Services\ReportServer\bin as well. Try that.
> >> Also, are you referencing shared or nonshared methods on the assembly?
> >>
> >> Carolyn
>
>

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?
>
>