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.
| Author |
Topic |
|
larryesurat
Starting Member
1 Post |
 Posted - 2009-02-27 : 01:25:48
|
| I'm facing a big challenge in reporting services. Using two dataset for computationPlease see below for details.sample dataEmployee WeekEnding Date A B C D00001 27-Feb 23-Feb 1 3 0.33 300001 27-Feb 24-Feb 1 2 0.50 400001 27-Feb 25-Feb 1 7 0.14 200001 27-Feb 26-Feb 2 5 0.40 300001 27-Feb 27-Feb 1 6 0.17 2 TOTAL 6 23 0.26 2Formula:C = A/BD = If (Date between EffectiveStartDate and EffectiveEndDate) and C between Floor and Ceiling then Amount else 0rule in computing D: If Date fall under the EffectiveStartDate and EffectiveEndDate of Reference and if current row in C fall between Floor and Ceiling from the Reference, then use the Amount that satisifies the condition. Reference Floor Ceiling Amount EffectiveStartDate EffectiveEndDate0.1 0.29 2 2/1/2009 2/28/20090.3 0.49 3 2/1/2009 2/28/20090.5 0.69 4 2/1/2009 2/28/20090.7 1 5 2/1/2009 2/28/2009What I did was made the calculation in sql and stored it in column D. In reporting services, it computes the TOTAL as the summation of D. What I want to solve is the TOTAL should also compute based on the reference tableIn reporting services TOTAL in column D returns 14. Expected result should be 2.Is there a way in doing this in expressions. Compute for column D based on reference Dataset?Thanks for any idea that you may share in solving this.Regards,Larry |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-02 : 13:01:27
|
| you can give scope as Reference dataset while using aggregate function. something likeSUM(your field here,"Reference") |
 |
|
|
|
|
|
RS - Using two dataset for computation