using Dundasscript / C# in calculated elements

#1

Hi there! I’ve been trying a few different ways to calculate a difference in months between two date time columns in a datacube using a calculated element. I was looking at different tramsforms and it looks like there is not one that fits exactly what I need. I started out trying C# and then realized not all C# would work, so started following links the “Writing data scripts with DundasScript” page. I started with time span, thinking at least the simplest version of the script should work in a calculated element before I start editing,

When I use the example expression from calculated elements:

DateTime date1 = $Status_StartDate$
return date1.Year;

I get "Unexpected symbol ‘date1’ Line 2: column 8

I’ve also tried setting an alias in other ways and haven’t been able to. I have also been trying to work with TimeSpan, which I think would actually be the best way to write an expression that I want, and am struggling with even a simple variation on this as well

Return Timespan = $Status_EndDate$ - $Status_StartDate$

I understand DundasScript is a combination of different languages and am struggling with where I’m having issues with the syntax, have other people run into this issue / or successfully used DundasScript to create a time difference column using a “calculated element” in their cube?

#2

I calculate the difference in months using a script like:
return (($EndDateHere$.Year * 12) + ($EndDateHere$.Month)) - (($StartDateHere$.Year * 12) + ($StartDateHere$.Month));

I normally compare the dates to today, so you could swap in DateTime.Today.Year and DateTime.Today.Month if your EndDate isn’t a data value in your data.

#3

So, I was using a similar script and tried this variation, I guess the end dates being some unknown / some having a value is probably the problem. Before when I was doing more basic expressions it just returned unknown so I was hoping I would do that for this when there’s an unknown end date but that must be the case. I’ll keep working on this, thanks.

#4

Hi Heather,

Be sure to null check your scripts and return something meaningful too.

if ($columname$ == null)
{
return something…
}

2 Likes
#5

Hi Heather,

The script you based on the calculated elements article gives me the same message only when it’s missing one of the semi-colons, which is not optional in C# or DundasScript (at least between lines). This is the full example with both semi-colons:

DateTime date1 = $ModifiedDate$;
return date1.Year;

When calculating a TimeSpan, keep in mind this is not one of the types available to choose as the calculated element’s Data Type so you can’t return one directly. You could instead use its properties like TotalHours, and optionally calculate something using those before returning a number with Data Type set to a numeric type like Double.

#6

So, since posting this I’ve tried a bunch of different variations of the syntax I mentioned to return the values that I want… and @jamie.cherwonka you are absolutely right about the semicolon, hah, I can’t believe I didn’t see that originally, anyway I did actually realize it eventually and started using some of the expressions I was trying to use in this cube with other cubes and found that I at least had no problem running

DateTime date1 = $Status_StartDate$;

return date1.Year;

in other cubes I have access to. It’s just that cube, which pulls from a view, I’m having problems with and keep getting an error message from. I can’t figure out if it’s a problem with the data or what.

I’m getting these errors, and have now emailed dundas support and am taking a break from googling and trying to resolve what’s going on to work in a different project haha.

Anyone have to resolve something like this before?

Error 1 of 3

Title:
Data retrieval failed, as it was not possible to generate a valid query for the specified metric set.

Details:
Web response code: 500 Internal Server Error (Dundas.BI.Data.DataRetrieval.InvalidQueryException)
at Dundas.BI.Data.DataRetrieval.ReaderProcessor.Process()
at Dundas.BI.Data.DataRetrieval.TransformPlan.GetCellset(Int32 pageSequenceStart, Int32 pageSequenceSize)
at Dundas.BI.Entities.DataCubes.DataCubeService.PreviewTransformOutput(DataCube dataCube, Guid transformId, Int32 pageSequenceStart, Int32 pageSequenceSize, IEnumerable1 parameterValues, IEnumerable1 sortingRules)
at Dundas.BI.WebApi.Controllers.DataCubeController.PreviewTransform(Guid id, PreviewTransformOptions options, Nullable1 sessionId) at lambda_method(Closure , Object , Object[] ) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass6_1.<GetExecutor>b__3(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary2 arguments, CancellationToken cancellationToken)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ApiControllerActionInvoker.d__1.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ExceptionFilterResult.d__6.MoveNext()

Version: 6.0.1.1001

Error 2 of 3

Title:
Runtime error Object reference not set to an instance of an object. Line: 2 Column: 14

Details:
Web response code: 500 Internal Server Error (Dundas.BI.Scripting.ScriptException)
at Dundas.BI.Scripting.ScriptEngineData.Throw(ScriptException scriptException)
at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)
at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)
at Dundas.BI.Scripting.ReturnNode.Run(Object inputObj)
at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)
at Dundas.BI.Scripting.CodeBlockNode.Run(Object inputObj)
at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)
at Dundas.BI.Scripting.ScriptEngine.RunScript()
at Dundas.BI.Scripting.SuperScript.RunScript(SuperScriptState state)
at Dundas.BI.Data.Transforms.ScriptProcessor.ExecuteScript(ScriptElement element, StepInfo step, Boolean isFinalResult, SuperScriptState scriptState)
at Dundas.BI.Data.Transforms.ScriptProcessor.ComputeScriptStep(ScriptElement element, Int32 stepIndex, SuperScriptState scriptState)
at Dundas.BI.Data.Transforms.ScriptProcessor.ComputeElement(ScriptElement element)
at Dundas.BI.Data.Transforms.ScriptProcessor.EvaluateOutputExpressions()
at Dundas.BI.Data.Transforms.StandardTransforms.CalculatedElementTransform.Read()
at Dundas.BI.Data.DataRetrieval.TransformReader.Read()
at Dundas.BI.Data.DataRetrieval.ReaderProcessor.ConvertRawTabularResult(ITransformReader transformReader)
at Dundas.BI.Data.DataRetrieval.ReaderProcessor.ConvertTabularResult(ITransformReader transformReader)
at Dundas.BI.Data.DataRetrieval.ReaderProcessor.Process()

Error 3 of 3

Title:
Object reference not set to an instance of an object.

Details:
Web response code: 500 Internal Server Error (System.NullReferenceException)
at System.Object.GetType()
at Dundas.BI.Scripting.PropertyNode.Run(Object inputObj)
at Dundas.BI.Scripting.ScriptTreeNode.Run(Object inputObj, ScriptTreeNode parent)

#7

This top message “Data retrieval failed, as it was not possible to generate…” is a blanket message meant for people potentially seeing this outside the cube, while error 2 below is indicating an error in the script along with the line and column (character) numbers in that script where the error occurred. “Object reference not set to an instance of an object” means it was trying to access a property or method on a null value and you should be able to check for null like Jeff mentioned to avoid it

#8

This is what ended up working in case anyone has interest. I guess there were just some syntax details I was missing, I also kept using single quotes in different iterations, hah. C# is new to me! But slowly getting a little more familiar with it.

DateTime now = DateTime.Now;
if ($Status_StartDate$ == null)
{
return “n/a”;}
else if ($Status_EndDate$ == null)
{
return ((now.Year * 12) + (now.Month)) - (($Status_StartDate$.Year * 12) + ($Status_StartDate$.Month));
}
else {
return (($Status_EndDate$.Year * 12) + ($Status_EndDate$.Month)) - (($Status_StartDate$.Year * 12) + ($Status_StartDate$.Month));
}