Update Member Variable Programmatically

Author: Joyce Zhou

Introduction

If you're using a Member Variable as a Filter's default value, you might want to update the value of the Member Variable programmatically. Let's take a look at how to do that, in this article.

Example

1. Open SQL Server Management Studio and connect to your server. 

2. Select New Query and enter your query to update the UniqueName and Properties column in the [dbo].[ApplicationMemberVariableMembers] table.

Assuming we have a Member Variable called Current Month, here is a sample query 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

3. Click Execute to run the query.