416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Advanced Scripting with Parameters

Hide navigation
RSS
Modified on Mon, 27 Jan 2014 04:22 PM Categorized as Level-Advanced, Level-Intermediate, Parameters, Scripting

This article describes how to use script in situations more sophisticated than what was discussed in the first Scripting with Parameters article.

Tokens

A token represents an intelligent selection of values. They can be disabled by toggling the “Token Menu Visibility” property of the parameter control. Tokens are typically used on date-time parameters but you can also use them on categorical and numeric parameters. There are several types of tokens but the most important distinction is the difference between tokens that represent a single value (i.e. range start and range end) and those that represent an entire range (i.e. date value). The difference is described in the section dedicated to discussing date tokens.

Tokens need special consideration when scripting because they replace the use of key values. Recall the ParameterInfo structure:
Summary of the ParameterInfo Hierarchy

Summary of the ParameterInfo Hierarchy


Because tokens can represent an entire selection, keys and DashboardMemberValueData objects may be absent from the parameter structure. Basic and Date Value tokens exhibit this behavior. For example, compare the structures of a product selection versus selecting “All” products:

Manual SelectionAll Values Selection
Image
Image
DashboardMemberValueData mvdA = new DashboardMemberValueData(); DashboardMemberValueData mvdB = new DashboardMemberValueData(); string hats = "Hats"; string shoes = "Shoes"; mvdA.Values.Add(hats); mvdB.Values.Add(shoes); DashboardFilterValueData fvdA = new DashboardFilterValueData(); DashboardFilterValueData fvdB = new DashboardFilterValueData(); fvdA.MemberValues.Add(mvdA); fvdB.MemberValues.Add(mvdB); ParameterInfo prodParam = new ParameterInfo(); prodParam.FilterValues.Add(fvdA); prodParam.FilterValues.Add(fvdB); prodParam.ParameterName = "Product"; Product.UpdateFromParameterInfo(prodParam); Product.InvalidateFilter(); DashboardFilterValueData fvd = new DashboardFilterValueData(); fvd.NamedTokenValue = NamedToken.All; ParameterInfo prodParam = new ParameterInfo(); prodParam.FilterValues.Add(fvd); prodParam.ParameterName = "Product"; Product.UpdateFromParameterInfo(prodParam); Product.InvalidateFilter();

The table below lists all of the different categories of tokens.

Token CategoryCategory Description
BasicThese tokens are used in all three types of parameters: numeric, categorical, and date.
NumericThese tokens are only used in numeric parameters.
DateThese tokens are only used in date parameters.
Range EndThese tokens are only used in the second choice when you have a ranged parameter.
Range StartThese tokens are only used in the first choice when you have a ranged parameter.
Date ValueThese tokens are used in the first selection of a ranged date parameter as well as a single date parameter.

All basic tokens span the entire selection hence no DashboardMemberValueData objects are needed. However, tokens such as InfinityNegative only represent one end of a selection. Not only does it mean that these tokens are populated at the DashboardMemberValueData level but it also means that a token can be combined with an actual value. Range Start and Range End tokens exhibit this behavior. In the example below, all values less than 100 will be returned:

Mixed Range Selection
Image
ParameterInfo prodParam = new ParameterInfo(); DashboardMemberValueData mvdA = new DashboardMemberValueData(); DashboardMemberValueData mvdB = new DashboardMemberValueData(); mvdA.NamedTokenValue = NamedToken.InfinityNegative; mvdA.Operator = ComparisonOperator.GreaterThan; mvdB.Values.Add(100); mvdB.Operator = ComparisonOperator.SmallerThan; DashboardFilterValueData fvd = new DashboardFilterValueData(); fvd.MemberValues.Add(mvdA); fvd.MemberValues.Add(mvdB); ParameterInfo qtyParam = new ParameterInfo(); prodParam.FilterValues.Add(fvd); prodParam.ParameterName = "FilterParameter1"; FilterParameter1.UpdateFromParameterInfo(prodParam); FilterParameter1.InvalidateFilter();

The tables below list all of the tokens available in the current version of Dundas Dashboard. The first table is a reference by token name while the second is a reference by token category.

