CR9: can i have a CrossTab with A accumalative running total next to each row in the report? if not , pls help on the running total

Posted on 2006-05-19
Last Modified: 2012-08-14
hi, i thought i finally worked out how a crosstab works but no go.
I have Agents
I have Year
I have Month
I have SalesTotal

This is my dataset:
<xs:element name="Agents" type="xs:string" minOccurs="0" />
<xs:element name="Year" type="xs:string" minOccurs="0" />
<xs:element name="Month" type="xs:string" minOccurs="0" />
<xs:element name="SalesTotal" type="xs:double" minOccurs="0" />

This is what i need the report to look like
For Each Agent:
           2004   R         2005   R       2006 R
1      50         50
2      10         60
3      20         80
4      30         110
5      5          115
6      0          115
7      0          115
8      0          115
9      0          115
10    0          115
11    0          115
12    0          115

R is an accumalative running total for the year.
i dont have a set number of years,
if the agent sold from 1980, he'll have those figures for each year.
i have tried everything with a cross tab, but i just cant add the running total.
i made a running total and cannot insert it into the crosstab.
pls help!
is a similar problem, but with no answer yet
Question by:jxharding
    LVL 7

    Expert Comment

    What is your first column?  Is it AgentID, Month?
    LVL 7

    Expert Comment

    Assuming Column 1 is month, will each agent have 12 months of data for each year (showing  zero Total Sales for months that they had no sales) - or only a row for each month that they actuall had a SalesTotal?
    LVL 7

    Expert Comment


    Author Comment

    hi janmarini
    this was exactly what i was looking for thanks!
    i now saw the the version of crystal im using(with 2003) does not have the Horizontal option on Customize style,
    nor the Format Field crosstab to change 'Display Text'.
    i think i can manage a running total for 1 Year, the problem is that there are X years for example and i dont know how to do a horizontal grouping without a crosstab.

    this report was done in cobol and there was no hassle
    now im trying it in, it looks like a basic report and they think im an idiot :)

    could u pls help me as with regards to getting the different Years next to each other?
    LVL 7

    Accepted Solution

    Unfortunately I do know that the .Net version of Crystal is limited in its functionality, although I can't find documentation listing the specific limitaions.  I tried it myself and also found no options to customize the style for the summarized fields, yet it does exist in the Standalone version.  I searched hi and lo for additional information or documentation to compare the Standalone version of CR to the .Net version, especially for Crosstab functionality but came up empty handed.

    So, if you have a Standalone version of Crystal your best bet would be to create your report using that, then add the report file to your VS 2003 project.

    If you don't have a standalone version, not sure what you can do - it appears the functionality for what you need to do just doesn't exist in the .Net version.

    An alternative would be to pivot your data first, and use that as your data source in a regular Crystal Report (not Crosstab) - not sure how you would do this with XML though.  Here's an example using SQL:

    SELECT Agent, Month,
        SUM(CASE Year WHEN '1999' THEN SalesTotal ELSE 0 END) AS Y1999,
        SUM(CASE Year WHEN '2005' THEN SalesTotal ELSE 0 END) AS Y2005,
        SUM(CASE Year WHEN '2006' THEN SalesTotal ELSE 0 END) AS Y2006,
        SUM(CASE Year WHEN '2007' THEN SalesTotal ELSE 0 END) AS Y2007
    FROM YourTable
    GROUP BY Agent, Month
    ORDER BY Agent

    Note that the year's are hard-coded - not sure if this can be done dynamically.  You could start with the first year that you have data for, and perhaps add 5 - 10 future years.  Then in your report you could suppress years that have no sales, and create running totals for years that do have values.

    LVL 7

    Expert Comment

    BTW, Here's what the result would look like using the above SQL:

    Orig Data:
    Agent       Total Sales     Mo       Year
    7      12.0000           3      2005
    7      555.0000           9      2005
    4      75.0000           10      2005
    7      75.0000           10      2005
    1      23.0000           10      2005
    6      344.0000           10      2005
    6      344.0000           5      2006

    Agent        Mo           Y1999       Y2005       Y2006        Y2007
    1      10      .0000      23.0000      .0000      .0000
    4      10      .0000      75.0000      .0000      .0000
    6      5      .0000      .0000      344.0000      .0000
    6      10      .0000      344.0000      .0000      .0000
    7      3      .0000      12.0000      .0000      .0000
    7      9      .0000      555.0000      .0000      .0000
    7      10      .0000      75.0000      .0000      .0000

    Author Comment


    thank you for all the effort and time,
    you were a great great help and now i learnt a great load,
    i can apply this technique to other reports now too!
    LVL 7

    Expert Comment

    Glad I could help!  


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now