April 16, 2008 at 7:05 am
Hi Reporting Services Gurus,
I wanted to use the below expression in SQL 2000 reporting services 2000 in the filter of the Group1 of a table:
=sum( IIF( Fields!category.Value = "FA", Fields!Totals.Value, 0),"table5_Group1")
However it gives me an error as to check the data by the filter.
The expression works if I use count instead of sum.
Can you suggest some method to implement it the above expression in the filter.
April 16, 2008 at 8:15 am
What is the exact error you are getting? I have basically copied and pasted your code into an existing report and run it without issue.
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
April 16, 2008 at 9:02 am
Thanks Jack,
The query works fine if i use it in a textbox of a table , but if i use it in a filter of the group1 of teh table5 it says :
An Error has Occured during Report Processing
The processing of filter expressions for the table5 cannot be performed.The comparison failed.Please check the datatype returned by the filter expression.
If i use count instead of sum , it works.
April 16, 2008 at 9:06 am
even =sum( Fields!Totals.Value,"table5_Group1") in the filter with the operator say > 100 does not work and ends up with the same mentioned above error.
Could you please look into it.It is quite urgent.
April 16, 2008 at 9:07 am
Is your Fields!Totals.Value numeric?
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
April 16, 2008 at 9:10 am
yes it is
April 16, 2008 at 9:11 am
it is working with count but not with sum.
April 16, 2008 at 9:24 am
Attached is a screenshot of a working filter on my report.
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
April 16, 2008 at 9:28 am
What's your entire filter statement?
SSRS filters are inconsistent comparing numeric data. You may need to be creative and come up with a Boolean filter to accomplish what you need.
For example, instead of:
Expression Operator Value
=SUM(Fields!Totals.Value) > 100
Try
=SUM(Fields!Totals.Value) > 100 = True
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
April 16, 2008 at 9:38 am
Thanks , I am using sql 2000 reporting services.also when i declare the value as specified by you it still gives the same error
April 16, 2008 at 9:44 am
Thanks Jack , the jpg file has the same expression as defined by me.Is the group to be the same as specified in the expression.
April 16, 2008 at 9:46 am
If Jack's method and my method didn't work, then I'm stumped (for now)
Sorry
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
April 18, 2008 at 12:57 am
Thanks Jack and Toolman .I was able to work it out as mentioned below :
=CInt(sum( IIF( Fields!category.Value = "FA", Fields!Totals.Value, 0),"table5_Group1"))
The expression was returning a char value on the whole.When I converted it to int it worked.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply