BusinessObjects Board

Self join with 2 different columns

I am trying to create report with 2 years data with self join. The issue I am facing is I need data from one table in 2 different columns for Crystal report. Any help is appreciated. Thanks

Putting data from one table into 2 columns shouldn’t be a problem. But, you need to give more details as to what you are trying to do. What does your data look like? Why do you need a self join to get two years worth of data? What does the output need to look like?

Thanks for the response. I am trying to create a report to compare last years student count vs this years for different terms. all the data I need is in one table that is why I need self join. My report will have 2-3 columns, have different rows like student count, distance learning student count, different locations of campuses student count, New student count, returning students etc. really appreciate you help.
Thanks

You don’t need a self join for that, you’ll just add an unnecessary level of complexity.
Filter your data for the 2 years you need and do a cross tab on year and add in your various measures. Or, you can do a regular table and create two variables for each measure adding in a where year = x condition to the formula.

Two approaches for this.

Either have a smart calendar table in your database/data warehouse and leverage that or use more basic case expressions if you’re writing the SQL code yourself.

Sales TY:
SUM(CASE WHEN FactSales.SalesYear = Year(GETDATE()) THEN FactSales.SalesValue ELSE 0 END)

Sales LY:
SUM(CASE WHEN FactSales.SalesYear = Year(GETDATE())-1 THEN FactSales.SalesValue ELSE 0 END)

The approach for a smart calendar would be to have a year offset flag in your calendar table, where current year = 0 and last year =1.
Then you’d create a similar case expression logic as the above but using the YearOffset = 1, which makes the solution DBMS-agnostic…