Subtract Values From Different Datasets Based Upon Text Value of Column

Question

sheddpaf on Mon, 14 Jan 2013 20:15:21


Hello,

I am trying to subtract values for each month from two different datasets in my report.  I have a current year row and a budgeted row in my report and I am trying to subtract the difference between the two for each month.  The current year row and budgeted row are separate datasets.  I also wanted to add I have a column with that contains a text value that is the description of the row.  How could I build an expression that would display the subtracted values of the rows?  I have tried the following:

=IIF(First(Fields!Current_Year_Bookings.Value, "CurrentYearData") LIKE "*T*",Sum(Fields!Jan.Value, "CurrentYearData") , 0)

It displays 0 every time even though the description has a T in it.  Thanks for your help.





Replies

krootz on Mon, 14 Jan 2013 20:22:35


Hi,

Find the common field between the two datasets and use the Lookup function (check here).

As for the sum expression, try using Instr like below:

=IIF(Instr(First(Fields!Current_Year_Bookings.Value, "CurrentYearData"),"T")>0,Sum(Fields!Jan.Value, "CurrentYearData") , 0)

sheddpaf on Wed, 16 Jan 2013 17:42:08


Hi Krootz,

I have one more question on this.  Is there a way to have the lookup subtract the difference only based upon the text value on the Current_Year_Bookings column?  Thanks.


krootz on Wed, 16 Jan 2013 18:01:21


Hi,

I am not clear on what you mean. Use the lookup to fetch a value and subtract this value only for a certain current_year_booking? or check first the text value of the current booking and if it meets a certain criteria, do the lookup and perform the subtraction?

Can you post some data samples from your datasets tol help clarify your requirement?

sheddpaf on Wed, 16 Jan 2013 19:25:15


Say I have the current setup for my tables:

Current Year Bookings                Jan

Total 2013                                 $20

Last Year Bookings

Total 2012                                 $40

These are two different datasets from two different sources.  What I want to do is when the column text contains "Total" I would like to subtract the $20 from the $40 so I can get the difference between the two years worth of data.  I have a separate column for each month during the year  Is there a way I could go about doing this?  Thanks for your help.

Mike Yin on Sun, 20 Jan 2013 17:51:57


Hi sheddpaf,

Based on your scenario, if you use SSRS 2008 R2 or SSRS 2012, you can use the Lookup() function which returns the first matching value for the specified name from another dataset that contains name/value pairs. For more information, please see:
Lookup Function (Report Builder 3.0 and SSRS)

Regards,