Is It Possible To Dynamically Populate A Parameter List With Values Based On Another Parameter Value?
Aug 11, 2005
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
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
I have a units conversion table. I'd like to populate the parameter drop down with the name of the unit and have the value selected be the numrical value found in a different attribute.
Here is my current MDX:
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Units].[Units].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim Units].[Units].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim Units].[Units].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim Units].[Units].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])
Units Parameter Caption Parameter Value Parameter Level (null) All [Dim Units].[Units].[All] 0 Gross Ton Gross Ton [Dim Units].[Units].&[Gross Ton] 1 Hundred Weight Hundred Weight [Dim Units].[Units].&[Hundred Weight] 1
I can't seem to get different attribute to show up for ParameterValue.
Also, is there a way to NOT include the "all" member? For what I am trying to do it would have no meaning.
I have a report where a user selects a AD Global Group. Then I have another parameter that populates with the members of that group. My problem is the userlist parameter isn't populating. If I hard code a Global group name it works, but it isn't getting the Global Group parameter when I use @GlobalGroup.
SELECT sAMAccountName, displayName, distinguishedName FROM 'LDAP://DC=xxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx' WHERE objectCategory = 'Person' AND objectClass = 'user' AND memberOf = 'CN=@GlobalGroup,OU=Groups,OU=SCSM,OU=Applications,DC=xxxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=us'
My basic goal is to try to simplify inputs for the user. I have 3 parameters: Begin Date, End DAte and Duration. Duration will contain 3 choices: All, 2 Years and Range and is meant to give them a shortcut to dates as described below:
All - Would automatically populate the start date to 10/01/2005 and an end date to current date
2 Years - Would automatically populate the start date to current date minus 2 years, and the end date to current date.
Range - Would allow the user to select any dates as desired.
I'm able to get the dates to populate based on the duration field using non-queried values based on the Duration value, but the problem is that if I want to allow them to select Range the calendar control is not available and a text box is displayed.
I've tried to create some code in the properties that would populate, but I keep getting that this item is Read Only. The code I've created is as follows:
public function populateDates(Duration) as String
Select Case Duration Case = "Range" Report.Parameters!pBeginDate = Report.Parameters!pBeginDate Report.Parameters!pEndDAte = Report.Parameters!pEndDAte Case = "All" Report.Parameters!pBeginDate = #10/01/2005# Report.Parameters!pEndDAte = Now().Today case = "Two" Report.Parameters!pBeginDate = DateAdd("yyyy", -2, Now().Today) Report.Parameters!pEndDAte = Now().Today end select end sub
My only goal is to give the User the 3 choices, but still keep the calendar control available, and I can't seem to do this?
I created a report with a school parameter for all our schools in SQL report. The school parameter gets it's value from a query. The report is generated based on the selection made by the user. If user choose school "A", report is generated for school "A" and if user choose school "B" report is generated for school "B".
I am using a centralized user id and password for all users. A report URL (report link) is sent to user to access the report. Currently, from the school parameter (school drop-down list) users from any school may/can choose report for other schools other than theirs schoos. The content of the report is sensitive and we do not want anyone to see anyone else report.
I want to create a user based security, so based on the user id for school "A", he/she will only be able to see school "A" report but nothing else.
I was trying to accomplish this by creating linked report but it does not look realistic to create a folder for each school on the report server (which means about 180 or more folder for all our schools ). Does anyone else have better suggestions what can/should I do?
I have two report , first is main report which is matrix and have one parameter User_ids which is multi value selection and my second report is basic chart of user_wise performance.
Now, my main report (matrix ) works fine for Multiple selection of users and i have putted one textbox on main report chart which has action properties set for chart report, when user click on chart button it must goes to chart with user selected in main report. Now , i have used expression for parameter to send it like ..
=join(parameter!user_id!value,",") which pass selected value to chartÂ
And when I am selecting single user it passing that value to chart parameter list but , when it is more than one user it errors with conversion failed when converting the nvarchar value '121,128' to data type int. But my chart also works when passing 121,128 in user parameter in preview of report .
I need to write SP where user select SUN to MON check boxes. If user select Class A with sun,mon and wed check boxes then i need to insert data as below
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters: @subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ FRRIC FRMM ROBECO FRJV MAIL FRUKV FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL , @numDaysCutoff INT = 1 , @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount) , SecurityID = ISNULL(h.security_id, lo.security_id) , SecurityName = s.name , QtyHeldAndPending = ISNULL(h.quantity, 0) + (CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1) WHEN lo.type = 2 THEN lo.resulting_quantity ELSE 0 END ) , L.AverageDailyVolume , XDaysVol = L.AverageDailyVolume * @numDaysPcts , CutoffVol = L.AverageDailyVolume * @numDaysCutoff , DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0) , HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100 , TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd , CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd , TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100 , DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) / NULLIF((L.AverageDailyVolume * s.price_usd), 0) , NDaysCutoff = @numDaysCutoff , NDaysPcts = @numDaysPcts FROM subaccount_positions_table h --vGlobalHoldings h JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id) LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99 WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount) OR lo.subaccount = ISNULL(@subaccount, h.subaccount) ) AND status = 1 AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol OR -- Targeted Vol exceeds cutoff ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) > ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol ) ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
my dataset from sharepoint list. and this dataset value assign to parameter. i want when no any parameter is selected than it should filter like "ALL". when i select alow null value it give me prompt error you  can not select null in multivalue parameter.How can i do it. i am using share point list.
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 can€™t 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 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 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 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 have parameters in my report. The user can choose the year, month and date (3 parameters). Now I want to set default vaules for the parameters , so that the user sees the report for example for the current day without selecting the parameters. I tried to set the type of the parameters to DateTime and the default value for example for the year to "=Today().Year" . But when I execute the report an error occures . Something like : no validValue for this parameter.
My Attributes for the year month and date are from an Analyis Services Cube from a Server Time dimension . Does somebody know how to make it possible to set default values for this parameters?
Other question :
Does somebody know how I can reduce the values for a parameter. For Example I have a parameter "year" from a server time dimension from a cube. The values which are available are "Year 2004", "Year 2005", "Year 2006", "Year 2007". But I want that the user only can choose "Year 2006" or "Year 2007" ant not every Year or "All". Or Other Example: The User should only choose a Date that is int the past or Today but not a Date in the future.
I have a parameter (hidden) that gets its value using an expression base on another parameter. When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.
When I change the parent parameter again, the value of the child parameter does not seem to change.
How can I make this parameter change automatically when the parent is changed ?
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 have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9] ... [8_4],[8_7] ... [14_1], [14_2],...[14_10] etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null drop table #tempAllergy select * into #tempAllergy from dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.
What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX
If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.
After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.
CASE HB.PLAN_TYPE
WHEN '10' THEN 'Y'
ELSE 'N' END AS 1A,
CASE HB.PLAN_TYPE
WHEN '10' THEN 'N'
ELSE ' ' END AS 1B,
' ' AS 1C,
CASE HB.PLAN_TYPE
WHEN '10' THEN HB.BENEFIT_PLAN
ELSE ' ' END AS 1D,
CASE HB.PLAN_TYPE
WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'
WHEN HB.COVRG_CD ='2' THEN 'YY'
WHEN HB.COVRG_CD ='3' THEN 'ZZ'
ELSE 'WW' END)
ELSE ' ' END AS 1E,
It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX
While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL. Any assistance in this matter would be greatly appreciated.
Hi, I'm creating a dynamic SQL statement in MS SQL Server that is similiar to this: EXEC('IF @' + @current_column + ' (SELECT ' + + @current_column etc... I'm basically looping through a large list of parameters that correspond to column names. However, since SQL Server treats EXEC() as its own scope when it gets to what "@' + @current_column" evaluates to it says the parameter must be declared. Is there a way to convert "@' + @current_column " into the actual value of the parameter?
Does anybody know how to set the parameter's default value dynamically?
I'm working on a report with some parameters against datacube, and I hope the default value of one of the parameters could be set dynamically based on the user's login.
I am working on a report where some customization is need to be delivered.. situation is , i have some parameter in report @USER_ID , @Report_Type where i am proving selection to user to select Report Type (Pending Or Completed) and passing USER_ID auto matically from URL string of user login(C# code).
I have another parameter @USER_IDS which is multiple selection for user and it will be filled with the users which lie under passed @USER_ID means i just need to add dataset with the query to select users from mapper table where reporting_head =@USER_ID, simple, but i have requirement to populate the underlined users with the selection of @USER_ID and @Report_Type and it need some TSQL code to populate so i am using Another store procedure and using same parameter as my main store procedure has .
Now i am using dataset with this store procedure  to fill my @USER_IDS  parameterÂ
Both parameters value will be passed from main report parameters now , when i am previewing a report i am getting error
and i also tried to write exec statement in dataset query with the main repport paramters but exec is not supported ..
I have a formview with name, email, and password. I bind all fields to sql except the password which is blank. In my sqldatasource, I define parameters for name, email and id: UpdateCommand="UPDATE UserProfile SET Name = @Name,Email = @Email WHERE (ID = @ID)"><UpdateParameters><asp:Parameter Name="Name" /><asp:Parameter Name="Email" /><asp:Parameter Name="ID" /></UpdateParameters> In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event. Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.UpdatingDim password As TextBox = FormView1.FindControl Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not password.Text.ToString & "" = "" Then SqlProfile.UpdateParameters.Add(New Parameter("@Password", TypeCode.String, password.Text.ToString)) End If
I am creating SSRS reports on top of SSAS cubes. I want the default value of parameter to change dynamically based on the current year or it should select the last of the parameter values.
