ASP.Net

Looking for some good examples of how folks used ASP.Net to pass in
parameters to SQL2005 Reporting Services - not liking how the parameters
have to be coded within the designer itself.We used a single string parameter and embedded an XML document within
that, translating it in a wrapping stored procedure to be the
parameters required by the real sp on the database.
This allowed us to do things like data driven parameters to the report
(in the ASP.Net web application we wrote as the front end)
Cheers
Greg
Joe wrote:
> Looking for some good examples of how folks used ASP.Net to pass in
> parameters to SQL2005 Reporting Services - not liking how the parameters
> have to be coded within the designer itself.|||I use stored procedures as the datasource and set the parameters with the
procedure variables. Using report parameters takes way too long on large data
sets from what I have experienced. All the data is loaded before the
filtering takes place (from what I was told).
"Joe" wrote:
> Looking for some good examples of how folks used ASP.Net to pass in
> parameters to SQL2005 Reporting Services - not liking how the parameters
> have to be coded within the designer itself.
>
>

AS/400 Parameters

Connected to AS/400 server through ODBC. Can submit queries, just built a matrix report, looks good.

However I want to incorporate a Parameter and have tried prefacing the parameter COSTCENTER with everything under the sun:

@.COSTCENTER (SQL Server version of parameter declaration)

:COSTCENTER (Oralce version of parameter declaration)

%COSTCENTER

#COSTCENTER

etc. etc.

I keep getting the same error:

Error in WHERE clause near '@.'. Unable to parse query text.

My inquiries are as follows:

1. Can you use parameters with an AS/400 connection?

2. If yes, howshould the parameter be declared?

I searched BOL, GOOGLE, so far nothing...

Which ODBC driver are you using?

AS/400 Parameters

Connected to AS/400 server through ODBC. Can submit queries, just built a matrix report, looks good.

However I want to incorporate a Parameter and have tried prefacing the parameter COSTCENTER with everything under the sun:

@.COSTCENTER (SQL Server version of parameter declaration)

:COSTCENTER (Oralce version of parameter declaration)

%COSTCENTER

#COSTCENTER

etc. etc.

I keep getting the same error:

Error in WHERE clause near '@.'. Unable to parse query text.

My inquiries are as follows:

1. Can you use parameters with an AS/400 connection?

2. If yes, howshould the parameter be declared?

I searched BOL, GOOGLE, so far nothing...

Which ODBC driver are you using?

AS Cube as DataSet for RS Multi-Value & Range Parameters?

Platform: All SQL '05 Std

I'm struggling with two issues involving the use of AS Cube data as parameters in a SSRS Report.

Background: I've built a report exclusively using an AS cube as it's data source, including the source for multi-value and range parameters. As a note, the cube is complex and at least one of the dimensions is large (~10,000 attribute values). Lastly, the date field being used is a "date time" datatype (more on this in a moment).

The two issues are...

(1) Even after initial caching (both in VS and as deployed to Report Mgr), the entry of all parameter values suffers long delays (~40 seconds) during apparent data refreshing of tyhe small dimensions, and > 60 seconds for the large dimension, in the parameter list. If possible for the large dimension, I'd like to create a separate lookup table for the large dimension, but it does not look straightforward when my data source is the OLAP cube. QUESTION: Is this possible to do, and/or is there a better way?

