AX SSRS use expression from another dataset in total

Question

I have a header section which is showing totals etc. I have two fields in another dataset (dataset estimates) which I want to pull into header dataset.

Fee (circled) = IIf(Right(Fields!ProjCategoryId.Value, 3) <> "EXP", Fields!Value.Value, 0) 3rd Party (circled) = IIf(Right(Fields!ProjCategoryId.Value, 3) = "EXP", Fields!Value.Value, 0)

I know you can sum datasets from another dataset with no issues, but how to use IIF etc. as well?

Make an invisible textbox and put the value in it, then call that textbox. http://dba.stackexchange.com/questions/53127/ssrs-2008-how-to-total-values-from-an-expression — Snowlockk, Mar 28 '17 at 08:31
I don't think that will work... I believe I still have to use First(Field.Value) rather than it evaluating categoryid's for every line which i want. — jhowe, Mar 29 '17 at 10:38
This works! You do not need custom code. if you provide as answer I will mark it as answer. — jhowe, Mar 30 '17 at 10:28

BishNaboB · Answer 1 · 2017-03-30T09:33:21.750

1

You can use custom code and a lookupset().

Code:

Function SumLookup(ByVal items As Object()) As Decimal
    If items Is Nothing Then
        Return Nothing
    End If

    Dim suma As Decimal = New Decimal()
    Dim ct as Integer = New Integer()

    suma = 0
    ct = 0

    For Each item As Object In items
        suma += Convert.ToDecimal(item)
        ct += 1
    Next

    If (ct = 0) Then return 0 else return suma 
End Function

To add this, right-click on the blue background of the report and select Report Properties.
Click on the Code option.
Paste it into this window
Call this with =Code.SumLookup(lookupset("EXP", right(Fields!ProjCategoryId.Value, 3), Fields!Value.Value, "DatasetName")) in your expression where you want the number to appear

This will add up all the Values where the ProjCategoryId starts with "EXP".

For <> "EXP", you might need to do several calls to it to add them all up for each thing that it can start with. For example..

Code.SumLookup(lookupset("ONE", right(Fields!ProjCategoryId.Value, 3), Fields!Value.Value, "DatasetName")) + Code.SumLookup(lookupset("TWO", right(Fields!ProjCategoryId.Value, 3), Fields!Value.Value, "DatasetName"))

edited Mar 30 '17 at 09:33

answered Mar 28 '17 at 11:24

BishNaboB

1,047
1
12
25

is there no better way of doing this? why is it so difficult to reference values from another dataset... – jhowe Mar 29 '17 at 10:39
1

You can just use a `lookup()` if it's a single value you want. `Code.SumLookup(lookupset())` is for aggregating a set of values from another dataset. – BishNaboB Mar 29 '17 at 12:17
Hi thanks for your help... i'm not too familiar with adding custom code. Could you edit your post to give me a step by step? It would be much appreciated. Also after I add the code how do i reference the values i've looked up in my dataset.... – jhowe Mar 29 '17 at 15:37
@jhowe Edited. Let me know if you face any issues following the steps. – BishNaboB Mar 30 '17 at 09:33

score 1 · Accepted Answer · answered Mar 30 '17 at 10:33

This is actually really simple. Find the Textbox name that contains the expression.

(When we say textbox this does not have to be a textbox from the toolbox, it can be a field within tablix). Where you want to reference the value simply add the expression and you can reference the result.

AX SSRS use expression from another dataset in total

2 Answers2