?
Solved

Report filter

Posted on 2013-11-05
4
Medium Priority
?
386 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
  • 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 2000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 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