Need to show rank number in crosstab report

[AnnetteB1305]

hello,

I need a column in my crosstab to show the rank number based on calls in a given month which is a column in my crosstab. Is there a way of doing this?

 

[lbass]

This is not enough information. How is the crosstab set up (rows, columns, summaries)? Rank within each month? Who/what is being ranked by number of calls?

-LB

 

[AnnetteB1305]

Sorry I was hoping it was just a formula, I have a vague recollection on my first ever crystal training day, many moons ago that there was a formula for this.

OK so my crosstab is a top10 calling customers chart.
It looks at calls from customers in a requested month and 2 months before. The top 10 is displayed on the number of calls on the previous month and I need a column to show the rank number in the requested month.

My cross tab is as follows:-

Rows:- @Customersite being 2 table fields
Column No header
Summarised fields are distinctcount of @prevmo which is
if {@Logdate} in dateserial(year({?Month}),month({?Month})-2,1) to
dateserial(year({?Month}),month({?Month})-1,0) then
{opencall1.callref} else
tonumber({@null})

and

distinctcount of @currmo which is
if {@Logdate} in dateserial(year({?Month}),month({?Month}),1) to
dateserial(year({?Month}),month({?Month})+1,1)-1 then
{opencall1.callref} else
tonumber({@null})

and Sum of @0 which is
whilereadingrecords;
0

Thanks,

Annette

 

[agorjest]

Not sure if this would be applicable in your situation, but there is an "nthlargest" function for rankings of 100 or less:

NthLargest
Basic and Crystal syntax.
Overloads
NthLargest (N, fld)
NthLargest (N, fld, condFld)
NthLargest (N, fld, condFld, cond)
Arguments
N is any integer from 1 to 100 (inclusive).
fld is any valid database or formula field that can be evaluated by the function.
condFld is a field used to group the values in fld by.
cond is a String indicating the type of grouping for condFld. You only specify this argument when condFld is a Date, Time, DateTime or Boolean field. For more information on the valid strings for this argument, see Conditions for summary functions .
Returns
Fractional Number
Action
Determines the Nth largest value in a given field, either for the entire report or for each instance of the (condFld) group.
Examples
The following examples are applicable to both Basic and Crystal syntax:
NthLargest(1, {Customer.CUSTOMER ID}
Returns 50, where the Customer ID field contains numerical values ranging from 12 to 50.
NthLargest(5, {Customer.CUSTOMER NAME}, {Customer.REGION})
Returns the fifth largest value in the Customer Name field, per region.
NthLargest(1, {orders.ORDER AMOUNT}, {Customer.REGION}, "monthly")
Groups values in the Amount field by region, then returns the largest value in the Amount field for each region, per month.
Note: Using this function in a formula forces the formula to be evaluated at print time.
For more information on evaluation time considerations, see Evaluation Time.

 

[AnnetteB1305]

The problem is I don't want to show the top whatever I want to show what the rank number would be. They are currently being ranked by the number of calls in the previous month I just want to show the rank number in the current month.
i.e. if they are top this month for number of calls they may have only been ranked 150 last month so I want to show 150 in the column

 

[lbass]

I think you should get rid of inserted crosstab--you don't really need this since you are aren't using a column. You can just insert a group on {@customersite}, place your month formulas in the detail section and then insert summaries on them at the group level and then suppress the details and group header, after dragging the groupname into the group footer. You can then use a top10 group sort on the results.

For the requested month's ranking, save the current report under another name and insert it as a subreport but limit the record selection to only the requested month. Link the sub to the main report on the requested month and on {@customersite} and place the sub in the group footer. Then go into the subreport selection formula and remove it from the selection criteria (report->selection formula->record). Do a group sort within the sub but for all records, not just the top10. Then add a groupnumber to the group in the sub--this will be your rank. You will now see all customersites displayed for each customersite in the main report. Next go into the section expert of the subreport->group section containing the groupnumber->suppress->x+2 and enter:

{@customersite} <> {?pm-@customersite}

This will allow the rank to be maintained. Then suppress all sections of the subreport except the one showing the rank.

-LB

 

[AnnetteB1305]

This sounds a great solution but unfortunately due to the fact this report is so complicated and that this particular section is already a subreport I can't use this solution, however will keep this in mind as think may have another report coming up where this will help. Not sure how on earth I'm going to do this!!

Thanks as always

Annette

 

[AnnetteB1305]

I'm looking at this again and was thinking maybe I will try another subreport containing the rank try and do it as you've said and then try and line it up....I'll let you know how it goes after pulling my hair out no doubt

 

[lbass]

I don't think that will work because the sub needs to use suppression based on the value in the main report, and you won't be able to pass that to the sub from the crosstab.

-LB

 

[AnnetteB1305]

Oh no back to the drawing board then

 

[AnnetteB1305]

Do you think I could do something with runningtotals and the Nth largest formula? Can you feel the straw and the clutching