(2) My date dimension field that records the actual date (ig. yyyy-mm-dd) also contains, as a date-time datatype field, the HH:MM:SS values (hh:mm:ss is all zero's, by the way). In other words, the values look like yyyy-mm-dd hh:mm:ss . Anyway, SSRS is throwing an error on the " : " (colon) symbols. Of course, if this field were being used as an output field, I could just change the field format in SSRS, but it's not output, it's an input parameter, and I don't know how it's possible to alter formatting on such a field. In the VS preview, the following error appers...

"An error occurred during local report processing. An error has occurred during report processing. Query execution failed for data set "FromDimSvcDateFullDate". Range operator (:) operands have different levels; they must be the same"

QUESTIONS: (a) Is it possible, either in SSAS or in SSRS, to alter formatting on the date field to remove the hh:mm:ss info? (b) What other solutions are suggested here?

Note: If I have to go back to my source data and change the data type, I'm afraid that my OLAP report (and others) will no longer function.

Note: Nobody on the SSRS thread is apparently able to provide feedback on this SSAS / SSRS issue.

(1): To isolate whether the delays are occurring in MSAS, the report (or both), you can trace MDX query execution times for SSAS by using SQL Profiler. If some of the MDX parameter queries are taking too long, maybe those could be tuned.

(2): What does the MDX query for the FromDimSvcDateFullDate data set look like - typically, there will be calculated measures defined, like ParameterCaption and ParameterValue? You should then be able to edit the definition appropriately to customize the data returned, using theVBA data coversion functions supported in MDX.

|||

The reason you were getting this error:

"An error occurred during local report processing. An error has occurred during report processing. Query execution failed for data set "FromDimSvcDateFullDate". Range operator (:) operands have different levels; they must be the same"

is most likely because you picked different Levels of dates for the FROM and TO in reporting service: Ie: "2004Q1" and "2006". It should have nothing to do with the colons in the dates. Picking 2004-01-01 00:00:00 to 2004-01-03 00:00:00 doesn't render an error.

I found this post when I was looking for a clean way around this or a way to give the user a more friendly message in this case... I'm still looking though :)

|||Thank you for responding.

AS Cube as DataSet for RS Multi-Value & Range Parameters?

Platform: All SQL '05 Std

I'm struggling with two issues involving the use of AS Cube data as parameters in a SSRS Report.

Background: I've built a report exclusively using an AS cube as it's data source, including the source for multi-value and range parameters. As a note, the cube is complex and at least one of the dimensions is large (~10,000 attribute values). Lastly, the date field being used is a "date time" datatype (more on this in a moment).

The two issues are...

(1) Even after initial caching (both in VS and as deployed to Report Mgr), the entry of all parameter values suffers long delays (~40 seconds) during apparent data refreshing of tyhe small dimensions, and > 60 seconds for the large dimension, in the parameter list. If possible for the large dimension, I'd like to create a separate lookup table for the large dimension, but it does not look straightforward when my data source is the OLAP cube. QUESTION: Is this possible to do, and/or is there a better way?

(2) My date dimension field that records the actual date (ig. yyyy-mm-dd) also contains, as a date-time datatype field, the HH:MM:SS values (hh:mm:ss is all zero's, by the way). In other words, the values look like yyyy-mm-dd hh:mm:ss . Anyway, SSRS is throwing an error on the " : " (colon) symbols. Of course, if this field were being used as an output field, I could just change the field format in SSRS, but it's not output, it's an input parameter, and I don't know how it's possible to alter formatting on such a field. In the VS preview, the following error appers...

"An error occurred during local report processing. An error has occurred during report processing. Query execution failed for data set "FromDimSvcDateFullDate". Range operator (:) operands have different levels; they must be the same"

QUESTIONS: (a) Is it possible, either in SSAS or in SSRS, to alter formatting on the date field to remove the hh:mm:ss info? (b) What other solutions are suggested here?

Note: If I have to go back to my source data and change the data type, I'm afraid that my OLAP report (and others) will no longer function.

Note: Nobody on the SSRS thread is apparently able to provide feedback on this SSAS / SSRS issue.

(1): To isolate whether the delays are occurring in MSAS, the report (or both), you can trace MDX query execution times for SSAS by using SQL Profiler. If some of the MDX parameter queries are taking too long, maybe those could be tuned.

(2): What does the MDX query for the FromDimSvcDateFullDate data set look like - typically, there will be calculated measures defined, like ParameterCaption and ParameterValue? You should then be able to edit the definition appropriately to customize the data returned, using theVBA data coversion functions supported in MDX.

|||

The reason you were getting this error:

"An error occurred during local report processing. An error has occurred during report processing. Query execution failed for data set "FromDimSvcDateFullDate". Range operator (:) operands have different levels; they must be the same"

is most likely because you picked different Levels of dates for the FROM and TO in reporting service: Ie: "2004Q1" and "2006". It should have nothing to do with the colons in the dates. Picking 2004-01-01 00:00:00 to 2004-01-03 00:00:00 doesn't render an error.

I found this post when I was looking for a clean way around this or a way to give the user a more friendly message in this case... I'm still looking though :)

|||Thank you for responding.

article row filter - 2 parameters

hello,

i need to filter an article based on a user-supplied datetime filter (the datetime parameter is specified by the subscriber just before replication). at the same time i need to filter again by user (different subscribers get different rows).

i already did the user-based filter using HOST_NAME( ). but the difficulty here (al least i think so) lies in passing 2 parameters to the filter. i cannot rely on using SUSER_SNAME to pass the user filter, because no one will want to create 500 user accounts. so i guess the only solution here is to pass both parameters using only HOST_NAME( ) and then write 2 splitting functions which uses HOST_NAME( ) as its parameter. am i right ?

publisher/distributor is sql server 2005, all subscribers use sql mobile.

TIA, kamil nowicki

Using datetime would not be recommended since it is not deterministic.

For eg:

lets say you have a filter to get rows being touched only in the last 7 days.

Initially you get all rows.

After 7 days having not touched any rows, you would expect the merge agent deletes the 7 rows which will not be the case.

I would advise you to use SUSER_SNAME() or HOST_NAME() to get specific rows and in addition use another column 'status' or something that you can set/reset according to your business needs.

|||

thanks for replying.

i think that i have to use a datetime filter because the parameter to this filter has to be dynamic (worst case scenario: each subscriber uses different filter parameter for each of his replication sessions). is there another way to accomplish this ?

also, is there a way to store something in SUSER_SNAME() like using SqlCeReplication.HostName to store something into HOSTNAME() ?

|||

If each session of the subscriber uses a different filter, you will get an error with mismatched partitions. You would need to reinitialize the subscriber in that case. Are you ready to reinitialize the subscribers for every sync?

But note that as I mentioned previously, you will not be able to rely on datetime filter. Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

|||

>> Use a status column or something like that and update this column when you want rows to be in partition or out of partition.

but that would mean that i have to use a "fixed filter" (same "replicate from ..." date for every subscriber), wouldn't it ?

|||How about: login=SUSER_SNAME and status='Y'|||

We have a best practice article for time-based filtering, you may want to reference it, it's what Mahesh is talking about:

Best Practices for Time-Based Row Filters

http://msdn2.microsoft.com/en-us/library/ms365153.aspx

|||

and how does that solve my scenario ? i want each subscriber to be able to choose a date ( "replicate from..." ) before each of his replication sessions. SP on the server will not know those dates when executed, so how am i supposed to update the "status" column ?

thanks for the link, i have read that before starting the thread.

|||

Are you saying that every time the subscriber syncs, it sends a new date and expects only releant rows? You cannot achieve this using the non-deterministic filter functions. If you are ready to reinitialize your subscriptions every time you sync, you may do that. Do a reinit on the publisher/subscriber, then send in the appropriate date as the hostname to get the relevant rows. However you wont be able to upload in this session because your filters may not match data that you want to upload.

|||

i took a different approach and now everything is working as it should. i wrote a SP on the backend server which updates every row in the filtered article for a given user (SP is parametrized with @.user nvarchar and @.filter datetime), changing rep_status tinyint column. the SP is called from subscriber on the distributor just before each replication session, so now the SP has all the info to update the filtered article (@.user and @.filter). article is filtered by rep_status.

the drawback is that the backend has to be put out of LAN to the internet (public IP), but later i will write a WebService so that all the data will pass through IIS and then the backend will be again NATed.

edit: and of course this works without subscription reinitialization :)

Arrays in a Stored Procedure

Can any one help me with a sample code, which can take an array of

elements as one of it's parameters and get the value inserted into a table in a

stored procedure.

Thanks in advance

vnswathi.

SQL dosen't support array, so how do you want to passing an 'array' of elements as parameters to a stored procedure? Maybe you can use a string that delimits the elements of an array by some character(s) and then split the string into a set of values that can be inserted into a table. If so, the key point is how to split the string with specific delimitor, and you can take a look at this post:

http://forums.asp.net/thread/1300724.aspx

Array parameters in stored procedures in SQL 2005?

Hi,
I am not sure if I have understood what is happening with the new SQL server
2005.
Will we be using .Net classes instead of stored procedures?
Will I be able to use an array or list type of parameter for my queries with
SQL server 2005? If yes, how would I do this?
Thanks,
Morten> Will we be using .Net classes instead of stored procedures?
Well this is an option available with us. Not that this is the only way. The
T-SQL style still exists nevertheless.

> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
AFAIK, this is still not possible.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Morten" <morten@.imano.nospam> wrote in message
news:%23L9KEr6AFHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am not sure if I have understood what is happening with the new SQL
server
> 2005.
> Will we be using .Net classes instead of stored procedures?
> Will I be able to use an array or list type of parameter for my queries
with
> SQL server 2005? If yes, how would I do this?
>
> Thanks,
> Morten
>|||>> Will I be able to use an array or list type of parameter for my
queries with SQL server 2005? <<
The short answer is no. But the real qustion is why do you want to
make SQL less relational instead of more standardized?
The way this is done in Standard SQL is with a table constructor:
BEGIN
DELETE FROM Parmlist;
INSERT INTO Parmlist
VALUES (a1), (a2), .., (an);
CALL Foobar (...);
END:
Then you use the parameter table in the query or other statement in the
usual manner.

Array Parameter

Is there any way to make a CLR stored procedure that accepts an array style set of data? I want to make a stored procedure that accepts to parameters of type int, and then one more that is an array of name/value pairs. Is it possible to do something like this?

Yes, there is. In fact there is a sample at http://www.codeplex.com/MSFTEngProdSamples called "Array Parameter" which you can browse or download along with the other engine database samples. This sample contains code for passing an array to a CLR stored procedure using a CLR UDT. But be careful in SQL Server 2005 as you are limited in size to 8000 bytes. A future version of SQL Server is expected to relax that constraint. The other option which doesn't have that problem is to encode your data in XML and pass it using an XML parameter and rehydrate the objects in the CLR stored procedure.

Array As parameters to procedure

Hi friends,
Please help me I want to pass an Aarry parameters from asp.net to sqlserver stored procedure. Is it possible, if yes how.

regards,
Asad Mahmood

Hi,

SQL Server doesn't seem to have any array-like parameter so what you can do is that create a CSV (comma separated values) out from your data and pass that to the procedure (which could take that as varchar(8000) etc depending on the needed length).

In the proc you could parse this CSV into say a temp table containing values as integers, if you use a function.Here is an example of such function.

|||It will be easier if you are using Arraylist but try this link for how to do it. Hope this helps.
http://www.sommarskog.se/arrays-in-sql.html

Argument not specified for parameters error

hello, I am doing some tutorials to learn SQL reporting services. One of the tutorials is using asp to call a web services and blah blah etc...
Basically the report opens with two calenders and an execute button. the user would pick two dates and then hit execute.

the code is this:

Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim report As Byte() = Nothing
'Create an instance of the Reporting Services
'Web Reference
Dim rs As localhost.ReportingService = New localhost.ReportingService
'Create the credentials that will be used when accessing
'Reporting Services. This must be a logon that has rights
'to the Axelburg Invoice-Batch Number Report.
'***Replace "LoginName", "Password", and "Domain" with
'the appropriate values. ***
rs.Credentials = New _
System.Net.NetworkCredential("Administrator", _
"password", "localhost")
rs.PreAuthenticate = True
'The Reporting Services virtual path to the report.
Dim reportPath As String = _
"/Galactic Delivery Services/Axelburg/Invoice-Batch Number"
' The rendering format for the report.
Dim format As String = "html4.0"
'The devInfo string tells the report viewer
'how to display with the report.
Dim devInfo As String = _
"<DeviceInfo>" + _
"<Toolbar>False</Toolbar>" + _
"<Parameters>False</Parameters>" + _
"<DocMap>True</DocMap>" + _
"<Zoom>100</Zoom>" + _
"</DeviceInfo>"
'Create an array of the values for the report parameters
Dim parameters(1) As localhost.ParameterValue
Dim paramValue As localhost.ParameterValue _
= New localhost.ParameterValue
paramValue.Name = "StartDate"
paramValue.Value = calStartDate.SelectedDate
parameters(0) = paramValue
paramValue = New localhost.ParameterValue
paramValue.Name = "EndDate"
paramValue.Value = calEndDate.SelectedDate
parameters(1) = paramValue
'Create variables for the remainder of the parameters
Dim historyID As String = Nothing
Dim Credentials() As localhost.DataSourceCredentials = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings() As localhost.Warning = Nothing
Dim reportHistoryParameters() As _
localhost.ParameterValue = Nothing
Dim StreamIDs() As String = Nothing
Dim sh As localhost.SessionHeader = _
New localhost.SessionHeader
rs.SessionHeaderValue = sh
Try
'Execute the report.
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)
sh.SessionId = rs.SessionHeaderValue.SessionId
'Flush any pending responce.
Response.Clear()
'Set the Http headers for a PDF responce.
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response().ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
' filename is the default filename displayed
'if the user does a save as.
HttpContext.Current.Response.AppendHeader( _
"Content-Disposition", _
"filename=""Invoice-BatchNumber.HTM""")
'Send he byte array containing the report
'as a binary response.
HttpContext.Current.Response.BinaryWrite(report)
HttpContext.Current.Response.End()
Catch ex As Exception
If ex.Message <> "Thread was being aborted." Then
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write( _
"<HTML><BODY><H1>Error</H1><br><br>" & _
ex.Message & "</BODY></HTML>")
HttpContext.Current.Response.End()
End If
End Try
End Sub
End Class

