1. 

   mitchbeatty, Member

   DROP PROCEDURE IF EXISTS sp_DBR_DealerReport
   $$
   CREATE PROCEDURE `sp_DBR_DealerReport`()
   BEGIN
   select 'dbr.headerstyle', 'background-color: blue; color: white';
   select 'dbr.css', 'tr.odd td { background: #cae1ff; color: black}';
   select 'dbr.css', '.blueresult td { background: lightblue; color: black}';
   select 'dbr.resultclass', 'tr.odd';
   SELECT 'dbr.rownum' as 'Row number',dealers.name, programs.name, contracts.effective_date, contracts.program_name, contracts.agreement_number, contracts.vehicle_make, contracts.vehicle_model, contracts.vin, contracts.transmit_date, contracts.vehicle_mileage
   from tripac.contracts, tripac.dealers, tripac.programs
   where contracts.dealer_id = dealers.id and contracts.program_id = programs.id
   group by dealers.name, programs.name, contracts.effective_date, contracts.program_name, contracts.agreement_number, contracts.vehicle_make, contracts.vehicle_model, contracts.vin, contracts.transmit_date, contracts.vehicle_mileage;

   END
   $$
2. 

   myDBR Team, Key Master

   You can use dbr.title command combined with MySQL's current_date() function to show the date in the title.

   Please note that in HTML there are no pages. When you export the report to PDF, myDBR will automatically add date and pagenumber to the PDF.

   Btw, you are using GROUP BY in the query where you do not have aggregate functions (sum, min, max, count...) in the SELECT. Also, you could use table aliases instead of the full table names:

   select
   
     'dbr.rownum' as 'Row number',
   
     d.name,
   
     p.name,
   
     c.effective_date,
   
     c.program_name,
   
     c.agreement_number,
   
     c.vehicle_make,
   
     c.vehicle_model,
   
     c.vin,
   
     c.transmit_date,
   
     c.vehicle_mileage
   
   from tripac.contracts c
   
     join tripac.dealers d on d.id = c.dealer_id
   
     join tripac.programs p on p.id = c.program_id
   
   ;
   
   

   --
   myDBR Team

Reply

You must log in to post.