Solved

Report filter

Posted on 2013-11-05
4
378 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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now