Submit a request

Update Member Variable Programmatically

Avatar
Kepion Support
  • Updated

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.

 

1-SQL_query.png

 

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. Select Execute to run the query. 

Related articles