Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > Reporting Services
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Reporting Services SQL Server Reporting Services. Please specify which version.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Reporting Services section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 16th, 2006, 03:49 PM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default Expression Filter

I tried putting this in an expression for my table filter in SSRS 2005. I'm not sure how to form this and then the operator and value for this expression for my report filter. If you go to table properties / filters, I added this expression...but not sure how to get the syntax right in order for SSRS to accept it in conjunction with the operator and value as well:

=(Fields!Branch.Value = '00002' and
Fields!CustomerNumber.Value = '0000002' or
Fields!CustomerNumber.Value = '0000003' or
Fields!CustomerNumber.Value = '0000004' or
Fields!CustomerNumber.Value = '0000155' or
Fields!CustomerNumber.Value = '0000156' or
Fields!CustomerNumber.Value = '0000159' or
Fields!CustomerNumber.Value = '0000160' or
Fields!CustomerNumber.Value = '0000161' or
Fields!CustomerNumber.Value = '0000118' or
Fields!CustomerNumber.Value = '0000153' or
Fields!CustomerNumber.Value = '0000152' or
Fields!CustomerNumber.Value = '0000108' or
Fields!CustomerNumber.Value = '0000158' or
Fields!CustomerNumber.Value = '0000133')

OR

(Fields!Branch.Value <> '00002' and
Fields!CustomerNumber.Value = '0000053' or
Fields!CustomerNumber.Value = '0000058' or
Fields!CustomerNumber.Value = '0000072' or
Fields!CustomerNumber.Value = '0000073' or
Fields!CustomerNumber.Value = '0000079' or
Fields!CustomerNumber.Value = '0000080' or
Fields!CustomerNumber.Value = '0000143' or
Fields!CustomerNumber.Value = '0000146' or
Fields!CustomerNumber.Value = '0000157' or
Fields!CustomerNumber.Value = '0000135')



Reply With Quote
  #2 (permalink)  
Old March 2nd, 2006, 01:25 AM
Registered User
 
Join Date: Mar 2006
Location: Seattle, WA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Are you sure that's the best spot for your filter? Obviously I don't know the requirements, but this seems like it might be a good candidate for putting that filter in your SQL code.

James

MCSD.MCDBA.MCT
Reply With Quote
  #3 (permalink)  
Old March 2nd, 2006, 10:31 AM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If I put that in my SQL code, then basically I'll have different stored procs to filter out certain companies for each table in my report. The problem I see with that, is how would I ever parameterize the report then...provide a way for them to change companies on the fly on the reporting UI side using SSRS 2005 Report Param prompts? What I am afraid is, if I code all in SQL, then I loose the ability to provide prompts on the UI side to the user to change criteria on the fly, since everying coming into the report has already been hard coded such as dates, companies, etc. type of criteria. Is this a valid concern or ist here an approach that still lets me code this in SQL but also still allow them to change params even though stuff is hard coded before it's imported via the SSRS datasets I create for those stored procs?

Reply With Quote
  #4 (permalink)  
Old March 2nd, 2006, 10:33 AM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

For example, let's stay which is the case now, that in about 10 of my stored procs (I have around 20 for this repot), the sql created compares Today's Date or date ranges based on this month. If I have that comparison / check in my SQL and not in SSRS 2005, how can I possibly parameterize this report so that I can prompt the user later to change these dates on the fly by prompting them in my SSRS 2005 Report? To me it seems you can't at that point.

Reply With Quote
  #5 (permalink)  
Old March 3rd, 2006, 02:33 AM
Registered User
 
Join Date: Mar 2006
Location: Seattle, WA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The approach I've taken is to let the db do as much work as possible. Generally that means taking an input param from the UI and passing it into the stored procedure when running the report. It's pretty flexible. If many of your queries are similar, it might also reduce the number of them. If performance becomes an issue, there are ways to deal with that.. like report caching or writing to staging tables in the db. If you want more details, let me know. That's just what I'd look at first. I haven't seen that Filters dialog box take anything beyond simple expressions though.

James

MCSD.MCDBA.MCT
Reply With Quote
  #6 (permalink)  
Old March 3rd, 2006, 11:35 AM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Oh, I didn't know you could go the other way around. Let's say for example, in my SSRS 2005 report, I have a dataset. That dataset runs a stored proc. I have a table in my report that is based off that dataset for it's data source. How would I create params to talk to that dataset and then pass that info back to my stored proc? Wouldn't it have to flow the other way in order for any data to show up period in that table, or does SSRS prompt the user for those params first then send it back to your stored proc through the dataset then do a round trip back? How is this all done, sorry but this more advanced then I've ever tried and I'm new to SSRS 2005. Any help / hand holding would be greatly appreciated!

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter Expression In Datatables Anil_ARB C# 0 July 31st, 2008 03:49 AM
Filter on Certain dates DianaTokatlidis Access 5 October 22nd, 2007 10:56 AM
Message> in query expression <expression>. (Error ybg1 Access 5 July 15th, 2007 06:42 AM
Filter Help mcloum Classic ASP Databases 2 November 10th, 2005 05:40 PM
Filter Help mcloum BOOK: Beginning ASP 3.0 1 August 26th, 2005 04:10 PM



All times are GMT -4. The time now is 07:26 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.