Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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
8
Medium Priority
?
275 Views
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!
http://www.experts-exchange.com/Databases/Crystal_Reports/Q_21850259.html
is a similar problem, but with no answer yet
0
Comment
Question by:jxharding
  • 6
  • 2
8 Comments
 
LVL 7

Expert Comment

by:janmarini
ID: 16718719
What is your first column?  Is it AgentID, Month?
0
 
LVL 7

Expert Comment

by:janmarini
ID: 16718882
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?
0
 
LVL 7

Expert Comment

by:janmarini
ID: 16720179
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

Author Comment

by:jxharding
ID: 16734568
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!
0
 
LVL 7

Accepted Solution

by:
janmarini earned 2000 total points
ID: 16736493
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.

0
 
LVL 7

Expert Comment

by:janmarini
ID: 16736562
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
0
 

Author Comment

by:jxharding
ID: 16740230
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!
0
 
LVL 7

Expert Comment

by:janmarini
ID: 16740460
Glad I could help!  

Jan
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

571 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