August 12, 2010 at 11:32 am
Hi All,
I am currently creating an SSRS report that pulls data from SP, I am fairly new to SSRS so please excuse me if this is a simple question. I am looking to filter out $0 customers based on a series of columns, in order to do this I decided to try and create a calculation but the filter is filtering some results that should not be.
In the group filter tab I am using the following formula:
=cint(Fields!CalcYTDTotal.Value)+cint(Fields!CalcMTDTotal.Value)+cint(Fields!CalcBudget.Value)+cint(Fields!CalcLastYTDTotal.Value)+cint(Fields!CalcBacklog.Value)+cint(Fields!CalcOrders.Value)
with an Operator of !=
A Value of Cint(0)
The thought was if I add all the columns together and the value is 0 I don't not want to include it.
If I remove the formula the missing records are there, what am I doing wrong?
Thanks for any help
Grant
August 12, 2010 at 1:14 pm
Are there any negative values that could result in a zero even though it is data you want to show? Are you losing any data by casting as an INT that is being set to zero as a result?
August 12, 2010 at 2:16 pm
no there are negative numbers in this example....
August 13, 2010 at 1:05 am
Are all of your numbers integers or are they decimal numbers ? I am thinking that you may be converting your numbers to integers which strips off the digits after the decimal point. If this is the case, you could use cdbl instead of cint.
Better still, why not simply change the statement you use to get the data so that the records you don't want are are never sent to reporting services ?
August 13, 2010 at 5:13 pm
I had almost the exact same sitution and also found this method to not be reliable. It is better I think to do something like:
Column1 <> 0 OR Column2 <> 0 or Column3 <> 0 etc.......
This way if any of the columns are not zero then the record gets included.
That's what I had to do anyway, although I did in fact have negative numbers.
don
August 15, 2010 at 11:49 pm
Hi,
ideally you should filter the records at Query level.
Add a filter clause to your T-SQL statement and later deployt the dataset to reporting service.
As a best practice, it is advisible to use minimum number of IIF's statement in SSRS.
Thumb Rule: Bring only what you want!!!
Raunak J
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply