Placing detail info in multiple columns

[tofuTnT]

Hi All,

I have two tables: one with name info and the other one has gifts info. The relationship is one to many.

What I want to see on my report is as follow:

(header row) | FY03 | FY02
Name | gift date/amount | gift date/amount
| in year 2003 | in year 2002
| only | only
| |
| |

By using conditional supress, i can get something like
Name 6/30/2003
2/4/2003
5/10/2002
3/20/2002

I like to have 6/20/2003 and 5/10/2002 at the same row as name.

something like
Name 6/30/2003 5/10/2002
2/4/2003 3/20/2002


Thanks in advance!
Song

 

[bdreed35]

Check out this thread.
thread149-708483

~Brian

 

[tofuTnT]

My problem is a little different from that. I need to show the date range in year 2003 in column 1 and all date range in year 2002 in column 2.
I tried using Multiple Column in Details before and it didn't give me what I wanted.

Song

 

[lbass]

I think you could handle this with a crosstab, as long as you don't expect a large number of dates. Insert a crosstab and use name as the row, and {table.giftdate} as the column. Still in the crosstab, click on the {table.giftdate} column and select group options and choose "print for each year." For the summary field, if you want to display date and amount, create a formula which concatenates them:

totext({table.giftdate},"MM/dd/yyyy&quot+" - $" + totext({table.amt},0,"&quot

Use this as the summary field and choose NthSmallest, N = 1. Add the formula multiple times, each time incrementing N by 1 up to the maximum number of dates you expect per year.

If you don't want the grid, you can choose the customize style tab->format grid and uncheck show gridlines. You would also want to check "suppress row totals" and "suppress column totals."

-LB

 

[tofuTnT]

CrossTab was the second thing I tried and it didn't really give me what I want either.

lbas, I tried the crosstab the way you describe, it is close to what I want. However, I couldn't suppress those blank summary fields, so the report came out kindof ugly.
BTW, I wanted the columns to be fiscal year, July - June, not the calander year. Now I just used a formula to get the fiscal year and used it as column. The summary field will just have the amount in it. This way I could get the total info, but not the detail gift date, amount.

I am really surprised there is no easy and simple way to do this. :-(

Song

 

[lbass]

I suggested the concatenation formula so that you could display date and amount, since you didn't seem to need to total the amounts. Are you now saying you want a total?

If you choose "suppress row totals" and "suppress column totals" in the customize style tab, you shouldn't see any blank summary fields. Or do you mean that if there was only one gift, but the crosstab allowed space for more, there were remaining spaces?

You could always do a manual crosstab if you want more control. Group on Name and then create formulas like {@FY'03}:

if {table.date} in Date(2002, 07, 01) to Date(2003, 06, 30) then totext({table.date},"MM/dd/yyyy&quot + " - $"+totext({table.amount},0,"&quot

Repeat for FY'02. Place these in the detail sections, and insert repeated summaries on each using NthSmallest, N= 1 and increasing N with each insertion. Drag one summary for each formula to its own group header section (N=1 in a, N=2 in b,N=3 in c, etc.). Go to format section and for each group header check "suppress blank section".

Then suppress the details section.

-LB