Create a Macro and use it in a Transformation statement

Open the DWH ribbon

Confirm that the intended Data Warehouse (DWH) project is open. Click the DWH toolbar tab to display the commands for maintaining its objects.

Open a new Macro

Click Macros to open the list of reusable Macro definitions.

In the Macro list, click New to open the Macro editor.

Name and describe the Macro

In the Macro editor, enter an unused Macro name. Enter a Description explaining the calculation, its arguments, and its result. Use this same Macro name in the call you add later.

1. Macro — A reusable statement expanded where it is called. Changing a shared definition can affect other statements that call it.
2. Macro name — The saved name used after @ in a call. The date-key example uses CalendarDateKey; Macro 1 is the name pictured in the editor.
3. Description — A purpose note identifying the Macro's arguments and result. For the date-key example, describe its date input and 1980–2040 calculation boundaries.

Select T-SQL as the language

In Language, select T-SQL for the SQL Server view expression used in this workflow.

1. Language — The language of the Macro statement. The list also includes DAX, MDX, and SSIS. SSIS refers to SQL Server Integration Services (SSIS). Those choices are outside this T-SQL workflow.

Set the Macro's table reference

If the Macro uses a table as a reference, select that table in Referenced table. Identify it by its schema and table name according to the calculation you are defining. A schema groups database objects under a common namespace. Do not add a table reference solely to match the screenshot.

1. Referenced table — Identifies which table the Macro uses as a reference. The pictured value DWH.DIM_Calendar identifies the DIM_Calendar table in schema DWH. Select it when that is your Macro's reference table; use your own table for a different reference.

Enter the Macro statement

In Statement, enter the reusable expression. Where a value must come from the caller, use its positional placeholder. This lets different calls supply different columns or values to the same calculation.

1. Statement — The reusable expression. For this workflow, enter the calculation here; the complete CREATE VIEW statement belongs in the Transformation.
2. :1, :2, :3 — Positional placeholders for the first, second, and third call arguments. The date-key expression below uses only :1.

Date-key example: use the following expression when you need a date converted to a day-based integer key with these boundaries. Its dates are part of this example's calculation, not required Macro settings.

CASE
    WHEN :1 < '19800101' THEN 0
    WHEN :1 > '20401231' THEN
        CONVERT(bigint, DATEDIFF(DD, '19800101', '20401231') + 1)
    ELSE CONVERT(bigint,
        ISNULL(DATEDIFF(DD, '19800101', CONVERT(date, :1)) + 1, 0))
END

1. CASE / WHEN / ELSE / END — Chooses the result. A date before 1980-01-01 returns 0; a date after 2040-12-31 returns the final key in the example's range.
2. DATEDIFF(DD, ...) — Calculates a difference in days. DD is the day date-part in this expression. Adding 1 makes 1980-01-01 map to 1.
3. CONVERT(date, ...) / CONVERT(bigint, ...) — Converts the input to a date and the result to a large integer.
4. ISNULL(..., 0) — Replaces a NULL result with 0. Invalid date text can still cause a conversion error.

To calculate a key for a different date, change the argument in the call, leaving :1 in the definition. Change the boundary dates only when the key scheme itself must change; that change affects every caller using the definition.

Save and check the Macro

Check Macro name, Language, Referenced table, and the complete Statement against your intended definition. Click Save.

In the navigation tree, expand Macros, right-click your saved Macro, and click Edit macro. Confirm that its name, language, reference table, and complete statement have been retained before using it in a Transformation.

Open the prepared Manual transformation

In the dataflow diagram, double-click the prepared Transformation. Confirm that its Name and Schema identify the object you intend to edit and that TransType is Manual. Select the VIEW tab to open its manually maintained SQL.

A view is a named database query. In this editor, Name and Schema identify that view, while VIEW contains its definition. Confirm that the SQL header uses the same name and schema. If the Transformation has not yet been prepared as Manual, complete Create and save a Manual transformation first.

Insert the Macro call in the VIEW statement

In the VIEW statement, insert @MacroName(argument) where the calculation is needed. Replace MacroName with the saved Macro name and supply the expressions required by its placeholders, in positional order. For several arguments, separate them with commas, as in @MacroName(argument1, argument2).

1. @MacroName(argument) — The Macro call is replaced by the saved statement with the supplied arguments substituted. For example, @CalendarDateKey([T1].[Date]) substitutes [T1].[Date] for each :1 in the date-key definition.
2. Missing arguments — Omitted arguments become NULL. Supply every argument needed for the intended calculation rather than relying on that fallback.
3. T1 / DWH / DIM — In the SQL example, T1 is a table alias, DWH is a schema identifier, and DIM_ is an object-name prefix. Use your actual database identifiers.

Complete-view example: when the prepared view is [DWH].[Macro_Calendar] and its input is [DWH].[DIM_Calendar] with a date-compatible Date column, the following statement returns the date as CalendarDate and the calculated key as CalendarKey. Use it for those intended inputs and outputs. Otherwise, insert the call into your own complete view definition.

CREATE VIEW [DWH].[Macro_Calendar] AS
SELECT
    [T1].[Date] AS [CalendarDate],
    @CalendarDateKey([T1].[Date]) AS [CalendarKey]
FROM [DWH].[DIM_Calendar] AS [T1];

Check that each argument has a compatible type and that every referenced column belongs to an input named in the view's FROM or JOIN clauses. In the example, T1 is declared in FROM. If you rename an existing view output column, enter its previous and new names in the editor's Old column name and New column name columns.

Fixed-date check: @CalendarDateKey(CONVERT(date, '19800101')) illustrates a call whose date-key result is 1. The fixed date is a test value; use the caller's required date expression in the actual calculation.

Review and save the Transformation

Before saving, review the definition and the project's save behavior:

4.10.1. Confirm the Transformation's Name, Schema, and TransType = Manual. Check that the SQL header names the same view.

4.10.2. Confirm that the Macro name matches the saved definition, each required argument is present, and its type and position match the statement. Keep a complete view definition with the required inputs and output columns.

4.10.3. Confirm the target database and Transformations Createviews value: 0 = No does not create the view on save; 1 = Compile only requests compilation; 2 = Yes requests view creation. Proceed with the configured value only when that behavior is intended for this development project.

Click Save. If AnalyticsCreator reports an error, correct the reported problem and save again before continuing.

Check the saved call

Reopen the Transformation by double-clicking it in the dataflow diagram. Select VIEW and confirm that the saved definition contains the call to your Macro with the intended arguments. Compare the Macro's saved statement and the Transformation's call with the checks in Expected Results.

If the view has been created in the development database, query its output using your project's database query tool and compare representative inputs with the intended calculation. For the complete-view example in Insert the Macro call in the VIEW statement, query the example's output columns:

SELECT [CalendarDate], [CalendarKey]
FROM [DWH].[Macro_Calendar]
ORDER BY [CalendarDate];

For your own view, use its actual schema, name, and output columns. If saving did not create the view, saved-definition checks do not establish its database output; complete the project's view-creation and validation workflow before relying on calculated results.