This is the area underlined as failing:
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)

The errors all pretty much go like this:
c:\inetpub\wwwroot\AxelburgFrontEnd\ReportFrontEnd.aspx.vb(95): Argument not specified for parameter 'ParametersUsed' of 'Public Function Render(Report As String, Format As String, HistoryID As String, DeviceInfo As String, Parameters() As localhost.ParameterValue, Credentials() As localhost.DataSourceCredentials, ShowHideToggle As String, ByRef Encoding As String, ByRef MimeType As String, ByRef ParametersUsed() As localhost.ParameterValue, ByRef Warnings() As localhost.Warning, ByRef StreamIds() As String) As Byte()'.

I searched around, but didn't really understand what these errors mean. Any help is greatly appreciated.Anybody? Is this that hard?

Are Tooltips on Report Parameters Possible?

Dear Anyone,

Are Tooltips on Report Parameters Possible? Somehow, only the controls in the report itself have tool tips. It would be nice if tool tips will also be available for Report Parameters.

Thanks,

Joseph

Custom tooltips for the parameter prompts is not currently supported. You would need to write a custom UI to do this.

Are there example for cascading parameters

I want to use cascading parameters for my report.
Are there example for cascading parameters?Yes you can find an example here:
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_5
Im sure that if u search on google you will find more.
regards.
"ad" wrote:
> I want to use cascading parameters for my report.
> Are there example for cascading parameters?
>
>|||Yes, you can find an example here:
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_5
I'm sure there are more if you do a search on google.
regards,
KS
"ad" wrote:
> I want to use cascading parameters for my report.
> Are there example for cascading parameters?
>
>|||Thank,
I had read the article, but the example can run correctly.
I thought maybe the author forget to modify the mdx for ProductData dataset:
----
--
SELECT { [Measures].[Store Sales], [Measures].[Store Cost] } ON COLUMNS,
{ Descendants([Product].[All Products], [Product].[Brand Name], LEAVES) }
ON ROWS,
{ Time.[1997].[Q1],Time.[1997].[Q2],Time.[1997].[Q3],Time.[1997].[Q4] } ON
PAGES
FROM Sales
WHERE ([Store].[All Stores].[USA].[OR].[Portland].[Store 11])
----
--
How can I modify the mdx for ProductData, so that it can link to the
cascading parameters?
"saleek" <saleek@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:9224052F-9E41-4C1B-8791-DF46A7054FCD@.microsoft.com...
> Yes, you can find an example here:
> http://www.databasejournal.com/features/mssql/article.php/10894_3386441_5
> I'm sure there are more if you do a search on google.
> regards,
> KS
> "ad" wrote:
> > I want to use cascading parameters for my report.
> > Are there example for cascading parameters?
> >
> >
> >|||Thank,
I can't find any other example aobut cascading parameters of Reporting
Servicing.
What are the keywords to serach that?
"saleek" <saleek@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:D3CB4C75-8FD7-4864-BFBD-6F299721A7F7@.microsoft.com...
> Yes you can find an example here:
> http://www.databasejournal.com/features/mssql/article.php/10894_3386441_5
> Im sure that if u search on google you will find more.
> regards.
> "ad" wrote:
> > I want to use cascading parameters for my report.
> > Are there example for cascading parameters?
> >
> >
> >|||An explanation of usage is aslo here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_interactive_v1_50fn.asp
"ad" wrote:
> Thank,
> I can't find any other example aobut cascading parameters of Reporting
> Servicing.
> What are the keywords to serach that?
> "saleek" <saleek@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :D3CB4C75-8FD7-4864-BFBD-6F299721A7F7@.microsoft.com...
> > Yes you can find an example here:
> > http://www.databasejournal.com/features/mssql/article.php/10894_3386441_5
> >
> > Im sure that if u search on google you will find more.
> >
> > regards.
> >
> > "ad" wrote:
> >
> > > I want to use cascading parameters for my report.
> > > Are there example for cascading parameters?
> > >
> > >
> > >
>
>

