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')

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

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?

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.

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

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!