August 18, 2008 at 7:35 am
I need to have two tables that get their data from a stored proc. Here's what I'm trying to accomplish:
One table needs to include members added in the last seven days.
Table two needs to include members who bought product in the last seven days regardless of when they were added. This means that there may a member(s) in both tables at times, which is expected. MY sql code works and it passes all those added in the last seven days or who purchased in the last seven days.
My challenge is how do I make sure those added last seven days but not purchased in last seven days show in table one or those added in last seven days and bought in last seven days show up in both. Also those not added in last seven days but bought in last seven days to show up in table two, and again those who bought in last seven days and added in last seven days to show up in both tables.
Is there a way to set up filters in RS when the data source is a stored proc? Or do I create two stored procs that splits the data and have two data sources connecting to the report? if so, then how?
Any light on this will be appreciated.
thx,
John
August 18, 2008 at 7:47 am
Sure you can add filtering to a table in a report so you only make 1 round trip to the database.
Go to the Table Properties -> Filters Tab, add the column(s) you need to filter on to the Expression, choose an operator and in the Value box enter your expression. The last step is the key step it is an expression so you need to put =7. So for table 1, those added in the last 7 days your filter would be:
Expression Operator Value
---------- -------- ------
=DateDiff(DateInterval.Day, Fields!DateAdded.Value, Today) <= =7
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 8:59 am
Thanks for the help. However I entered the expression exactly the way you worded it in the table properties/filter section, but when I try to run the report I get an error msg:
"An error has occurred during report processing.
The processing of filter expression for the table 'table1' cannot be performed. The comparison failed. Please check the data type returned by filter expression."
What can be causing this?
thx,
John
August 18, 2008 at 9:46 am
SSRS doesn't play nice with numbers when filtering.
Try these:
convert your numeric expression to string
Expression______________________________________________Operator__Value
=CStr(DateDiff(DateInterval.Day, Fields!DateAdded.Value, Today)) <= 7
OR
Turn your filter into something Boolean
Expression______________________________________________Operator__Value
=DateDiff(DateInterval.Day, Fields!DateAdded.Value, Today) <= 7 = True
You may need to play with quotes around your check values i.e. "7" or "True" or maybe not. Can't recall. Don't use filters much cuz they flake out with numbers.
HTH
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
August 18, 2008 at 1:59 pm
John,
What about creating 2 data sets?
Create one call it add7days add the select statement to get the results you want for table1
Create a second call it sld7days add the select statement to get the desired results you want for table2
go to layout table and add the two tables.
Open properties of table 1 and set the dataset name to add7days
Open properties of table2 and set the dataset name to sld7days.
I have founf the filter in reprting stinks and try to keep all filtering if possible done in my query.
August 18, 2008 at 2:08 pm
The issue is now making 2 round trips to the database, caching 2 execution plans. If you can avoid round trips your report will scale better. I have used filters without issue. Sure they can be a bit of a pain to get setup because of the implementation that MS used, but in my experience they work fine.
I think toolman has the best solution and in an SSRS class or book I read that was the suggestion they made as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 2:15 pm
Jack I will take your word on that.
I have done it a couple of times on some minor reports with a limited amount of data.
When you say scale better what does that mean?
I'm relatively new having taught myself Access, SQL 2000, and Report Services myself thru books and forum help.
In the process of teaching myself HTML and then ???? to create web sites.
August 18, 2008 at 2:30 pm
One of the rules I learned early was to limit round trips. Think about it this way. If I use 2 datasets that each return 1000 rows and 700 are the same I returned 2000 rows instead of the 1300 I really needed for my report, so while Gigabit networking is fairly common I am more quickly using up a finite resource which is network bandwidth. Also for every connection to SQL Server there is some overhead, so I am doubling that. Also, sinc eSQL Server wants to cache plans I now have 2 plans and again my plan cache is limited so I may be causing even more frequently used plans to "age" out of the cache. In most SQL Server implementations you probably won't notice, but at what point does it become an issue, but this is true of cursors as well, and I don't know anyone who recommends cursors.
I just think that, in the long run, you are better off using the best practice you know the first time rather than have to go back later because of performance issues.
There are definitely reasons to have multiple datasets in a report, but it does not seem to like this is one of them.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2008 at 7:24 am
Thans all for your knowledgeable suggestions. However since Jack's and Toolman's didn't work, and since my code is a complex stored proc that creates five temp tables within, I added two calculated fields to my code; one for the datediff in days between the DateAdded and getdate() as julian dates (integers); another as the datediff, days between lastdatesold and getdate() as julian too. The result; I created two tables in RS, one with a simple filter of DateAddedDiff <= 7; and the other DateSoldDiff <= 7, and it works, so I'll stick with this solution. Besides, I also agree with Jack about making several roundtrips to the Db.
Regards,
John
August 19, 2008 at 7:30 am
That seems like a good solution that should not have an impact on performance and may actually improve the report performance. Wish I'd thought of it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 6:56 am
Glad you have a working solution, although I think I might have approached this differently. I would have written code to create a single result set that has two status columns for each person that qualifies under either of the two criteria. Each of these status columns would be a "Y" or "N" value. Then, all one need do is filter on the text values and have a single dataset that feeds two RS tables. Does that make sense?
Steve
(aka sgmunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 20, 2008 at 8:38 am
I see where you're coming from, however, I'm still passing one result set into the report. As for the filters, I have two tables referencing the same dataset only with different filters.
Thanks for the suggestion.
John
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply