1. 

   -nth-, Member

   I've got 2 linked reports, "Master" and "Detail". I've successfully linked them using a primary key. The user will start at the Master report and then link to the Detail one. The challenge I'm having is that the Detail report may not contain records. If it doesn't, the user needs to be able to create a record. I'm working on creating an editable report... but if there's a better/easier way, please let me know.

   Thanks,
   -nth-
2. 

   myDBR Team, Key Master

   The easiest way would be to check in the detail report if any records exists. If not, you can add an option for user to add new detail rows. You can do this by adding a linked report to detail report which will be invoked by user interaction.

   --
   myDBR Team
3. 

   -nth-, Member

   Here's what I've got so far:

   Master report:
   

   
   
   IF object_id('sp_DBR_TrackingV1','P') IS NOT NULL
   
   DROP PROCEDURE [sp_DBR_TrackingV1]
   
   GO
   
   CREATE PROCEDURE sp_DBR_TrackingV1 @StartDate date, @EndDate date
   
   AS
   
   BEGIN
   
   select 'dbr.report', 'sp_DBR_TrackingComments','TranID=1';
   
   SELECT A.TranID, b.ReceiverName, A.ServiceDate, A.ServiceCode, A.ServiceAmt
   
   FROM mydbr_Data..Detail as A
   
   JOIN mydbr_Data..Transaction As B ON
   
   A.TranID = B.TranID
   
   WHERE A.ServiceDate between @StartDate and @EndDAte
   
   ORDER BY A.ServiceAmt DESC;
   
   END
   
   GO
   
   

   So the master can call the following detail report "TrackingComments":

   
   
   IF object_id('sp_DBR_TrackingComments','P') IS NOT NULL
   
   DROP PROCEDURE [sp_DBR_TrackingComments]
   
   GO
   
   CREATE PROCEDURE sp_DBR_TrackingComments @TranID varchar(50)
   
   AS
   
   BEGIN
   
   select 'dbr.report', 'sp_DBR_CommentsCreate', 'popup', 'TranID=1';
   
   select 'dbr.button', 'New Comment';
   
   select 	h.TranID as 'TranID',
   
   	h.UserID as 'UserID',
   
   	h.CommentDate as 'Date',
   
   	h.Comment as 'Comment'
   
   from mydbr_Data..Tracking as h
   
   where h.TranID = @TranID
   
   END
   
   GO
   
   

   So the final step is to put in a "Create Comment" report:

   
   
   IF object_id('sp_DBR_CommentsCreate','P') IS NOT NULL
   
   DROP PROCEDURE [sp_DBR_CommentsCreate]
   
   GO
   
   CREATE PROCEDURE sp_DBR_CommentsCreate @inLogin varchar(30), @Comment varchar(500), @TranID varchar(50)
   
   AS
   
   BEGIN
   
   INSERT INTO mydbr_Data..Tracking (UserID, CommentDate, Comment, TranID)
   
   VALUES (@inLogin,CURRENT_TIMESTAMP,@Comment,@TranID);
   
   select 'dbr.refresh', 0;
   
   END
   
   GO
   
   

   But this doesn't work. The Tracking Comments report only displays the 'New Comment' button (no row result set at all). When 'New Comment' is clicked a popup window appears and it will let me enter a comment, but it never gets saved back to the database.

   What am I missing?

   Thanks,
   -nth-
4. 

   myDBR Team, Key Master

   Hi,
   you might want to separate the displaying of the detail data from creating new detail rows in sp_DBR_TrackingComments. Change it to something like this:

   /* Show the detail rows */
   
   select  h.TranID as 'TranID',
   
       h.UserID as 'UserID',
   
       h.CommentDate as 'Date',
   
       h.Comment as 'Comment'
   
   from mydbr_Data..Tracking as h
   
   where h.TranID = @TranID
   
   /* Add an option to create new row and show it as a button */
   
   select 'dbr.report', 'sp_DBR_CommentsCreate', 'popup', 'TranID=1';
   
   select 'dbr.button', 'New Comment';
   
   select @TranID;

   --
   myDBR Team
5. 

   -nth-, Member

   That did the trick! I had to add a title to it so that the button wasn't placed so close to the previous report.

   
   
   /* Show the detail rows */
   
   select  h.TranID as 'TranID',
   
       h.UserID as 'UserID',
   
       h.CommentDate as 'Date',
   
       h.Comment as 'Comment'
   
   from mydbr_Data..Tracking as h
   
   where h.TranID = @TranID
   
   /* Add an option to create new row and show it as a button */
   
   select 'dbr.title', 'Enter a comment';
   
   select 'dbr.report', 'sp_DBR_CommentsCreate', 'popup', 'TranID=1';
   
   select 'dbr.button', 'New Comment';
   
   select @TranID;
   
   

   Thanks again!
   -nth-

Reply

You must log in to post.