Running Totals in a Cross-Tab 3

[ajoyp2]

I have a problem which may be a common problem while summarizing Reports .I have Running Totals which I use in a Cross Tab.However when I am trying to calculate percentage from these 2 Running Totals I get an error message ,"A Print Time formula that modifies variables is used in a cart or a map Details % G1OnTime
Are there any workarounds or any other ways to get the overall percentage of Running Totals?

 

[lbass]

Yes, but we need more information. What are your row, column, and summary fields? Is the percentage you want per row, column, or grand total? Please show a sample of how you want the crosstab would look with the correct percentages.

The solution also depends upon your CR version, which you should ALWAYS specify.

-LB

 

[ajoyp2]

Ok , The version is Crystal XI
The Cross Tab Structure is as follows :

Rows: - Different Support Teams ( database field)
Column 1 : Tickets Due ( Running Total )
Column 2 : Tickets within Commit Date ( Running Total)
Column 3 : Ontime % ( Column 2/Column 1 * 100 )
I can't include Column 3 in my Cross Tab on which I get the above error message .
I hope this makes it more clear

 

[lbass]

From what you say, you have no columns. Instead, you have three summary fields. Add the first two summaries to the crosstab, and then create a formula to add as the third summary:

whilereadingrecords;
0

Then in preview mode, select the first summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tickdue := currentfieldvalue;
false

Then select the second summary->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar tickintime := currentfieldvalue;
false

Finally, select the third summary->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
numbervar tickdue;
numbervar tickintime;
if tickdue <> 0 then
totext(tickintime%tickdue,2)+"%" //2 is the number of decimals

-LB

 

[ajoyp2]

Thanks a lot , this works !!!

 

[jdaily]

Using CR XI and Windows XP Pro.

lbass I have a question about your formula above.

I was able to use this somewhat successfully when adding two fields in a crosstab together.

I have one summary column with two summary fields in my crosstab.
Maximum(Interest_Amt)
Sum(Total_Pmts) (If there is an interest pmt then this will be zero)

My rows are (in order):
State
Check#
Program
Quarter
ClaimType
LabelerCode

For each quarter I can have multiple ClaimTypes and multiple LabelerCodes. For example:

NC[tab]12345[tab]Medicaid[tab]4Q2005[tab]ORIG[tab]54321[tab]$1.00 (interest pmt) Then directly below the interest pmt would be your formula that I modified to combine the interest pmt and the total pmt. In this example the total pmt would be $1.00.
NC[tab]12345[tab]Medicaid[tab]4Q2005[tab]ORIG[tab]54321[tab]$0.00 (interest pmt) Then directly below the interest pmt would be your formula that I modified to combine the interest pmt and the total pmt $1,250.00. In this example the total pmt would be $1,250.00.

Here is where I am having a problem:
There are two subtotals, one by Check# and one by State. My report is still showing the Maximum(Interest_Amt) for both interest_pmt subtotals and Sum(Total_Pmts) for both total_pmt. In the example above it would be $1.00 for interest_pmt and $1,250.00 for total_pmt on theck$ total and the state total.
What is should have is $1,251.00 for both totals.

 

[lbass]

You need to use separate variables for two different rows. If you want more help, you would have to share the content of your formulas.

-LB

 

[jdaily]

I have the detail totals working correctly per the formulas that you posted above. My only problem is that I can't do a summary total by Check# and a final summary total by State in the Crosstab.

I have two summarized fields that are coming into the crosstab.
Maximum(clmreq_interest_amt)
{@Total} (This formula will total up several other formulas)

To get the correct detail totals I have the following:

{@NewTot}
whilereadingrecords;
0

Then in preview mode, select the first summary Maximum(clmreq_interest_amt)->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar inttot := currentfieldvalue;
false


Then select the second summary {@Total}->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar total := currentfieldvalue;
false

Finally, select the third summary {@NewTotal}->right click->format field->DISPLAY STRING->x+2 and enter:
whileprintingrecords;
numbervar inttot;
numbervar total;
totext(inttot+total,2)

I think this is where part of my problem is. I can't seem to sum the inttot and total variables.


Is this what you were looking for on the formulas?

Thanks in advance!

John

 

[lbass]

You need to have these same formulas in the formatting areas of the subtotals and totals, not just in the inner cells. You can do this by selecting the inner cells, the subtotals and totals cells for each summary field and then entering the formulas.

-LB

 

[jdaily]

I see now!

One last question. How do I sum on maximum(interest_pmt)?

Thank you for all of your help!

John

 

[lbass]

I have no context for your question. If this is an additional summary, the same steps would apply, but you'd have to name the variables something different.

-LB

 

[jdaily]

For my inner cells I am pulling in the following data: Maximum(clmreq_interest_amt) and {@Total}.

I have been able to combine Maximum(clmreq_interest_amt) with the field {@Total} through the formulas you provided in this thread to come up with a combined inner cell. The value is either going to be Maximum(clmreq_interest_amt) or {@Total} and not both.

What I am not able to do is show a subtotal for Maximum(clmreq_interest_amt). Probably because it's using Maximum. I can show the correct subtotal for {@Total} becuase it's just a formula.
I have tried creating a formula with the Maximum(clmreq_interest_amt) and assigning it to a NumberVar variable and then trying to sum that but it doesn't work.

I am sure there is a way to sum the results of Maximum(clmreq_interest_amt) but I don't know how at this time.

Thanks,

John

 

[lbass]

Still I don't really know where you're trying to subtotal this, but try something like the following in the suppression area:

whileprintingrecords;
numbervar summax := summax + currentfieldvalue;
false

Then reference summax in the display string area.

-LB

 

[sahmartin]

I'm trying to make this solution work for a similar crosstab in Crystal Reports Professional 8.5 and running into some problems:

1. After I've created the third summary field, I don't see any option to add it to the crosstab. (This is the formula with contents:
whilereadingrecords;
0)

2. I don't see an option for "Display String" for any of the crosstab fields.

Do you know if there is any way to implement this solution in 8.5?

Your help is much appreciated!

Thanks,

SHM

 

[jdaily]

Sahmartin,

1. You will need to add the third summary field into the Crosstab's Summarized Field area. I don't remember exactly how the 8.5 crosstab expert is setup so please forgive me if I'm wrong.

2. Once you close out the crosstab expert then you will right click on that summary field in the crosstab in your report. Then click on Format Field and Display string should be at the bottom of the dialog box. Then click on x-2 to add the formula.

Hope this helps!

John

 

[lbass]

Display String isn't available in 8.5. Please see thread149-1242381 for a solution.

-LB

 

[sahmartin]

Thanks! That worked!!

 

[eliner]

Good Morning – I have a report that is 11 columns across. The report is grouped by vendors with group sub totals. We receive a commission from vendors and pass some of that to our clients. My current cross tab is a summary and work fine. We have added two columns to the report: Gross Comm % and Net Comm %. They are working fine going across. I have also been able to get an over group sub total of these columns. I have been unable to get these two group sub totals into my summary.
My cross tab summary looks like this:
Rows:
Commissionable
Gross Comm
Upfrt Disc
Chk Disc
Net Comm

Columns:
Depend upon the number of vendors on the report.

I want to add Gross Comm % and Net Comm % group sub totals to the report.
I am using Crystal 9.

Thanks

 

[Madawc]

eliner, please start a new thread. Refer back to the old one if it is relevant, but it is a new topic.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10