Group: Measures & Dimensions (Reports)

The idea of this fields is have an easy way to create reports based on an entity, referring to the posibility of using the commands to group the information (group by statement) and perform aritmethic operations (ie: sum statement) on a query.

It's important to know that when this attributes are used the result of the get service is replaced by a query that uses thoses attributes and is different form the original described by the entity (match attribute)

This attributes are objects inside of the the group attribute of the all service of the entiity.

{
	"name": "invoice",
	...
	"service": {
		"all": {
			"group":{
				"order":[...],
				"dimensions":{
					...
				},
				"measures":{
					...
				}
			}
		}
	}
}

Measures

Represents the groups by which the query will be grouped

"dimensions": {
	"<group_name>": {
		"field": "<field_name>",
		"attribute": "<attribute_name>"
	}
}

group_name is the name used to indicate the service to use the group
field defines the field for wich the query will be group
attribute defines alias o name of the field inside the query

Example of a query with the above attributes:

SELECT  field_name as attribute_name FROM entity.table GROUP BY  field_name

Dimensions

Represents the operations (usually arithmetic) that will be performed and will be include as part of the query

"measures": {
	"<measure_name>": {
		"field": "<operation>",
		"attribute": "<attribute_name>"
	}
}

measure_name is the name used to indicate the service to use the group
field operation to perform as a field of the query. i.e.: sum(total), count(amount)
attribute alias o name of the field inside the query

Example of a query with the above attributes:

SELECT  operation as attribute_name FROM entity.table

Not only measures can be a sql operation the dimension also can, the only thing is that one has to remember that dimensions are fields to group the query. For both dimensions and measure the operations are (o can be) limited/restricted by the database type used.

To use this attributes on the query it's necessary to add them to the service request as url parameters, usin GROUP to indicate which attributes of the groupe will be used and using AGGREGATE to indicate which opererations will be included on the query.

http://localhost:8080/api/entity?GROUP=dimension_1,dimension_2&AGGREGATE=measure_1,measure_2

Order

When making reports one sometimes is important to order the queries for convenience. The group attribute works with two types of order a default one and a requested one. If no order order is given the query will be order by the dimensions in the order they are declare on the entity json.

Default order

Is the default order for the query and it's defined as and array of dimensions and measures, each followed by the type of order (asc or desc). If not type or order is defined the default will depend on th database used.

"group":{
	"order": ["field_1","field_2 asc","field_3 desc"]
	"dimensions":{
		"field_2":{...},
		"field_4":{...}
	},
	"measures":{
		"field_1":{...},
		"field_3":{...}	
	}
}

Request order

Is the order requested on the servcie call and overwrites the default order (if it was declared). The request order is include in the service URL under the parameter GROUP and the sintaxis for using it is similar to the default order with two diferences:

a field must always had an order type
the sintaxis is field'+'order_type

Using the above JSON an example of URL using ORDER will be:

http://localhost:8080/api/entity?GROUP=dimension&AGGREGATE=measure&ORDER=field_1+desc,field_4+asc

Is important to note that the fields in the order are the name used declared in the dimensions and measures.

This is a basic entity for invoice that will be modify for the examples:

{
	"name": "invoice",
	"path": "/invoice",
	"table": "invoice",
	"weight": 0,
	"pk": "invoiceid",
	"fk": {
		"invoicetypeid": {
			"relation": "invoicetypeid@invoicetype",
			"field": "invoicetypeid",
			"type": "object",
			"value": "invoicetype.invoicetypeid"
		}
	},
	"mapping": [
		{
			"field": "invoiceid@invoice",
			"attribute": "invoiceid",
			"external": true
		},
		{
			"field": "date@invoice",
			"attribute": "date"
		},
		{
			"field": "number@invoice",
			"attribute": "number"
		},
		{
			"field": "total@invoice",
			"attribute": "total"
		},
		{
			"field": "tax@invoice",
			"attribute": "tax"
		},
		{
			"field": "invoicetypeid@invoicetype",
			"attribute": "invoicetype@invoicetypeid",
			"external": true
		},
		{
			"field": "name@invoicetype",
			"attribute": "invoicetype@name",
			"external": true
		}
	],
	"service": {
		"all": {}
	}
}

Result:

{
   "invoiceid": "8",
    "date": "2016-07-10T00:00:00.000Z",
    "number": "10",
    "total": "37.00",
    "tax": "21.00",
    "invoicetype": {
      "invoicetypeid": "1",
      "name": "A"
    }
},
{...},
{
  "invoiceid": "12",
  "date": "2016-07-10T00:00:00.000Z",
  "number": "3",
  "total": "12.00",
  "tax": "0.00",
  "invoicetype": {
    "invoicetypeid": "3",
    "name": "C"
  }
}

Here is some examples of a report with the invoice, using the mesures and dimensions.

Examples

Example 1

Grouping by type and showing the totals for each type.

"service": {
		"all": {
			"group":{
				"dimensions": {
					"type": {
						"field": "invoicetype.name",
						"attribute": "type"
					}
				},
				"measures":{
					"total":{
						"field":"sum(total)",
						"attribute":"total"
					}
				}
			}
		}
	}

Result:

{
  "type": "A",
  "total": "150.00"
},
{
  "type": "B",
  "total": "41.00"
},
{
  "type": "C",
  "total": "12.00"
}

