In SEI reports, the information from X3 may not be in a format a client prefers. As an example, a Budget Analysis report shows Financial Year and Fiscal periods as numbers as highlighted on the screen shot. It may be desirable to have these as description years and months instead of numbers. In this blog, I will give step by step instruction to get years and months displayed on the report.

You will need to create views in the SQL backend that map the fiscal periods in number format to calendar months and the fiscal years in numbers to year descriptions.Log on to the SQL backend and on a new Query, create a month description view to convert periods to months, in the SEI_XXXX database (where XXXX is your own application folder)

In the example below, replace x3data by your own X3 database

• Do the same for converting Fiscal Year codes (1, 2, 3, …) to Year descriptions, 2018, 2019, ….

=====================================================================================

USE [x3data]

GO

/****** Object:  View [SEI_SEED].[YEAR_DESC]    Script Date: 11/20/2019 8:42:09 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE view [SEI_SEED].[YEAR_DESC]

AS

SELECT 1 AS Year,        '2018' AS Year_Text                UNION

SELECT 2 AS Year,        '2019' AS Year_Text                UNION

SELECT 3 AS Year,        '2020' AS Year_Text                UNION

SELECT 4 AS Year,        '2021' AS Year_Text                UNION

SELECT 5 AS Year,        '2022' AS Year_Text                UNION

SELECT 6 AS Year,        '2023' AS Year_Text                UNION

SELECT 7 AS Year,        '2024' AS Year_Text                UNION

SELECT 8 AS Year,        '2025' AS Year_Text                UNION

SELECT 9 AS Year,        '2026' AS Year_Text                UNION

SELECT 1 AS Year,        '2027' AS Year_Text                         

GO

Next, we need to add the views we created to the Budget analysis Process in SEI. Log on to SEI and on the Budget Analysis process, right click and select "Design Process"

On the design process window, right click on an open space and “Add table”

Select the view you created, Month_DESC(or Year_DESC)

Join the field PER_0 (Period) from the Fact Table, BUD_ACTV11 to “month” on your Month_DESC view

Edit the "Description Field" and "Description Format" as below for the Fiscal Period

For the Description Field, select Month_Text(Month_DESC.Month_Text)

Save the process

Now go to the Budget Analysis worksheet properties to set the sort order on our created descriptions.

Save and close the worksheet properties, and hold your breath.

Refresh the report, and it now displays years and months instead of the fiscal years and fiscal periods.

I hope this blog illustrates some of the flexibility available in SEI in presenting information on reports.