Define a custom query

Before executing a query, Insight compares database types and takes the corresponding custom query. If no custom query exists for the target database, the query defined for Any is attempted, and if it does not exist, an error message is shown.

You can use <FromDate>, <ToDate>: These parameters define the data load interval.

If the execution plan is triggered from the Viewer, you can use the following templates.

• <FromDate>, <ToDate>: The time interval from the dashboard or data load.

• <UserProperties::Login>: The login of the authorized user. This property can be used only with the Insight user or Login form.

• <Parameter::ParameterName>: The value of the parameter driver with the ParameterName parameter on the dashboard is used to replace the template. If no parameter driver exists, the default value from the parameter is used if defined. If there is no default value, the template is replaced with NULL.

• <DashboardFilter::Dimension>: The Dimension parameter is the ID of the dimension, which is the source for the filter. Dashboard filter is used to replace the template when the data is requested for the record.

• <UserFilter::FilterName>: The value of the FilterName user filter for a logged in user is used to replace the template when the data is requested for the record.

• IsFilterEmpty(<UserFilter::FilterName>)/IsFilterEmpty(<DashboardFilter:Dimension>): Use this template to check if the Dashboard filter or the User filter have any values. In case the filter is empty, the function is replaced with "1=1"; otherwise, it is replaced with "1<>1".

Insight replaces the User filter and Dashboard filter expressions with string values from the filter using the Unicode format. If you need to use a non-Unicode format, wrap the template in single quotes, for example: '<DashboardFilter::Dimension>'. If the filter contains multiple values, they are separated by commas, for example: N'A', N'B', or 'A','B' for non-Unicode formats.

If the filter option does not contain any values, the template is replaced with NULL.

For example, the SQL query may look like the following.

INSERT INTO [Actions]	
                ([Action],[Login], [ExternalSessionId], [Comment], [Time], [ReassignUser], [InvoiceId], [HasInvoiceId]) 
          VALUES (
	               'Reassign', 	
                <UserProperties::Login>, 	
                <UserFilter::ExternalSessionId>,	
                <Parameter::Comment>,	
                GETDATE(),	
                <DashboardFilter::User>,	
                <DashboardFilter::InvoiceId>,	
                CASE WHEN IsFilterEmpty(<DashboardFilter::InvoiceId>) THEN TRUE ELSE FALSE END);