Conditional Group Sort Based on Parameter - different summaries 1

[crogers111]

CRXI
SQL

I have a report with a Top N parameter that Groups by Name (see below) with summaries in the GF

I'd like to have a parameter where the user can select how to sort this group of data, either by the Sum(Data.Charge), Sum(Data.Paid) or the Count(Data.Claim#)

I have it working with the Charge and Paid which are the same datatypes and uses the same summary (see @Sort below) but when I try to include the count of claim# in the formula it fails with data type errors (a string is required etc). Also, it appears the group sort can only handle one type of summary


@Sort
If{?Sort Preference} = "Charge" then
{Data.Charge}
else
//If{?Sort Preference} = "Claims" then
//{Data.ClaimNumber}
//else
if {?Sort Preference} = "Paid"
then
{Data.AmountPaid}

SAMPLE of DATA OUTPUT

Name Charge Paid Claim#Count
(String) (Num) (Num) (String)


GF1 Jones 500 300 4
GF1 Smith 700 600 8
GF1 Wilson 900 0 1


Detail
GF1 Name Sum(Data.Charge) Sum(Data.Paid) Count(Data.Claim#)

My report has a Bar Chart in the RH and it also needs to match the sorting in the report which I do have working for Charge & Paid.

Any thoughts or ideas are appreciated !

 

[lbass]

Try replacing the field {Data.ClaimNumber} with a formula that can also be summed:

if not isnull({Data.ClaimNumber}) then 1

-LB

 

[crogers111]

That tip got the wheels turning in the right direction.

Since claim# isn't always unique for each row of data, I had to add a line so that the sum would work correclty and each unique claim# is added only once to the sum.

if not isnull({Data.ClaimNumber}) and {Data.LineNumber} = 1 then 1

Thanks again LB !