Cascading Parameters In SQL Server Reporting Services 2005
May 29, 2007
I have a Reporting Services 2005 report which uses cascading parameter multi-select drop-down boxes. The report runs perfect after making initial selections. However, each subsequent parameter selection causes the page to refresh to a blank page until the View Report button is clicked. What I would like to happen (which should be the norm) is for the user to be able to make all necessary selections without the page auto-refreshing after each selection. The user would then click the View Report button to regenerate the report. We are currently using reports which contain up to 5 cascading parameters. This becomes very time-consuming when the page refreshes after each selection.
Has anyone come across a solution or code alternative to this problem??
I am using SSRS 2008 with a SQL Server 2008 R2. It's been many years since i have used SSRS but i have used this and some other reporting tools in the past.I have created a cascading parameters where upon selecting 'ALL' or one of the other values in parameter A , the other values gets displayed in paramter B which is working in the Report builder. i am handling this in the dataset query where i handle that through a case statement ( something like WHERE 1 = (CASE WHEN @Prm_A = 'ALL' THEN 1 END) OR column_X= ( CASE WHEN @Prm_A <>'ALL' THEN @Prm_A END ).Also tried WHERE @Prm_A = 'ALL' OR column_X= @Prm_A
This seems to work in the report builder on my machine or when i run it by editing the report on the web, but when i publish it on the report server, i don't see that behavior, meaning it does not cascade. I also tried to simplify it by making the query simple(WHERE column_X= @Prm_A ), but it still would not cascade on the web.
I'm using a SSIS cube as source for reporting services. In my DataSet I have 3 parameters: Year, ProductGroup, Product. By default all parameters are cascading when defined on the DataSet. This is great for ProductGroup -> Product, but I don't want it for Year -> ProductGroup (I want to avoid the roundtrip). I removed the link in the generated MDX, I removed the parameters on the DataSet, but when I run the report, ProductGroup is still grayed out until I choose a year.
I have created two report parameters and want them as Cascading. District Parameter depends on Region Parameter which should allow Multi selection. When I select single value in Region it works perfectly. But when I choose multiple values, District turns out to be a blank text box. I have used the In clause in my code :
After adding cascading parameters my report which was running earlier is failing. I'm using Report builder 3.0, windows 2008r2, am running the report from the server
Error message "An error has occurred during report processing.(rsProcessingAborted)
Query execution failed for dataset 'LastName' (rsErrorExecutinGcOMMAND) Incorrect Syntax near ','."
Is there a way where i can look up the code in xml using report builder or some other way so that i can delete the extra '','.
I have cascading parameters in 'Main Report'. When i try to pass cascading parameters in to sub-report, I could pass only 'first parameter' value to sub-report.Is there a way that i can pass cascading parameters to the child report?
I am creating a report where there are 3 tables in one report which have visibility set as an expression so that when a certain criteria is selected in the "Report" parameter, a different table is shown below. This works as expected.
When table 3 is shown, I want a new parameter to be visible to the user called "Ward" where the user will select the ward they are interested in and the table will display data below just for the ward selected. However, I don't want the ward parameter visible when either of the other 2 options are selected. Is this possible and how to do this?
I was trying to do cascading parameters based off my cube and I wasn't able to do this. Is it possible?
For example, I have a dimension that has Products so I first select the parameter for product type (Dairy, Frozen, Candy) and then I have another dropdown listbox that has the name of each product (Milk, Ice Cream, Lemon Drops). The second dropdown listbox should only contain the products that match what parameter was selected in the first dropdown.
When I couldn't get that to work, I went to the source system containing the Dimension tables and just did nice and easy SQL statements from there. It worked but I, for some reason that I can't explain, think this is not the proper way to do it.
Also, is there a way to have a default on the second parameter based on the first parameter selected? I would assume that default would be [All].
I'm trying to create reports in RS2005 using AS2000 as my data source. I understand that if I use RS2005 on AS2000, I wont be able to enjoy the OLAP based parameters as in using AS2005. Does anyone know an easy way to easily use Parameters in RS2005 while still using AS2000?
Is there any way to pass a dataset parameter in RS 2000? Basicly, I have some data in my web page and want to print it. But I don't want to create another page to pass parameters to RS. I want to print the page as it is with the data already in the page, so if there is a way I can send the data through a dataset to the report directly, would be great. Is there?
So this is my first time posting on MSDN but I am running into an issue I don't know how to circumvent. This issue isn't causing my report not to run but it makes it very confusing for end users. I am building a report for a user and he was cascading parameters. The parameters are Department (the Parent), Site (the Child) and Vehicle Class (the grandchild).
Here is my issue: If I select 2 Departments named Dept1 and Dept2, the Site parameter populates. If Dept1 and Dept2 have sites named the same, example fleet, Fleet shows up in the site parameter twice.
Then the Vehicle Class parameter populates. Now if at those sites they have the same vehicle class, lets say 1/2 ton pickup, 1/2 ton pickup is in the vehicle class parameter twice.
All three of these parameters are populated by a query and all of the queries are using SELECT DISTINCT. I know I could manually input values for the parameters but there are to many departments and sites and they all vary to much depending on the department.
Is there a way to to only have distinct values populate the parameter even if the parents have the same child?
I am creating a report that the user will pick a value from about 6 items. The project manager would like these values to be represented as radio buttons on the ReportViewer control. I am unable to determine how do create these 6 radio buttons.
Here is a simple example to demonstrate what I need. Lets say the report returns the total number of fruit in a stores inventory. The choices are: Apple, Banannas, Oranges, Grapes, Cherries, Water Mellon.
I have a variable @Fruit that I would like populated by the user and I want them to select which piece of fruit they want to see by selecting a radio button. Is there a way to do this using Reporting Services 2005 Report Designer?
I have two parameters, lets say P1 and P2. P2 is cascaded with P1. P1 -> P2.
For the parameter P2, the following proterty set are
1. Multi Select 2. The default value is all the available value selected (Same dataset is assigned to both "Available Values" and "Default Values")
The data relation be, P1 P2 A a A b A c A d B a
Now the issue is,
Step 1 : When i choose A in P1 first time, a,b,c and d in P2 are selected Step 2 : When i change B in P1, a is selected Step 3 : When i change back to A in P1, only a is selected in P2 (a,b,c and d should be selected)
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
I am having a problem. I know of hidden parameters in SQL 2005 RS, but I need to have this functionality as well for SQL 2000 RS. I read on the Net that MS SQL 2000 RS Service Pack 1 adds this functionality. I have installed SP4 for SQL 2000, then I installed RS SP1 on my laptop, but I don't see that functionality in VS 2003 Enterprise Architect. I even installed RS SP2 on my other machine, but still no joy. Am I missing something here or is Microsoft lying about this functionality? My client will be upgrading to SQL 2005 sometime in the near future (if they don't change their minds again), but I need to implement this kind of functionality now. I can't wait for them to upgrade to SQL 2005. This parameter I need to hide in the reports is necessary to ensure the reports show data by specific department. I'll implement it in such a way that when they call the report from the web application, I'll pass the departmentID to the report via the query string.
P.S. It is not possible for me to achieve this using JOINs in the queries. The parameter lists themselves depend on this value.
This is the code which I have written in code window.
Public Shared Function CalcLocalFactor(ByVal CalcLifeCode As Integer, ByVal CalcFiscalAge As Integer, ByVal CalcLifeYearsUsed As Double, ByVal CalcLocConvention As String, ByVal CalcSRate As Integer) As Double Dim locCalcFiscalAge As Integer = 0 Dim locFactor As Double= 1.0 Dim locFactor1 As Double = 1.0 Dim REM1 As Integer = 1 Dim DEP As Double = 0 Dim YR As Integer Dim HALF_YEAR As Double Dim LINEAR As Double Dim MACR As Double If CalcFiscalAge > CalcLifeCode + 1 Then locCalcFiscalAge = 0 locFactor = 1.0 End If If (CalcLocConvention <> "HALF-YEAR" And CalcLifeYearsUsed < CalcLifeCode) Then locFactor = Math.Round((CalcLifeYearsUsed / CalcLifeCode), 4) End If If (CalcLocConvention = "HALF-YEAR") Then for YR = 1 to CalcFiscalAge step 1 If YR = CalcLifeCode + 1 Then locFactor1 = 1 Exit For End If If (YR = 1 Or YR = CalcFiscalAge) Then HALF_YEAR = 2 Else HALF_YEAR = 1 End If LINEAR = Math.Round(REM1 / (CalcLifeCode - YR + 1.5) / HALF_YEAR, 4) MACR = Math.Round(REM1 / CalcLifeCode * CalcSRate / HALF_YEAR, 4) If MACR >= LINEAR Then DEP = MACR Else DEP = LINEAR End If locFactor1 = locFactor1 + DEP REM1 = 1 - locFactor1 locFactor = locFactor1 Next End If Return locFactor End Function
I'm calling this code in a Report Parameter like below:
It is working fine for the first record where as for other records, the value is not getting changed. i.e. the first records value is coming repeatedly for all other records also.
How can I dynamically change the parameter values of the function?
Parameter is not accepting directly the field names, hence I used other parameter to initialize the field and used that parameter for this.
Ex. Parameter Name ; FieldPurchDate (internal) FieldName : PURCHDATE Other parameter: FieldInDate (internal) FieldName : InDate
While initializing the new parameter CalcPurchDate,, I used an expression for this: Parameters!CalcFiscalAge.Value
=iif(Parameters!FieldPurchDate.Value is nothing, Parameters!FieldInDate.Value,Parameters!FieldPurchDate.Value)
and using this CalcPurchDate for processing of the parameter:
I have a report which takes around 5 seconds when run in BIDS but takes around 20 seconds when deployed on report server.Execution log says TimeDataRetrieval is around 3-4 seconds and rendering time is around 15-17 secs.From this report I am passing 8 parameters to a drill through report and there are 36 text boxes where I have defined these parameters for drill through action.All these parameters are populated in main dataset.When I deployed the same report without any drill through action and parameter, it takes 5 seconds.So I am suspecting that because of drill through parameters report is taking more rendering time on server.I am using 2008R2 and IE11.
Is it expected behavior that due to so many parameters for drill through action, report will take more rendering time?If yes, then why is it not taking same time in BIDS?
My requirement for the parameter is multivalue parameter with a text box. for example when user enters aa15 it need to include product aa15. when the user enters aa15, aa16, zz15 than it needs to include all the three products. the last case is when the user enters AA** than i need to inclued all the products start with AA. when i use default multivalue parameter with data source analytical services than i am getting a drop down box. I dont want that. I need a text box where user can enter the value. 1. In sql we have a like key word to query . for example select * from product where product like "AA%". what is equavalent mdx query to get such results ? 2.How to impliment the multivalue parameters without using dropdown box?
Hi I have three params p1 , p2 and p3. All 3 are non queried with values Yes and NO . if p1 is yes only i have to enable the remaining twp params otherwise disable them. Can some one suggest as to how this can be achieved. Thanks
I am using RS 2000. I used 2 input parameters for one of the report. The 2nd parameter is based on the 1st parameter. i.e. First Parameter is Country and 2nd Parameter is State. Based on the Country selection the list of states for the selected country will get displayed. This is working fine in Reporting services as well as report server. When deploying into the application it doesn't work.
If you have solution for this I would like to share.
I am doing report development against cube (OLAP) and I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do we achieve this in a cube environment? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Hope this helps. If you have questions, please let me know.
In my report I want to make certain parameters to depend on the previous parameters. I think we can use those as cascading parameters. How to make a parameter a cascading parameter? and how to use those cascading parameter ? It would be nice of you if you can help me.
I have a report that is based on 2 listboxes, the second one's values dependent on the value selected in the first box. How would I display all values in both list boxes on the report if I so decided?
I have 2 parameters that are of type string. The user can enter anything they want in them. The third parameter is query based and uses the first 2 parameters to get a list of people. Is there a way I can prevent the third parameter from propigating until the first 2 are both filled in? The first 2 cannot be drop downs however, they are used in a wild card fashion.
I have a listbox which selects distinct brands from the products table.
Then another list box which lists all the orders with order description. Each order has a unique system generated ORDERID and the user provides the orderdescription which could be duplicate.
Depending on the Brand selected by the user in the earlier list box, only those orders containing the the brands in order details files should be available for selection in the list box.
e.g. BRands Lisbox shows: Cream A, Cream B and Cream C
If the user selects 'Cream A', then the next list box shows orderdescription as 'Cream A order for regular sale','Cream A order for exhinition sale', 'Cream A order for exhibition sale'
The problem here is that if the user selects a description which is duplicate (could be the case), then the system brings back the wrong order details.
How is it possible to allow a user to select a order description but search on the OrderID?
I am using SQL Server 2005 reporting services. I am having a problem with Cascading Parameters. In my report there are 4 parameter . (Product Class, Product Type, Product SubType and Activites). when user open the report page he/she will have to select a value for product class depending on the selection Product Type and Sub type will be populated. So far its good, but Activities also depends on the Product Class. My question is can we have 2 parameters depend on same parameter. Here in this case Product Type and Activities both depend on the Product Class. I want only those activies in the dropdown which belongs to the Product Class that is selected.
I am trying to migrate my reports from SQL server 2000 reporting services 32bit to 2005 64bit. I am following the migration steps that MS specified. Restored my Reportserver and ReportserverTempDB databases Then I was using the configure Report services to upgrade these databases but I always end up getting the follwoing exception when I run the upgrade on the "Database Setup" configuration for 'ReportServerTempDB' database System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database 'ReportServerTempDBTempDB'. No entry found with that name. Make sure that the name is entered correctly. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script) at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)
It's driving me crazy, why is it looking for 'ReportServerTempDBTempDB' in the catalog instead of 'ReportServerTempDB'? Is it possible to migrate from 32bit to 64bit?
I have the folowing problem with RS2005. I have 2 parameters in my report. Firs one is a list of projects which is a single select. The second one allows to select multiple employees. When I select a project from the first parameter, the list in second parameter gets narrowed to list of employees that are assigned to this project.
The unexpected behaviour: 1. I select "Proj1" from first parameter 2. The second parameter gets narrowed. I select "Select All" employees 3. I generate my report. OK
4. I change the selection in first param to "Proj2" 5. The second parameter gets narrowed, but employees that are common for "Proj1" and "Proj2" are still selected, which is unwanted. ERR
The users of this report gets confused as they generate the report just after point 4. and they think all employees are still selected.
What I want to do is uncheck all employees from second parameter every time the first one is changed. That would cause the user to get a prompt for selecting second parameter.
Is there a way I can do this? Any clue would be nice.
I have a problem with cascading parameters. I have 10 parameters in my report.
Parameters 1, 2, 9 and 10 are independant. Parameters 3 to 5 are cascading parameters as well as parameters 6 to 8.
Parameters 3 to 5 have no relationship with 6 to 8. But when I change the value for parameter 3 the values for parameters 4 to 10 are reset ???! I don't understand why, only parameters 4 to 5 should be reset.
Can someone please tell me how to avoid parameters 6 to 10 being reset ?
I have 3 parameters in a report related with each other, but using them makes the report go extremly slow... is this a known bug? I have been reading about the problem with postbacks and stuff is this the problem?
I have the following conundrum: I have a report that utilises 3 cascading report parameters: region, area manager and business manager. They are in the correct order in the list of report parameters i.e. region then area manager then business manager. When I select the region, then an area manager within that region, after having made the selection the area manager dropdown list changes to some arbitrary choice. I also have a textbox equal to the label value of the area manager report parameter and this label displays something entirely different from my choice and the one that seems to be the "default". Please note that I don't have any actual defaults for the parameters.
My question is why does the area manager dropdown change after the postback from my choice to something other?
Hi all, did any of you had the same issue ? how can I add cascading parameters to my report from the SAME hierarchy? If I have 4 levels in hierarchy- according to level 1 - filter level2 (param2), then according to level 2 - filter level 3 (param3).
