Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

How To Implement a 4-4-5 Calendar


Overview

The 4-4-5 calendar is primarily used for the analysis across accounting periods equally.  The same applies to the other well-known 4-5-4 and 5-4-4 calendar variants.

 

 

Process

 

It is strongly recommended that only users with an advanced knowledge of SQL attempt the steps contained in this article.  If this customization needs to be made to your project but you feel you cannot complete the below steps without assistance, please contact your BI Consultant or support@jetreports.com.

 

Step 1: Add the Accounting Period Table

 

For the purposes of this example, we will be using the Accounting Period table from a NAV 2015 database.

 

If you are not using Navision and/or you do not have an Accounting Period table available, you will need to create something similar either in SQL or Excel.  The process described here relies on the specific month and year start dates to be in place in the Accounting Period table (or similar).
To bring your Accounting Period table into your Jet Data Manager project, scroll down to your Data Sources and click on the source in question.  Upon clicking on the data source, you will see the navigation pane shown on the right-hand side of the Jet Data Manager.  Scroll down to the Accounting Period table and ensure that the fields as shown below are selected (Name, New Fiscal Year, and Starting Date).

 

Once added, locate the newly added table in your Staging database.  Right-click on the table and select Deploy and Execute to continue.

 

Step 2: Modify View Query as Needed

Next, you will need to open up the file named SQLQuery that is contained within the attachment at the bottom of this KB article.  Once opened, modify the query to fit your specific fiscal calendar and ensure that the naming conventions used in the query match your local environment (i.e. Database names, table names, etc.).

To verify that the query will work within your environment, you can comment out the first line of the query by placing two dashes -- in front of the 'CREATE VIEW' statement.  Once the 'CREATE' portion is commented out, you will be able to execute the query locally. 

 

Step 3: Create View

Once you've verified that the SQLQuery file will work in your environment, the next step is to add the query into the project as a View.  To do so, right-click on the 'Views' node in your data warehouse and select 'Add Custom View'

In the window that opens, you will paste the code from the SQLQuery file into the entry section.  For the Name as in script , make sure to name it the same as the View creation title.  Click OK to proceed.


 

Once created, the next step is to deploy the view.  To do so, right-click on the view you just created and select Deploy .  In the window that opens, simply click Start to begin the deployment process and then click Close when the process finishes.

Once deployed, you will need to right-click on the view once more and this time select Read View Fields .

 

Step 4: Integrate View with Date Table

With the view created and deployed, the next step is to begin bringing the view fields into your Date table as lookups.

Each of these lookups will use the following as the join condition:

1) FiscalStartDate is less or equal to Date

2) FiscalEndDate is greater or equal to Date .


After adding all of the view fields to your date table, the next step is to create a new custom field on the date table.  To do so, right-click on your Date table and select Add Field .

In the window that opens, give the new field a name such as FiscalWeekNumber. You will also need to set the Data Type to Text , the Text Length to 5 , and check the Unicode box.  Click OK to proceed.

 

 

 

 

 

 

 

Once created, right-click on your new field and select Field Transformations .

In the Field Transformation pane that appears on the right-hand side, leave the Operator set to Custom and then click Add .

In the window that opens, you will need to paste the following code into the entry area.  If your fields are named differently, you will need to adjust the SQL code accordingly. Click OK to proceed.

 

Custom SQL Transformation

RIGHT('0' + CAST(DATEDIFF(d, [FiscalYearStartDate], [Date]) / 7 + 1 as VARCHAR), 2)

 

Once the FiscalWeekNumber field has been created, the next step is to create two additional custom fields.  This will essentially repeat the process from the FiscalWeekNumber creation.

To start, right-click on the Date table and select Add New Field .  For the two custom fields, one should be named FiscalYearWeekNumber and the other should be named FiscalYearWeekName .  The settings for these fields should be as shown below.

For each of these fields, you will be applying the following transformations:

 

Fiscal Year Week Number

CAST([FiscalYear] AS varchar(4)) + CAST([FiscalWeekNumber] as varchar(2))

Fiscal Year Week Name

CAST([FiscalYear] AS varchar(4)) + ' Wk ' + CAST([FiscalWeekNumber] as varchar(2))


 

With all of the modifications to the Date table complete, right-click on the Date table and select Deploy and Execute


 


Step 5: Create Additional Dimension Levels

The rest of this process will be conducted within the Cubes tab of the Jet Data Manager.  To add the new Fiscal elements to your Date dimension, right-click on the Date dimension and select Add Dimension Level .

