Solved

Report filter

Posted on 2013-11-05
4
377 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

743 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

13 Experts available now in Live!

Get 1:1 Help Now