Wrox Programmer Forums
| 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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #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')



  #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
  #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?

  #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.

  #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
  #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!



Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter Expression In Datatables Anil_ARB C# 0 July 31st, 2008 02:49 AM
Filter on Certain dates DianaTokatlidis Access 5 October 22nd, 2007 09:56 AM
Message> in query expression <expression>. (Error ybg1 Access 5 July 15th, 2007 05: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 03:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.