Cascading Parameters With Values Supplied By Query
Feb 11, 2008
Hi,
I would like to set up cascading report parameters but need to supply the list of values in my dataset query as they are not being pulled from a table or stored procedure.
I would like to set up something like
select 'LS' Source
Union
select 'RH' Source
Union
select 'UK' Source
as the value list for my first parameter, then, I would like the second parameter to default to '1' if the first parameter has 'RH' or 'UK' selected but default to blank if the first parameter has 'LS' selected. I can get all to default to blank or 1 but can not seem to make it dependent.
thanks!
Martha
View 2 Replies
ADVERTISEMENT
Mar 18, 2007
A have a multi-valued parameter (B) which is dependent on a single-valued parameter (A) on my report. When a value is selected in A, I want all matching values in B to be selected by default and the "Select All" option checked. To do this I have set the Default Values section in B to point to the same dataset as the "Available Values" section. Both A and B have default values so the report runs automatically.
One of the values in parameter A (say Value1) yields more values in parameter B than the other (say Value2).
If I run the report the first time with Value1 selected as the default for parameter A, all values in B are checked correctly. If I run the report with Value2 selected the first time and then change the selected value to Value2 and run my report, all values in B are displayed but only the values that were previously checked (when Value1 was selected), are now checked, leaving the "Select All" unchecked.
What am I doing wrong? Why are all the values in B not checked? The dataset is the same in "Available Values" section and "Default Values" section.
View 8 Replies
View Related
Mar 10, 2006
I have a query that provides a list of detachments that are used as a parameter for the user to select when running a report.
It works fine.
I want to set up this report to also run on a schedule.
When I set the prameters on the report for any value other than the first one in the list it fails to execute with the following error.
Parameter validation failed. It is not possible to provide valid values for all parameters. (rsParameterError) (there are no other details provided)
The query results are
All
A
B
C
D
E
G
H
I
N
V
W
X
I get this error if I input a value in the list other than All or any value that is not in the list. Any suggestions?
We are running Report Services 2000 SP1
Many thanks Ayla
View 6 Replies
View Related
Aug 31, 2007
I'm kind of new at doing something like this. I'm trying to pull data into a results that exists in one of two databases. It works fine with query analyzer
but gives me the error "has no parameters and arguments were supplied" when I try to convert it to a stored procedure.
The procedure is as follows, any help would be appreciated.
CREATE PROCEDURE sp_getInvoiceNoTest AS
declare @InVoiceNo as VarChar(30)
delete From Invoice_NBR
Insert into Invoice_NBR (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)
select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.Acuity_App.dbo.tarInvoice as a
inner join
Colucw17.Acuity_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.Acuity_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.Acuity_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.Acuity_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@InvoiceNo'
--Insert into Invoice_NBr (Tran_NBr,ADDR_Name,ADDR_Line2,ADDR_Line3,CITY_NAM E,State_Name,ADDR_NAME2,ADDR_LINE4,ADDR_LINE5,CITY _NAME2,State_Name2)
select a.TranNo,b.AddrLine1,b.AddrLine2,b.AddrLine3,b.cit y,b.StateID,c.AddrName,c.AddrLine2,c.AddrLine3,c.C ity,c.StateID
from Colucw17.CSM_App.dbo.tarInvoice as a
inner join
Colucw17.CSM_App.dbo.tciAddress as b
on a.BilltoAddrKey=b.AddrKey
inner join
Colucw17.CSM_App.dbo.tciAddress as c
on a.BilltoAddrKey=c.AddrKey and a.BilltoAddrKey=a.BilltoCustAddrKey
inner join
Colucw17.CSM_App.dbo.tarCustomer as d
on a.CustKey=d.CustKey
inner join
Colucw17.CSM_App.dbo.tciContact as f
on a.confirmtoCntctKey=f.CntctKey
where a.CreateuserID<>'admin' and a.TranNo='@InvoiceNo'
GO
View 4 Replies
View Related
Dec 15, 2005
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
View 1 Replies
View Related
Mar 28, 2006
Hi All
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.
Regards
Venkataraman M
ramanmahalingam@hotmail.com
View 6 Replies
View Related
Apr 15, 2008
Hi
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.
View 1 Replies
View Related
Mar 21, 2007
hi all,
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.
Thanking you,
Lekshmi
View 4 Replies
View Related
Jun 1, 2007
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?
Thanks,
The Rook
View 3 Replies
View Related
Sep 24, 2007
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.
Thanks!
View 6 Replies
View Related
Apr 25, 2007
Hi,
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?
Thanks for the help
regards
josh
View 1 Replies
View Related
Mar 20, 2007
Hi,
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.
Can anyone please give an ideas?
Thanks
Ashwini
View 10 Replies
View Related
Apr 1, 2008
Hi,
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.
Best Regards.
View 5 Replies
View Related
Jan 17, 2007
Hi,
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 ?
View 3 Replies
View Related
Jul 9, 2007
Hi,
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?
Any workaround or fix for this?
Best Regards,
View 3 Replies
View Related
Mar 7, 2007
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?
View 1 Replies
View Related
May 19, 2008
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).
any ideas ?
Thanks in advance
View 4 Replies
View Related
Nov 17, 2007
I need the ability to choose between quite a few cascading olap time query parameters for a report without my creating a report or subreport for every combination of these parameters. If the recordset were smaller perhaps I could filter it when it's passed back to the report but it's a few million. Will switching to expression-based command text and building the mdx queries in code work? I would like to be able to tell rs to remove the parameter from the query if no selections are made.
View 2 Replies
View Related
Feb 1, 2008
Hi,
I am currently working on a report with 3 cascading parameters. These three parameters depend on the datasets whose data are retrieved from a large table with SELECT DISTINCT. As the table grows larger, selecting values for these parameters cause postback, and slow in performance from user perspective. I am looking for a way to reduce postback. Can anyone suggest the way to retrieve the dataset all once and filter the dataset without causing postback? Or any other way to improve the performance will be greatly appreciated.
Thanks,
NL
View 7 Replies
View Related
Dec 4, 2006
In the standard report manager I'm running a report with 5 parameter choices. The first 3 are determinants of the 4th, and the combined 1-4 determine the 5th.
Although this works fine in my VS BIDS (sp1) with SQL 2005 sp2 CTP, when I deploy it to my sp2 CTP Report server, it throws an error as soon as I select the 3rd parameter.
One or more data sources is missing credentials
I found this answer from MS in another thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=923684&SiteID=1) and since it deals with my problem, wanted to pass it along to anyone else that is dealing with this problem.
" This is a known regression with CTP2 that will be fixed before SP2 is released. The problem is not specific to multivalue parameters. It has to do with dependencies between parameters.
In some cases, you can work around the issue if you give the dependent parameter a default value. You can also work around the issue by removing the valid values dependency between the parameters, though this may not be possible for a given report."
The suggested workaround does work! I can get beyond the 3rd parameter now because I applied a default value!
View 2 Replies
View Related
Jun 6, 2007
Hello,
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].
Thank you.
-Gumbatman
View 4 Replies
View Related
Mar 3, 2007
I have 5 cascading parameters. They all have default values. I would like the default value of each of these to be selected when the report opens.
It currently works correctly when I preview the report, however when I deploy the report to the server it does not. In the deployed report, the default value of the first parameter is not selected. However, when I select a value for this one, on postback the rest of these parameters get set to their default value. They're all configured the same. I'm confused as to why the first one doesn't default to its default value while the others do.
Got any ideas?
Here's how I have it configured:
Allow null value CHECKED
Allow blank value CHECKED
The rest are unchecked.
Available values: From query
Default Values: Non-queried with a value supplied that exists in the dataset.
View 1 Replies
View Related
Jun 26, 2015
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.
View 2 Replies
View Related
Aug 27, 2007
Hello,
since some hours I'm struggling with 2 multi-value cascading parameters which default values should be always "Select ALL"
First Parameter:
available vlaues: From query
dataSetsGetCountry (defined as select from tblCountries)
ValueFied:ContryName
LabelField:CountryName
DefaultValues: from Query
dataSetsGetCountry
ValueFied:ContryName
SecondParameter:
available vlaues: From query
dataSetsGetAreas (stored procedure which parameter is the list of the selected countries)
ValueFied:AreaName
LabelField:AreaName
DefaultValues: from Query
dataSetsGetAreas
ValueFied:AreaName
First time I open the report
the first and the second parameters are properly filled and for both parameters "select all" is checked.
I select one option from the first parameter, the second parameter's content change dinamically and "Select All" option is still selected !Cool
Now I change the selection on the first Parameter, by checking AN ANOTHER item from the list , the second parameter list refresh dinamically but "Select all" IS NOT selected and only the item that were previously checked kept the selection !!!NO!!
Is this a bug in Reporting services and I have to say to ther user that is not possible to develop what they would like to have or there is , even programmatically, a way to solve it?
Thankx for any helps!
Marina B
View 7 Replies
View Related
Mar 15, 2007
Is it possible to create cascading parameters with in Report Builder? I have done this with Report Designer but i need a solution with Report Builder.
(I need the structure: First the user selects the state, afer the postpack the cirties of the state are listed in another dropdownlist for selection).
Thanks in advance
View 1 Replies
View Related
Aug 27, 2007
Hello,
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.
Suggestions are very welcome...
View 4 Replies
View Related
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??
Any help would be most appreciated.
Thank you.
Tony
View 3 Replies
View Related
Mar 11, 2008
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in creating a cascading parameter.
The following scenario:
I have a dimension "Period" as Paremeter.
The Period dimension is cascaded to year- quarter- month.
So, I would like to create a cascading parameter for period as year - quarter - month.
Please let me know, how to acheive this.
thanks,
Naveen.
View 5 Replies
View Related
Apr 23, 2015
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 :
SELECT
DISTINCT
SalesDistrict
FROMDistrict(NOLOCK)
WHERESalesRegion
IN(@SalesRegion)
View 2 Replies
View Related
Nov 21, 2007
How do I create value from the dropdown list of each parameter within the "Define Query Parameters" box, which appears when I run a query/dataset from the "Data" tab of the SSRS Report Designer ?
It usually list all the "Parameter Name"s I defined, and has another column for "Parameter Value", but it always shows only <Null> or <Blank> even though I have assigned the report parameters with data (from a Query, not hard coded).
The parameter values work fine when in "Preview" mode, but it would be of convenience to have them during design time.
Thanks in advance.
View 7 Replies
View Related
Sep 2, 2015
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 '','.
View 6 Replies
View Related
Nov 10, 2015
I have two reports.
1. Main Report
2. Sub Report.
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?
View 4 Replies
View Related
Jan 18, 2007
SSRS - SP2
We have many reports with cascading multivalue parameters. The reports and the parameters work as expected within the BI development and when deployed to the RS server. The issue we are having is that when we go to the Report Manager webpage for one of these reports and create a New Subscription the report parameters in the subscription field fail to populate. The first one or two parameters may properly populate but the 3rd, 4th, and 5th fail to populate. As a result we can not select the parameters to submit the subscription.
We have two example reports where one report uses only sql (text) and the other report uses only stored procedures. Both reports have 2 or more cascading parameters.
Any known issues with cascading multivalue parameters in the Report Manager subscription view?
View 1 Replies
View Related