Token NameToken CategoryToken Friendly Name
AllBasic, Numeric, Date, Range Start, Date ValueAll values
NullBasic, Numeric, Date, Range Start, Date ValueNull
NotNullBasic, Numeric, Date, Range Start, Date ValueNot null
DefaultBasic, Numeric, Date, Range Start, Date ValueDefault
InfinityNegativeNumeric, Range Start-Infinity
InfinityPositiveNumeric, Range End+Infinity
CurrentDayDate, Range Start, Date ValueToday
CurrentWeekDate, Range Start, Date ValueCurrent week
CurrentMonthDate, Range Start, Date ValueCurrent month
CurrentYearDate, Range Start, Date ValueCurrent year
PreviousDayDate, Range Start, Date ValueYesterday
PreviousMonthDate, Range Start, Date ValuePrevious month
PreviousYearDate, Range Start, Date ValuePrevious year
MonthToDateDate, Range Start, Date ValueMonth to date
YearToDateDate, Range Start, Date ValueYear to date
CurrentDateTimeDate, Range End, Range StartNow
BeginOfTimeDate, Range StartBeginning of time
CurrentDayBeginDate, Range StartBeginning of today
CurrentWeekBeginDate, Range StartBeginning of current week
CurrentMonthBeginDate, Range StartBeginning of current month
CurrentYearBeginDate, Range StartBeginning of current year
PreviousDayBeginDate, Range StartBeginning of yesterday
PreviousMonthBeginDate, Range StartBeginning of previous month
PreviousYearBeginDate, Range StartBeginning of previous year
EndOfTimeDate, Range EndEnd of time
CurrentDayEndDate, Range EndEnd of today
CurrentWeekEndDate, Range EndEnd of week
CurrentMonthEndDate, Range EndEnd of current month
CurrentYearEndDate, Range EndEnd of current year
PreviousDayEndDate, Range EndEnd of yesterday
PreviousMonthEndDate, Range EndEnd of previous month
PreviousYearEndDate, Range EndEnd of previous year

Token CategoryTokens
BasicAll, Null, NotNull, Default
NumericAll, Null, NotNull, Default, InfinityNegative, InfinityPositive
DateAll, Null, NotNull, Default, CurrentDay, CurrentWeek, CurrentMonth, CurrentYear, PreviousDay, PreviousMonth, PreviousYear, MonthToDate, YearToDate, CurrentDateTime, BeginOfTime, CurrentDayBegin, CurrentWeekBegin, CurrentMonthBegin, CurrentYearBegin, PreviousDayBegin, PreviousMonthBegin, PreviousYearBegin, EndOfTime, CurrentDayEnd, CurrentWeekEnd, CurrentMonthEnd, CurrentYearEnd, PreviousDayEnd, PreviousMonthEnd, PreviousYearEnd
Range StartAll, Null, NotNull, Default, InfinityNegative, CurrentDay, CurrentWeek, CurrentMonth, CurrentYear, PreviousDay, PreviousMonth, PreviousYear, MonthToDate, YearToDate, CurrentDateTime, BeginOfTime, CurrentDayBegin, CurrentWeekBegin, CurrentMonthBegin, CurrentYearBegin, PreviousDayBegin, PreviousMonthBegin, PreviousYearBegin
Range EndInfinityPositive, CurrentDateTime, EndOfTime, CurrentDayEnd, CurrentWeekEnd, CurrentMonthEnd, CurrentYearEnd, PreviousDayEnd, PreviousMonthEnd, PreviousYearEnd
Date ValueAll, Null, NotNull, Default , CurrentDay, CurrentWeek, CurrentMonth, CurrentYear, PreviousDay, PreviousMonth, PreviousYear, MonthToDate, YearToDate

Date Tokens

The tokens that manage dates need special consideration since they are slightly more complex than the other tokens. Date tokens can be resolved using the services.NamedTokenHelper.ResolveToken() method. This method will provide you with either a DateTime object or a collection of DateTime objects depending on whether it is a Date Value token. The script below resolves the Range Start token CurrentDayBegin:

DateTime dt = services.NamedTokenHelper.ResolveToken(NamedToken.CurrentDayBegin, 0, DayOfWeek.Sunday);

The script below shows how to resolve the Date Value token YearToDate:

Collection cdt = services.NamedTokenHelper.ResolveToken(NamedToken.YearToDate, 0, DayOfWeek.Sunday);

When resolving a Date Value token, it’s important to remember that the operator that is used on the end date is ComparisonOperator.SmallerThan. In other words, the date range is exclusive of the end date. For example, when resolving a CurrentYear token in 2011, the dates returned will be January 1, 2011 00:00 and January 1, 2012 00:00. With the exception of CurrentDateTime, all date tokens will return a date with the time set to midnight.

Date Offset

