Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Croos tab value Post Reply Post New Topic
Page  of 2 Next >>
Author Message
helpdesk
Newbie
Newbie


Joined: 15 Apr 2009
Online Status: Offline
Posts: 3
Quote helpdesk Replybullet Topic: Croos tab value
    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 englishSmile
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
helpdesk
Newbie
Newbie


Joined: 15 Apr 2009
Online Status: Offline
Posts: 3
Quote helpdesk Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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 
                   
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
helpdesk
Newbie
Newbie


Joined: 15 Apr 2009
Online Status: Offline
Posts: 3
Quote helpdesk Replybullet Posted: 15 Apr 2009 at 9:19am
Clap Both works, Thank DBlank I never mentioned function Running total field.
I really appreciate your help.Smile
IP IP Logged
hcpro
Newbie
Newbie


Joined: 22 Apr 2009
Online Status: Offline
Posts: 5
Quote hcpro Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
IP IP Logged
hcpro
Newbie
Newbie


Joined: 22 Apr 2009
Online Status: Offline
Posts: 5
Quote hcpro Replybullet 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.



Edited by hcpro - 22 Apr 2009 at 9:21am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.