tabular SQL, Tabular reference, tabular model, tabular set

[arroju_venkat]

Hi ,

what are tabular SQL, Tabular reference, tabular model, tabular set????????
Plz. explain ,where & how to use ???????????

[Desperado]

There are four types of Tabular Objects.
1. Tabular Model
2. Tabular SQL
3. Tabular Set
4. Tabular Reference
Only one tabular object can be added to the Tabular Data area of the cube object. However, multiple tabular objects can be added to child tabular objects. (These will be discussed later in this documentation.)
Tabular Model
The Tabular Model is the most basic of tabular objects. It is a basic list query. This is the type of object that Cognos inserts if you allow it to build the query automatically as you build the report.
The default setup for a tabular model is show in the screenshots below. There are areas to insert data items, filters, and child tabular objects.
By default, the Auto Group & Summarize property is set to YES.
Tabular Reference
A tabular reference is a shortcut to another tabular object somewhere in another query (within the same report specification). A tabular reference contains only one property - Query. This points the tabular reference to the appropriate query in your report specification. Nothing else about a tabular reference can be modified. For example, you cannot rename the tabular reference, add data items to the tabular reference, or open the tabular reference to add child tabular objects.
If you have a single set of data that you want to group and filter in different ways, a tabular reference can be very helpful. You can create a single query that pulls in all data items needed (including common calculations) and filters the data appropriately. If you then create a new query that has a tabular reference back to the original, you can add other filters and group the data without affecting the master set of data. You can then create a second query that points back to the original and apply a completely different set of filters and grouping criteria. These two secondary queries are completely independent of each other, but are both using the same set of master data.
Tabular SQL
Tabular SQL objects allow you to write and edit your own SQL. You can enter your SQL in either the database syntax or Cognos syntax. After you have entered your SQL, Cognos will parse the statement and give the projected data items. This allows you to then add dimensions and levels to your cube object based upon your SQL.
When specifying your own SQL, you need to tell Cognos what database to point to. One of the properties of the Tabular SQL object is Connection. By choosing the ellipsis next to this property, you can choose your database from this list. For DSS users (regardless of Student, Financial, or Employee access), you will need to choose DSS_cognosone from the list of connections. For other databases, please choose the correct database from the list.
For tabular object besides the tabular SQL, the combination of the tabular objects and the cube objects will determine the SQL passed to the database for the query specification. However, tabular SQL objects override that and explicitly define the SQL to be passed.
While viewing the Generated SQL for a regular tabular model or a tabular set, you have the option to ‘Convert to SQL.’ If you choose this option, Cognos will convert your tabular model/set into a tabular SQL object. Once you do this, you cannot convert back to a tabular model/set object. DO NOT CONVERT TO SQL UNLESS YOU REALLY WANT TO MODIFY THE SQL MANUALLY.
Tabular Set
A tabular set object returns a single result set using the union, intersect, or except (minus) operators. A tabular set will merge rows from two result sets. Any type of tabular object can be inserted into the left and right pieces of the tabular set. You could put a tabular set in the left and a tabular SQL in the right. Any combination of tabular objects is acceptable.
A tabular set allows you to create two separate tabular objects and combine their results into one set of data. There are a couple of considerations that you need make as you are designing your tabular set. First, both the left and right data sets need to have the same number of columns. Secondly, the data types of each corresponding data item must be the same. For example, if column 1 in the left set is numeric data, column 1 in the right set must also be numeric.
Tabular sets give you the option to remove or preserve the duplicate rows. If the left tabular object has the exact row produced by the right tabular object, you can choose to keep oone of the rows or to keep both rows.
Child Tabular Objects and Their Applications
Child tabular objects are only allowed to be added within a tabular model. Any number of child models may be added to any one parent model. You can also nest an unlimited number of tabular models. However, it is suggested that you keep nesting to a minimum to improve processing speed and reduce the complexity of the query.
Sub-Queries
You can use a sub-query to filter one set of data based on the existence (or non-existence) of a matching value in another set of data. To do this, you will use child tabular models.
Let’s look at an example. We want to know the current month-end balance of all accounts that were overdrafted last month. To do this, we need to define 2 sets of data: (1) all accounts overdrafted last month and (2) current balances of all of those accounts.
The parent tabular model will be a listing of the current month balances for all accounts. The child model will contain a listing of all of our accounts that were overdrafted last month. To connect the two datasets and limit our parent model, we need to add a filter. We want to filter the parent to only return those accounts that are in the child set of data.
This sub-query will produce the following SQL:
Using Derived Tables
Derived tables are virtual tables that can then be used to create a more complex result set. They can be created by adding two or more child tabular models to a parent tabular model.
For example, we want to see year-to-date transactions as well as month-to-date transactions for a given set of accounts. To get the two different sets of transactions, we are going to need two different queries to allow for the different date filters required. Insert a tabular model for each set of data. Join the two by adding a filter to set the account numbers from each set of data equal to each other.
Viewing the tabular data for the parent tabular model yields the following partial results. You can see that we know returned both the YTD transactions as well as the MTD transactions for each account.

