• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

Report filter

I have an unexpected problem with my report filter value. Two parameter filter lists: Year and Person Name. Person names are selected based on the selected year .

Let's say a person record:
PersonID    PersonName    Year
12345           Mike Smith     2012

The problem is that Mike does not show in the Person Name parameter list if:
After clicking Preview,  click Select All in the Year parameter filter list. Mike does not show in the Person Name filter list.

If I select 2012 in the Year filter after Preview is clicked, Mike shows in the Person Name filter list, and then I select "Select All" in the Year filter list, Mike also shows in the filter list.

The query statement is working fine.

Why/How can this happen? I appreciate any help.
0
minglelinch
Asked:
minglelinch
  • 2
  • 2
1 Solution
 
dannygonzalez09Commented:
Can you post the SQL that you are using to get the parameter list for users? it might need a tweak
0
 
minglelinchAuthor Commented:
I've changed a little. The second filter is based on the Year Filter and made the Person filter not related to Year filter. See the queries below. Unfortunately, I still have the same issue -
If I select all in Year filter right after clicking Preview, I lost some records.
If I select some items in the Year filter list, run it, and then select All years in list, then all records show.

Year parameter:
SELECT DISTINCT CAST(myYear AS varchar(50)) AS myYear
FROM   myView
ORDER BY myYear

second parameter:
SELECT DISTINCT PerName, PerName AS Expr1
FROM        myPeriods
WHERE     (myYear IN (@myYear))
ORDER BY PerName

Person Filter
SELECT DISTINCT ID, LastName + ', ' + FirstName AS PersonName
FROM  myView
WHERE LEN(LTRIM(RTRIM(myField)))>0
ORDER BY LastName
0
 
dannygonzalez09Commented:
Do you have an expression behind this variable.. @myYear ? can you post that

I'm just trying to make sure that you have used the Join Function correctly, if at all you are using it

Ex: http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/
0
 
minglelinchAuthor Commented:
This is the reason why. Due to too many items to pass into the split/joint function and reached the limitation. The issue was temp resolved by limiting the unnecessary filter items.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now