Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/88
Table Type: Company
Table Name: Financial Report
Database Table Name: CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972
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 Financial Report can be queried.
Show/hide an example select of all columns
SELECT
*
FROM
[CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns by name
SELECT
[Name]
,[UseAmountsInAddCurrency]
,[PeriodType]
,[ShowLinesWithShowNo]
,[Dim1Filter]
,[Dim2Filter]
,[Dim3Filter]
,[Dim4Filter]
,[CostCenterFilter]
,[CostObjectFilter]
,[CashFlowFilter]
,[GLBudgetFilter]
,[CostBudgetFilter]
,[DateFilter]
,[Description]
,[Financial Report Row Group]
,[Financial Report Column Group]
,[$systemId]
,[$systemCreatedAt]
,[$systemCreatedBy]
,[$systemModifiedAt]
,[$systemModifiedBy]
FROM
[CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972]Show/hide an example select of all columns including those from all joined tables
SELECT
*
FROM
[CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Financial Report]
LEFT JOIN
[CRONUS UK Ltd_$Acc_ Schedule Name$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Acc. Schedule Name]
ON
[Financial Report].[Financial Report Row Group] = [Acc. Schedule Name].[Name]
LEFT JOIN
[CRONUS UK Ltd_$Column Layout Name$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Column Layout Name]
ON
[Financial Report].[Financial Report Column Group] = [Column Layout Name].[Name]
LEFT JOIN
[User] AS [User]
ON
[Financial Report].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
[User] AS [User]
ON
[Financial Report].[SystemModifiedBy] = [User].[User Security ID]Show/hide columns in Financial Report table
| Field No. | Field Name | Field Caption | Class | Type | Length | SQL Column Name | SQL DataType |
|---|---|---|---|---|---|---|---|
| 2 | Name   | Name | Normal | Code | 10 | Name | nvarchar(20) |
| 3 | UseAmountsInAddCurrency | Use Amounts in Additional Currency | Normal | Boolean | 4 | UseAmountsInAddCurrency | tinyint |
| 4 | PeriodType | Period Type | Normal | Option | 4 | PeriodType | int |
Available options are:
| |||||||
| 5 | ShowLinesWithShowNo | Show All Lines | Normal | Boolean | 4 | ShowLinesWithShowNo | tinyint |
| 6 | Dim1Filter | Dimension 1 Filter | Normal | Text | 2048 | Dim1Filter | nvarchar(4096) |
| 7 | Dim2Filter | Dimension 2 Filter | Normal | Text | 2048 | Dim2Filter | nvarchar(4096) |
| 8 | Dim3Filter | Dimension 3 Filter | Normal | Text | 2048 | Dim3Filter | nvarchar(4096) |
| 9 | Dim4Filter | Dimension 4 Filter | Normal | Text | 2048 | Dim4Filter | nvarchar(4096) |
| 10 | CostCenterFilter | Cost Center Filter | Normal | Text | 2048 | CostCenterFilter | nvarchar(4096) |
| 11 | CostObjectFilter | Cost Object Filter | Normal | Text | 2048 | CostObjectFilter | nvarchar(4096) |
| 12 | CashFlowFilter | Cash Flow Filter | Normal | Text | 2048 | CashFlowFilter | nvarchar(4096) |
| 13 | GLBudgetFilter | G/L Budget Filter | Normal | Text | 2048 | GLBudgetFilter | nvarchar(4096) |
| 14 | CostBudgetFilter | Cost Budget Filter | Normal | Text | 2048 | CostBudgetFilter | nvarchar(4096) |
| 15 | DateFilter | Date Filter | Normal | Text | 2048 | DateFilter | nvarchar(4096) |
| 50 | Description | Description | Normal | Text | 80 | Description | nvarchar(160) |
| 51 | Financial Report Row Group | Financial Report Row Group | Normal | Code | 10 | Financial Report Row Group | nvarchar(20) |
Key to join to the Acc. Schedule Name table.Show/hide example querySELECT
[Financial Report].[Financial Report Row Group]
,[Acc. Schedule Name].[Name]
FROM
[CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Financial Report]
LEFT JOIN
[CRONUS UK Ltd_$Acc_ Schedule Name$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Acc. Schedule Name]
ON
[Financial Report].[Financial Report Row Group] = [Acc. Schedule Name].[Name] | |||||||
| 52 | Financial Report Column Group | Financial Report Column Group | Normal | Code | 10 | Financial Report Column Group | nvarchar(20) |
Key to join to the Column Layout Name table.Show/hide example querySELECT
[Financial Report].[Financial Report Column Group]
,[Column Layout Name].[Name]
FROM
[CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Financial Report]
LEFT JOIN
[CRONUS UK Ltd_$Column Layout Name$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Column Layout Name]
ON
[Financial Report].[Financial Report Column Group] = [Column Layout Name].[Name] | |||||||
| 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 querySELECT
[Financial Report].[$systemCreatedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Financial Report]
LEFT JOIN
[User] AS [User]
ON
[Financial Report].[$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 querySELECT
[Financial Report].[$systemModifiedBy]
,[User].[User Security ID]
FROM
[CRONUS UK Ltd_$Financial Report$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Financial Report]
LEFT JOIN
[User] AS [User]
ON
[Financial Report].[$systemModifiedBy] = [User].[User Security ID] | |||||||