Formula help needed to produce a summary table

[Hales008]

Hello Everyone,

I need some assistance with regards to creating a summary table via formula.

This report needs to be grouped by centre, our centres are USA, UK, APAC. I've managed to do this in a group header, however the data which needs to be retrieved is:

Year
Q1 Q2 Q3 Q4
Number of 2d bids 7 2 6 8
Number of 3d bids 10 8 5 2
Number of 4d bids 1 0 0 0
Average Bid value 5.7 7.5 4.9 1.8
Number of bids won 2 1 2 1

This is all presented in a table, like above.

Does anyone know how I can do this? I've tried the following:

2DQ1Sum:
If {View__All_Bids_for_Export.FYDate<String>} = "2006" And {View__All_Bids_for_Export.QuarterDate<String>} = "Q1" And {View__All_Bids_for_Export.Dimension<String>} = "2D" Then
Count ({View__All_Bids_for_Export.Dimension<String>})

and also this:

3DQ1Sum
If {View__All_Bids_for_Export.FYDate<String>} = "2006" And {View__All_Bids_for_Export.QuarterDate<String>} = "Q1" And {View__All_Bids_for_Export.Dimension<String>} = "3D" Then
ToNumber ({View__All_Bids_for_Export.BidTotal<String>})

The values it is picking up, seems to be the total amount of 2d or 3d bids in the DB.....

Any help would be great.

Thanks,

Hayley.

 

[lbass]

Before considering a manual crosstab, try inserting a crosstab in the group header where you use Year and Quarter as your column fields, and then for your first three summaries, insert these formulas, using SUM, not count, as the summary:

//{@2D}:
if {View__All_Bids_for_Export.Dimension<String>} = "2D" then 1

//{@3D}:
if {View__All_Bids_for_Export.Dimension<String>} = "3D" then 1

//{@4D}:
if {View__All_Bids_for_Export.Dimension<String>} = "4D" then 1

Then insert an average of your bid value field, and
a count of bids won (don't know if this is a field or what).

Don't enter a row field.

-LB