Crosstab summary

[americvish]

hi,
I need some help regarding buliding crosstab report

1) can we have cross tab summary bulit on condition like:
if <condition> then count(field) else average(field)

2)can cross tab summary be summarized on already summarize field in the formula like:
formula is count(field) then using this formula in crosstab summary doing max of the formula.

3) and i am summarizing the field in cross tab and want to present as NO data for empty field. how can i do it?

can somebody suggest me on approaching and solving above issues.

thanks in advance.

 

[Madawc]

Sorry, no, you are asking Crystal to do more than it is written for.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

I disagree.

1) You can add this formula to a crosstab and then use a maximum summary.

2) Yes

3) Create a formula like this (assuming field is a string):

if isnull({table.field}) then
"No Data" else
{table.field}

Use this formula in the crosstab.

-LB

 

[americvish]

the issues i have is:

1) the values are repeating if i am using summarized formula like count(field) and the crosstab summary is not grouping by the column which is datetime field in my case.

2) If i am using count(field) or avg(field) the summary value is not grouping accordingly.

3)If i use the formula as:
if isnull({table.field}) then "No Data" else {table.field}

then i need to change the {table.field} to string, then how will i be able to make average or count of the field.

the formula i have to use is:
if isnull(Avg({table.field})) then "No Data" else
Avg({table.field}).

the problem is with the string field?

can you help me out in resolving the above issues.
thanks.

 

[lbass]

In the crosstab expert, what are your row, column, and summary fields? If you are using a formula for any of these, please show the content.

It looks like you need to add the group condition to your summaries, as in:

average({table.amt},{table.groupfield})

Regarding the null issue, for a number, use a formula like this:

if isnull({table.number}) then
0 else
{table.number}

Use this in the crosstab, and then in preview mode, right click on the summary->format field->display string->x+2 and enter:

if currentfieldvalue = 0 then
"No Data" else
totext(currentfieldvalue,2) //2 for two decimals

-LB

 

[americvish]

The crosstab report I have like below:

Group1:client name
Group2: metric name

2010-07 2010-08 2010-09
Row1 Row2 Row3 Row4 Row 5 summarzied value

Where row1- level1_resource_name, row2-level2_resource_name, row3-resource_type,
Row4- resource_Groupname, row5- threshold_val.

Column is on datetime field by monthly.

The crosstab summary is based on formula below:
IIF(Int(Count({MET_REPORT_METRICS_BY_RESOURCE.RES_TYPE}))>1,

IIf(DateValue ({MET_REPORT_METRICS_BY_RESOURCE.START_LCL_TS}) <= DateValue (CurrentDateTime),
Count({@source value})& " Values",
Count({MET_REPORT_METRICS_BY_RESOURCE.PROJECTION_VALUE})& " Values"),

IIf(DateValue ({MET_REPORT_METRICS_BY_RESOURCE.START_LCL_TS}) <= DateValue (CurrentDateTime),
(IIf(IsNull(Average ({@source value})),"No Data",Cstr(Average ({@source value}),"0.##"))),
(IIf(IsNull(Average ({MET_REPORT_METRICS_BY_RESOURCE.PROJECTION_VALUE})),"No Data",cstr(Average({MET_REPORT_METRICS_BY_RESOURCE.PROJECTION_VALUE}),"0.##")) )
))


The issue I have is the values are getting repeated in the crosstab summary and not grouping by monthly.

I cannot mention the condition for summarizing as
Count(field, column, “monthly”) since the report is not grouped on column.

Can you pls check on this revert back to me pls.

 

[lbass]

All of this nesting is way too complex to follow, but just some comments.

Not sure why you would use int() on a count since it will always be an integer.

As I said before, if you are expecting to see group values, and the summary is built into the field you are summarizing, then you would have to specify the group condition.

I never use iif() in Crystal, partly because it doesn't handle nulls well.

I would not try to combine summaries (numeric) with text as you are trying to do. You can add text by using the display string function if you feel it is necessary.

I'm not sure you are using the conditional criteria correctly (maybe you are)--I'm wondering whether you really mean:

a) if the row meets this criterion, then include it in the summary of this type verus the summary of that type; or

b) if the row meets this criterion, show me a summary of all records (which is what your formula does now) of this type versus a summary of all records of that type.

I think you would be better off just creating a manual crosstab.

-LB

 

[americvish]

Do you have any PDF on how to start with manual cross tab?

 

[lbass]

There are some faqs, if that's what you mean.

Here is a simplified example. First insert groups on what would have been your rowfields in an inserted crosstab. Then create conditional formulas, one for each column instance. So if you would have used {Table.date} printed on change of month for an inserted crosstab, you would create formulas like this:

//{@currentmo}:
if {table.date} in date(year(currentdate),month(currentdate),1) to
dateserial(year(currentdate),month(currentdate)+1,1)-1 then
{table.amt} //or 1 for a count

//{@previousmo}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-1,1) to
dateserial(year(currentdate),month(currentdate),1)-1 then
{table.amt} //or 1 for a count

//{@twomonthsago}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-2,1) to
dateserial(year(currentdate),month(currentdate)-1,1)-1 then
{table.amt} //or 1 for a count

Place these in the detail section and insert summaries on them at the group level for which you want the crosstab displayed. Drag the groupname into the group footer, and then suppress the detail sections. Note that you should be inserting a sum (NOT a count) on a conditional formula that results in 1 or 0 in order to achieve an accurate count.

-LB