1. 

   eugfri, Member

   I am new to MYDBR but starting to love the product. Got full premium license and trying to implement my first linked report - its very simple, purchase orders linking to purchase order details by REFNUMBER.
   Linked reports demo (country, cities etc) works just fine on my installation, so I am sure its not the setup or environment.
   Here is my "master" stored proc:
   CREATE DEFINER=root@localhost PROCEDURE sp_DBR_PurchaseOrdersOpen() BEGIN select 'dbr.column.title', 'ExpectedDate', 'Expected'; select 'dbr.column.title', 'RefNumber', 'PO#'; select 'dbr.column.title', 'VendorRefFullName', 'Vendor'; select 'dbr.column.title', 'TermsRefFullName', 'Terms'; select 'dbr.column.title', 'ShipMethodRefFullName', 'Ship'; select 'dbr.column.title', 'TotalAmount', 'Amount';

   SELECT 'dbr.report', 'sp_DBR_PurchaseOrderDetails', 'VendorRefFullName', 'inline', 'inRefNumber=RefNumber';

   select ExpectedDate, RefNumber, VendorRefFullName, TermsRefFullName, ShipMethodRefFullName, TotalAmount from ssn.PurchaseOrder where IsFullyReceived=0 and IsManuallyClosed=0 order by ExpectedDate;

   END

   and here is my "details" stored proc:

   CREATE DEFINER=root@localhost PROCEDURE sp_DBR_PurchaseOrderDetails( inRefNumber varchar(11) ) BEGIN

   select 'dbr.column.title', 'PurchaseOrderLineItemRefFullName', 'PartNumber'; select 'dbr.column.title', 'PurchaseOrderLineDesc', 'Description'; select 'dbr.column.title', 'Qty', 'Qty'; select 'dbr.column.title', 'Price', 'Price'; select 'dbr.column.title', 'Amt', 'Amt';

   select PurchaseOrderLineItemRefFullName, PurchaseOrderLineDesc, ROUND(PurchaseOrderLineQuantity,0) as Qty, ROUND(PurchaseOrderLineRate,0) as Price, ROUND(PurchaseOrderLineAmount,0) as Amt from ssn.PurchaseOrderLine po where po.RefNumber=inRefNumber;

   END

   I tried to execute them separately in MySQL Workbench and both work just fine, but no matter what I try, I do not see Vendor column showing hyperlink as per this
   SELECT 'dbr.report', 'sp_DBR_PurchaseOrderDetails', 'VendorRefFullName', 'inline', 'inRefNumber=RefNumber';

   What am I doing wrong or what am I missing?

   Any pointers are greatly appreciated.

   Eugene
2. 

   myDBR Team, Key Master

   In order for myDBR to separate the link location (VendorRefFullName) from the target (inline), use brackets around the column reference:

   SELECT 'dbr.report', 'sp_DBR_PurchaseOrderDetails', '[VendorRefFullName]', 'inline', 'inRefNumber=RefNumber';

   --
   myDBR Team
3. 

   eugfri, Member

   hi,

   Thank you for your response!
   I just tried that, but it still does not work, no link appears on VendorRefFullName column that would link to my inline view...

   Anything else I need to check?

   Eugene
4. 

   myDBR Team, Key Master

   Eugene,
   The syntax looks ok. Make sure you have added the sp_DBR_PurchaseOrderDetails as a myDBR report and that the user has been granted access to it.

   You can also save some work if you add the column titles directly into the select-statement instead using the dbr.column.title-command. The dbr.column.title-command is usually required only if the column is dynamic.

   So instead of:

   select 'dbr.column.title', 'ExpectedDate', 'Expected';
   
   select 'dbr.column.title', 'RefNumber', 'PO#';
   
   select 'dbr.column.title', 'VendorRefFullName', 'Vendor';
   
   select ExpectedDate, RefNumber, VendorRefFullName
   
   from ssn.PurchaseOrder;

   You can use:

   select
   
     ExpectedDate as 'Expected[exp_date]',
   
     RefNumber as 'PO#[po]',
   
     VendorRefFullName as 'Vendor'
   
   from ssn.PurchaseOrder;

   where exp_date would be the ColumnReference to the column.

   --
   myDBR Team
5. 

   eugfri, Member

   Thank you again!
   Everything works like a charm now, I did not realize that "details" proc has to be defined as report as well, this is why it was not working.

   And, yes, about column headers - I do you sql for custom header names, I was just trying out dbr.column.title

   I must add - I am very pleased with mydbr. Very good product!
6. 

   myDBR Team, Key Master

   Eugene,
   the "details"-report is a report as any other report. By being a report it has all characteristics of a report (URL, name, permissions etc). For example you can have the linked report to show to some user group while being invisible to others.

   --
   myDBR Team
7. 

   eugfri, Member

   Yes, I got the principle, it makes sense.

Reply

You must log in to post.