Update Member Variable Programmatically

Author: Joyce Zhou

Introduction

If you're using a Member Variable as the default value of a filter, it may sometimes be desirable to update the value of the Member Variable programmatically. Let's take a look at how to do that.

Example

To modify the selected value of a Member Variable programmatically, we need to update both the UniqueName and Properties column in the [dbo].[ApplicationMemberVariableMembers] table.

Assume we have a Member Variable called Current Month. Here is a sample query that we can use to update its selection:

UPDATE VM
SET VM.UniqueName = N'[Time].[Months].&[' + CONVERT(NVARCHAR(MAX),DT.MemberId) + N']'
,VM.Properties = N'{"MemberLabel":"' + DT.MemberLabel + N'","MemberName":"' + DT.MemberName + N'","Input":"' + CONVERT(NVARCHAR(MAX),DT.Input) + N'","Annotate":"' + CONVERT(NVARCHAR(MAX),DT.Annotate) + N'","CalendarType":"' + DT.CalendarType + N'","Granularity":"'+ DT.Granularity + N'","Date":"'+ CONVERT(NVARCHAR(MAX),FORMAT(DT.[Date],'MM/dd/yyyy HH:mm:ss')) + N'","FirstDayOfMonth":"'+ CONVERT(NVARCHAR(MAX),DT.FirstDayOfMonth) + N'","FirstDayOfYear":"' + CONVERT(NVARCHAR(MAX),DT.FirstDayOfYear) + N'","LastDayOfYear":"'+ CONVERT(NVARCHAR(MAX),DT.LastDayOfYear) + N'","Year":"'+ CONVERT(NVARCHAR(MAX),DT.[Year]) + N'","Quarter":"'+ CONVERT(NVARCHAR(MAX),DT.[Quarter]) + N'","Month":"' + CONVERT(NVARCHAR(MAX),DT.[Month]) + N'","Week":"' + CONVERT(NVARCHAR(MAX),DT.[Week])  + N'","CalendarWeek":"' + CONVERT(NVARCHAR(MAX),DT.CalendarWeek) + N'","Day":"' + CONVERT(NVARCHAR(MAX),DT.[Day]) + N'","DayOfWeek":"' + CONVERT(NVARCHAR(MAX),DT.[DayOfWeek]) + N'","MonthOfYear":"' + CONVERT(NVARCHAR(MAX),DT.MonthOfYear) + N'","QuarterOfYear":"' + CONVERT(NVARCHAR(MAX),DT.QuarterOfYear) + N'","YearName":"' + DT.YearName + N'","QuarterName":"' + DT.QuarterName + N'","MonthName":"' + DT.[MonthName] + N'","WeekName":"' + DT.WeekName + N'","CalendarWeekName":"' + DT.CalendarWeekName + N'","DayName":"' + DT.[DayName] + N'","DayOfWeekName":"' + DT.DayOfWeekName + N'","MonthOfYearName":"' + DT.MonthOfYearName + N'","QuarterOfYearName":"' + DT.QuarterOfYearName+ N'","YearDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.YearDate,'MM/dd/yyyy HH:mm:ss')) + N'","QuarterDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.QuarterDate,'MM/dd/yyyy HH:mm:ss'))+ N'","MonthDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.MonthDate,'MM/dd/yyyy HH:mm:ss'))+ N'","WeekDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.WeekDate,'MM/dd/yyyy HH:mm:ss')) + N'","CalendarWeekDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.CalendarWeekDate,'MM/dd/yyyy HH:mm:ss'))+ N'","FiscalYear":"' + CONVERT(NVARCHAR(MAX),DT.FiscalYear) + N'","FiscalQuarter":"' + CONVERT(NVARCHAR(MAX),DT.FiscalQuarter) + N'","FiscalMonth":"' + CONVERT(NVARCHAR(MAX),DT.FiscalMonth) + N'","FiscalWeek":"' + CONVERT(NVARCHAR(MAX),DT.FiscalWeek) + N'","FiscalDate":"' + CONVERT(NVARCHAR(MAX),DT.FiscalDate) + N'","FiscalMonthOfYear":"' + CONVERT(NVARCHAR(MAX),DT.FiscalMonthOfYear)+ N'","FiscalQuarterOfYear":"' + CONVERT(NVARCHAR(MAX),DT.FiscalQuarterOfYear)+ N'","FiscalYearName":"' + DT.FiscalYearName + N'","FiscalQuarterName":"' + DT.FiscalQuarterName + N'","FiscalMonthName":"' + DT.FiscalMonthName + N'","FiscalWeekName":"' + DT.FiscalWeekName + N'","FiscalDateName":"' + DT.FiscalDateName + N'","FiscalMonthOfYearName":"' + DT.FiscalMonthOfYearName+ N'","FiscalQuarterOfYearName":"' + DT.FiscalQuarterOfYearName+ N'","FiscalYearDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.FiscalYearDate,'MM/dd/yyyy HH:mm:ss'))+ N'","FiscalQuarterDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.FiscalQuarterDate,'MM/dd/yyyy HH:mm:ss'))+ N'","FiscalMonthDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.FiscalMonthDate,'MM/dd/yyyy HH:mm:ss'))+ N'","FiscalWeekDate":"' + CONVERT(NVARCHAR(MAX),FORMAT(DT.FiscalWeekDate,'MM/dd/yyyy HH:mm:ss')) + N'","Caption":"' + DT.MemberLabel + N'","IsLeaf":true,"MemberId":' + CONVERT(NVARCHAR(MAX),DT.MemberId) + N'}'
FROM ApplicationMemberVariableMembers VM
INNER JOIN ApplicationMemberVariables V
ON VM.ApplicationMemberVariableID = V.ApplicationMemberVariableID
INNER JOIN D_Time DT
ON 1 = 1
WHERE V.ApplicationMemberVariableName = N'Current Month'
AND DT.MemberId = ROUND(CONVERT(NVARCHAR(10), GETDATE(), 112) / 100, 0) * 100