Are report parameters cached in the report server?

Dear Anyone,

I would like to ask if the report parameters in RS2005 are cached in the server. Im worried about this because each of our reports has an average of 6 report parameters. Each of which uses 2 data sets. 1 for retrieving the list of values and the other for retrieving the default values. Each report parameters are multi-select and almost all of them will contain not less than 500 selections. We have a very significant number of users and my worry is that this might bog down on our database server which is located on a seperate box.

Any opinions or clarifications anyone?

Thanks,

Joseph

No, the results of parameter queries are not cached.|||

One problem that we're looking at if the report parameters are not cached is the strain thta it will put on our database server.

We have several reports that has an average of 10 report parameters. Each of which has 2 data sets that calls upon stored procedures for their content. Some of this report parameters have dependencies with eachother.

Do you have any suggestions on how we can optimize the performance of the report parameters so that it would not affect the performance of our database server?

|||

1 .Create a custom "parameter picker" webform (ASP.NET 2.0) for your report which utilizes SQL Cache Notification -- Essentially, your ASP.NET app will hit the various parameter value lookup tables in SQL once and store the results in session, only returning to hit the SQL box again if the values in the tables change (and normally these values remain pretty static).

2. After your user has used your "custom" parameter picker to select values, either create a URL Access string and render the report, or utilize the ReportViewer control and programatically set the parameter values before rendering.

