sheddpaf on Mon, 14 Jan 2013 20:15:21
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.
krootz on Mon, 14 Jan 2013 20:22:35
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
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
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
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)
See other lastest posts
sql server setup
- SQL 2012 Management Studio messages outcome is not in english after running any statement/command
- SQL Server 2008 R2 download
- MDW Data Collection Set - Unable to setup a cached collection
- SQLServer 2008r2 at branch office - unable to connect
- unable to find the documentation to apply SP2 on standalone sql server 2008 R2
apps for sharepoint 2013
- Running o365 from Napa and live.com account to usual web-office, possible?
- Delete Subsite with REST request - does not fall into the Recycle Bin
- How to create list item in a folder using REST API
- What does it take to convert Low trust App(Office 365) to convert to High Trust App(SharePoint on-Premise).
- sharepoint provider hosted app adding telerik reference does not work