Read only or hidden report parameters in Reporting Services

I have 3 parameters in my report page. I am setting 2 parameters in default from my aspx application for this report. So i dont want to show these 2 parameters when i execute my report. I tried rc:parameters = false in querystring which is just hiding the whole parameters. But is there a way to make the other 2 parameters readonly or hidden and show 1 parameter to take input when i execute the report.Try going to Report Manager portal, editing the report parameters, and setting visibility to false.
Alternatively, I think you can also do this if you remove the name of the parameter in the report designer.sql

READ COMMITTED SNAPSHOT ON causes performance degradation

I am running a benchmark test with multiple connections running the same
stored procedure with different parameters. This stored procedures does only
SELECT. There are no other activity on the database.
The stored procedure containst this select
SELECT Model,AVG(Price),MIN(Price),MAX(Price),COUNT(*)
FROM SH_Product
WHERE Project_Number = @.Station
AND EmployeeID = 0
AND Type = @.Match100
GROUP BY Model
ORDER BY Model
When the database is set in READ COMMITTED SNAPSHOT OFF mode, the number of
transactions per second increases linearly as more and more connections are
added.
But when the database is set to READ COMMITTED SNAPSHOT ON, the performance
degrades after 20 users, the total transactions processed per second remains
constant when number of users increase. That means for each user the
transactions per second reduces.
I can understand this if there was any other INSERT/UPDATE/DELETE activity
happening on the database, as SELECT will have to traverse the row version
chain to get the data, but in SELECT only environment, how can the
performance degrade.
With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
overhead for SQL Server. I have a PSS ticket open for this, but I am getting
a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
row version it is slower, but my point is if there is no data change why does
SQL Server has to go to tempdb?
Am I missing something?. Please help.
Thank youOn Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
(snip)
>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>happening on the database, as SELECT will have to traverse the row version
>chain to get the data, but in SELECT only environment, how can the
>performance degrade.
>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>overhead for SQL Server. I have a PSS ticket open for this, but I am getting
>a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
>row version it is slower, but my point is if there is no data change why does
>SQL Server has to go to tempdb?
Hi Shailesh,
I'm not intimately familiar with the internals of READ COMMITTED
SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
can't know that there are no previous row versions there without looking
first.
Have you considered setting the database to READ ONLY? That will fully
eliminate all locking overhead.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Thanks Hugo
I am seeing this behavior while running a benchmark, which has different
sets of tests, one of them being CPU intensive test which only does SELECT.
It is not on a real production database so putting database in READ ONLY
mode is not an issue, but I wanted to understand the performance issue
without doing it.
I looked at page file structure in Kalen Delaney's book and I don't see any
information about whether SQL server puts a status bit on the page itself
for locked rows. But with READ COMMITTED SNAPSHOT ON, SQL server puts a 14
byte data in each row to store Transaction Sequence number (XSN), it is only
added when the row is updated. So logically speaking when a connection tries
to SELECT from a row, it has a XSN and when it goes to check the row in disk
if there is no XSN field then it should immediately know that the row is not
modified and should not go to tempdb to check.
Even if there is XSN for the row, and if it's value is less than SELECT XSN
then it should check lock records before going to tempdb. And this overhead
is also incurred when database is in READ COMMITTED SNAPSHOT OFF mode. So I
don't really get why the performance suffers so much.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:m3oqf31go4ti3d37736nsegqp2patoqjin@.4ax.com...
> On Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
> (snip)
>>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>>happening on the database, as SELECT will have to traverse the row version
>>chain to get the data, but in SELECT only environment, how can the
>>performance degrade.
>>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>>overhead for SQL Server. I have a PSS ticket open for this, but I am
>>getting
>>a satisfactory answer. All I get is since SELECT needs to go to tempdb to
>>get
>>row version it is slower, but my point is if there is no data change why
>>does
>>SQL Server has to go to tempdb?
> Hi Shailesh,
> I'm not intimately familiar with the internals of READ COMMITTED
> SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
> can't know that there are no previous row versions there without looking
> first.
> Have you considered setting the database to READ ONLY? That will fully
> eliminate all locking overhead.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

rdlc show querystring parameter in page header

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

JIM_LANGDON wrote:

I pass in 3 querystring parameters to my web form. The Object Data Sources pick up these parameters

