Create a filter object with a custom expression
tip
You can try out this workflow at REST API Playground.
Learn more about MicroStrategy REST API Playground here.
This workflow sample demonstrates how to create a new filter object using a custom expression through the Modeling service
- Create a changeset using
POST /api/model/changesets - Create a new filter using a custom expression with
POST /api/model/filters. - Commit the changeset using
POST /api/model/changesets/{changesetId}/commit - Delete the changeset using
DELETE /api/model/changesets/{changesetId}
- Escape sequences for full paths
- Escape sequences for double quotes
- Escape sequences for the applycomparison function
Additional samples are provided in More samples.
You want to create a new "(Year(CurrentDate()) - 5)< Year@ID" filter object under the Public Objects folder in the MicroStrategy Tutorial project. The folder object ID is 98FE182C2A10427EACE0CD30B6768258. The project ID is B7CA92F04B9FAE8D941C3E9B7E0CD754.
info
Get the authorization token needed to execute the request with POST /api/auth/login.
Get the project ID from GET /api/projects.
Changesets are used in this workflow. For information on how to create and use changesets, see Changesets.
Create a new filter using a custom expression with POST /api/model/filters.
Sample Request Header:
"accept": "application/json"
"X-MSTR-AuthToken": "ns42kvi9lb36ae7g3scphn0ga9"
"X-MSTR-MS-Changeset": "8DF1659E9D74484D9D47B9478D4C7D00"
Construct the Request Body:
Make a copy of the input template and modify <filter_name>, <destination_folder_id>, and <custom_expression> accordingly to construct the request body. The template looks like the following:
{
"information": {
"subType": "filter",
"name": "<filter_name>",
"destinationFolderId": "<destination_folder_id>"
},
"qualification": {
"tokens": [
{
"value": "%",
"type": "character"
},
{
"value": "<custom_expression>"
}
]
}
}
Sample Request Body:
{
"information": {
"subType": "filter",
"name": "filter_for_last_5_years",
"destinationFolderId": "98FE182C2A10427EACE0CD30B6768258"
},
"qualification": {
"tokens": [
{
"value": "%",
"type": "character"
},
{
"value": "(Year(CurrentDate()) - 5)< Year@ID"
}
]
}
}
Sample Curl:
In this sample workflow, you want to return the newly created filter and its custom expression in all formats. Thus, you should set showFilterTokens=true, showExpressionAs=tree, and showExpressionAs=tokens.
curl -X POST "https://demo.microstrategy.com/MicroStrategyLibrary/api/model/filters?showFilterTokens=true&showExpressionAs=tokens&showExpressionAs=tree" -H "accept: application/json" -H "X-MSTR-AuthToken: ns42kvi9lb36ae7g3scphn0ga9" -H "X-MSTR-MS-Changeset: 8DF1659E9D74484D9D47B9478D4C7D00" -H "Content-Type: application/json" -d '{"information":{"subType":"filter","name":"filter_for_last_5_years","destinationFolderId":"98FE182C2A10427EACE0CD30B6768258"},"qualification":{"tokens":[{"value":"%","type":"character"},{"value":"(Year(CurrentDate()) - 5)< Year@ID"}]}}'
Sample Response Body: You can view the new filter's definition in the body of the response.
{
"information": {
"versionId": "C181D36B086346A885CD2C7F77A06BFA",
"primaryLocale": "en-US",
"objectId": "F31F4B055D7C408EA7847FF5728EE767",
"subType": "filter",
"name": "filter_for_last_5_years",
"destinationFolderId": "98FE182C2A10427EACE0CD30B6768258"
},
"qualification": {
"text": "(Year(CurrentDate) - 5) < Year (ID)",
"tree": {
"type": "predicate_custom",
"predicateId": "2A2870D13AE04C21A7CE053453BF7A38",
"predicateText": "(Year(CurrentDate) - 5) < Year (ID)",
"predicateTree": {
"expression": {
"text": "(Year(CurrentDate) - 5) < Year (ID)",
"tree": {
"function": "less",
"children": [
{
"function": "minus",
"children": [
{
"function": "year",
"children": [
{
"function": "current_date",
"levelType": "metric_level",
"type": "operator"
}
],
"levelType": "metric_level",
"type": "operator"
},
{
"type": "constant",
"variant": {
"type": "int32",
"value": "5"
}
}
],
"levelType": "metric_level",
"type": "operator"
},
{
"attribute": {
"objectId": "8D679D5111D3E4981000E787EC6DE8A4",
"subType": "attribute",
"name": "Year"
},
"form": {
"objectId": "45C11FA478E745FEA08D781CEA190FE5",
"subType": "attribute_form_system",
"name": "ID"
},
"type": "form_shortcut"
}
],
"levelType": "metric_level",
"type": "operator"
},
"tokens": [
{
"level": "resolved",
"state": "initial",
"value": "%",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "(",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "Year",
"type": "function",
"target": {
"dateCreated": "2001-11-09T15:47:55.000Z",
"dateModified": "2018-01-29T21:13:05.000Z",
"versionId": "32737F3011E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "6F7DF5F8449111D5BEA300B0D01A55EF",
"subType": "function",
"name": "Year",
"description": "Returns the year of the input date."
}
},
{
"level": "resolved",
"state": "initial",
"value": "(",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "CurrentDate",
"type": "function",
"target": {
"dateCreated": "2001-11-09T15:47:54.000Z",
"dateModified": "2018-01-29T21:13:05.000Z",
"versionId": "32363DBE11E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "6F7DF5F1449111D5BEA300B0D01A55EF",
"subType": "function",
"name": "CurrentDate",
"description": "Returns the current date based on the database timer. This function takes no input parameters."
}
},
{
"level": "resolved",
"state": "initial",
"value": "(",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": ")",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": ")",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "-",
"type": "character",
"target": {
"dateCreated": "2001-01-02T20:47:41.000Z",
"dateModified": "2018-01-29T21:13:06.000Z",
"versionId": "330711DC11E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "8107C311DD9911D3B98100C04F2233EA",
"subType": "function",
"name": "-",
"description": "Returns the difference between two values."
}
},
{
"level": "resolved",
"state": "initial",
"value": "5",
"type": "integer"
},
{
"level": "resolved",
"state": "initial",
"value": ")",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "<",
"type": "character",
"target": {
"dateCreated": "2001-01-02T20:47:39.000Z",
"dateModified": "2018-01-29T21:13:07.000Z",
"versionId": "3333B30411E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "8107C318DD9911D3B98100C04F2233EA",
"subType": "function",
"name": "<",
"description": "Returns TRUE if the first value is lesser than the second value."
}
},
{
"level": "resolved",
"state": "initial",
"value": "Year@ID",
"type": "object_at_form",
"target": {
"dateCreated": "2001-01-02T20:48:10.000Z",
"dateModified": "2012-01-27T12:00:32.000Z",
"versionId": "24CEEC7044C6C7008EE0DF96565E9459",
"primaryLocale": "en-US",
"objectId": "8D679D5111D3E4981000E787EC6DE8A4",
"subType": "attribute",
"name": "Year"
},
"attributeForm": {
"objectId": "45C11FA478E745FEA08D781CEA190FE5"
}
},
{
"level": "resolved",
"state": "initial",
"value": "",
"type": "end_of_text"
}
]
}
}
},
"tokens": [
{
"level": "resolved",
"state": "initial",
"value": "%",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "(",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "Year",
"type": "function",
"target": {
"dateCreated": "2001-11-09T15:47:55.000Z",
"dateModified": "2018-01-29T21:13:05.000Z",
"versionId": "32737F3011E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "6F7DF5F8449111D5BEA300B0D01A55EF",
"subType": "function",
"name": "Year",
"description": "Returns the year of the input date."
}
},
{
"level": "resolved",
"state": "initial",
"value": "(",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "CurrentDate",
"type": "function",
"target": {
"dateCreated": "2001-11-09T15:47:54.000Z",
"dateModified": "2018-01-29T21:13:05.000Z",
"versionId": "32363DBE11E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "6F7DF5F1449111D5BEA300B0D01A55EF",
"subType": "function",
"name": "CurrentDate",
"description": "Returns the current date based on the database timer. This function takes no input parameters."
}
},
{
"level": "resolved",
"state": "initial",
"value": "(",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": ")",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": ")",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "-",
"type": "character",
"target": {
"dateCreated": "2001-01-02T20:47:41.000Z",
"dateModified": "2018-01-29T21:13:06.000Z",
"versionId": "330711DC11E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "8107C311DD9911D3B98100C04F2233EA",
"subType": "function",
"name": "-",
"description": "Returns the difference between two values."
}
},
{
"level": "resolved",
"state": "initial",
"value": "5",
"type": "integer"
},
{
"level": "resolved",
"state": "initial",
"value": ")",
"type": "character"
},
{
"level": "resolved",
"state": "initial",
"value": "<",
"type": "character",
"target": {
"dateCreated": "2001-01-02T20:47:39.000Z",
"dateModified": "2018-01-29T21:13:07.000Z",
"versionId": "3333B30411E8053925F00080EF7500C5",
"primaryLocale": "en-US",
"objectId": "8107C318DD9911D3B98100C04F2233EA",
"subType": "function",
"name": "<",
"description": "Returns TRUE if the first value is lesser than the second value."
}
},
{
"level": "resolved",
"state": "initial",
"value": "Year@ID",
"type": "object_at_form",
"target": {
"dateCreated": "2001-01-02T20:48:10.000Z",
"dateModified": "2012-01-27T12:00:32.000Z",
"versionId": "24CEEC7044C6C7008EE0DF96565E9459",
"primaryLocale": "en-US",
"objectId": "8D679D5111D3E4981000E787EC6DE8A4",
"subType": "attribute",
"name": "Year"
},
"attributeForm": {
"objectId": "45C11FA478E745FEA08D781CEA190FE5"
}
},
{
"level": "resolved",
"state": "initial",
"value": "",
"type": "end_of_text"
}
]
}
}
Response Code: 201 (A new filter is created successfully in the changeset.)
More samples
The following are additional examples to replace <custom expression> from the input template.
Escape sequences for full paths
Use full paths to the object to avoid ambiguity. Make sure to use escape sequences for any backslashes in the expression.
The sample request body shown below contains escape sequences for the backslashes in the following expression:
\Schema Objects\Attributes\Geography\Region]@ID=1
Sample Request Body:
{
"information": {
"subType": "filter",
"name": "<filter_name>",
"destinationFolderId": "<destination_folder_id>"
},
"qualification": {
"tokens": [
{
"value": "%",
"type": "character"
},
{
"value": "[\\Schema Objects\\Attributes\\Geography\\Region]@ID=1"
}
]
}
}
Escape sequences for double quotes
Make sure to use escape sequences for any double quotes in the expression.
The sample request body shown below contains escape sequences for double quotes in the following expression:
Promotion@DESC in ("Valentine's Day", "Summer Fun")
Sample Request Body:
{
"information": {
"subType": "filter",
"name": "<filter_name>",
"destinationFolderId": "<destination_folder_id>"
},
"qualification": {
"tokens": [
{
"value": "%",
"type": "character"
},
{
"value": "Promotion@DESC in (\"Valentine's Day\", \"Summer Fun\")"
}
]
}
}
Escape sequences for the ApplyComparison function
If you use the ApplyComparison function in an expression, make sure to use escape sequences for the double quotes.
The sample request body shown below contains escape sequences for double quotes within the following ApplyComparison function:
ApplyComparison (\"#0 between #1 and #2\",? [Value Prompt Date], [Order Date]@ID, [Ship Date]@ID)
Sample Request Body:
{
"information": {
"subType": "filter",
"name": "<filter_name>",
"destinationFolderId": "<destination_folder_id>"
},
"qualification": {
"tokens": [
{
"value": "%",
"type": "character"
},
{
"value": "ApplyComparison (\"#0 between #1 and #2\",? [Value Prompt Date], [Order Date]@ID, [Ship Date]@ID)"
}
]
}
}