Adding extra crosstab columns to calculate summary

[crystaldev1]

I'm using CR 9 and Sql Server 2005.

I have a crosstab calculating sum of payments received.

Columns are grouped by fee name and rows are grouped by pay date.

There are also 2 numbers attached to the fee name which I would like to add as columns as well for different calculations in the summary. These numbers are fields located in the same table which is why it's not possible to group separately. For ex, I have the following grouped by fee name and number:

Tax Fee Surcharge
400-1 100-5

1/1/09 $150 $300



I would like to have the following grouped by fee name and have 2 separate columns for numbers attached:


Tax Fee Surcharge
400-1 500-2 100-5 300-6

1/1/09 $150 $200 $300 $90





Thanks.

 

[lbass]

Please show how these fields display if placed in the detail section of a report. Show enough rows so that we can see how the fields relate to each other, and label each field.

-LB

 

[crystaldev1]

If they were placed in detail sections it would look like the following:

Issued Date Fee Name Fee # Amount GL # Amount

1/1/09 Tax Fee 400-1 50 500-2 50
1/1/09 Surcharge 100-5 100 300-6 100


These fields are all stored in the same table "Fees" except Issued Date and Amounts which are stored in "Pay" table.

 

[lbass]

I can't see how this detail level data results in the summary in your first post. Please show more detail data that will make it obvious how the amount summaries are achieved.

-LB

 

[crystaldev1]

So basically, this is what I have:

MasterFeeRecord
RecordID Name Account# Code#
1 Tax 100 105
2 Surcharge 200 205
3 Misc 300 305
...

PayDate
RecordID Date PayAmount
1 1/1/2009 $100
2 1/5/2009 $200
...


From the crosstab, I would like to show the following:

Tax Surcharge ..
100 105 200 205 ..

1/1/2009 $100 $50 $200 $100 ..
...


The PayAmount for the Code# will always be half the PayAmount from PayDate table and PayAmount for the Account# is the full PayAmount from PayDate. I can create a formula for Code# PayAmount.


I just can't figure out how I can add multiple columns under the FeeName which is stored in the same table.

 

[lbass]

You have changed the field names and the way they display. Your last post does not show how these fields display in the detail section when the tables are linked. I can't really follow this.

Why would the amounts be half? Are you compensating for duplicate records?

What exactly is the problem you are encountering?

-LB

 

[crystaldev1]

Amount being half is just a formula for our use that we would like to show in the report. It's not stored in the database.

The problem is .. I can group the crosstab columns by fee name but I do not know how I can create two sub-columns called "Account#" and "Code #". The reason is because they are all stored in the same table as 1 record. For ex, RecordID #1 has a fee name called "Tax", Account# "100", and Code# "105".

So in the crosstab, I added MasterFeeRecord.Name to group by fee names for columns. I would like to add another group to show "Account#" and "Code#" below that but I do not know how I can get both fields to show up side by side as a column.

 

[lbass]

You could get that to display as a label, but you seem to want summaries to relate separately to each field, and I can't follow how your data relates to each.

-LB

 

[crystaldev1]

For the summary, I would want to keep track of Account# and Code# payments. Basically, Account# payment is from PayDate table. Code# payment is half of that payment.

So for the summary, I would have something like:

if column is account#, then payment amount else
if column is code#, then payment amount/2 ...

using paydate as row.

 

[lbass]

Create a formula like this:

{table.name} &' '& {table.acctno} &' '&{table.codeno}

Add this as your column field and then stretch the label vertically to force it to wrap. You might be able to align this with summaries by adjusting the spaces.

Then you could then just add in the two summaries and format the summaries to display horizontally.

-LB