[arroju_venkat]

Thank you verymuch ......


Arroju_Venkat

There are four types of Tabular Objects.
1. Tabular Model
2. Tabular SQL
3. Tabular Set
4. Tabular Reference
Only one tabular object can be added to the Tabular Data area of the cube object. However, multiple tabular objects can be added to child tabular objects. (These will be discussed later in this documentation.)
Tabular Model
The Tabular Model is the most basic of tabular objects. It is a basic list query. This is the type of object that Cognos inserts if you allow it to build the query automatically as you build the report.
The default setup for a tabular model is show in the screenshots below. There are areas to insert data items, filters, and child tabular objects.
By default, the Auto Group & Summarize property is set to YES.
Tabular Reference
A tabular reference is a shortcut to another tabular object somewhere in another query (within the same report specification). A tabular reference contains only one property - Query. This points the tabular reference to the appropriate query in your report specification. Nothing else about a tabular reference can be modified. For example, you cannot rename the tabular reference, add data items to the tabular reference, or open the tabular reference to add child tabular objects.
If you have a single set of data that you want to group and filter in different ways, a tabular reference can be very helpful. You can create a single query that pulls in all data items needed (including common calculations) and filters the data appropriately. If you then create a new query that has a tabular reference back to the original, you can add other filters and group the data without affecting the master set of data. You can then create a second query that points back to the original and apply a completely different set of filters and grouping criteria. These two secondary queries are completely independent of each other, but are both using the same set of master data.
Tabular SQL
Tabular SQL objects allow you to write and edit your own SQL. You can enter your SQL in either the database syntax or Cognos syntax. After you have entered your SQL, Cognos will parse the statement and give the projected data items. This allows you to then add dimensions and levels to your cube object based upon your SQL.
When specifying your own SQL, you need to tell Cognos what database to point to. One of the properties of the Tabular SQL object is Connection. By choosing the ellipsis next to this property, you can choose your database from this list. For DSS users (regardless of Student, Financial, or Employee access), you will need to choose DSS_cognosone from the list of connections. For other databases, please choose the correct database from the list.
For tabular object besides the tabular SQL, the combination of the tabular objects and the cube objects will determine the SQL passed to the database for the query specification. However, tabular SQL objects override that and explicitly define the SQL to be passed.
While viewing the Generated SQL for a regular tabular model or a tabular set, you have the option to ‘Convert to SQL.’ If you choose this option, Cognos will convert your tabular model/set into a tabular SQL object. Once you do this, you cannot convert back to a tabular model/set object. DO NOT CONVERT TO SQL UNLESS YOU REALLY WANT TO MODIFY THE SQL MANUALLY.
Tabular Set
A tabular set object returns a single result set using the union, intersect, or except (minus) operators. A tabular set will merge rows from two result sets. Any type of tabular object can be inserted into the left and right pieces of the tabular set. You could put a tabular set in the left and a tabular SQL in the right. Any combination of tabular objects is acceptable.
A tabular set allows you to create two separate tabular objects and combine their results into one set of data. There are a couple of considerations that you need make as you are designing your tabular set. First, both the left and right data sets need to have the same number of columns. Secondly, the data types of each corresponding data item must be the same. For example, if column 1 in the left set is numeric data, column 1 in the right set must also be numeric.
Tabular sets give you the option to remove or preserve the duplicate rows. If the left tabular object has the exact row produced by the right tabular object, you can choose to keep oone of the rows or to keep both rows.
Child Tabular Objects and Their Applications
Child tabular objects are only allowed to be added within a tabular model. Any number of child models may be added to any one parent model. You can also nest an unlimited number of tabular models. However, it is suggested that you keep nesting to a minimum to improve processing speed and reduce the complexity of the query.
Sub-Queries
You can use a sub-query to filter one set of data based on the existence (or non-existence) of a matching value in another set of data. To do this, you will use child tabular models.
Let’s look at an example. We want to know the current month-end balance of all accounts that were overdrafted last month. To do this, we need to define 2 sets of data: (1) all accounts overdrafted last month and (2) current balances of all of those accounts.
The parent tabular model will be a listing of the current month balances for all accounts. The child model will contain a listing of all of our accounts that were overdrafted last month. To connect the two datasets and limit our parent model, we need to add a filter. We want to filter the parent to only return those accounts that are in the child set of data.
This sub-query will produce the following SQL:
Using Derived Tables
Derived tables are virtual tables that can then be used to create a more complex result set. They can be created by adding two or more child tabular models to a parent tabular model.
For example, we want to see year-to-date transactions as well as month-to-date transactions for a given set of accounts. To get the two different sets of transactions, we are going to need two different queries to allow for the different date filters required. Insert a tabular model for each set of data. Join the two by adding a filter to set the account numbers from each set of data equal to each other.
Viewing the tabular data for the parent tabular model yields the following partial results. You can see that we know returned both the YTD transactions as well as the MTD transactions for each account.