1. 

   Ghostryder, Member

   Hi
   I recently downloaded the mydbr demo to test out and I'm having difficulty understanding how to link reports properly.

   I am able to link one report to another report so all rows from the linked table are displayed using:
   select 'dbr.report', 'sp_DBR_results';

   But I am trying to use a column value in my first table to filter the results of the linked table. I have read through the documentation and searched the forums but I'm just not grasping how it is done. My code looks like this:

   BEGIN
   select 'dbr.report', 'sp_DBR_results', 'Eventcode=[ID]';
   select t.ID as 'ID', t.date as 'Date', t.description as 'Description', t.location as 'Location'
   from stat.tracks t;
   END

   any help would be appreciated
2. 

   myDBR Team, Key Master

   With a command like:

   select 'dbr.report', 'sp_DBR_results', 'Eventcode=[ID]';
   You are expected to have a report sp_DBR_results which has one parameter called 'Eventcode'. The paramerter gets it's value from the clicked row's ID column. In the report then you can use the parameter to query the selected row.

   Please note that you might want to use parameter names that are distinct from column names so that MySQL does know how to separate paramerers from column names. You could use something like:

   select 'dbr.report', 'sp_DBR_results', 'inTrackID=ID';
   --
   myDBR Team
3. 

   Ghostryder, Member

   thanks for the reply,
   I have been trying to get this working again today but I'm still lost.
   Do you mean the parameter inside the mydbr report should be named inTrackID?

   If I list the code from both reports, maybe you can explain it further for me.
   Both of the reports use mysql stored views that contain various tables and joins, and like I said earliier, I'm trying to pass the value from sp_DBR_Events V.ID and use it with v.eventcode from sp_DBR_Results.

   ----------
   report 1
   ----------
   DROP PROCEDURE IF EXISTS sp_DBR_Events
   $$
   CREATE PROCEDURE sp_DBR_Events()
   BEGIN
   select 'dbr.report', 'sp_DBR_results', 'eventcode=ID';
   select v.ID as 'ID',
   v.Date as 'Date',
   v.Description as 'Description',
   v.Location as 'Location'
   from stat.view_events v;

   END
   $$

   ----------
   report 2
   ----------
   DROP PROCEDURE IF EXISTS sp_DBR_Results
   $$
   CREATE PROCEDURE sp_DBR_Results()
   BEGIN
   select v.eventcode as 'Eventcode',
   v.classcode as 'Classcode',
   v.Position as 'Position',
   v.Name as 'Name',
   v.laps as 'Laps',
   v.Finish Time as 'Finish Time',
   v.Personal Best as 'Personal Best',
   v.pb Mph as 'pb Mph',
   v.pb % as 'pb %',
   v.Average Lap as 'Average Lap',
   v.Avg Mph as 'Avg Mph',
   v.Avg % as 'Avg %',
   v.Statistical as 'Statistical',
   v.Slow Mph as 'Slow Mph',
   v.Slow % as 'Slow %',
   v.Overall Rating as 'Overall Rating'
   from stat.view_results v;
   END
   $$

   ----------

   I'll see how obviously stupid I'm being once I managed to a link my first report properly.
   :/
4. 

   myDBR Team, Key Master

   Change the command:
   

   select 'dbr.report', 'sp_DBR_results', 'eventcode=ID';

   into
   

   select 'dbr.report', 'sp_DBR_results', 'inEventID=ID';

   so your parameters are not mixed with column names when used in sp_DBR_Results.

   Then add the inEventID as a parameter into the sp_DBR_Results and add it to where-clause:
   

   CREATE PROCEDURE sp_DBR_Results( inEventID int )
   
   BEGIN
   
   select v.eventcode as 'Eventcode'
   
   ...
   
   from stat.view_results v
   
   where v.eventcode = inEventID;
   
   END
   
   $$
   
   

   --
   myDBR Team
5. 

   Ghostryder, Member

   Thanks very much for the detailed explanation, I have got it working now.
   :)

Reply

You must log in to post.