0

I have a row "A" that has a column "Qty" I have a row "B" that has a column "Needed" that is to be subtracted or added from "Qty" and placed in another column on row B "Total"

The data in row B is populated using a LookupSet. So in the "Needed" column, there are n lines of numbers that need to be incrementally subtracted or added.

E.G. Qty in row A is 10, Needed in row B is 1, 2, 5, 2 Total in row B needs to show 9, 7, 2, 0

Is there a way to utilize SSRS functions to iteratively subtract or add numbers in a lookup set?

Please let me know additional information needed.

Data example

The 495 is what should be added subtracted from, the blue is what should be subtracted (and example totals). The second row shows an example of the LookupSet output.

Dom Vito
  • 507
  • 8
  • 38
  • I think it is possible, but share a complete example of your data and what you are expecting. – alejandro zuleta May 26 '16 at 17:03
  • Trying to wrap expressions around the LookupSet function is going to get pretty messy. You'd be better off compiling the data into a usable format on the query side. Provide a sample of how the datasets look if you need help on that front. – StevenWhite May 26 '16 at 17:57
  • @alejandrozuleta added a drawing. Does that help? – Dom Vito May 26 '16 at 19:00
  • @StevenWhite does the drawing help exemplify what the data set looks like? – Dom Vito May 26 '16 at 19:00
  • @DominicG.Yeah, this should be done in the query, not the report. Your report will end up being unmaintainable if you go down this path. – StevenWhite May 26 '16 at 21:47
  • @StevenWhite how can I do this in the query? Create a list or something? – Dom Vito May 26 '16 at 23:53
  • @StevenWhite can I add all the blue numbers together and get a total? That would at least give me something to go off of – Dom Vito May 27 '16 at 00:01
  • @DominicG. Start by joining the second dataset as a subquery. If you run into a specific problem with that, post a new question showing what you tried and where you're stuck. – StevenWhite May 27 '16 at 15:31

1 Answers1

0

Try using the following expression in the blue column:

=Sum(Fields!Qty.Value, "DataSetName") - RunningValue(Fields!Qty.Value, Sum, Nothing)

Replace DataSetName by the actual name of your dataset.

This expression is substracting the running value to the total sum of Qty field.

I've recreated a simple example of your requeriment:

enter image description here

Using the above expression in the highlighted cell I get this:

enter image description here

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • The "Total" field you reference is not a sum. It is a different number (aka the running value does not equal the total in all cases). Also, how do I code for the second half, since I am using LookupSets? here is what I have: RunningValue((LookupSet(Fields!PART_NO.Value, Fields!PART_NO.Value, Fields!QTY_REQD.Value, "ShopOrder")), Sum, "ShopOrder") – Dom Vito May 26 '16 at 20:45
  • @DominicG., What is the column where you are using LookupSet? My solution doesn't require any Lookup function. – alejandro zuleta May 26 '16 at 20:49
  • I get the Qty number by using the LookupSet. (5, 10, 5, 10 in your example.) – Dom Vito May 26 '16 at 20:52
  • `LookupSet` gives you multiple values in an one-dimensional array. How are you getting rows? Maybe converting the array to string and adding End of Line between Qty's?. – alejandro zuleta May 26 '16 at 20:58
  • I am not getting rows, it is returning like in the picture I added above. Is there any way at all to get a runningvalue of the array? – Dom Vito May 26 '16 at 20:59
  • Conversely, how difficult is converting the array to a string and adding EOL betweetn Qtys? It would be much easier to work with my existing format here, I am at a bit of a time crunch. – Dom Vito May 26 '16 at 21:01
  • It think it is possible by using custom code. Passing every value of the array and the total as args to the function, for every value the function will return the correct value. – alejandro zuleta May 26 '16 at 21:10
  • I just don't think I'm that technically inclined to write that. Do you know of anyway to even get a sum of the QTYs from the LookupSet? At least that would be of value. – Dom Vito May 26 '16 at 21:12