Show data from quarterly records in a single row

Each quarter's sales data is contained in 开发者_如何学Pythona row in the data source.

Account 1's 4 quarters of sales data would be in 4 separate records, each containing the account name, quarter number, and count of items purchased.

The report should show, in each detail row: account name, q1 count, q2 count, q3 count, q4 count, total year count.

I'm new to Crystal, but it seems like this should be easy; how would I do this?

---

I'd probably create the result list using some slightly complex sql and they just display it on the Crystal report...but if you're wanting to accomplish this entirely inside Crystal, take a look at http://aspalliance.com/1041_Creating_a_Crosstab_Report_in_Visual_Studio_2005_Using_Crystal_Reports.all.

Here's a stab at the SQL that would be required...

select
accountName, 
(select sum(itemCount) from myTable where quarterName = 'q1') as q1Count, 
(select sum(itemCount) from myTable where quarterName = 'q2') as q2Count, 
(select sum(itemCount) from myTable where quarterName = 'q3') as q3Count, 
(select sum(itemCount) from myTable where quarterName = 'q4') as q4Count, 
(select sum(itemCount) from myTable) as yearCount
from myTable  
group by accountName ;

---

If your data source has the sales date in it (and I assume it would), you can create a formula called @SalesQuarter:

if month({TableName.SalesQuarter}) in [1,2,3] then '1' else
if month({TableName.SalesQuarter}) in [4,5,6] then '2' else
if month({TableName.SalesQuarter}) in [7,8,9] then '3'
else '4' 

You can then add a cross-tab to your report, and use the new @SalesQuarter field as the column header of your cross-tab.

This assumes your sales are all within the same year.

---

Add a group on {account} In the group footer add a Running total for each quarter.

For each quarter, create a running total with following settings:

  Running Total Name: create a unique name for each formula, for example Q1,Q2,Q3,Q4
  Field to summarize: {items purchased}
  Type of summary: sum
  Evaluate: Use a formula - {quarter number}= --should be 1,2,3, or 4, depending on which quarter you are summing
  Reset: On Change of Group {account}