summing value based on group ssrs

[k1ngTMO]

I have two datasets where I pull date from for this report...dataset1 has sales by month and trans dataset has trans spend.

I want to include sales in the location groupings but only sales for that location....right now it seems to sum up all the sales even though I put it inside the location group...

how do I do that...do I use a iif function??

 

[k1ngTMO]

i think I need something like this...
=iif(First(Fields!group.Value, "DataSet1") like Fields!group.Value, Sum(Fields!JAN.Value, "DataSet1"),0)

but this returns no value...

 

[xlbo]

Best bet is to do this in your query - ssrs doesn;tlike trying to put data from 2 datasets in 1 table...

Other than that, there are 2 issues with your syntax:

1: Don't think you can use "like" in a formula\

2: 0 and Sum(Fields!JAN.Value, "DataSet1") must be the same format otherwise you get an aggregation error. Whenever trying to do something like that, you need to use:

cdbl(Sum(Fields!JAN.Value, "DataSet1")),cdbl(0))


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question