Bridge Parameter - Date Range Script

[Amir (Amir Sadovic)]

Hi guys,

I have created a Bridge Parameter for a Date hierarchies, and I am creating a script for this according to your information here: https://www.dundas.com/support/learning/documentation/cleanse-consolidate-modify-data/using-a-bridge-parameter

This implies for section 2.6 – Date Range Selection “Range Member”, under your documentation.

Problem:

I am going to connect this bridge parameter with a “calendar range” filter in the dashboard. It works perfect when I have date values in “from” and “to” , but it does not work when either “from” or “to” date range section is empty (when its on “all” or “open range boundary”).

Works when it is like this:

Does not work like this:

I was wondering if someone can take a look at my script and help me out here?

var parameterValue = $input$.Resolve();

var datetimeFrom = parameterValue.LowerBoundaryValue.MemberTime;

var datetimeTo = parameterValue.UpperBoundaryValue.UpperBoundaryTime;

if (!parameterValue.IncludeLowerBoundary) {

datetimeFrom = parameterValue.LowerBoundaryValue.UpperBoundaryTime;

datetimeTo = parameterValue.UpperBoundaryValue.MemberTime;

}

return new RangeDateTimeValue($id$, datetimeFrom, true, datetimeTo, true);

[jamie.cherwonka]

Hi - I think the problem is that these particular tokens can’t be “resolved” to any particular time hierarchy member like most other tokens would be, and that’s why UpperBoundaryValue is null and results in an error that stops the execution of this particular script (LowerBoundaryValue may similarly be null if a similar token is selected there). This case may not have been addressed by the particular example you were looking at that needs to pass specific dates directly into a SQL query’s single WHERE clause with only >= and < operators, because there is not really any date or other value to pass here that properly corresponds with “Open Range Boundary” or “All”.

It’s possible to decide exactly which tokens are available on a filter and exclude some by setting its Shown Tokens property, or if these token values should be available and work, you may need to decide what to do with them in the script depending on where this RangeDateTimeValue is being used that you’re returning in the script. For example I don’t know if this is somehow being used in a manual query or somewhere else. For a manual query, maybe it might be written to accept null and treat that as open range boundary, or you might decide to use some kind of specific reasonable date appropriate for your case far enough in the past and/or future to represent “open range boundary” or “all”. There are other constructors for RangeDateTimeValue that allow you to pass on the original token directly, either when the token can’t be resolved or maybe instead of trying to resolve it if it’s appropriate: https://www.dundas.com/support/api-docs/net/#html/T_Dundas_BI_Data_Parameters_RangeMemberValue.htm

[Amir (Amir Sadovic)]

Hi Jamie, sorry for the late reply Busy days

We have created a “master cube” which consists of several source cubes joined in together in the master cube. This is for architectural purposes and to make the performance of the cube best as possible. The manual query in the source cubes is quite big and comprehensive.

Furthermore, we want to create bridge parameters for all the date-dimensions in the master cube and connect this with a range-date filter in the dashboard. The possibilities would then be to filter on these alternatives in the dashboard:

1. “All” dates
2. “From first date (lower boundary)” --> Open Range (upper boundary)
3. Open range (lower boundary) --> “To second date” (upper boundary)
4. “From first date” --> “To second date”

Furthermore, I just started testing on one random date to see if it works.
I selected our standard time dimension for this date in the bridge parameter, which is “Year --> Quarter --> Month --> Day”.

I tested another script which almost worked perfectly. It works when we have “From first date” --> “To second date”, but when we try the other alternatives it only return “all dates” in the range-date filter.

Right now, the script is returning “All” when I have open range at the upper boundary and the lower boundary. I want it to show the date I selected in the filter --> The open range boundary.

In the picture below I want it to return 2017 --> open range boundary, but it returns “All”, which means it returns all the dates in the figure I have used this filter on. The same applies from the other way around (open range --> 2017 for example).

I suspect this is due to the:

“var return_dt = new RangeDateTimeValue(PredefinedToken.All.Id, PredefinedToken.All);”

I have tried using PredefinedToken.OpenRangeBoundary, but without any success. Do you have any tips on this script?

Lastly, if I select “All” in the date filter, it does not work either.

Script:

var parameterValue = $input$.Resolve();

//check if open range boundary is selected.

if (parameterValue.UpperBoundaryToken != null && parameterValue.UpperBoundaryValue == null || parameterValue.LowerBoundaryToken != null && parameterValue.LowerBoundaryValue == null)
{
var return_dt = new RangeDateTimeValue(PredefinedToken.All.Id, PredefinedToken.All);
}
else
{
var datetimeFrom = parameterValue.LowerBoundaryValue.MemberTime;
var datetimeTo = parameterValue.UpperBoundaryValue.UpperBoundaryTime;
if (!parameterValue.IncludeLowerBoundary)
{
datetimeFrom = parameterValue.LowerBoundaryValue.UpperBoundaryTime;
datetimeTo = parameterValue.UpperBoundaryValue.MemberTime;
}
var return_dt = new RangeDateTimeValue($id$, datetimeFrom,true,datetimeTo,true);
}
return return_dt;

[jamie.cherwonka]

Hi,

I think when there is just a single “All” token selected for From & To at once, there is no lower vs. upper boundary value, instead the single Token property provides this overall value. You could check for this first:

if (parameterValue.Token != null) {
   return new RangeDateTimeValue($id$, parameterValue.Token); 
}

This passes the original token through, which is an option that might simplify your other script if you do effectively just want to pass “Open Range” or “All” values through that could not be resolved. I guess you are maybe using bridge parameters in your case to expose a single parameter outside the data cube that is connected to multiple source cube parameters at once, or if not I’m curious what exactly the bridge parameter is connected to?

[Amir (Amir Sadovic)]

Hi Jamie,

Thanks for the tip

It seems like it works for all tokens I choose expect for “Open Range Boundary”. So you have to manually select when it does not work, but in this first phase that’s enough And yes you are right, I am using bridge parameters to expose a single parameter outside the data cube.

Here was my script:

var parameterValue = $input$.Resolve();

//check if open range boundary is selected.

if (parameterValue.Token != null) {
var return_dt = new RangeDateTimeValue($id$, parameterValue.Token);
}

else if (parameterValue.UpperBoundaryToken != null) // when token is selected in 'to date’
{
var datetimeTo = new SingleDateTimeValue($id$, parameterValue.Token);
var datetimeFrom = parameterValue.LowerBoundaryValue.UpperBoundaryTime;

   var return_dt = new RangeDateTimeValue($id$, datetimeFrom,true,datetimeTo,true);

}

else if (parameterValue.LowerBoundaryToken != null) // when token is selected in 'from date’
{
var datetimeFrom = new SingleDateTimeValue($id$, parameterValue.Token);
var datetimeTo = parameterValue.UpperBoundaryValue.MemberTime;
var return_dt = new RangeDateTimeValue($id$, datetimeFrom,true,datetimeTo,true);
}

else
{
var datetimeFrom = parameterValue.LowerBoundaryValue.MemberTime;
var datetimeTo = parameterValue.UpperBoundaryValue.UpperBoundaryTime;
if (!parameterValue.IncludeLowerBoundary)
{
datetimeFrom = parameterValue.LowerBoundaryValue.UpperBoundaryTime;
datetimeTo = parameterValue.UpperBoundaryValue.MemberTime;
}
var return_dt = new RangeDateTimeValue($id$, datetimeFrom,true,datetimeTo,true);
}

return return_dt;