Force to show sum of a field

[derekjones]

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

[#2 · Accepted solution]

you can calculate it outside the chart table using set analysis (becouse SA doesn't work over chart dimension)

make a variables and paste there =sum({<BidNotes=,BidType={'Bid'}>} BidAmount)

and paste the variable as a chart column

[derekjones]

That worked a treat. Nice little trick, didn't know you could get around it that way.

Many thanks for your help.

[#4]

be carrefull when you add more dimensions, listboxes etc

you allways have to check variable expression when you change layout:)

[derekjones]

Thanks Pari, will keep this in mind

[derekjones]

Ah, I see what you mean, I just checked it worked by selecting a particular orderID. In my real data I have multiple order id's and the report actually lists the results by orderID, it's summing all BidType='Bid' from all OrderID's selected when I want it against just the OrderID of each line being reported.

[#7]

send qvw with more data

it will help me to clearly understand you:)

[derekjones]

Thanks Pari

Attached is a slightly more detailed version, but still no where near the amount of fields in my actual data, but essentially it covers my issue as the line level detail of the report will be at Order ID, and I want each individual BidNotes and BidAmount where BidType='Rebate, then a total BidAmount for each OrderID where BidType='Bid'. As you can see, it totals the Bids Used over all OrderID's using the variable version.

[#9]

add a little script:

table123:

load OrderID,

sum(BidAmount) as NewValue

resident BidApps where BidID=1 group by OrderID;

[derekjones]

Thanks Pari, I was trying to avoid adding to the data as the Orders table is my largest at over 4m records.

Wanted to check first if there was anyway to do this via the object before embarking to add to the dataset. Looks like I'll have no choice but to go back create a mapping load to add this to the Orders table.

Thansk again for your help.