|||

I am wondering and confused why the developers of Reporting Services (even since the 2000 version) did not inlcude any cache option the report parameters.

Its so ovious that several report parameters can be used in a report and each of them can use 2 different data sets. This would always translate to several database hits especially of there are parameter dependencies.

I hope this is a feature that will be included in the next version of Reporting Services or maybe the next service pack. I like Reporting Services alot but not having any cache option in the report parameters really stinks.

|||

I don't think we anticipated that parameter queries would be costly, so we didn't think caching them would be a significant benefit. Our intended usage was that you would run a simple query to bring back 10 or 20 valid values for the parameter.

Thanks for the feedback, we'll consider this for a future version. It's always interesting to find out how customers end up using the features!

|||

I think we found a viable alternative to the caching of several report parameters.

All we need to do is put the actual parameters (id and values) into a single table. Since all parameters will not have equal numbers, we would just need to fill them with NULLS. We would only need one data set to call this table. We would also need to set our report parameters not to allow null report values.

I think with this, we can survive without having parameter cache for quite a while... assuming we dont have several cascading parameters which this workaround is totally not applicable.

|||

This would look like this kind of a table

ParamID1 ParamValue1 ParamID2 ParamValue2 ParamID3 ParamValue3

a d f f j j

b e g g k k

c NULL h h l l

