asked on

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

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!
https://www.experts-exchange.com/questions/21850259/Multi-Column-Report-with-3-Groupings.html
is a similar problem, but with no answer yet

janmarini

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

janmarini

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?

janmarini

Here's some info to help get you started:

http://support.businessobjects.com/library/kbase/articles/c2011808.asp

jxharding

ASKER

hi janmarini
this was exactly what i was looking for thanks!
i now saw the the version of crystal im using(with vb.net 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 vb.net, 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?
thanks!

ASKER CERTIFIED SOLUTION

janmarini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

janmarini

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

Result:
---------
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

jxharding

ASKER

BRILLIANT!!!!!!!!!!!!!!!!!

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!

janmarini

Glad I could help!

Jan