1. 

   Bilbo Baggins, Member

   I need to know how to pass character variables using dbr.report
   I’m passing a customer name into a stored procedure so I can add a new record into my table
   Main procedure
   CREATE PROCEDURE sp_DBR_CustomerRebateEdit(inCustomer VARCHAR(40))
   BEGIN

   select 'dbr.title', concat('Enter a new rate for ', inCustomer);
   select 'dbr.report', 'sp_DBR_DBR_Add_New_Rebate', 'popup', 'ID=1', 'Customer=inCustomer';
   select 'dbr.button', 'New Rebate Rate';
   select CustomerCode as 'Customer', RR_Index as 'Index No', WEEE_Cat as 'Category', (Rate / 100) as 'Rate (£ per Tonne)'
   from RebateRate
   where (inCustomer = CustomerCode)
   order by WEEE_Cat;

   END
   
   inCustomer is selected from a drop list when the report first runs..

   And am getting the following error message..
   Error in dbr.report call sp_DBR_DBR_Add_New_Rebate parameter (Customer=inCustomer).
   Reference not a number.
   Any help welcome!
2. 

   myDBR Team, Key Master

   Report parameters are referenced with negative numbers (-1 being the first parameter, -2 the second etc).

   If you want to pass a variable declared inside the report, just include it to query and refer to it as to any other column.

   See manual for report linking: http://mydbr.com/doc/?start.linking.html

   --
   myDBR Team
3. 

   Bilbo Baggins, Member

   Many thanks - that worked a treat...

   However now got another small problem in that if there are 4 entries in my table, I get the 'New Rebate Rate' button 4 times & if there are say 6 entries I get it 6 times etc!!
   How do I get just a single button regardless of the number of entries in the table?
   code below.. again any help most welcome..

   DROP PROCEDURE IF EXISTS sp_DBR_CustomerRebateEdit
   $$
   CREATE PROCEDURE sp_DBR_CustomerRebateEdit(inCustomer VARCHAR(40))
   BEGIN

   IF ((inCustomer = '*All Customers*') OR (inCustomer = '*All Hauliers*'))
   THEN
   SELECT 'dbr.text', concat('Cannot run this report using ',inCustomer,'. Please select a single individual Customer/Haulier, thank you.');
   ELSE
   select 'dbr.text', concat('Editing ',inCustomer,'.');
   SELECT 'dbr.text', 'Click on required rate to adjust it.';
   select 'dbr.report', 'sp_DBR_UpdateRebateRates', 2, 'popup', 'ID=1', 'Category<=2', 'Rebate_Rate<=3';

   select RR_Index as 'Index No', WEEE_Cat as 'Category', (Rate / 100) as 'Rate (£ per Tonne)'
   from RebateRate
   where (inCustomer = CustomerCode)
   order by WEEE_Cat;
   END IF;

   select 'dbr.title', concat('Enter a new rate for ', inCustomer);
   select 'dbr.report', 'sp_DBR_DBR_Add_New_Rebate', 'popup', 'ID=1', 'Cust=-1';
   select 'dbr.button', 'New Rebate Rate';
   select CustomerCode as 'Customer', RR_Index as 'Index No', WEEE_Cat as 'Category', (Rate / 100) as 'Rate (£ per Tonne)'
   from RebateRate
   where (inCustomer = CustomerCode)
   order by WEEE_Cat;

   END
4. 

   myDBR Team, Key Master

   How would you like the report to operate?

   First note:
   You have the linked report allowing the update declared as:

   select 'dbr.report', 'sp_DBR_DBR_Add_New_Rebate', 'popup', 'ID=1', 'Cust=-1';

   where "ID" refers to "CustomerCode"-column and "Cust" refers to inCustomer-parameter which looks to the the same as the "CustomerCode"-column?

   Second note:
   The dbr.button-command combined with dbr.report-command turns each result set row to a button, therefore you get as many buttons as you have rows. To produce a single "New Rebate Rate" button after the RebateRate-list:

   select 'dbr.report', 'sp_DBR_DBR_Add_New_Rebate', 'popup', 'ID=1';
   select 'dbr.button', 'New Rebate Rate';
   select inCustomer;

   Third note:
   Your report does produce the "New Rebate Rate"-button even to All Customers/All Hauliers. You might want to end the if-statement after the button creation.

   --
   myDBR Team
5. 

   Bilbo Baggins, Member

   CustomerCode coll is the numeric index, whereas Cust is the alphanumeric name such as 'Biffa'

   inCustomer is passed in to the report on start-up & then displays the current rebate rates for each WEEE category which the user can amend using

   select 'dbr.report', 'sp_DBR_UpdateRebateRates', 2, 'popup', 'ID=1', 'Category<=2', 'Rebate_Rate<=3';

   Would it therefore be better to have the insert new rate as a separate report rather than combining both reports??
6. 

   myDBR Team, Key Master

   CustomerCode coll is the numeric index, whereas Cust is the alphanumeric name such as 'Biffa'

   Just noticed that your report had

   select 'dbr.report', 'sp_DBR_DBR_Add_New_Rebate', 'popup', 'ID=1', 'Cust=-1';

   select CustomerCode as 'Customer'
   from RebateRate
   where (inCustomer = CustomerCode)

   So the ID would be CustomerCode and since inCustomer=CustomerCode and first parameter is inCustomer, that would make Cust also be CustomerCode.

   Would it therefore be better to have the insert new rate as a separate report rather than combining both reports??

   Your report consists of displaying existing RebateRates, the ability to edit them and a button where user can add new RebateRate. Sounds logical that user can do all this in one form.

   --
   myDBR Team
7. 

   Bilbo Baggins, Member

   Have taken out the reference to 'ID' - see below
   select 'dbr.report', 'sp_DBR_DBR_Add_New_Rebate', 'popup', 'Cust=-1';

   select CustomerCode as 'Customer'
   from RebateRate
   where (inCustomer = CustomerCode)

   But still get multiple boxes - is there anyway to fix this??
8. 

   myDBR Team, Key Master

   The dbr.report (and the dbr.button) is applied to every row that the following query produces. Now that your RebateRate-table has multiple rows for a given CustomerCode, you will get multple buttons.

   Now that you want a single button for given customer see the previous answer:

   select 'dbr.report', 'sp_DBR_DBR_Add_New_Rebate', 'popup', 'ID=1'; select 'dbr.button', 'New Rebate Rate';

   select inCustomer;

   --
   myDBR Team
9. 

   Bilbo Baggins, Member

   That works a treat - thanks so much :-)

Reply

You must log in to post.