Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
how many types of BidNotes(or BidID) do you have? only this three?
Thousands of them unfortunately, the sample app was only just to show the issue. I expect the user to use selections to narrow down the results by Customer, Date, Product etc, so it's not as if the end report will be using them all at once.