NULL NULL i i NULL NULL

if the report parameters that will use this single data set is set to not to allow nulls, we will be able to use a single data set to power several report parameters.

Are report parameters cached in the report server?

Dear Anyone,

I would like to ask if the report parameters in RS2005 are cached in the server. Im worried about this because each of our reports has an average of 6 report parameters. Each of which uses 2 data sets. 1 for retrieving the list of values and the other for retrieving the default values. Each report parameters are multi-select and almost all of them will contain not less than 500 selections. We have a very significant number of users and my worry is that this might bog down on our database server which is located on a seperate box.

Any opinions or clarifications anyone?

Thanks,

Joseph

No, the results of parameter queries are not cached.|||

One problem that we're looking at if the report parameters are not cached is the strain thta it will put on our database server.

We have several reports that has an average of 10 report parameters. Each of which has 2 data sets that calls upon stored procedures for their content. Some of this report parameters have dependencies with eachother.

Do you have any suggestions on how we can optimize the performance of the report parameters so that it would not affect the performance of our database server?

|||

1 .Create a custom "parameter picker" webform (ASP.NET 2.0) for your report which utilizes SQL Cache Notification -- Essentially, your ASP.NET app will hit the various parameter value lookup tables in SQL once and store the results in session, only returning to hit the SQL box again if the values in the tables change (and normally these values remain pretty static).

2. After your user has used your "custom" parameter picker to select values, either create a URL Access string and render the report, or utilize the ReportViewer control and programatically set the parameter values before rendering.

|||

I am wondering and confused why the developers of Reporting Services (even since the 2000 version) did not inlcude any cache option the report parameters.

Its so ovious that several report parameters can be used in a report and each of them can use 2 different data sets. This would always translate to several database hits especially of there are parameter dependencies.

I hope this is a feature that will be included in the next version of Reporting Services or maybe the next service pack. I like Reporting Services alot but not having any cache option in the report parameters really stinks.

|||

I don't think we anticipated that parameter queries would be costly, so we didn't think caching them would be a significant benefit. Our intended usage was that you would run a simple query to bring back 10 or 20 valid values for the parameter.

Thanks for the feedback, we'll consider this for a future version. It's always interesting to find out how customers end up using the features!

|||

I think we found a viable alternative to the caching of several report parameters.

All we need to do is put the actual parameters (id and values) into a single table. Since all parameters will not have equal numbers, we would just need to fill them with NULLS. We would only need one data set to call this table. We would also need to set our report parameters not to allow null report values.

