Analysis Line – Dynamics 365 BC Table Reference

Dynamics 365 BC Table Reference Tables Company Tables AAnalysis Line

Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/7114

Table Type: Company

Table Name: Analysis Line
Database Table Name: CRONUS UK Ltd_$Analysis Line$437dbf0e-84ff-417a-965d-ed2bb9650972

Table Number: 7114

Due to how Dynamics BC tables and columns are named, square brackets need to be wrapped around the table name and column names. Below are example queries showing how Analysis Line can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

   [CRONUS UK Ltd_$Analysis Line$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns by name

SELECT

   [Analysis Area]
   ,[Analysis Line Template Name]
   ,[Line No_]
   ,[Row Ref_ No_]
   ,[Description]
   ,[Type]
   ,[Range]
   ,[New Page]
   ,[Show]
   ,[Bold]
   ,[Italic]
   ,[Underline]
   ,[Show Opposite Sign]
   ,[Dimension 1 Totaling]
   ,[Dimension 2 Totaling]
   ,[Dimension 3 Totaling]
   ,[Group Dimension Code]
   ,[Indentation]
   ,[$systemId]
   ,[$systemCreatedAt]
   ,[$systemCreatedBy]
   ,[$systemModifiedAt]
   ,[$systemModifiedBy]

FROM

   [CRONUS UK Ltd_$Analysis Line$437dbf0e-84ff-417a-965d-ed2bb9650972]

Show/hide an example select of all columns including those from all joined tables

SELECT
   *
FROM
   [] AS [Analysis Line]
LEFT JOIN
   [CRONUS UK Ltd_$Location$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Location]
      ON
         [Analysis Line].[Location Filter] = [Location].[Code]
LEFT JOIN
   [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension]
      ON
         [Analysis Line].[Group Dimension Code] = [Dimension].[Code]
LEFT JOIN
   [User] AS [User]
      ON
         [Analysis Line].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [Analysis Line].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in Analysis Line table

Field No.	Field Name	Field Caption	Class	Type	Length	SQL Column Name	SQL DataType
1	Analysis Area	Analysis Area	Normal	Option	4	Analysis Area	int
	Available options are: “Sales” “Purchase” “Inventory”
2	Analysis Line Template Name	Analysis Line Template Name	Normal	Code	10	Analysis Line Template Name	nvarchar(20)
3	Line No.	Line No.	Normal	Integer	4	Line No_	int
4	Row Ref. No.	Row Ref. No.	Normal	Code	20	Row Ref_ No_	nvarchar(40)
5	Description	Description	Normal	Text	100	Description	nvarchar(200)
6	Type	Type	Normal	Option	4	Type	int
	Available options are: “Item” “Item Group” “Customer” “Customer Group” “Vendor” “Sales/Purchase Person” “Formula”
7	Range	Range	Normal	Text	250	Range	nvarchar(500)
8	New Page	New Page	Normal	Boolean	4	New Page	tinyint
9	Show	Show	Normal	Option	4	Show	int
	Available options are: “Yes” “No” “If Any Column Not Zero”
10	Bold	Bold	Normal	Boolean	4	Bold	tinyint
11	Italic	Italic	Normal	Boolean	4	Italic	tinyint
12	Underline	Underline	Normal	Boolean	4	Underline	tinyint
13	Show Opposite Sign	Show Opposite Sign	Normal	Boolean	4	Show Opposite Sign	tinyint
14	Source Type Filter	Source Type Filter	FlowFilter	Option	4
15	Date Filter	Date Filter	FlowFilter	Date	4
16	Item Budget Filter	Item Budget Filter	FlowFilter	Code	10
17	Location Filter	Location Filter	FlowFilter	Code	10
18	Dimension 1 Filter	Dimension 1 Filter	FlowFilter	Code	20
19	Dimension 2 Filter	Dimension 2 Filter	FlowFilter	Code	20
20	Dimension 3 Filter	Dimension 3 Filter	FlowFilter	Code	20
21	Dimension 1 Totaling	Dimension 1 Totaling	Normal	Text	80	Dimension 1 Totaling	nvarchar(160)
22	Dimension 2 Totaling	Dimension 2 Totaling	Normal	Text	80	Dimension 2 Totaling	nvarchar(160)
23	Dimension 3 Totaling	Dimension 3 Totaling	Normal	Text	80	Dimension 3 Totaling	nvarchar(160)
24	Source No. Filter	Source No. Filter	FlowFilter	Code	20
25	Group Dimension Code	Group Dimension Code	Normal	Code	20	Group Dimension Code	nvarchar(40)
	Key to join to the Dimension table. Show/hide example query SELECT [Analysis Line].[Group Dimension Code] ,[Dimension].[Code] FROM [CRONUS UK Ltd_$Analysis Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Analysis Line] LEFT JOIN [CRONUS UK Ltd_$Dimension$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Dimension] ON [Analysis Line].[Group Dimension Code] = [Dimension].[Code]
26	Indentation	Indentation	Normal	Integer	4	Indentation	int
2000000000	$systemId	System ID	Normal	GUID	16	$systemId	uniqueidentifier
2000000001	SystemCreatedAt	Created At	Normal	DateTime	8	$systemCreatedAt	datetime
2000000002	SystemCreatedBy	Created By	Normal	GUID	16	$systemCreatedBy	uniqueidentifier
	Key to join to the User table. Show/hide example query SELECT [Analysis Line].[$systemCreatedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$Analysis Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Analysis Line] LEFT JOIN [User] AS [User] ON [Analysis Line].[$systemCreatedBy] = [User].[User Security ID]
2000000003	SystemModifiedAt	Modified At	Normal	DateTime	8	$systemModifiedAt	datetime
2000000004	SystemModifiedBy	Modified By	Normal	GUID	16	$systemModifiedBy	uniqueidentifier
	Key to join to the User table. Show/hide example query SELECT [Analysis Line].[$systemModifiedBy] ,[User].[User Security ID] FROM [CRONUS UK Ltd_$Analysis Line$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Analysis Line] LEFT JOIN [User] AS [User] ON [Analysis Line].[$systemModifiedBy] = [User].[User Security ID]