Croos tab value
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6080
Printed Date: 28 Apr 2024 at 2:02am
Topic: Croos tab value
Posted By: helpdesk
Subject: Croos tab value
Date Posted: 15 Apr 2009 at 1:01am
|
I have Problem with adding accumulation value from cross tab/chart, here is my case: I want to accumulate value from old date to today. date Value 2/1/2009 $100 2/15/2009 $150 2/20/2009 $220 3/25/2009 $250 3/30/2009 $100 4/1/2009 $10 4/4/2009 $50 I need result like this Month 2 3 4 $470 $820 $880 ........ Could someone help me *Sorry for my bad english  |
Replies:
Posted By: DBlank
Date Posted: 15 Apr 2009 at 7:04am
|
in your crosstab if you wan ti to run across: set the Columns using the date field. Click on group Options.
Set it for ascending, in "The column will be printed" option select for each month.
Add the amount field to the summarized fields section and set the summary function to SUM.
If you want it to go up and down do the same thing except move the date field from the COlumns to the Rows.
|
Posted By: helpdesk
Date Posted: 15 Apr 2009 at 7:43am
|
I already do that, but the result is for each month is sum value for that month:
Month 2 3 4 $470 $350 $60 ........ I Want each moth result added to next month. Month 2 3 4 $470 $820 $880 ........ I Already create specified group order for this case: month 2 : between 2/1/2009 and 2/28/2009 month 3 : between 2/1/2009 and 3/31/2009 month 4 : between 2/1/2009 and 4/30/2009 . . . but no luck, the result sill same. *Sorry for my bad english
|
Posted By: DBlank
Date Posted: 15 Apr 2009 at 8:07am
|
Sorry, missed the cumulative summing you wanted.
Hopefully you do not have to use a crosstab as you can easily get this using a running total.
Set up a group on the Date field set to group on the month (same as in your crosstab.
Suppress the group name field (right click the field, select Format field, common tab check Suppress).
In section expert suppress GH1 as Suppress blank section and Suppress no drill down the details section.
Place a the date field in the GF1 and change the format to "month Year" or whatever you want it to look like.
Create a running total called "Cumulative Sum" (or whatver you want).
Field to summarize is your amount field
Type of summary is SUM
Evaluate: for each record
reset: Never
Place this on your GF1 .
You should end up with
GH1 - Group name (suppressed as blank)
Details - Suppressed
GF1 - Date field (formated to March-09) Running total field
E.g.
Feb 09 $470
March 09 $820
April 09 $880
|
Posted By: DBlank
Date Posted: 15 Apr 2009 at 8:16am
|
If you need it in a crosstab-
Create the running total field as I suggested.
Set your crosstab up.
Create a formula field as a text called "Cross Tab Row":
"Cummulative Sum"
Place the x-1 Cross Tab Row field in your row
Replace the Summarized field from SUM of amount field to using the Running total of "Cummlative Sum".
I think this will work in the crosstab OK.
|
Posted By: helpdesk
Date Posted: 15 Apr 2009 at 9:19am
 Both works, Thank DBlank I never mentioned function Running total field.I really appreciate your help. |
Posted By: hcpro
Date Posted: 22 Apr 2009 at 6:41am
| DBlank- I found this post very useful however i used your suggested row as a column but the minute i try to add a row my numbers are off. Can you add a second dimension to the crosstab with a running total. It looks like it is still trying to cumulate down. |
Posted By: DBlank
Date Posted: 22 Apr 2009 at 6:59am
|
Hi HC,
NOt sure I understand the problem. Can you explain your data, set up and problem a little more?
|
Posted By: hcpro
Date Posted: 22 Apr 2009 at 9:20am
|
Sure I have to count within a publication how many cumulative titles i had by month. I am counting the number of total subscribers and their titles on the months they had subscribed. But not counting how many were added but how many were total at the time. So my eventual cross tab from the original data will look like (simplified left out April in original data)... Cross tab- Titles Jan '09 Feb '09 Mar '09 Apr '09 Administrator 2 3 5 7 CEO 1 2 5 6 Original Data CustomerID Title OrderDate 0001 Administrator 1/1/2009 0002 Administrator 1/1/2009 0003 CEO 1/1/2009 0004 CEO 2/1/2009 0005 Administrator 2/1/2009 0006 CEO 3/1/2009 0007 CEO 3/1/2009 0008 CEO 3/1/2009 0009 Administrator 3/1/2009 0010 Administrator 3/1/2009 So it looks like the running totals would be the right application, but it seems like it is trying to total in the column as well as the row so instead of the CEO Jan '09 being 1 it would be 3. I hope this isn't to confusing. Thanks for your help. |
Posted By: DBlank
Date Posted: 22 Apr 2009 at 9:27am
|
I think you do not have it set up to "reset at the correct time". In your main report, group your data by OrderDate (set per month) then by Group 2 on Title. Create your running total to as a count of CustomerID reset at group level2 and place in group footer 2. Validate the numbers.
place that RT field in the crosstab.
|
Posted By: hcpro
Date Posted: 22 Apr 2009 at 10:33am
|
ok i tried that it works as if i had no running total as a summarized field. In other words each cell counts how many new subs for that title that month instead of the accumulated amount for each month. See below... The crosstab is reflecting the group numbers so i think the problem isn't the crosstab but how the running total is calculating the count of subs grouped by date then title.  |
Posted By: DBlank
Date Posted: 22 Apr 2009 at 10:52am
|
I think we may be complicating this. You just need a count of CustomerIDs per month per title. No Running total is necessary.
in your crosstab keep your colums as the date and your row as the Title but change your Summarized field to the CustomerID and summarize it as a Count or a DistinctCount. A count will include it more than once if it can appear more than ionce in these groupings. A DistinctCount will oply count it once per grouping even if it appears more than once.
|
Posted By: hcpro
Date Posted: 22 Apr 2009 at 11:24am
|
Actually we got this to work. I did need the running total because we still wanted the cumulative total at a point in time (by month). The difference is we didn't need two groups just grouped on titles and used order date in our column in the cross tab grouping by month. We had to throw in a sort at the report level by orderdate and that seemed to be the magic elixir. But we were unable to do running totals in cross tabs until today and couldn't have got this far without your help so much appreciated. Actually just thought of something esle. The months were not new subs were added we are getting zeros in the cross tab instead of the cumulative total to that point. Seems strange. Any thoughts? |
Posted By: DBlank
Date Posted: 22 Apr 2009 at 11:54am
Gotcha. Lost track of the cumulative total part 
Are there NULLS in the table? Running totals do not like them and may cause the problem you are describing. You could use a formula to convert the nulls into something else then count on that. Not sure exactly how as I am a not sure what data you actually have to use for it...
|
Posted By: hcpro
Date Posted: 22 Apr 2009 at 1:04pm
From my guesstimation... it seems like when there are no new subs (customer id's original data) or new rows during a month it wants to put a zero instead of the cumulative sum up to that point. So we are still counting our subs it is just that not every month we have a subscriber or customer for every title. Seems like crystal doesn't know what to do so it inserts a 0 in those cells. Looks like this... zeros are the months where there are no new subs.
|
Posted By: DBlank
Date Posted: 22 Apr 2009 at 1:34pm
| Makes sense. I would guess if no rows exist past a certain point there is no running total to create the value so it just stops at the last row and displays the 0 past it. If you have not done so, maybe if you place the same running total field at the report footer it can display the totals through. |