Assign Values using a SQL expression

[shaleen7]

Goal:

Number of Cases Per Month
Mar Apr May June 4 month goal
Name
Henry 15 32 23 11 100
Marcia 23 69 15 2 150
Jerry 5 3 8 56 125

I want to assign a specified value ( the 4 month goal) to each employee. Can I assign these values in a SQL expression and then add it to my crosstab query.

 

[synapsevampire]

I think so.

The syntax is database dependent, which you didn't share.

In SQL Server a CASE would work, in Oracle a DECODE.

You might also create a Crystal formula which states:

If {table.name} = "Henry" then
100
else
If {table.name} = "Marcia" then
150
If {table.name} = "Jerry" then
125
else
0

-k

 

[lbass]

I don't think you will be able to add the goals column using an inserted crosstab and still get the layout you show above. You could do this with a manual crosstab though. Group on {table.name} and then create a formula for each column. Each month column would be based on a formula like:

if {table.date} in date(2003, 03, 01) to date(2003, 03, 31) then {table.value}

The goals column would be based on a formula like SV's.

Insert a summary on each formula to get results at the group footer level (for the goals column, you would need to insert a maximum or minimum to return the value itself), and then suppress the details.

-LB

 

[shaleen7]

Thanks it worked