Loading...

MacroExpansionProvider API

The MacroExpansionProvier has an API that you can use to implement your macros. The API provides services and properties for getting information about the current solution, metadata about objects in the solution, the arguments passed to the current execution context (typically originating from SetParamValue(…) in Workbooks), and so on.


MacroExpansionProvider.Context

The Context API provides information about the current execution context of the macros. For example, for macros in a SQL Script, the current execution context of the macros is the invocation of the SQL Script.

The Context has the following APIs


Properties:

ObjectId : string

The Id of the object in the current execution context, for example the Object Id of a SQL Script or Report.

SolutionId : string

The ID of the solution in the current execution context, for example the id of the solution of the SQL Script being executed.

IsDevMode : boolean

Indicates whether the execution context is being invoked from the Designer or not.

Arguments : ArgumentsProvider

Provides access to read the arguments passed in to the execution context, for example the arguments passed by SetParamValue(…) when executing a SQL Script of loading data to a SQL Report.

CustomArguments : ArgumentsList

Provides an API for creating custom arguments from within macros. When generating parameterized SQL expressions, it is best practice to use query parameters instead of coding the values into the expressions. You can use the CustomArguments list to register query parameters from within your macro.

UserId : string

The id of the currently logged-in user, for example, user@corp.com.

GetUserUniqueId() : string

Returns the ID of the user account of the currently logged in user. If Azure AD is used as the identity provider, this is the OID (GUID) coming from Azure AD. If Windows AD is used, the ID is the SID.

LanguageCode : string

The culture name in the format languagecode2-country/regioncode2, for example, EN-US. Note that this property usually just returns a two-letter language code (for example EN), because InVision is usually set up to use two-letter cultures codes.

TwoLetterLanguageCode : string

The ISO 639-1 two letter language code, for example, EN.


The ArgumentsProvider has the following APIs

bool TryGetValue<T>(string name, out T value)

bool TryGetValue(string name, out object value)

T GetValue<T>(string name)

object GetValue(string name)

bool Contains(string name)


The following example shows how the Context API can be used to create the ConditionalWhere macro.

namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Profitbase.Invision.Scripting.Dynamic;
public class MyMacros : MacroExpansionProvider
{
public string ConditionalWhere(string tableAlias)
{
if(this.Context.IsDevMode)
{
return "WHERE 1 = 1";
}
else
{
if(this.Context.Arguments.TryGetValue<int>("@Level", out int level))
{
// @SelectedProductId is passed as argument to the execution context
// for example by SetParamValue(...).
// If it is not, provide a default value
if(!this.Context.Arguments.Contains("@SelectedProductId"))
{
this.Context.CustomArguments.Add("@SelectedProductId", "myDummyValue");
}
switch(level)
{
case 1:
return $"WHERE {tableAlias}.L1 = @SelectedProductId";
case 2:
return $"WHERE {tableAlias}.L2 = @SelectedProductId";
default:
return $"WHERE {tableAlias}.LEAF = @SelectedProductId";
}
}
else
{
return "WHERE 1 = 2";
}
}
}
}
}

MacroExpansionProvider.SqlCommandService

The SqlCommandService provides an API to execute arbitrary sql commands. (Examples further down).


Methods:

Task<T> ExecuteScalarAsync<T>(string connectionString, string query, params SqlParameter[] sqlParameters)

Executes a query and returns the first column of the first row on the result of the query.

Task<IEnumerable<string>> GetColumnNamesAsync(string connectionString, string dbObjectName)

Gets the name of all of the columns in the database object.


MacroExpansionProvider.ObjectModel

The ObjectModel API provides access to metatadata information about the objects in the solution, for example getting connection strings, database table names for materialized objects, column names in the materialized objects etc. (Examples further down).


Method:

IObjectModelReference Object(string solutionObjectName)

Returns an instance of a metadata providerservice for the specified solution object.


Method:

IDimensionReference Dimension(string dimensionName)

Returns an instance of a metdata provider service for the dimension with the specified name.

IObjectModelReference


Method:

IObjectModelStorageService Storage()

Returns an instance of a service that provides information about the materialized storage object (database table) of a solution object.


Method:

IDimensionMetadataService Metadata()

Retuns an instance of a service that provides metadata information about the dimension.


IObjectModelStorageService

Provides APIs for accessing information about the materialized storage object (database table) of a solution object


Method:

Task<IEnumerable<string>> GetColumnNamesAsync(bool includeSystemColumns = false)

Returns the column names in the database tableof the solution object.

Task<string> GetConnectionStringAsync()

Returns the connection string to the database table.

Task<string> GetDbObjectNameAsync()

Returns the database object name (table, view) of the solution object.


Examples

The following example shows how the Context, SqlCommandService and ObjectModel API can be used to create a macro (SelectList) that returns a collection of columns that can be used as the select list of a query.

namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Linq;
using Profitbase.Invision.Scripting.Dynamic;
public class MyMacros : MacroExpansionProvider
{
public async Task<string> SelectList()
{
var customerMetadataStorage = this.ObjectModel.ForObject("Customer Metadata").Storage();
var connectionString = await customerMetadataStorage.GetConnectionStringAsync();
var customerMetadataTableName = await customerMetadataStorage.GetDbObjectNameAsync();
var customerCategory = await this.SqlCommandService.ExecuteScalarAsync<int>(connectionString,
$"SELECT CustomerCategory FROM {customerMetadataTableName} WHERE CustomerID = @customerId",
new SqlParameter("@customerId", this.Context.Arguments.GetValue("@CustomerID")));
if(customerCategory == 1)
{
return string.Join(",", (await this.ObjectModel.ForObject("Customer Sales")
.Storage().GetColumnNamesAsync()).Where(c => c.Contains("Cat1")));
}
else
{
return string.Join(",", (await this.ObjectModel.ForObject("Customer Sales")
.Storage().GetColumnNamesAsync()).Where(c => !c.Contains("Cat1")));
}
}
}
}

IDimensionMetadataService


Method:

Task<Hierarchy> GetHierarchyAsync(string? name = null)

Returns the hierarchy with the specified name. If no name is specified, the first hierarchy is returned.


Hierarchy


Methods

string GetIdColumn()

Returns the name of the leaf level column which contains the id of the dimension member

string GetNameColumn(string? langId = null)

Returns the name of the leaf level column which contains the display name of the dimension member

string GetIdColumn(int level)

Returns the name of the column at the specified level which contains the id of the dimension member

string GetNameColumn(int level, string? langId = null)

Returns the name of the column at the specified level which contains the display name of the dimension member


Example

The following example shows to use the dimension metadata API to create a SQL query which will return data from the leaf and level 1. It will generate the following query:

SELECT L1, L1_Name,ItemID, ItemID_Name_EN
FROM Dim_MyDim_11062021094945

namespace Local
{
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Profitbase.Invision.Scripting.Dynamic;
public class MyMacros : MacroExpansionProvider
{
public async Task<string> CreateQuery()
{
var hierarhcy = await this.ObjectModel.Dimension("PCDim").Metadata().GetHierarchyAsync();
var tableName = await this.ObjectModel.Dimension("PCDim").Storage().GetDbObjectNameAsync();
var idColumnName = hierarhcy.GetIdColumn(1);
var displayColumnName = hierarhcy.GetNameColumn(1);
var leafIdColumnName = hierarhcy.GetIdColumn();
var leafDisplayColumnName = hierarhcy.GetNameColumn(this.Context.LanguageCode);
return $"SELECT {idColumnName}, {displayColumnName},{leafIdColumnName}, {leafDisplayColumnName} FROM {tableName}";
}
}
}