In the example of above the URL to make the get should be:

http://localhost:8080/api/invoice?GROUP=type&AGGREGATE=total

The query that service will generate wil be something like:

SELECT invoicetype.name as type,sum(total) as total FROM invoice  INNER JOIN invoicetype ON invoicetype.invoicetypeid=invoice.invoicetypeid  WHERE GROUP BY invoicetype.name   ORDER BY invoicetype.name

Example 2

Add invoice date to the group and a basic tax estimation

"dimensions": {
	"type": {
		"field": "invoicetype.name",
		"attribute": "type"
	},
	"origin_date": {
		"field": "date",
		"attribute": "date"
	}
},
"measures":{
	"total":{
		"field":"sum(total)",
		"attribute":"total"
	},
	"tax":{
		"field":"sum(total) * 0.21",
		"attribute":"basic_tax"
	}
}

URL of the service

http://localhost:8080/api/invoice?GROUP=type,origin_date&AGGREGATE=total,tax

Result

{
  "type": "A",
  "date": "2016-07-11T00:00:00.000Z",
  "total": "150.00",
  "basic_tax": "31.5000"
},
{
  "type": "B",
  "date": "2016-07-09T00:00:00.000Z",
  "total": "4.00",
  "basic_tax": "0.8400"
},
{
  "type": "B",
  "date": "2016-07-10T00:00:00.000Z",
  "total": "37.00",
  "basic_tax": "7.7700"
},
{
  "type": "C",
  "date": "2016-07-10T00:00:00.000Z",
  "total": "12.00",
  "basic_tax": "2.5200"
}

Example 3

In the above example maybe is better format the date for something more user friendly, only the origin_date attribute needs to be updated. (In the example the to_char method reffers to the PostgreSQL)

"origin_date": {
	"field": "to_char(date,'YYYY-MM-DD')",
	"attribute": "date"
}

URL of the service:

Same than at Example 2

Result

 {
  "type": "A",
  "date": "2016-07-11",
  "total": "150.00",
  "basic_tax": "31.5000"
},
{
  "type": "B",
  "date": "2016-07-09",
  "total": "4.00",
  "basic_tax": "0.8400"
},
{
  "type": "B",
  "date": "2016-07-10",
  "total": "37.00",
  "basic_tax": "7.7700"
},
{
  "type": "C",
  "date": "2016-07-10",
  "total": "12.00",
  "basic_tax": "2.5200"
}

Example 4

Add default order by date and total.

"group":{
	"order":["origin_date asc","total desc"],
	"dimensions": {
		"type": {
			"field": "invoicetype.name",
			"attribute": "type"
		},
		"origin_date": {
			"field": "date",
			"attribute": "date"
		}
	},
	"measures":{
		"total":{
			"field":"sum(total)",
			"attribute":"total"
		},
		"tax":{
			"field":"sum(total) * 0.21",
			"attribute":"basic_tax"
		}
	}
}

URL of the service

http://localhost:8080/api/invoice?GROUP=type,origin_date&AGGREGATE=total,tax

Result

{
  "type": "B",
  "date": "2016-07-09",
  "total": "4.00",
  "basic_tax": "0.8400"
},
{
  "type": "B",
  "date": "2016-07-10",
  "total": "37.00",
  "basic_tax": "7.7700"
},
{
  "type": "C",
  "date": "2016-07-10",
  "total": "12.00",
  "basic_tax": "2.5200"
},
{
  "type": "A",
  "date": "2016-07-11",
  "total": "150.00",
  "basic_tax": "31.5000"
}

This service will generate a query like:

SELECT invoicetype.name as type,to_char(date,'YYYY-MM-DD') as date,sum(total) as total,sum(total) * 0.21 as basic_tax FROM invoice  INNER JOIN invoicetype ON invoicetype.invoicetypeid=invoice.invoicetypeid  WHERE  GROUP BY invoicetype.name,to_char(date,'YYYY-MM-DD')  ORDER BY to_char(date,'YYYY-MM-DD') asc,sum(total) desc

Example 5

Send a request order to order by total and type (ovewrite the default if exists).

The only thing that changes is the URL of the service

http://localhost:8080/api/invoice?GROUP=type,origin_date&AGGREGATE=total,tax&ORDER=total+desc,type+asc

Result

{
  "type": "A",
  "date": "2016-07-11",
  "total": "150.00",
  "basic_tax": "31.5000"
},
{
  "type": "B",
  "date": "2016-07-10",
  "total": "37.00",
  "basic_tax": "7.7700"
},
{
  "type": "B",
  "date": "2016-07-09",
  "total": "4.00",
  "basic_tax": "0.8400"
},
{
  "type": "C",
  "date": "2016-07-10",
  "total": "12.00",
  "basic_tax": "2.5200"
}

Entities: Group, Dimension and Measures - dinmax/letrest GitHub Wiki

Group: Measures & Dimensions (Reports)

Measures

Dimensions

Order

Default order

Request order

Examples

Example 1

Example 2

Example 3

Example 4

Example 5

⚠️ GitHub.com Fallback ⚠️

Entities: Group, Dimension and Measures - dinmax/letrest GitHub Wiki

Group: Measures & Dimensions (Reports)

Measures

Dimensions

Order

Default order

Request order

Examples

Example 1

Example 2

Example 3

Example 4

Example 5

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️