Hi Bruce, When I have stuff to hard to me and I feel I will loose time to write in web2py what I usually do is to write a backend view (SQL) and define the SQL view has a web2py db that I use in my reprensent. Off course, you can't add record or update, but you can, read and select. By setting it to migrate false there is no ticket from web2py.
Richard On Tue, Feb 14, 2012 at 6:37 PM, Bruce Wade <[email protected]> wrote: > Hi, > > We have a pretty complicated query that uses multiple summed fields and I > would like to use SQLFORM.grid to display them. Two situations first I need > to figure out how to translate this query into DAL (unless grid can handle > executesql?), then need to know how to use the sum() like you would with a > normal query.select(). > > Normal SQL > """ > select to_char(report_date,'yyyy-mm') report_date,distributor_id, > sum(sponsorbonus) sponsorbonus, sum(leadbonus) leadbonus, sum(binarybonus) > binarybonus, > sum(matchingbonus) matchingbonus, sum(growingbonus) growingbonus, > sum(adviewpayback) adviewpayback, sum(adpackcomm) adpackcomm, > sum(kvalue_deduction) kvalue_deduction, sum(group_management_fee) > group_management_fee > from bonus_daysummary > where distributor_id = %(distributor_id)s > group by to_char(report_date,'yyyy-mm'), distributor_id > """ > > DAL so far: > sum_sponsor_bonus = db.bonus_summary.sponsorbonus.sum() > sum_lead_bonus = db.bonus_summary.leadbonus.sum() > sum_binary_bonus = db.bonus_summary.binarybonus.sum() > sum_matching_bonus = db.bonus_summary.matchingbonus.sum() > sum_growing_bonus = db.bonus_summary.growingbonus.sum() > sum_adview_payback = db.bonus_summary.adviewpayback.sum() > sum_ad_pack_comm = db.bonus_summary.adpackcomm.sum() > sum_kvalue_deduction = db.bonus_summary.kvalue_deduction.sum() > sum_group_manage_fee = db.bonus_summary.group_management_fee.sum() > > query = db(db.bonus_daysummary.distributor_id == distributor.id) > > monthlyBonusReport= SQLFORM.grid( > query, paginate=10, deletable=False, editable=False, > details=False, csv=True, formname='monthlyBonus', > ) > -- > -- > Regards, > Bruce Wade > http://ca.linkedin.com/in/brucelwade > http://www.wadecybertech.com > http://www.warplydesigned.com > http://www.fitnessfriendsfinder.com >