and select the appropriate records.

I want to display one of the querystring parameters in my Page Header, specifically the one for Fiscal Year.

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

How do I populate the report control textbox with the value of querystring parameter?

Thanks!

Jim

Are you using a report generator?

If not, then isn't this is as simple as setting the expression of the textbox to:

=Fields!FiscalYear.Value

|||

I don't know what you mean by "Report Generator"..isn't it SSRS?

I pass in the parameter via the call to the webform that contains the

reportviewer control -- webform.aspx?fy=2007.

The reportviewer control that is bound to the report.rdlc.

The RDLC has several datasources which show up as objectdatasource controls

on the web form. The objectdatasource controls are configured to use the appropriate

datasource method and the parameters are defined to use the querystring and "fy" as

the parameter name.

Doesn't the Fields! collection just have the datasource columns? I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

|||

JIM_LANGDON wrote:

I tried what you suggested

Fields!fy.Value, since the name in querystring is "fy"...no success.

This is probably because you need to specify the dataset on the textbox. Right click the textbox and select properties. In the "containing group or dataset" textbox, you need to either select or enter the dataset that contains 'fy'.

Basically, you can treat a textbox similarly to a table/matrix. It can have a dataset associated with it and you can populate the textbox with any value from the parameters or fields list.

|||

But the querystring does not belong to a "dataset"....so I can't reference it with Fields!fieldname. Hmmm.

|||

Isn't fiscal year one of your fields in a dataset? Aren't you trying to get fiscal year in a textbox?

Why do you want to touch a querystring?

|||

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned...Therefore, I must get the querystring parameter that was originally passed in...

The goal is to display a specific querystring parameter in the page header of the report.

|||

JIM_LANGDON wrote:

I could return the Fiscal Year in a column from the data source, but the Fiscal Year would not populate if

no records were returned

Yes, and you could conditionally specify what the Fiscal Year should default to if no records are returned.

If you'd like to pursue the querystring path to resolve this, feel free. I'm just somewhat confused with your reasoning.

|||

The Fiscal Year that is passed to the web form is 2006. If no Fiscal Year is passed, it will default to 2007. The records selected are based on the year that is passed in.

If I pass in the year 2006, I want the page header to display "Fiscal Year: 2006" and the body to display "No records found."

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

|||

JIM_LANGDON wrote:

It seems clear to me that the querystring is the only place to logically capture the Fiscal Year. Is there another method you had in mind to meet the goal or another way to do things to accomplish the same thing?

Maybe I just don't know what you mean by "querystring".

I think everything that you have mentioned so far can be accomplished by using a dataset (as either text or stored procedure).

|||

Sounds like what you have to do is one of the following:

add a new parameter to the report (with no valid values) and set the value of this parameter from the querystring. In your report textbox use Parameters!<your param name>.value to display the value

OR

create a new dataset in your report and load the querystring parameters into it in code, pass this additional dataset to the report

|||

The report page is initiated by a link <a>, html anchor tag.

<a href="report_web_form.aspx?fy=2006">Run This Report</a>

The href property assignment is what I mean by "querystring".

The dataset that is generated uses the 2006 value to determine what

records to select. If no records are returned, you cannot use the dataset to

get the value 2006. I don't see how this can be accomplished using a dataset.

|||

Thanks Adam..

From your first solution how do I "set the value of this parameter from the querystring". Where is that done. Are we talking about an assignment in the page load event of the report web form?

From your second solution how do I "load the querystring parameters into it in code"?

Sorry, I just need a bit more explanation.

|||

OK, here we go. The solution. Thanks to the exhausting yet always productive discussion today.

1. Define a report parameter in the report.rdlc:

Name: FISCAL_YEAR

Data Type: String

"Hidden" checkbox checked.

Default Values: "null" checkbox checked.

2. Place a text box in the page header of the report.rdlc with the value "=parameters!FISCAL_YEAR.Value".

3. Place the following code in the report web form:

Imports Microsoft.Reporting.WebForms

Partical Class reportWebForm

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim _fiscalYearParameter As New ReportParameter("FISCAL_YEAR", "2007")

_fiscalYearParameter.Values(0) = Request.QueryString("fy")

ReportViewer.LocalReport.SetParameters(New ReportParameter() {_fiscalYearParameter})

End Sub

