Filtering a Data Set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Filtering a Data Set

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-04 : 15:06:03

Hi all,

I have a data set containing IDs and costs for each ID by Year, I need to only show an ID if in any year they exceeded $100,000 in costs.

So for example, ID 1 spends $75K in 2009 and $180K in 2010, I would want them in the report. ID has $90K in 2009 and $90K in 2010, I want to removethem. I can filter the data set such that I only report years over $100K, which gives the right member count, but I want to show all costs from both years even if 1 is below the threshold.

I currently have a matrix in the report.

...........Year(Can be expanded)
...........2009.....2010....Total
ID.........Costs.................

Thanks for any and all help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:44:39

how are your bringing data from database? are you bringing it as year columns or are you doing year pivoting in report?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-07 : 08:38:37

Visakh, I found a temporary solution by putting a filter on the data set (which was grouped by ID then years) to say the row had to sum to > $100K. Which worked for this particular report but the problem is that now only costs for members show if they had spend over that amount in the matrix per each year.

Example

...........Year(Can be expanded)
...........2009.....2010....Total
ID.........(Null)....$108,300....$108,300
Now that ID could have costs of, say, $80K in 2009 but because it did not meet the criteria it does not show up. I really want...

...........Year(Can be expanded)
...........2009.....2010....Total
ID.........$80,000....$108,300....$188,300

But to ONLY show a member if any year was over $100K.

Let me know if you think this is possible.

Thanks,

Derek

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:29:25

just make your query like


SELECT ID,Costs,Year
FROM
(
SELECT ID,Costs,Year,COUNT(CASE WHEN Costs>100000 THEN 1 ELSE NULL END) OVER (PARTITION BY ID) AS Cnt
FROM table
)t
WHERE Cnt > 0

then apply the grouping on this based on year column in your report

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-07 : 09:41:06

Thanks,

appreciate the response.

We are using a data model for the reports (Report Builder 3.0) so I can not customize it in the background like that (thats why I was hoping to find a filtering solution), unless I can build a data set off of another data set? If I was generating the query in the background myself that would definately work.

Appreciate your help.

-Derek

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:49:04

quote:
Originally posted by DP978

Thanks,

appreciate the response.

We are using a data model for the reports (Report Builder 3.0) so I can not customize it in the background like that (thats why I was hoping to find a filtering solution), unless I can build a data set off of another data set? If I was generating the query in the background myself that would definately work.

Appreciate your help.

-Derek

then apply the same filter expression in the report dataset

=Count(IIF(Fields!Costs.Value>100000,1,Nothing)) > 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-07 : 09:57:10

I believe it does not let you use aggregate formula's in the dataset?

Let me check and get back to you, thanks.

I have checked and aggregates can not be used.

-Derek

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:59:33

quote:
Originally posted by DP978

I believe it does not let you use aggregate formula's in the dataset?

Let me check and get back to you, thanks.

-Derek

it will allow in filters
you can specify the scope also
=Count(IIF(Fields!Costs.Value>100000,1,Nothing),"Dataset name here") > 0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-07 : 10:04:32

I am getting this error...

A FilterExpression for the dataset ‘DataSet1’ includes an aggregate function. Aggregate functions cannot be used in dataset filters or data region filters.

:(

I tried filtering within the tablix as well with no success.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2011-11-07 : 10:17:23

Is this a limitation of RB 3.0, or is this just how it is set up? Would this be a viable option when using the full SSRS version? I may just be doing something wrong, let me know if it appears I am in the wrong place or applying the filters incorrectly.

I really do appreciate all the help.

-Derek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources