Need help building a report 1

[Scroller52]

hi, i would say i'm an intermediate crystal user. the report i need to build seems very simple, but i am having trouble designing it correctly. i am using CRXI release 2 with a SQL server DB. any thoughts/tips would be greatly appreciated.

its basically a compensation report for all employees. i would like to have the information displayed like this:

Base Bonus Q1 Bonus Q2 Bonus Q3 Bonus Q4 Other Total
2005
2006
2007
etc..

Here is a sample of my data from my DB

EmployeeYear Quarter Base Salary Bonus Other
John 2006 3/31/2006 - 5,000 -
John 2006 6/30/2006 - 10,000 -
John 2006 9/30/2006 - 5,000 -
John 2006 12/31/2006 - 5,000 -
John 2006 12/31/2006 50,000 2,000
John 2007 3/31/2007 - 5,000 -
John 2007 6/30/2007 - 10,000 -
John 2007 9/30/2007 - 5,000 -
John 2007 12/31/2007 - 5,000 -
John 2007 12/31/2007 75,000 2,000
Diane 2006 3/31/2006 - 10,000 -
Diane 2006 6/30/2006 - 20,000 -
Diane 2006 9/30/2006 - 10,000 -
Diane 2006 12/31/2006 - 15,000 -
Diane 2007 12/31/2006 100,000 4,000
Diane 2007 3/31/2007 - 10,000 -
Diane 2007 6/30/2007 - 20,000 -
Diane 2007 9/30/2007 - 10,000 -
Diane 2007 12/31/2007 - 15,000 -
Diane 2006 12/31/2007 125,000 4,000
Steven 2006 3/31/2006 - 20,000 -
Steven 2006 6/30/2006 - 40,000 -
Steven 2006 9/30/2006 - 15,000 -
Steven 2006 12/31/2006 - 15,000 -
Steven 2006 12/31/2006 200,000 8,000
Steven 2007 3/31/2007 - 20,000 -
Steven 2007 6/30/2007 - 40,000 -
Steven 2007 9/30/2007 - 15,000 -
Steven 2007 12/31/2007 - 15,000 -
Steven 2007 12/31/2007 200,000 8,000


-Steven

 

[lbass]

Do you want this display per employee?

-LB

 

[Scroller52]

yes, the report is grouped by employee.

 

[lbass]

Okay, then insert a Group #2 on year. Place {table.salary} and {table.other} in the detail section and insert maximums on them at the year group level. Then create 1 formula per quarter like this:

//{@Qtr1}:
if datepart("q",{table.quarter}) = 1 then {table.bonus}

Repeat, changing the quarter number. Place these formulas in the detail section and insert sums at the year group level. Then suppress the detail section and drag the year groupname into the group footer and suppress the year group header.

-LB

 

[Scroller52]

thanks lbass, i'll try this out later and see if it works.

 

[Scroller52]

thanks lbass, your solution worked like a charm. however, now my boss is telling me that they would like to pivot the report so display something like this:

Employee 2003 2004 2005 2006 2007 Total
Base Salary
Bonus Q1
Bonus Q2
Bonus Q3
Bonus Q4
Other
Total

And they would want to show only for the latest 5 years. The dataset is the same but I'm pretty stumped as to how to achieve this.

 

[lbass]

Insert a group on employee and a group #2 on {@Qtr}:

datepart("q",{table.quarter})

Then create these formulas:

//{@basesal5yrsago}:
if year({table.quarter}) = year(currentdate)-5 then {table.basesalary}

//{@other5yrsago}:
if year({table.quarter}) = year(currentdate)-5 then {table.other}

//{@bonus5yrsago}:
if year({table.quarter}) = year(currentdate)-5 then {table.bonus}

Repeat these formulas for years 2004 to 2007, just changing the number you are subtracting from the current year. Add all formulas under the year column label in the detail section. For the base salary and other formulas, right click on each and insert a summary (maximum) at the employee group level. For the bonus formulas, right click and insert summaries (maximums) at the quarter group level. You can drag the employee summary to a group header1b section, and the other summary will be in group footer 1a. Then suppress the detail section.

-LB

 

[Scroller52]

Thanks Lbass, thats is similar to what i tried doing, but i kept putting the -5 inside the parenthesis...then i didnt know what the hell was going on

i'll try that tomorrow!

 

[Scroller52]

I'm having an issue where if the employee has a bonus on the same quarter on different years, the detail section is putting them in two different rows. so now it looks something like this

Employee 2003 2004 2005 2006 2007 Total
Base Salary 100 100 100 100 100 500
Bonus Q1 10
Bonus Q1 10
Bonus Q2 50
Bonus Q2 50
Bonus Q3
Bonus Q4
Other
Total

Any idea as to why this is happening? my 2nd group by quarter is this:
datepart("q",{Compensation.Quarter of GL Date})

Is there something else I need to add to combine the fields by year?

 

[lbass]

You should be inserting maximums on the detail formulas at the quarter group levels and then suppressing the detail section.

-LB

 

[Scroller52]

Thanks LB you've been amazing, your help is very much appreciated. I have one last issue that I'm not sure how to solve.

In another report, grouped by employee, deal and year, sections are not 'kept together' so for every 'deal', if it goes to the next page, i want it to start on a new page. How can i do this?

 

[lbass]

Please start a new thread.

-LB