RDLC Reporting Service - setting up query parameters - is this possible?

Hi, I have my RDLC report, called on a ReportViewer, which receives a parameter (id of a column to filter the data), and the report receives this parameter very well, however, I want to use the value of this parameter to a query parameter of the dataset. On VS2005 we can asociate data sets to a report, but not a report parameter to a query parameter...is there any way to make this work?

I've heard that this is not possible using client report and report viewer, as print button. If I use a server report, will I have the print button on my application available?If not, how can I present the report with the print button available?

Thanks a lot!

You must use a server report to use reporting services built in print functionality.

For the parameters, you can programaticallly declare an array of parameters and pass them to the report. The report has a parameter section in the report properties where you can map the parameters. Alternatively, you can use any "parameters" in the creation of your datasets and not pass any parameters to the report.

|||

dr_99:

You must use a server report to use reporting services built in print functionality.

For the parameters, you can programaticallly declare an array of parameters and pass them to the report. The report has a parameter section in the report properties where you can map the parameters. Alternatively, you can use any "parameters" in the creation of your datasets and not pass any parameters to the report.

Yes I'm sure about that, thanks a lot!Only one problem, on IE the print button appears, but on Firefox does not appear, has anyone ideia what can cause this?It is set to visible!Thanks!

rdlc parameters error

I have :

Dim params(0)As Microsoft.Reporting.WebForms.ReportParameterparams(0) =New Microsoft.Reporting.WebForms.ReportParameter("aa","47",False)

ReportViewer1.LocalReport.SetParameters(params)

ReportViewer1.ServerReport.Refresh()

but it make error :

An error occurred during local report processing.

Microsoft.Reporting.WebForms.LocalProcessingException was unhandled by user code
Message="An error occurred during local report processing."
Source="Microsoft.ReportViewer.WebForms"
StackTrace:
at Microsoft.Reporting.WebForms.LocalReport.CompileReport()
at Microsoft.Reporting.WebForms.LocalReport.SetParameters(IEnumerable`1 parameters)
at _Default.Page_Load(Object sender, EventArgs e) in E:\Moje dokumenty\Visual Studio 2005\WebSites\VB\AJAXEnabledWebSite5\Default.aspx.vb:line 13
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Hello,

Maybe u need to determine the process mode by

ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local

or add to

ReportViewer2.ReportPath = Path Of report & "Inv_DamageVoucher_Template_En"

ReportViewer2.SetParameter("<Perametername>", "<Parameter Value>")

ReportViewer2.Parameters = Microsoft.Samples.ReportingServices.ReportViewer.multiState.False

|||

I use it several times and it works fine the problem with the false parameters try use this

string strTime = System.DateTime.Now.ToShortTimeString();
RptParameters[0] =
 new Microsoft.Reporting.WebForms.ReportParameter("ReportParameterName",Value);
RptParameters[1]=
 new Microsoft.Reporting.WebForms.ReportParameter("ReportParameterName",Value);

http://forums.asp.net/p/1168478/1951923.aspx#1951923

RDLC Client Report and query parameters and print button

Hi, this is my first post here. I hope to be helpful trying to help and not only asking questions arround here. After I have my report ready I will share here the total experience from top to bottom!But for now, here's the issue:

I'm building a RDLC Repor on my ASP.Net VB web application. I added the .rdlc file to the application and created a table to show lines of data binded from a dataset. The thing is:

- The DataSet expects a parameter @.intNumber, a identifier to get the correct data to display the correct report.

- I'm using ReportViewer to view the report, and by code I've passed a Report Parameter to the *.RDLC report with success, just like this:

Dim parms(0) As ReportParameter
parms(0) = New ReportParameter("intNumber", 37)
ReportViewer1.LocalReport.SetParameters(parms)

The present issue is the following:
I want to use that parameter sent to the report to be sent to the query of the DataSet as parameter to the query to return the data to fill the report. I've heard that this is not possible, just with report server...

Another issue is the print button, also heard that only can appear on report server...no way to display and work on RDLC reports?Very confused right now...these issues are stupid, MS tools should allow these operations, which are not efficient if this is not possibla on RDLC...

In report viewer local mode, your application is loading the DataSet, not the Report Viewer. Therefore you need to supply the parameter when calling your TableAdapter's Fill method.

-Albert