CODEWITHSUNDEEP
reporting-servicesaxaptassrs-2012dynamics-365-operations
jhowe
jhowe

Reputation: 10848

AX SSRS use expression from another dataset in total

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) enter image description here 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?

Upvotes: 0

Views: 252

Answers (2)

jhowe
jhowe

Reputation: 10848

This is actually really simple. Find the Textbox name that contains the expression. enter image description here

(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.

enter image description here

Upvotes: 1

BishNaboB
BishNaboB

Reputation: 1070

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. Report Properties Window
  • 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"))

Upvotes: 1

Related Questions