I think with this, we can survive without having parameter cache for quite a while... assuming we dont have several cascading parameters which this workaround is totally not applicable.

|||

This would look like this kind of a table

ParamID1 ParamValue1 ParamID2 ParamValue2 ParamID3 ParamValue3

a d f f j j

b e g g k k

c NULL h h l l

NULL NULL i i NULL NULL

if the report parameters that will use this single data set is set to not to allow nulls, we will be able to use a single data set to power several report parameters.

are parameters more like filters than conditions?

Hello all
Am I missing a trick or does there seem to be a drawback in using parameters
in Reporting services. They do not act the same way as for example a prompt
in business objects . When a user specifies a value in a prompt in BO this
will alter the Where clause of the sql behind the report- therefore this will
usually decrease the amount of time the report takes to run.
However I have just created a parameter in RS and far from decreasing the
amount of time the report takes to run it increases it. It seems that it
acts as a filter on the sql already generated. It is bringing back all
values before restricting it. Am i doing something wrong? or is a parameter
more like a filter than a condition? If so - how do i create a condition
that the user can fulfill? arrgghh!!! lol - gregI think you hit the nail on the head. Parameters in Rpt Services are like
filters. My understanding is that regardless of the parameter/filter
setting, all of the data is returned, then filtered after the fact.
Now that may ot be true if you use parameters in your SQL dataset itself.
If your dataset calls a stored proc that takes in parameters directly that
would clearly limit the resultset and return less data. It would have to.
Another nice benefit of using a stored proc with it's own parameters is that
if you define your dataset this way in the wizard, Rpt Services will
automatically recognize that your dataset has parameters and include them in
the RDL as report parameters. Might be worth a try.
sebring1130
"Greg" wrote:
> Hello all
> Am I missing a trick or does there seem to be a drawback in using parameters
> in Reporting services. They do not act the same way as for example a prompt
> in business objects . When a user specifies a value in a prompt in BO this
> will alter the Where clause of the sql behind the report- therefore this will
> usually decrease the amount of time the report takes to run.
> However I have just created a parameter in RS and far from decreasing the
> amount of time the report takes to run it increases it. It seems that it
> acts as a filter on the sql already generated. It is bringing back all
> values before restricting it. Am i doing something wrong? or is a parameter
> more like a filter than a condition? If so - how do i create a condition
> that the user can fulfill? arrgghh!!! lol - greg
>|||I'm looking at a SQL Profiler trace of RS executing a report and it is
sending a parameratized query to SQL via sp_executesql.
What is the relative time increase?
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Tue, 9 Nov 2004 08:29:01 -0800, "Greg"
<Greg@.discussions.microsoft.com> wrote:
>Hello all
>Am I missing a trick or does there seem to be a drawback in using parameters
>in Reporting services. They do not act the same way as for example a prompt
>in business objects . When a user specifies a value in a prompt in BO this
>will alter the Where clause of the sql behind the report- therefore this will
>usually decrease the amount of time the report takes to run.
>However I have just created a parameter in RS and far from decreasing the
>amount of time the report takes to run it increases it. It seems that it
>acts as a filter on the sql already generated. It is bringing back all
>values before restricting it. Am i doing something wrong? or is a parameter
>more like a filter than a condition? If so - how do i create a condition
>that the user can fulfill? arrgghh!!! lol - greg|||No, you are definitely wrong here. I go against tables with between 1
million and 10 million rows and they are definitely not all coming back and
being filtered.
My guess is that a filter is being used, not a query parameter. Report
parameters are flexible, they can be used multiple ways. It is important to
understand the difference between a query parameter, a filter and a report
parameter. A report parameter can be used with either a filter or as a query
paramter. If the query string does not have a @.queryparamname (if SQL
Server) or a ? (oledb) then a filter is being used instead of query
parameters. The query parameter has to be defined and then mapped to the
report parameter. When a query parameter is defined RS automatically creates
the report parameter. If named parameters (going against SQL Server) then it
will name the report parameter the same way, otherwise it names it something
generic. I always rename the automatically created report parameter and then
remap the query parameters (click on the ..., go to the parameters tab).
Whether it is a stored procedure or not makes no difference. The only thing
is with a stored procedure RS will identify the stored procedure parameters
and create the report parameters for you. Otherwise you have to edit the sql
string and add the query parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sebring1130" <sebring1130@.discussions.microsoft.com> wrote in message
news:2F7303D3-E85C-4440-BC7F-ADF33BA5E2E4@.microsoft.com...
> I think you hit the nail on the head. Parameters in Rpt Services are like
> filters. My understanding is that regardless of the parameter/filter
> setting, all of the data is returned, then filtered after the fact.
> Now that may ot be true if you use parameters in your SQL dataset itself.
> If your dataset calls a stored proc that takes in parameters directly that
> would clearly limit the resultset and return less data. It would have to.
> Another nice benefit of using a stored proc with it's own parameters is
that
> if you define your dataset this way in the wizard, Rpt Services will
> automatically recognize that your dataset has parameters and include them
in
> the RDL as report parameters. Might be worth a try.
> sebring1130
>
> "Greg" wrote:
> > Hello all
> >
> > Am I missing a trick or does there seem to be a drawback in using
parameters
> > in Reporting services. They do not act the same way as for example a
prompt
> > in business objects . When a user specifies a value in a prompt in BO
this
> > will alter the Where clause of the sql behind the report- therefore this
will
> > usually decrease the amount of time the report takes to run.
> >
> > However I have just created a parameter in RS and far from decreasing
the
> > amount of time the report takes to run it increases it. It seems that
it
> > acts as a filter on the sql already generated. It is bringing back all
> > values before restricting it. Am i doing something wrong? or is a
parameter
> > more like a filter than a condition? If so - how do i create a
condition
> > that the user can fulfill? arrgghh!!! lol - greg
> >|||I guess I wasn't clear enough, but that is basically what I said. That
report parameters are only filters of the returned sql dataset - unless the
report parameters are also query parameters being passed to the SQL proc
"directly that would clearly limit the resultset and return less data." I
wasn't 100% sure about that last part ... thanks for clarifying.
sebring1130
"Bruce L-C [MVP]" wrote:
> No, you are definitely wrong here. I go against tables with between 1
> million and 10 million rows and they are definitely not all coming back and
> being filtered.
> My guess is that a filter is being used, not a query parameter. Report
> parameters are flexible, they can be used multiple ways. It is important to
> understand the difference between a query parameter, a filter and a report
> parameter. A report parameter can be used with either a filter or as a query
> paramter. If the query string does not have a @.queryparamname (if SQL
> Server) or a ? (oledb) then a filter is being used instead of query
> parameters. The query parameter has to be defined and then mapped to the
> report parameter. When a query parameter is defined RS automatically creates
> the report parameter. If named parameters (going against SQL Server) then it
> will name the report parameter the same way, otherwise it names it something
> generic. I always rename the automatically created report parameter and then
> remap the query parameters (click on the ..., go to the parameters tab).
> Whether it is a stored procedure or not makes no difference. The only thing
> is with a stored procedure RS will identify the stored procedure parameters
> and create the report parameters for you. Otherwise you have to edit the sql
> string and add the query parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "sebring1130" <sebring1130@.discussions.microsoft.com> wrote in message
> news:2F7303D3-E85C-4440-BC7F-ADF33BA5E2E4@.microsoft.com...
> > I think you hit the nail on the head. Parameters in Rpt Services are like
> > filters. My understanding is that regardless of the parameter/filter
> > setting, all of the data is returned, then filtered after the fact.
> >
> > Now that may ot be true if you use parameters in your SQL dataset itself.
> > If your dataset calls a stored proc that takes in parameters directly that
> > would clearly limit the resultset and return less data. It would have to.
> > Another nice benefit of using a stored proc with it's own parameters is
> that
> > if you define your dataset this way in the wizard, Rpt Services will
> > automatically recognize that your dataset has parameters and include them
> in
> > the RDL as report parameters. Might be worth a try.
> >
> > sebring1130
> >
> >
> > "Greg" wrote:
> >
> > > Hello all
> > >
> > > Am I missing a trick or does there seem to be a drawback in using
> parameters
> > > in Reporting services. They do not act the same way as for example a
> prompt
> > > in business objects . When a user specifies a value in a prompt in BO
> this
> > > will alter the Where clause of the sql behind the report- therefore this
> will
> > > usually decrease the amount of time the report takes to run.
> > >
> > > However I have just created a parameter in RS and far from decreasing
> the
> > > amount of time the report takes to run it increases it. It seems that
> it
> > > acts as a filter on the sql already generated. It is bringing back all
> > > values before restricting it. Am i doing something wrong? or is a
> parameter
> > > more like a filter than a condition? If so - how do i create a
> condition
> > > that the user can fulfill? arrgghh!!! lol - greg
> > >
>
>