Link to home
Start Free TrialLog in
Avatar of ModSpace
ModSpace

asked on

SSRS parameter customization

Can Microsoft Reporting service (2008 R2)  have custom parameters where user can input text and it gives them a filtered set of data to be applied as a parameter to the report if so how can that be achieved.
Any examples/ direction to resources is appreciated
Avatar of sureshbabukrish
sureshbabukrish
Flag of India image

for example you want report for managers from a department.

enter the dept name, then antoher parameter is filled with list of manager names available for the dept. then select any mgr, then click view report

Add two paramters First Department ( add this as a String)
Then Add Mangers ( add this as a String, Select Multi-Value Option)

Create a Dataset called Mgrs
Select MgrId, MgrName from Manager where DeptName = @Department

Go to Parameter page,
Select parameter Manager
select Available-Values , then select From Query
Select Dataset - Mgrs
Select MgrId as Value field
Select MgrName as Label field
Click OK

Create another dataset for the Report called MgrData
Select * from TableA Where MgrId in (@Manager)

To execute the report, enter Department Name , then Manager Paramter would be filled with values, then select managers and click view report
Avatar of ModSpace
ModSpace

ASKER

What I want is if the filter field is some text value and I want to get all fields with values starting with XX
can I have a field Text: (to be entered by user) and on entering it populates another multiselect parameter with all values beginning with XX and enable users to select that and click view to view the report with those parameters?

In case of above example let say If I want to select a report for all Managers whose name has "rob" in them at runtime can we create a custom text field where user types in rob or bob or anything of his choice and then the next parameter managers will be auto populated with multiselect with all manager names that have or begin with the typed text?
ASKER CERTIFIED SOLUTION
Avatar of sureshbabukrish
sureshbabukrish
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have created  data source ds_Manager with a simar query
Select MgrId, MgrName from Manager where MgrName like @MgrLookup

I have created a parameter MgrLookup as Text
From Query Dataset ds_Manager and Value and label as MgrName

I get Forward dependencies are not valid error when I preview the report.

Can you please give me an example on how I can get this for

1 text field user input (used for lookup for 2nd multiselect
2nd Multiselect parameter with list of Names?


I think I got it Thanks
I have created 2 parameters
1 Text no dependencies  MgrNameSearch
2 Text Multivalue from query for datasource

datasource is
select name ..... from Manager where name like @parameter1