Can I have crosstab type reporting while linking on a formula field?

[lydarose]

I have one table with dollar amounts and mmddyyyy dates broken out by grouping and class.
TableDollars for example:
Date grouping class amount
01192010 12345 abc 987.54
01072010 23456 abc 361.87
12152009 23456 xyz 567.23
01062010 23456 abc 345.67

I have another table with counts and the date in a YYYYMM format also broken by grouping and class.
TableCounts for example:
Date grouping class count
200912 12345 abc 75
200912 23456 xyz 56
201001 23456 xyz 54
201001 12345 abc 82

I would like to get the following where there are zeros where there are no corresponding values, but cannot seem to get it to work:
DATE GROUPING CLASS Sum of count Sum of amount
200912 12345 abc 75 $0
200912 12345 TOTAL 75 $0
200912 23456 xyz 56 $567.23
200912 23456 TOTAL 56 $567.23
200912 TOTAL 131 $567.23
201001 12345 abc 82 $987.54
201001 12345 TOTAL 82 $987.54
201001 23456 abc 0 $707.54
201001 23456 xyz 54 $0
201001 23456 TOTAL 54 $707.54
201001 TOTAL 136 $1695.08
Grand Total 267 $2262.31


I think part of my problem may be that I am linking on a formula field. Suggestions needed.

Thank you in advance!!!

 

[lbass]

How did you link on a formula? What database are you using?

-LB

 

[lydarose]

I tried creating a shared variable, but I could never get that to work.

Based on a reply you posted to another thread, I created a formula field on a report with the Table.Dollars to make the Date YYYYMM format and added a subreport to get the information from the Table.Counts. That exports a little yucky to Excel even after speeding extensive time working with the guidelines.

When checking the data on the report, I found that counts were not reported when there were no dollars paid for a grouping or class.

My idea situation would be a crosstab or something without a subreport for ease of formatting in Excel. If that is not an option, I would be grateful just to get it to report Counts even if there are no Dollars.

 

[lbass]

You should be using the table with the counts in the main report, and the table with the dollars as the subreport.

You only answered my first question. The other option might be to use a command as your datasource. What are the datatypes of the date field in one table and the yearmonth field in the other--are they numbers or strings?

-LB

 

[lydarose]

Would I not lose the dollars that did not have a corresponding count if count was the main report?

The table.dollars date is a date
The table.count date is a string [6]
We are in a db2 environment.