Hi there,
In a Dynamics 365 Online environment, I've written a report which needs to establish the total number of records that are still Open and Within SLA. I have a field in my dataset called "SLAStatus" and it's value is set to OpenInSLA, OpenOutSLA, ClosedInSLA or ClosedOutSLA.
My approach is to use the LookupSet function, and return 0 or 1 depending on the value of the SLAStatus field.
As the LookuupSet function returns an array of values, I've then used the code from Salvo's blog (http://salvoz.com/blog/2013/05/27/sum-result-of-ssrs-lookupset-function/) to create the "SumLookup" function which then adds all the values in the resulting LookupSet array.
A complication is that my dataset is larger than 50,000 records, so I have split my datasets by their geographic location (called "CCC" - Client Care Centre) and thus have the resulting expression in my report:
=Sum(
Code.SumLookup(
LookupSet(Fields!CCC.Value, Fields!CCC.Value, iif(Fields!SLAStatus.Value = "OpenInSLA", 1, 0), "CCC_1")
) +
Code.SumLookup(
LookupSet(Fields!CCC.Value, Fields!CCC.Value,iif(Fields!SLAStatus.Value = "OpenInSLA", 1, 0),"CCC_2")
)
)
The above works perfectly, except I cannot upload this now to the Dynamics 365 Online platform, given the restriction of RDL Sandboxing which I've just discovered does not permit any custom code within an RDL file.
I've tried replicating the login of the custom SumLookup function within the actual expression, however no matter where I try to "sum" or "convert.todecimal" etc, I'm just not getting it right - I keep getting an error.
I've been toying with the idea of replacing all the "sum" login with merely a "countif", but haven't figured this out yet either.
Any and all suggestions welcomed!! :)
