Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Difference between summaries

Status
Not open for further replies.

JustinPorter

Technical User
Apr 19, 2008
12
US
I have a crosstab set up with rows being groups and collums being dates. In the middle I have the sum of the information and the count. I have added a summery feild after the first 2 groupings and I'm looking to take the sum of one of the summeries and devide it by the count of another summery. Can some one help me because I have spent the last few hours trying to figure it out! Thanks!
 
Just to add. I actually need to take the sum of the two summaries and devide it by one of the counts.
 
If you want assistance, please identify your row, column, and summary fields by {table.name}, and also indicate the type of summaries you are using for the specific fields.

It also might help to see a sample that shows the calculation you want to see.

-LB
 
LB,
Thank you for offering to help, it is much appreciated.
I also am running into another road block I am hoping you can point me in the right direction with. I am using CR2008
My crosstab is set up like this:
Rows: {PROSALES.SDESCRIPT}
Jan07 Jan08 Jan09 - Feb07 etc
Member Greens Fees Haig (Count)
(Sum)
Squire (Count)
(Sum)
Champ (Count)
(Sum)
Member Cart Fees (Count)
(Sum)
Member Round Revenue (sum count from greens only)
(Sum of all sums)
Revenue per round (Sum count from greens) Devided by (Sum of all sums)

And then there are more categories.

I don't expect you to do it all for me, but if you could point me to the right direction that would be awesome. Or if you want I can email you a screenshot or anything you need.

Thanks for your help! It is much appreciated again!
 
Please go into the crosstab and provide the rows, columns, and summaries by {table.field}. I can't tell what I'm seeing here.

Then tell me what the calculation is you are trying to implement, using the summary fields.

You also did not identify what the new roadblock is.

-LB
 
My new roadblock is getting the subcategories.

Columns are set up with the formula you helped me with on the other thread. It is called YEAR.

Rows are set up with {PROSALES.SDESCRIPT} and grouped using "like" formulas. (i.e. Member Greens Fees, Member Cart Fees, Resort Greens Fees, Resort Cart Fees, etc)
However... I want to break it down further like this:
[ul square]
[li]Member Greens Fees[/li]
[ul circle]
[li]Haig[/li]
[li]Squire[/li]
[li]and so on[/li]
[/ul]
[li]Member Cart Fees[/li]
[/ul]

Summeries are set up with the count of {PROSALES.SDESCRIPT} and sum of {PROSALES.AMMOUNT}. Descript is the name of the item and ammount is the ammount it was sold for

I then need a row at the end of each group (i.e. members, outside, etc) for a summery that looks like:
[Sum of the count of greens fees] devided by [sum of the totals of greens fees and cart fee]. This will give me my ammount per round we are making.

Does this help?

Thanks again so much for your help!
 
I don't know what other thread you are referencing. Rows are not grouped--or do you mean you set up specified order or something? What are these "Like" formulas you are referencing? Please go into the crosstab expert and identify the row field, and if it's a formula, show the content of the formula.

If you want subcategories, please supply the name of the field the subcategory represents. This should be added as a second row.

-LB
 
Sorry yes - specified order.
I figured out how to do the "subrows" with formulas so I'm good there. This is what I have.

Columns
@Year: totext(month({PROSALES.SDATE}),"00")+"-"+totext(year({PROSALES.SDATE}),"0000")

Rows
{PROSALES.SDESCRIPT} with specified order.

@Courses:
if {PROSALES.SDESCRIPT} like "*Greens Fee Haig*" then "1 Haig" else
if {PROSALES.SDESCRIPT} like "*Greens Fee Squire*" then "2 Squire" else
if {PROSALES.SDESCRIPT} like "*Greens Fee Champion*" then "3 Champion" else
if {PROSALES.SDESCRIPT} like "*Greens Fee Palmer*" then "4 Palmer" else
if {PROSALES.SDESCRIPT} like "*Greens Fee General*" then "4 Palmer" else
if {PROSALES.SDESCRIPT} like "*Greens Fee Estate*" then "5 Estate" else
if {PROSALES.SDESCRIPT} like "*Cart Fee*" then "Cart Fee" else
if {PROSALES.SDESCRIPT} like "*Package*" then "Package" else {PROSALES.SDESCRIPT}

Summarized Fields
Count of {PROSALES.SDESCRIPT}
Sum of {PROSALES.SAMMOUNT}

So what I need...
I have created another row (under insert calculated row) and need to take the total count of {PROSALES.SDESCRIPT} for greens fees and devide it by the total sum of {PROSALES.SAMMOUNT}. Does that make sense?

I have attached a PDF export of the file so you can see what I'm talking about.
 
 http://cid-53358344fba766d3.skydrive.live.com/self.aspx/Public/Consolidated%20Report%20SAMPLE.pdf
That's much better. I just realized you are using CR2008, and I don't have that and am unfamiliar with how the "insert calculated row" functionality works, but isn't what you are looking for an average (amount/count)? Is there an option to insert that?

-LB
 
I'm having a problem selecting the correct fields. For the amount, I only want the two TOTAL feilds with the dollar amounts in them, and for the count I only want the green fee count total.
 
I can't tell which totals you are referring to or which green fee count total you mean. Can you identify these more specifically? You could use formatting formula areas to do this, but I can't direct you without knowing which totals you mean. There are multiple totals running down in each column and there are multiple references to greens.

-LB
 
What is the formula I can use to find the difference between grid values? Everytime I try and do it, it returns an error saying I need a value.

GridValueAt (5,CurrentColumnIndex,0)/
(GridValueAt (5,CurrentColumnIndex,1)+
GridValueAt (2,CurrentColumnIndex ,1))
 
HI Justin and LB I am having almost the same problem however i am using crystal reports version XI ...

If you have any idea how to solve this in crystal vesion XI i will be so thankful...
 
Justin,

Why are you talking about differences? I thought you wanted an average. Your formula is backwards by the way. I can't help you with the CR2008 functionality, so you could try reading the Help section on crosstabs. Otherwise, you could try inserting a summary on a formula {@0} in the new blank space:

whilereadingrecords;
0

Then select the totals you want to add together->right click->format field->suppress->x+2 and enter:

whileprintingrecords;
currencyvar amt;
if gridrowcolumnvalue("PROSALES.SDESCRIPT") startswith "Charter" then
amt := amt + currentfieldvalue else
amt := 0;
false

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

whileprintingrecords;
numbervar cnt;
if gridrowcolumnvalue("PROSALES.SDESCRIPT") = "Charter / Annual Full Golf & Sports Greens Fees" then
cnt := currentfieldvalue;
false

Then select the summary {@0} ->right click->format field->DISPLAY STRING->x+2 and enter:

whileprintingrecords;
currencyvar amt;
numbervar cnt;
totext(amt/cnt,2)+"%" //2 for two decimals

Not sure CR2008 will handle this the same way earlier versions would, but it's worth a try.

-LB
 
The second formula, it selects "gridrowcolumnvalue" and says This function can be used only within the context of a value grid
 
nevermind, I got it using grid values.

One more thing I promise! How can I do a "New Page After" a certain row?
 
I have no idea as I don't have CR2008. This wasn't possible in earlier versions (without placing the crosstab in a group).

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top