Select statement how to? 1

[CRilliterate]

Name Status1 Status2 Amount
ABC Cancelled NoInv $400
SCD Suspended Inv $300
VSD Active Cash $400

I need to show on Report following:

Amount where Status1 = 'Cancelled' asnd Status2 = 'NoInv'
Amount where Status1 = 'Suspended' asnd Status2 = 'Inv'
Amount where Status1 = 'Active' asnd Status2 = 'Cash'

Output
Name Cancelled/NoInv
ABC $400
SCD $300
VSD $400

Thanks


P.S. 4 columns that is ALL I have. There is no more columns.


________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[synapsevampire]

Create 3 Running Totals of the Amount, Reset set to Never, and in the Evaluate->Use a Formula place:

{table.Status1} = "Cancelled"
and
{table.Status2} = "NoInv"

The other 2 would have your other criteria:

Status1 = 'Suspended' and Status2 = 'Inv'
Status1 = 'Active' and Status2 = 'Cash'

Place them in the report footer.

-k

 

[le1burg]

In your selection criteria, test Status 1 'is one of' and insert your varied tests 'Cancelled' Suspended, Active, create a new criteria for Status 2 'is one of' NoInv Inv Cash. Having done so, you'll only get records that fit those criteria and therefore if you display the Name and Amt fields on the report, the records appearing there will be as your desired result indicates.

May the force be with you...

Charlie Flagiello
Programmer Analyst
Hammerman Associates
800-783-2269

 

[lbass]

I think you would need to use a record selection formula like the following if only those combinations are allowed:

(
{table.status1} = "Cancelled" and
{table.status2} = "NoInv"
) or
(
{table.status1} = "Suspended" and
{table.status2} = "Inv"
) or
(
{table.status1} = "Active" and
{table.status2} = "Cash"
)

-LB

 

[CRilliterate]

Sorry for being such an idiot, I misled you all.
It is suppose to be

Name Status1 Status2 Amount
ABC Cancelled NoInv $400
SCD Suspended Inv $300
VSD Active Cash $400

I need to show on Report following:

Amount where Status1 = 'Cancelled' asnd Status2 = 'NoInv'
Amount where Status1 = 'Suspended' asnd Status2 = 'Inv'
Amount where Status1 = 'Active' asnd Status2 = 'Cash'

Output
Name Cancelled/NoInv Suspended?Inv Active/Cash
ABC $400
SCD $300
VSD $400
_________________________
And I just ran into another thing where


Name Status1 Status2 Amount
ABC Cancelled NoInv $400
SCD Suspended Inv $300

VSD Active Cash $400
VSD Suspended Inv $200
VSD Cancelled NoInv $100

So now how do I group? I can't group by Name anymore.
Now output will have to read

Output
Name Cancelled/NoInv Suspended?Inv Active/Cash
ABC $400
SCD $300

VSD $400
VSD $200
VSD $100

Is there way?
Thanks

I've created formulas for every occurence and inserted like If (Status1 = 'Cancelled' asnd Status2 = 'NoInv') then Amount
etc...
It looked bad, but worked fine and now even that is a waste.
Thanks


________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[CRilliterate]

I've tried as Crosstab and it works but i really do not want to do this as Crosstab...

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[CRilliterate]

Crosstab is not good...there is another fields that I need to calculate All NotInv and AllInv so I will need to go other way.

Output I posted was wrong (again)
This is correct one

Output
Name Cancelled/NoInv Suspended?Inv Active/Cash
ABC $400
SCD $300

VSD $100 $200 $400


Is there way?
Thanks



________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[synapsevampire]

Sure, group by the Name, and create 3 Running Totals that have the conditionals for each in the Evaluate->Use a Formula, the amount as the field to Sum, Reset at the Group Level, place them in the Group Footer and suppress the Group Header and the Details.

-k

 

[CRilliterate]

I don;t get this
conditionals for each in the Evaluate->Use a Formula, the amount as the field to Sum, Reset at the Group Level

Where is Evaluate? How to Reset at the Group Level?

I have all running totals.

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[CRilliterate]

THANKS SO MUCH, it almost done!

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[CRilliterate]

One more...when I have it all done and over with, how do i put Summary to each field, all available is Max and Count.

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[CRilliterate]

I am unable to total by raws nor columns. I am sure there is a way...is there? Thanks

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[synapsevampire]

If you're using a numeric, it will sum, it depends on the data type of the field.

If you want grand totals, you can create additional RTs that have Reset set to Never.

-k

 

[CRilliterate]

I am affraid I don't understand this 'RTs that have Reset set to Never'
Please, explain.

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[CRilliterate]

I am done woth Report, thanks so much for your help. But I am still wondering what was that 'RTs that have Reset set to Never'
If you have a minute to waste - please, explain.

THANKS A LOT!!!

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server

 

[CRilliterate]

I just used it again for another Report and I've learned a lot again. THANKS and I hope there is another star to give.

________________________________________
I am using Windows XP, Crystal Reports 9.0 with SQL Server