different filters on different columns

Question

i am new to SSRS and i have to generate sales report in which we have to apply different conditions in different columns

i have three columns that has three conditions

in profit column condition is accountID=13 and TypeID=23

in units column condition is accountID=8 and TypeID=14

in Disbursement column condition is accountID=78 and TypeID=23

how to apply filters on three columns or is any other way to do this

Please Help me

Answer 1

Put an IIF Expression on the column.

Answer 2

The condition is per search, not per column.

What you want to do is say...

select AccountID, TypeID, Profit, Units, Disbursement
from yourTable
[join tables if necessary]
where AccountID in (13, 8, 78)
and   TypeID in (23, 14, 23)

this will give you a result set showing accountid, typeid, profits, units, and disbursement for each accountid in the criteria and each typeid in the criteria.

Hope this helps.

Answer 3

Similar to Gio, but only returning the three sets of combinations that you appear to want:

select AccountID, TypeID, Profit, Units, Disbursement
from yourTable
[join tables if necessary]
where (AccountID = 13 and TypeID = 23) or
      (AccountID = 8 and TypeID = 14) or
      (AccountID = 78 and TypeID = 23)

Answer 4

my data table contain lot of columns i am mentioning few which are necessory

ClientID ,DraftBillID, AccountID, TypeID,amount,Units

one clientID has different DraftbillIDs for each DraftBillID there is a accountID and TypeID is there

for the first column(profit) in the report we have to pull out all amounts which satify accountID=13 and TypeID=23 condition

for the second column(Units) in the report we have to pull out all units which satify accountID=8 and TypeID=14 condition

for the third column(disbursement) in the report we have to pull out all amounts which satify accountID=78 and TypeID=23 condition

Answer 5

Hello Shyaggi,

I see two possible ways to tackle your problem.

Change the query of your dataset to return only the results you require.

or use 'Expressions'

Expressions are REALLY useful in SSRS,

http://technet.microsoft.com/en-us/library/ms157328.aspx

Have a look there for a list of common Expressions and examples.

To enable an expression click the cell you want to change (i'm presuming you have a datagrid to display results on) and in the properties, under value you will see 'Expression'

Opening this will bring up the expression builder where you can build up an expression.

i.e

Add a filter so that IIF Fields!AccountID.Value=x.

You could take this a step further by adding a user parameter which would allow the user of the report to specify the filtering.

Either way you decide to tackle the problem (expressions or dataset SQL) i'd recommend reading up on expression, the majority of SSRS reports will use them in at least one place :)

Answer 6

IIf((Fields!ChargeAcID.Value=13) and (Fields!ChargeTypeID.Value=23),Fields!Amount.Value,0)

i applied above expression and it is giving error

"[rsUnexpectedCompilerError] An unexpected error occurred while compiling expressions. Native compiler return value: ‘-1073741502’."

is there any wrong in the expression