It is possible for most of the date tokens to be offset. The offset is saved in a parameter as the .NamedTokenOffset property of a DashboardFilterValueData or DashboardMemberValueData depending on the nature of the token. Since the token offset only stores an integer, here is an easy reference to determine the scale of the offset.

Token NameToken Offset Scale
CurrentDayDay
CurrentWeekWeek
CurrentMonthMonth
CurrentYearYear
PreviousDayDay
PreviousMonthMonth
PreviousYearYear
CurrentDayBeginDay
CurrentWeekBeginWeek
CurrentMonthBeginMonth
CurrentYearBeginYear
PreviousDayBeginDay
PreviousMonthBeginMonth
PreviousYearBeginYear
CurrentDayEndDay
CurrentWeekEndWeek
CurrentMonthEndMonth
CurrentYearEndYear
PreviousDayEndDay
PreviousMonthEndMonth
PreviousYearEndYear

Here are the tokens grouped by the offset scale.

Token Offset ScaleToken Name
DayCurrentDay, PreviousDay, CurrentDayBegin, PreviousDayBegin, CurrentDayEnd, PreviousDayEnd
WeekCurrentWeek, CurrentWeekBegin, CurrentWeekEnd
MonthCurrentMonth, PreviousMonth, CurrentMonthBegin, PreviousMonthBegin, CurrentMonthEnd, PreviousMonthEnd
YearCurrentYear, PreviousYear, CurrentYearBegin, PreviousYearBegin, CurrentYearEnd, PreviousYearEnd

There may be situations where you will need to discover the dates selected in your filter. Therefore, a script to get the date values from an existing date parameter FilterParameter1 could be written as:

Collection dateRange = new Collection(); foreach (DashboardFilterValueData fvd in FilterParameter1.FilterValues) { if (fvd.NamedTokenValue != NamedToken.None) { dateRange = services.NamedTokenHelper.ResolveToken(fvd.NamedTokenValue, fvd.NamedTokenOffset, DayOfWeek.Sunday); } else { foreach (DashboardMemberValueData mvd in fvd.MemberValues) { if (mvd.Values.Count > 0) { dateRange.Add(mvd.Values0); } else { dateRange.Add(services.NamedTokenHelper.ResolveToken(mvd.NamedTokenValue, mvd.NamedTokenOffset, DayOfWeek.Sunday)); } } } }

The start date would be at the first index of dateRange while the end date would be at the second index.

Script Parameter

It can sometimes be useful to save values among scripts. Script parameters do not directly affect any elements on the dashboard. Instead, they serve as variables to hold values relevant to your scripts. Since they are independent of KPI filters, the FilterlessValue collection should be used. This is a collection of objects so it can be populated with anything including dictionaries, and other collections. The script below will show the number 3.1415 in the script trace:

Dictionary myDict = new Dictionary(); myDict.Add("reference", 3.1415); ScriptParameter1.FilterlessValue.Add(myDict);
  1. TRACE ScriptParameter1.FilterlessValue0“reference”;

Value Filtering

A measure on a KPI or a Dataset can be exposed for filtering on the dashboard. Filtering values is nearly identical to filtering dates. When affecting a range of values, two DashboardMemberValueData objects need to populate the MemberValues collection in the DashboardFilterValueData object. Each DashboardMemberValueData object must have the operator explicitly set.

ParameterInfo param = new ParameterInfo(); DashboardFilterValueData fvd = new DashboardFilterValueData(); DashboardMemberValueData mvd = new DashboardMemberValueData(); mvd.Values.Add(10000); mvd.Operator = ComparisonOperator.GreaterOrEqualThan; fvd.MemberValues.Add(mvd); mvd = new DashboardMemberValueData(); mvd.Values.Add(12800); mvd.Operator = ComparisonOperator.SmallerOrEqualThan; fvd.MemberValues.Add(mvd); param.FilterValues.Add(fvd); Parameter1.UpdateFromParameterInfo(param); Parameter1.InvalidateFilter();

External Filters

When passing values to external filters such as stored procedure parameters, the process is identical to a single value being set on a parameter as shown in the first Scripting with Parameters article. This will work regardless of whether a look-up table is used by the corresponding virtual table and KPI/Dataset.

Formula Variables

Many formulae have parameters that can be changed. If parameters for these formulas are created on the dashboard then they can be manipulated through script. Each formula parameter has a property called .FormulaValue. This is all that needs to change. For example, the script below will change a multiplier to 2.

Parameter1.FormulaValue = 2; Parameter1.InvalidateFilter();

Related topics

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.