Cross Tab - formula: a print time formula that modifies a variable..

[shadystrip]

I have defined an average formula that gives the correct results in a group footer section of the report.
My cross tab is in the reprot footer section of my report.
I would like to have that average appear in my cross tab.

When I insert the average formula in the cross tab, I get the following error:

"A Print time formula that modifies a variable is used in a chart or map. Details: @Average Turnaround Days per Amp."

My formula is Total Days/number of units.

I have tried passing the values to a subreport and placing the cross tab in the subreport, but the cross tab does not show. I have varified the subreport is receiving the values passed.

I have tried converting the results of the formula to text and that did not work.

Any other suggestions?
Thanks!

 

[lbass]

Instead of paraphrasing, please show your actual formula. Also explain the crosstab setup--columns, rows, summaries.

-LB

 

[shadystrip]

My crosstab has rows which are amps (a group, made up of units) and my columns are month groupings, mar-09, april-09, etc through mar-10.

In my report detail I am summing date differences grouped by month (Number of Turnover Days/AMP)and counting each record (Count of Vacant Units in AMP/Period). I have a formula Average Turnaround Days per AMP = {#Number of Turnover Days/AMP}/{#Count of Vacant Units in AMP/Period} and this number does show correctly in my details.

I have found another article that indicates to add a summary field to the crosstab a second time and then format field, common, display string (X+2) and enter the formula ToText({#Number of Turnover Days/AMP}/{#Count of Vacant Units in AMP/Period}) and that does work, I get an average. But since my crosstab is in the report footer, the value displayed is the same in every cell equal to the value in the last cell of the crosstab.

If I move the crosstab up to my group footer for the amp, each average calculates correctly but I do not get the entire crosstab as one, but rather a crosstab for each group amp and date.

I hope this helps clarify things.

 

[lbass]

Why are you using running totals? Are you summing turnover days? What happens if you use average of turnover days (the field, not the running total) as the summary field in the crosstab?

-LB

 

[shadystrip]

I had done the average date diff for each record, restting on change of amp group and the calculation does not match the manual or calc from summing.

 

[lbass]

I'm not sure what you are saying. Please explain how you set up each of the running totals.

I wasn't suggesting that you do another running total. I was suggesting that you try inserting an average as the crosstab summary on the field that shows the turnover days.

-LB

 

[shadystrip]

Each AMP is an apartment complex. I am wanting to calculate the average turnaround time for each complex after a apartment in that complex becomes vacant. In some instances, when an apartment becomes vacant, work is done by cleaners, carpenters, plumbers, painters, etc. The work done by each of these work groups have a time entry for the work they do for the apartment to be ready for the next tenant. So there can be multiple records for each apartment.

The Average function is giving me a percentage counting each time entry per work group instead of counting the number of units.

I need my calculation for tunraround time per complex to be the total number of turnaround days/the number of units that became vacant for the month.

I hope that clears things up.

Thanks.

 

[lbass]

Can you tell me whether the following formula returns the correct amount if placed in the AMT group section?

sum({table.turnarounddays},{table.AMT})/distinctcount({table.vacantunits},{table.AMT})

I know you are not looking for the results in this section, but if this returns the desired results, then I think you could create SQL expressions to use in the crosstab in the report footer. Or you could use another method to calculate the average right in the crosstab.

-LB

 

[shadystrip]

My formula:

{#Number of Turnover Days/AMP}/{#Count of Vacant Units in AMP/Period}

does calculate correctly in the group section.

The sum of the trunaround days is based upon the
DateDiff ('d',{lrunitst.start_dt},{lrunitst.end_dt})
The turnaround days is not a field in the db/table.
I have never put an sql expression in a crystal report.
That is something I will have to look into.

Thanks.

 

[lbass]

I'm asking you to try the formula I showed. substitute your {@turnarounddays} for {table.turnarounddays}.

-LB

 

[shadystrip]

This is what I tried based upon my understanding of what you wanted me to try:

sum(({@Date Diff})/({#Count of Vacant Units in AMP/Period}))
I received the message ont he formula:

A field is required here

referring to
(({@Date Diff})/({#Count of Vacant Units in AMP/Period}))

Perhaps I do not have an understanding of what you wanted me to try.

 

[lbass]

No, I mean for you to set up a formula like I showed you:

sum({@datediff},{table.AMP})/distinctcount({table.vacantunits},{table.AMP})

If you don't have a group on AMP, insert one.

-LB

 

[shadystrip]

I understand now what you were wanting me to try.
The calculation for turnaround days per amp is correct based upon the formula you gave me:
sum({@Date Diff},{lrunit.cAmpkey})/({#Count of Vacant Units in AMP/Period})

The field is not available to insert into the crosstab to summarize.
Is this just the first step in getting the value in the crosstab?

Thanks!!!

 

[shadystrip]

My mistake, the formula is a field I can add to the crosstabs summary. However, when I do insert it, the numbers that appear in the crosstab cells are not the calculated numbers that appear in the amp group details.

 

[shadystrip]

Yet another mistake of mine.
I changed the summary options of the field to average and the numbers are correct in the crosstab cells.

I will have to review the differences in what you have provided with my results.

But I do sincerely appreciate the help you have given me!!!

 

[lbass]

FYI--If you are using a running total in the denominator, you are not using the formula I recommended.

-LB