CODEWITHSUNDEEP
sqlcrystal-reports
Liam neesan
Liam neesan

Reputation: 2551

Use case in Crystal report which is equivalent to SQL Query

How can I use a CASE in my Crystal Report query?

select 
    StoreID,       
    Sum(case when Year(Time)=2016 then ExtendedCost end) [Cost(2016)],
    Sum(case when Year(Time)=2017 then ExtendedCost end) [Cost(2017)],
    Sum(case when Year(Time)=2016 then ExtendedPrice end) [Sales(2016)],
    Sum(case when Year(Time)=2017 then ExtendedPrice end) [Sales(2017)]
from F_itemDailySalesParent

Upvotes: 0

Views: 51

Answers (1)

CoSpringsGuy
CoSpringsGuy

Reputation: 1645

This is 4 different Case statements with only one option. While I would agree doing that in SQL might be your best choice, in Crystal I think I would just use IF statements.

4 formulas

Cost2016 formula

if {fieldforyear} = 2016 then {ExtendedCost}

Cost2017 formula

if {fieldforyear} = 2017 then {ExtendedCost}

Sales2016 formula

if {fieldforyear} = 2016 then {ExtendedPrice}

Sales2017 formula

if {fieldforyear} = 2017 then {ExtendedPrice}

Upvotes: 1

Related Questions