Hi all, I need some help please.
I have two tables:
Bids = Stores 'bid' information e.g. Bid Type and Bid Notes
| BidID | BidNotes | BidType |
| 1 | Education | Bid |
| 2 | Q1 | Rebate |
| 3 | Offer | Rebate |
BidApps = A record of which bid was used on what order and the value used
| OrderID | BidID | BidAmount |
| 1 | 1 | 20.11 |
| 1 | 2 | 15.22 |
| 1 | 3 | 8.88 |
Now I'm trying to do a report which shows each time a Bid of BidType = 'Rebate' is used, shows the notes of that Bid Type and then shows wach of the Bid Amounts for the individual Rebates, but also shows ona column next to it if any BidType='Bid' was used against that order line (as in my report we are trying to check when an order has taken both BidType = 'Rebate' and also taken BidType = 'Bid'.
As per the table below, I'm looking to show each individual Bid Notes for each BidType = 'Rebate' and the individual 'Rebate Amount' in 'Rebate Used', however for the column for Bids Used, I will show the total of BidType 'Bid' used.
| BidNotes | Rebate Used | Bids Used |
| Offer | £8.88 | £20.11 |
| Q1 | £15.22 | £20.11 |
The only way i know to eliminate BidType = 'Bid' lines is by using a hidden calculated Dimension of:
=if(BidType='Rebate',BidType)
and suppressing this dimension if null (hence removing the BidType='Bid' line with it's notes.
However as you can see from my sample app attached, the Bids Used calculation is £0. Is there anyway to force this to sum the BidType = 'Bid' Bid Amount?
My thanks in advance for any suggestions.
Derek
