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

February 16th, 2006, 03:49 PM
|
Authorized User
|
|
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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')
|

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

March 2nd, 2006, 10:31 AM
|
Authorized User
|
|
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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?
|

March 2nd, 2006, 10:33 AM
|
Authorized User
|
|
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

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

March 3rd, 2006, 11:35 AM
|
Authorized User
|
|
Join Date: Feb 2006
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!
|
Thread Tools |
Search this Thread |
|
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
 |