You will be creating seven additional dimension levels.  Use the images below as a template.

 

Step 6: Set Dimension Level Types

For each of these 7 dimension levels that you just created, you will need to set the Type of the dimension level so that the Hierarchy will behave correctly. To do so, right-click on any of the Fiscal Dimension Levels just created and select Edit Dimension Level .

From within the window that opens, complete the following steps:

 

The step numbers listed below correlate to the numbers within the image below.

 

 

  1. Click Advanced
  2. Click the ellipses button '...'
  3. Select the appropriate Dimension Level Type (listed below)
  4. Click OK
  5. Click OK
  6. Click OK

 

Correct Dimension Level Types :

 

  • Years
    • Fiscal Year
  • Quarters
    • Fiscal Quarter & Fiscal Year Quarter
  • Months
    • Fiscal Month & Fiscal Year Month
  • Weeks
    • Fiscal Week & Fiscal Year Week

 

Step 7: Assign Dimension Level Keys

The next step is to go back through the dimension levels once more and assign a two-part Dimension Level Key .

To start, right-click on one of your Dimension Levels and select Edit Dimension Level


In the window that opens, first click the ellipses button '...' which will open up the multi-part Dimension Level Keys selector.  Once opened, scroll down until you locate Fiscal Year and click the box next to it.  Click OK within both opened windows to proceed.

 

You will need to set the two-part Dimension Level Keys for each of your new Fiscal date dimension levels before proceeding.

 

Step 8: Create Dimension Hierarchy

Now that you've created and setup the additional dimension levels for your Fiscal calendar, the next step is to create a hierarchy for these values.  To do so, right-click on your Date dimension once more and select Add Hierarchy .

In the Hierarchy window, start by giving your new hierarchy a name.  For this example, our new hierarchy will be named 'Fiscal YQMWD' (YQMWD = Year/Quarter/Month/Week/Day)

After naming your hierarchy, the next step is to select the dimension levels that you want contained within the hierarchy.  Upon clicking on a dimension level, it will be added to the hierarchy list in the right-hand pane.

Once you've added all five dimension levels as shown below, click OK to proceed.

 

The order in which the dimension levels appear within your hierarchy is important.  If the ordering of yours differs from that above, simply click-and-drag the level into the appropriate location.

The last step in updating your Date dimension requires you to assign the Dimension Level Relations .  To do so, right-click on your Date dimension and select Advanced > Dimension Level Relations

 


Once in the Dimension Level Relations window, you will simply click-and-drag items and drop them onto their child element.

To start, click and drag Fiscal Month and drop it onto the Fiscal Week element.  Continue this process by dragging Fiscal Quarter onto Fiscal Month , and finally Fiscal Year onto Fiscal Quarter . When finished, click OK to proceed.

 


Step 9: Add Scope Statement (Optional)

 

If you only want the 4-4-5 Calendar displayed, delete the standard 'Date YQMD' hierarchy and fix every time dependent measure by selecting the appropriate hierarchy.

 

The last step in implementing your 4-4-5 Calendar deals with displaying any time sensitive measures (i.e. YTD, MTD, etc.) correctly when you have your new Fiscal hierarchy in use.

To accomplish this, we need to create a Scope statement that will tell Analysis Services to calculate the measures differently anytime the Fiscal hierarchy is in use.

 

This process will need to be completed for each time-sensitive measure that exists within your cubes.  It is recommended that you name your Script Commands in such a way that you can identify them easily (i.e. Scope_INSERTMEASURENAME).

 

To start, expand the desired cube and right-click on Script Commands and select Add Script Command .

In the Add Script Command window, assign an identifiable name for the scope statement and then paste the following code into the script command window. Click OK to proceed.

 

 

 

 

 

The following sample code will need to be adjusted if used for any measures other than 'Sales Amount YTD' or any hierarchies named differently than 'Fiscal YQMWD'.
Sample Scope Statement for Sales Amount YTD

Scope(Descendants([Posting Date].[Fiscal YQMWD],,After));

[Measures].[Sales Amount YTD] = Sum(Ytd([Posting Date].[Fiscal YQMWD].CurrentMember), [Measures].[Sales Amount]);

End Scope;

 

 

 

Step 10: Use your new 4-4-5 Calendar

The last step is to use your new Fiscal Dates and Hierarchy in a PivotTable.

 

 
Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

Comments