Sage Intelligence provides a lot of flexibility when it comes to creating and using reports. Often, the same task can be accomplished in different ways and you can decide which one works best for you.
In this tip, I’m going to show you three different ways to accomplish the same goal in Sage Intelligence Reporting. I’m going to work with a Sales report that has a date field providing the date of each transaction. I want to add a new field which gives the name of the month the transaction took place in based on the date field. Let’s take a look at the three methods.
1. Using a SQL expression
If you are familiar with SQL then the most convenient way to accomplish this is by using a SQL expression. I have added one to my container as follows with its SQL code below.
CASE
WHEN DATEPART(mm,CAST(CAST([OESHDT].[TRANDATE]AS VARCHAR)AS DATETIME)) = ’01’ THEN ‘January’
WHEN DATEPART(mm,CAST(CAST([OESHDT].[TRANDATE]AS VARCHAR)AS DATETIME)) = ’02’ THEN ‘February’
WHEN DATEPART(mm,CAST(CAST([OESHDT].[TRANDATE]AS VARCHAR)AS DATETIME)) = ’03’ THEN ‘March’
ELSE ‘Other’
END
This example demonstrates how a CASE statement can be used in an SQL expression. Depending on the database you are connecting to, the SQL syntax required may differ from the example above. After adding the expression to my report in the Report Manager and running it out, the source sheet looked as follows.
2. Using an Excel Formula Expression
If you are more familiar with Excel and still like working in the Connector, then another way to do this is to use an Excel Formula expression. I have added one to my container as follows with its Excel syntax below.
IF(MONTH(DOCDATE)=01,”January”,IF(MONTH(DOCDATE)=02,”February”,IF(MONTH(DOCDATE)=03,”March”,”Other”)))
There are simpler Excel functions that can be used in this case but this example shows how more complex ones can be applied. For more information on adding Excel Expressions, take a look at the following tip: How to add an Excel formula as an expression to a container.
After adding the expression to my report in the Report Manager and running it out, the source sheet looked as follows.
For more information on expressions, take a look at the section on them under the Connector chapter in your Sage Intelligence help file.
3. Using the ExtendFormulaeToUsedRows add-in
The third method is using the ExtendFormulaeToUsedRows add-in. This is useful if you don’t have a Connector license or prefer working in Excel. To learn more about the add-in and how to apply it, take a look at the following tip: Quickly and easily automate the calculation of your data.
To return the month name of the date field, the Excel formula I added to my source sheet is:
=IF(MONTH(D2)=1,”January”,IF(MONTH(D2)=2,”February”,IF(MONTH(D2)=3,”March”,”Other”)))
And when setting up the add-in, the parameters I used are as follows:
After setting up the add-in in the Report Manager and running out my report, my source sheet looked as follows.
