Business Objects - Revenue Growth Report

BUSINESSOBJECTS

Tips and Tricks

BONYMAUG Meeting

April 10th, 2003

Presented by

Brie Anne Clark

http://www.creativetechtrain.com

Revenue Growth Report

Problem:

I want a report that shows the growth in sales revenue per quarter since last year, but I want BUSINESSOBJECTS to determine which years to use based on the current date, filter the report accordingly, and then calculate the variance.

Solution:

1. Create a report using Year, Quarter, and Sales revenue from the eFashion universe.

2. Set Quarter as the master cell, and then manually insert a variance that subtracts last year from this year. Also insert a percentage variance between the two years. Now you have values to which you can compare the variables that you create.

3. Next, create a variable named Last Year Flag to flag last year.

=If <Year>= FormatNumber((Year(CurrentDate())-1),"0000") Then 1

Also create a variable named Current Year Flag to flag this year.

=If <Year>= FormatNumber((Year(CurrentDate())),"0000") Then 1

4. Now that there are flags for the previous and current years, create a variable named Quarterly Variance that subtracts last year's revenue from this year's revenue.

=<Sales revenue> Where (<Last Year Flag>=1) - <Sales revenue> Where (<Current Year Flag>=1)

Also create a variable named Quarterly Variance % that subtracts last year's revenue from this year's revenue, and then divides that value by last year's revenue.

=(<Sales revenue> Where (<Last Year Flag>=1) - <Sales revenue> Where (<Current Year Flag>=1))/<Sales revenue> Where (<Last Year Flag=1)

5. Duplicate the sectioned report with the variance. Delete the variance rows from the block. Then, add a sum and an average for Sales revenue to create rows to hold the Quarterly Variance and Quarterly Variance % variables.

6. Drag Quarterly Variance and Quarterly Variance % from the report manager to replace the sum and average you inserted. Format the Quarterly Variance cell as currency and format the Quarterly Variance % cell to display as a percentage.

7. Next, create a variable named Variance Label to label the new variables.

=FormatNumber((Year(CurrentDate())) ,"0000") + "-" + FormatNumber((Year(CurrentDate())-1) ,"0000")

8. Drag Variance Label from the report manager to replace the Sum and Average labels.

9. Finally, add a filter to the block to show only last year and this year. To do this, click on the block, and then select Filters from the Format menu. In the Format Filters dialog box, select the Table 1 folder, click Add, select Year, and then click OK.

10. Next, select the Year filter under the Table 1 folder and click Define. Enter the following formula and click OK.

=<Current Year Flag>=1 Or <Last Year Flag>=1

11. Click OK to close the Format Filters dialog box and you should see the following report.

Past Six Months Summary Report

Problem:

I only want to display data from the first day of the month six months ago through the current date.

Solution:

1. Create a new report using Sales Person, Invoice Date, and Revenue from the Island Resorts Marketing universe.

2. Create a measure variable named Six Months Ago that subtracts six from the current month, and if that result is less than or equal to zero, it adds twelve. If the current month minus six is greater than zero, it returns that value.

=If MonthNumberOfYear(CurrentDate())-6<=0 Then MonthNumberOfYear(CurrentDate())+6 Else MonthNumberOfYear(CurrentDate())-6

3. Next, create a measure variable named Year of Six Months Ago that checks to see if the current month minus six is less than or equal to zero. If it is, then it returns the current year minus one. If it is a positive number, it simply returns the current year.

=If MonthNumberOfYear(CurrentDate())-6 <=0 Then Year(CurrentDate())-1 Else Year(CurrentDate())

4. Create a dimension variable named First Day of Six Months Ago that concatenates the Six Months Ago variable with an "01" (the first day of the month) and the Year of Six Months Ago variable. To do this, it must also perform the FormatNumber function on the variables because only character data types can be concatenated. This concatenation is nested within a ToDate function, which turns the concatenation into a valid, usable date.

=ToDate(FormatNumber(<Six Months Ago> ,"00") + "/" + "01" + "/" +FormatNumber(<Year of Six Months Ago> ,"0000") ,"mm/dd/yyyy")

5. Apply a filter on Invoice Date with the following definition:

=<Invoice Date> Between (<First Day of Six Months Ago> , CurrentDate())

6. Insert the following formula as a report title:

="Sales Invoices: " + FormatDate(<First Day of Six Months Ago> , "Mmmm d, yyyy") + " - " + FormatDate(CurrentDate() ,"Mmmm d, yyyy")

7. Your report should look similar to the one below.

Crosstab Labels

Problem:

I