How To Disable A Report Parameter Based On Another?
Oct 14, 2007
I have a report that has 2 report parameters. The first paramter is a string type that allows null. It is supposed to allow the user to enter a memberId or just select all members (if the NULL is checked). The second report parameter is a multi valued selection that contains a list of country codes populated from a query.
What I like to have is when the user decides to enter a memberid (uncheck the NULL) to have the country codes report parameters disabled.
Is there a way to do that?
Thanks,
Elie
Well i have three parameters, first two are interedependent . i.e. the second parameter dependent on the first one. In other words when i select the first one then that automatically selects or sets values to the second. In the mean while i have the third parameter that is not dependent on the first two. when the report loads, first and the third parameters highlights for selection. I don't want the third parameter available for selection unless the second parameter is selected. How can it be possilbe?
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months? In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.
I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries. When I added the prompt into the query for use as a filter value, it says that there is an error. It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised.
I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
In sql2000 the script looks like this...
="with " & "member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " & "Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName' " & "Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name' " & "SELECT " & "{[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns, " & "filter({descendants(" & Parameters!CustomerClass.value & ", [Customers by Class].[" & Parameters!CustomersLevel.value & "], SELF)}, " & " [Measures].[Ex Price] > 0) on rows " & "from Sales "
i've changed the script in sql2005 but not working yet..
with member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName' Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name'
SELECT {[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns, filter({descendants( STRTOMEMBER(@CustomerClass), "[Customer Class]." + STRTOMEMBER(@CustomersLevel), SELF)}, [Measures].[Ex Price] > 0) on rows from Sales
I have created a report using Report designer (Visual Studio, using Data Model as a data source), in the report I had created few datasets (with single filed) to populate the report parameters, lets say I have created a multi valued Parameter CustomerName and assigned field from a dataset,
result are coming correctly and combo box is getting populated but the customers are not in alphabetical order!
I want to sort it and need to specify it in report (please note that I am using Report Model as a data source and I cant sort the source table in the data base to get the result sorted)
Please let me know if anybody has done that or forward me if know some link which talks about it.
I have a report parameter named "Schools" which display a list of schools. For example, Alo elementary school, Balo middle school and Calo high school.
When "Alo elementary" is selected the report only display students from that school along with other assessments data fields. same goes for other schools too. But I want to display different data fields for "Calo high school" when it is selected. It is not currently possilbe becasue I am using the same template for all types of schools. There some fields only should be displayed for "Calo high school" but not for any other type of schools.
I can accomplish this by creating two separate report, one for "Calo high school" and the other for other schools. But I want to accomplish this just by creating one report. So when "Alo elementary school" is selected it displays report with certain fields and when "Calo high school" is selected then it displays same fields as "Alo elementary school" but as well as some other fields too in the report. Is this possible? Need help.
I have requirement where first I need to show only one report parameter. Based on user selection I need to prompt or show the user another report parameter.
Say suppose I have 3 parameters. User selects first value in first parameter I should not show the other 2 parameters. If user selects second value in first parameter I should show second parameter and hide third parameter. There is no relationship between these 2 parameters except user selection. Similarly if user third value in first parameter then I should show third parameter and hide second parameter.
Is this possible? I can not see any Visible property for report parameters.
I created the report, which has 4 pages. Each page contains bunch of tables and charts, which display data in scope of dataset, created for each page and reflecting certain subject. Each page also has Header and Footer.
My users requested, the report has possibility to include or not include data on Page #4.
Other words, report should display 3 or 4 pages based on the entered parameter.
1. I created parameter named “Include #### metrics”, which contains options: Y, N 2. For each object on the page 4 I added dependency on the selected parameter in the visibility option.
Now if user select parameter Not include Page #4, report displays 3 pages as expected and page #4 is empty, but with Header and Footer.
The problem: Their preference is to not to show page 4 at all if someone chooses to not to include #### metrics.
My question is how to force the report to display just 3 selected pages.
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? 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. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature available in any other updated version of ssrs?
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
I have some business analysts (I guess Microsoft would call them Information Workers) using Report Builder to design reports, but also as a querying tool. They often times dump the results of their reports/queries into Excel, do some crunching, and then come back to write a better report.
When the Report Builder builds groups, it inherently turns on the HideDuplicates property / element so that it only displays distinct values on the group by fields. This looks great on a report, but is impractical for Excel, where the data is going to be manipulated by row.
In the .rdl, I found the element "HideDuplicates" http://msdn2.microsoft.com/en-us/library/ms152916.aspx controls this behavior of the report. However, I cannot find any option in Report Builder that would allow an Information Worker (i.e. non-codie), to alter this property. My work around for now is to edit the .rdl manually, creating two versions of the report. Very impractical.
Any ideas or info on how to do this in Report Builder would be much appreciated.
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original - sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @datesmalldatetime ) AS
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel as select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
How do I get data on my linked report based on my grouped subtotal and grand total from the main report. The subtotal and grand total are calculated columns.
I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.
The following is code for my summary report. The results are shown below.
SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts, CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST' WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST' ELSE 'OTHER' END AS Actn_COAST
FROM tbl1 where ACTNDATE between @STARTDT and @EndDT
Code for my detail report contains the following SQL
SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY
I have linked my report based on the NumbOfAccts column. I am able to get data if I click any of the NumbOfAccts values related to the state I want. However when I am not sure how to make the subtotal and grand total work. I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast. If I click on the Grand Total, I only want data related to those 2 coasts.
Hello, since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON CREATE PROCEDURE [schema].[spCreateReportTest] @Name nvarchar(20)= ''
AS BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON set @slqSelectQuery = N'SELECT field1,field2,field3 from table' if (@Name <> '') begin set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + '''' end EXEC sp_executesql @slqSelectQuery end
Inside my business Intelligence Project I created: -the shared data source with the connection String - a data set : CommandType = Stored Procedure Query String = schema.spCreateReportTest When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter... So inside"Layout" tab, I added the parameter: Name allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
I had thought that this was possible but I can't seem to figure out the syntax. Essentially I have a report where one of the parameters is populated by a stored procedure.
Right now this is easily accomplished by using "exec <storedprocname>" as the query string for the report parameter. However I am not clear if it is possible to now incorporate User!UserID as parameter to the stored procedure. Is it? Thanks
I'll go to a dataset, open up the query designer, add a new parameter, then refresh the fields, but the parameter won't be added as a report parameter. If I go to the dataset properties under the list of parameters, the value in the dropdown will be blank. However, sometimes this will automatically add.
Is this a bug in Visual Studio? How do I get around this?
I have created a menu report listing available reports and am setting the Jump to settings of each text box in the list to take the user to the relevent report. But I am having a problem setting up drill down to reports with multivalued parameters.
What I want to happen when the user drills down is for the report to open up with no parameter values selected. This works for single value parameters where I have set Null as the default value. But I have not been able to figure out how to do this with MVPs. Does anybody know if this is possible?
As a workaround I have been using Jump to URL (rather than Jump to report), which has no problem opening the report without having to pass any values for the MPV. But I do not like using this as I use different DEV/UAT and PROD environments so would have to amend the URL for each environment.
Seems like this should be possible... Any idea?
Cheers, Mike
PS: I should add I can Jump to reports with MVP without specifying a parameter in development studio but not when the report is published.
