Limits (Collection)

Member of:

QuerySection object, DataModel object, TableSection object

Description:

Represents limits within a Results, Query, or Data Model section. It is analogous with the Limit line in Interactive Reporting. The identical syntax and collection used to manipulate regular query limit items are used for computed items. The one difference is the argument used in the Create.Limit(limit_item) method. The limit_item argument type is a string for both regular query items and computed items. The limit_item is a reference to the limit item on the limit line.

The reference to a regular query limit item is Topic.TopicItem. For example, given a Products table and a Product_Line column, the reference is CreateLimit(Products table and a Product_Line).

The reference to a computed item limit is Request.DisplayName. For example, given a "DoubleSales"computed item on the request line, the reference is CreateLimit("Request.Double Sales”).

Tip:

All collections have a “Item(NameOrIndex)” method. This is the default method for all collections that returns collection items at a particular index or by name. Use brackets ([]) to represent calls to the Item (Method). For example, these statements are identical: myItem = Documents[1] myItem = Documents.Item(1) myItem = Documents["StartUp.bqy"] myItem = Documents.Item("StartUp.bqy")

Example 1:

This example shows how to remove all existing limits and create a new query limit from an existing topic:

ActiveDocument.Sections["SalesQuery"].Limits.RemoveAll()
MyLimit = ActiveDocument.Sections["SalesQuery"].Limits.CreateLimit ("Sales_Fact.Unit_Sales")
MyLimit.Operator = bqLimitOperatorGreaterThan
MyLimit.CustomValues.Add(50)
MyLimit.SelectedValues.Add(50)
//Adds the limit to the Limit Line - 
ActiveDocument.Sections["SalesQuery"].Limits.Add(MyLimit)

Example 2:

This example shows how to remove all existing limits and create a new query computed item limit:

ActiveDocument.Sections["SalesQuery"].Limits.RemoveAll()
MyLimit = ActiveDocument.Sections["SalesQuery"].Limits.CreateLimit ("Requests.Sales_Per_Unit")
MyLimit.Operator = bqLimitOperatorLessThan
MyLimit.CustomValues.Add(20)
MyLimit.SelectedValues.Add(20)
//Adds the limit to the Limit Line - 
ActiveDocument.Sections["SalesQuery"].Limits.Add(MyLimit)

Example 3:

This example shows how to create a query computed item limit using the name of the computed item:

//Example creates a query computed item limit--use name of computed itemmylimit=ActiveDocument.Sections["Query"].Limits.CreateLimit("Request.Sales_Difference")ActiveDocument.Sections["Query"].Limits.Add(mylimit)

Example 4:

This example adds a Custom Value to an existing computed item limit:

//Example adds a CustomValue to an existing computed item limitActiveDocument.Sections["Query"].Limits[2].CustomValues.Add(‘2’)Or ActiveDocument.Sections["Query"].Limits["Sales Difference"].CustomValues.Add(‘2’)

Example 5:

This example adds a CustomSQL to an existing computed item limit:

//Example Adds CustomSQL to an existing computed item limitActiveDocument.Sections["Query"].Limits["Sales Difference"].LimitValueType=bqLimitValueTypeSQLActiveDocument.Sections["Query"].Limits["Sales Difference"].CustomSQL="SQLString"orActiveDocument.Sections["Query"].Limits[2].LimitValueType=bqLimitValueTypeSQLActiveDocument.Sections["Query"].Limits[2].CustomSQL="SQLString"

Example 6:

This example shows how to create and set variable limits:

// create and set variable limit - Store_Id
mylimit = ActiveDocument.Sections["Query"].Limits.CreateLimit("PCW_SALES.Store_Id")
mylimit.Operator = bqLimitOperatorLessThanOrEqual
mylimit.CustomValues.Add(10)
mylimit.SelectedValues.Add(10)
ActiveDocument.Sections["Query"].Limits.Add(mylimit)
mylimit.VariableLimit = true

Methods:

Add(Limit As Limit), Function CreateLimit(limitItem As String), Function Item(NameOrIndex), RemoveAll()

Note:

The argument for the CreateLimit method is different for regular limits, computed item limits, and aggregate limits. For regular limits the argument is a reference to the table topic and the topic item, for example, CreateLimit(“Sales_Facts.Amount_Sales”). For both computed item limits and aggregate limits the argument is a reference to the item’s Display Name on the request line, for example, CreateLimit(“Request.Amount Sales”).

Properties:

Read-only: Property Count As Number

Collections:

Parentheses object