Solved

Report filter

Posted on 2013-11-05
4
382 Views
Last Modified: 2013-11-08
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
Comment
Question by:minglelinch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39625594
Can you post the SQL that you are using to get the parameter list for users? it might need a tweak
0
 
LVL 1

Author Comment

by:minglelinch
ID: 39625708
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
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 500 total points
ID: 39626543
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
 
LVL 1

Author Closing Comment

by:minglelinch
ID: 39633798
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

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question