web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / How to get value from ...
Microsoft Dynamics NAV (Archived)

How to get value from a report?

(0) ShareShare
ReportReport
Posted on by YuliyanR 115

Hi, friends! 

I have two reports. One is queued that creates a file from the second one and sends letters after a court. My question is how do I filter the second by report for every newly created file?

Report 50108

First report where created second file.

IF ((StartDateA<>0D) OR (StartDateJ<>0D)) THEN BEGIN //check do you have time sheet for approved for any period 
   IF UserMail <> '' THEN BEGIN // check for mail <> empty 
      UserCode := User."Full Name"; // full name for letter
     SenderName := COMPANYNAME+'_'+'Time_Sheet_For_Approved';
      //---File
     ExcFilePath := '\\WC-MSNAV\NAV_Offer\';
     ExcFileName := SenderName       +'_'+FORMAT(DATE2DMY(TODAY,1))+'.'+FORMAT(DATE2DMY(TODAY,2))+'.'+FORMAT(DATE2DMY(TODAY,3))+'.xls';
    Attachment:= ExcFilePath+ExcFileName;
      IF EXISTS(Attachment) THEN
         ERASE(Attachment);
         ExcCreated :=REPORT.SAVEASEXCEL(50112,Attachment);
    END;
END;

This created file and send letter but second report not have filter

Return function for Name in second report 
ReturnName() : Code[50]
CLEAR(UserSetup);
IF ((StartDateA<>0D) OR (StartDateJ<>0D)) THEN BEGIN
IF UserMail <> '' THEN BEGIN

CLEAR(UserSetup);

UserSetup.SETRANGE("User ID",User."User Name");
IF UserSetup.FINDFIRST THEN
UserCode := UserSetup."Salespers./Purch. Code"
ELSE
UserCode := '';

END;
END;
EXIT(UserCode); 

Report 50112

Second report - where I want to put filter for every person 


Time Sheet Line - OnPreDataItem()
UsersCode := Report50108.ReturnName;

FILTERGROUP(2);
"Time Sheet Line".SETFILTER("Approver ID",UserCode);  // not working I put Message for result only empty 
FILTERGROUP(0);

How to filter it every time you sign up with the person from the first report?

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    Create a function in teh report 50112 called SetUserCode and call that function from the first Report and then Call the Report RUNMODAL.

  • YuliyanR Profile Picture
    YuliyanR 115 on at

    Hi, Suresh!

    I'm make this function in Report 50112

    REPORT 50112

    SetUserCode(UserCodeSecond : Code[50]) : Code[50]

    User_ID := UserCodeSecond; // User_ID is a global variable

    EXIT(User_ID);

    and this for Filter in Report 50112

     

    Time Sheet Line - OnPreDataItem()

    FILTERGROUP(2);

    "Time Sheet Line".SETFILTER("Approver ID",SetUserCode(User_ID));

    FILTERGROUP(0);

    In Report 50108, where I'm started with job queuе. Before creating a file, I set a parameter in the second report (50112).

    REPORT 50108

     

    IF ((StartDateA<>0D) OR (StartDateJ<>0D)) THEN BEGIN

     IF UserMail <> '' THEN BEGIN

     AppUser := AppUser;

     Report50112.SetUserCode(AppUser);

     Report50112.RUNMODAL;

    UserCode := User."Full Name";

    SenderName := COMPANYNAME+'_'+'Time_Sheet_For_Approved';

    //---File

    ExcFilePath := '\\SF-MSNAV\NAV_Oferti\';

    ExcFileName := SenderName +'_'+FORMAT(DATE2DMY(TODAY,1))+'.'+FORMAT(DATE2DMY(TODAY,2))+'.'+FORMAT(DATE2DMY(TODAY,3))+'.xls';

    Attachment:= ExcFilePath+ExcFileName;

    IF EXISTS(Attachment) THEN

      ERASE(Attachment);

    ExcCreated := REPORT.SAVEASEXCEL(50112,Attachment);

     END;

    END;

    But I do not have a filtered file, again. Where am I wrong?

  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    Firstly in your function in Report 50112 you don't need a return variable and exit statement it should be a simple function to accept the value and set the global value.

    SetUserCode(NewUserCode: Code[50])

    UserCode := NewUserCode;

     

    Then in your OnPreDataItem you need to set the filter the following way

     

    Time Sheet Line - OnPreDataItem()

    FILTERGROUP(2);

    "Time Sheet Line".SETFILTER("Approver ID",UserCode);

    FILTERGROUP(0);

    That should filter the dataitem. 

  • YuliyanR Profile Picture
    YuliyanR 115 on at

    Hi, Suresh!

    Second REPORT 50112

    I'm make this:

    SetUserCode(NewUserCode : Code[50])

    User_ID := NewUserCode; // 'sf\yraykov'; not have result

    Time Sheet Line - OnPreDataItem()

    FILTERGROUP(2);

    "Time Sheet Line".SETFILTER("Approver ID",User_ID);

    FILTERGROUP(0);

    But result not have filter.

    SetUserCode(NewUserCode : Code[50]) : Code[50]

    User_ID := 'sf\yraykov';//NewUserCode; have filter

    EXIT(User_ID);

    Time Sheet Line - OnPreDataItem()

    FILTERGROUP(2);

    "Time Sheet Line".SETFILTER("Approver ID",SetUserCode(User_ID));

    FILTERGROUP(0);

    Тhe result is filtered.

    How do I get a parameter from the first report before I create the file so I can filter it and then write it down.

    First Report 50108

     

    User - OnAfterGetRecord()

    CLEAR(UserSetup);

    UserMail := '';

    UserSetup.SETRANGE("User ID","User Name");

    IF UserSetup.FINDFIRST THEN BEGIN

    UserMail := UserSetup."E-Mail";

    AllUser := UserSetup."User ID";

    END ELSE BEGIN

    UserMail := '';

    AllUser := '';

    END;

    StartDate1:=0D;

    StartDate2:=0D;

    StartDateA:=0D;

    StartDateJ:=0D;

    CLEAR(TSLine);

    TSLine.SETRANGE(Status,TSLine.Status::Submitted);

    TSLine.SETRANGE("Approver ID","User Name");

    TSLine.SETFILTER(Type,'<>%1',TSLine.Type::Job);

    //TSLine.SETFILTER(Type,'%1|%2',TSLine.Type::Resource,TSLine.Type::Absence);

    IF TSLine.FINDSET THEN BEGIN

    StartDate1:=TSLine."Time Sheet Starting Date";

    REPEAT

       IF TSLine."Time Sheet Starting Date"<= StartDate1 THEN BEGIN

          StartDateA:=TSLine."Time Sheet Starting Date";

          StartDate1:= StartDateA;

       END;

    UNTIL TSLine.NEXT=0;

    END;

    CLEAR(TSLineJ);

    TSLineJ.SETRANGE(Status,TSLineJ.Status::Submitted);

    TSLineJ.SETRANGE("Approver ID","User Name");

    TSLineJ.SETFILTER(Type,'%1',TSLineJ.Type::Job);

    IF TSLineJ.FINDSET THEN BEGIN

    StartDate2:=TSLineJ."Time Sheet Starting Date";

    REPEAT

       IF TSLineJ."Time Sheet Starting Date"<= StartDate2 THEN BEGIN

          StartDateJ:= TSLineJ."Time Sheet Starting Date";

          StartDate2:= StartDateJ;

       END;

    UNTIL TSLineJ.NEXT=0;

    END;

    IF ((StartDateA<>0D) OR (StartDateJ<>0D)) THEN BEGIN 

    IF UserMail <> '' THEN BEGIN

     AllUser := AllUser;

    Report50112.SetUserCode(AllUser); // Current user but not return value in second report. I'm set user_id for test, but result not filtered.

    Report50112.RUNMODAL;

    UserCode := User."Full Name";

    SenderName := COMPANYNAME+'_'+'Time_Sheet_For_Approved';

    //---File

    ExcFilePath := '\\SF-MSNAV\NAV_Offer\';

    ExcFileName := SenderName +'_'+FORMAT(DATE2DMY(TODAY,1))+'.'+FORMAT(DATE2DMY(TODAY,2))+'.'+FORMAT(DATE2DMY(TODAY,3))+'.xls';

    Attachment:= ExcFilePath+ExcFileName;

    IF EXISTS(Attachment) THEN

      ERASE(Attachment);

    ExcCreated := REPORT.SAVEASEXCEL(50112,Attachment);

    END;

    END;

  • Verified answer
    Tharanga Chandrasekara Profile Picture
    Tharanga Chandrasekara 23,118 on at

    Agree with Suresh's suggestion. Please try like below and let me know how it goes. 

    Report 50112 - Create a new function

    SetUserCode(fUserID: Code[50]) : Code[50] gUserID := fUserID;

     

    Report 50108 - Define a report variable (R50012 : Report 50112).

    CLEAR(R50012);
    R50012.SetUserCode(userValue); R50012.RUNMODAL;

    ** I do not think you need to have Filtergroups in your report. 

     

  • YuliyanR Profile Picture
    YuliyanR 115 on at

    Hi, Taranga!

    ?here are no filtered records.

    Report 50112

    SetUserCode(NewUserCode : Code[50]) : Code[50]

    User_ID := NewUserCode; // User_ID is a global variable code

    Time Sheet Line - OnPreDataItem()

    FILTERGROUP(2);

    "Time Sheet Line".SETFILTER("Approver ID",User_ID);

    FILTERGROUP(0);

    Report 50108

    IF ((StartDateA<>0D) OR (StartDateJ<>0D)) THEN BEGIN

     IF UserMail <> '' THEN BEGIN

     AllUser := AllUser;

    CLEAR(Report50112);

    Report50112.SetUserCode(AllUser);

    Report50112.RUNMODAL;

    UserCode := User."Full Name";

    SenderName := COMPANYNAME+'_'+'Time_Sheet_For_Approved';

    //---File

    ExcFilePath := '\\SF-MSNAV\NAV_Offer\';

    ExcFileName := SenderName +'_'+FORMAT(DATE2DMY(TODAY,1))+'.'+FORMAT(DATE2DMY(TODAY,2))+'.'+FORMAT(DATE2DMY(TODAY,3))+'.xls';

    Attachment:= ExcFilePath+ExcFileName;

    IF EXISTS(Attachment) THEN

      ERASE(Attachment);

    ExcCreated := REPORT.SAVEASEXCEL(50112,Attachment);

    END;

    END;

    Send mail but for every people, but not filtered.

    4024.12.PNG Every file is 235 KB, not filter.

    The first report is started with job. It writes the second report by creating a file and sending the file according to the condition. If it does not fall into the condition it goes to the next record. The problem is that any report that is created, I want it to be filtered by the person to whom it is being sent, but it does not work. I think it is because the data in both reports is aggregated and does not hold any value.

  • Verified answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    Put a message box after this line

    Time Sheet Line".SETFILTER("Approver ID",User_ID);

    does it display the value for the User_ID ?

  • YuliyanR Profile Picture
    YuliyanR 115 on at

    I added a message and when I start it goes out empty. All my records are displayed.

    Report50112  - All code

    First photo

    50108_2D00_2.PNG

    Second photo

    0310.2.PNG

    Report 50108 -All code

    First 

    50108_2D00_1.PNG

    Second 

    50108_2D00_2.PNG

    Third 

    50108_2D00_3.PNG

    Forth

    50108_2D00_4.PNG

    My comments for Report 50108 - Third photo 

    50108_2D00_5.PNG

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 50,243 Super User 2025 Season 2 on at

    Yulivan,

    Message with the objects in .txt format.

  • YuliyanR Profile Picture
    YuliyanR 115 on at

    Hi, Suresh!

    I'm so sorry the previous post. 

    Report 50112 - Second

    SetUserCode(NewUserCode : Code[50])

    User_ID := NewUserCode; //User_ID is e global variable, type (code= 50 ) and NewUserCode is parameter again type (code=50)

    Here is filter:

    Time Sheet Line - OnPreDataItem()

    FILTERGROUP(2);

    "Time Sheet Line".SETFILTER("Approver ID",User_ID);

    FILTERGROUP(0);

    MESSAGE('%1',User_ID); // I'm put  this here for checking, but result is empty when you run report

     

    Report 50108 - First

     

    IF ((StartDateA<>0D) OR (StartDateJ<>0D)) THEN BEGIN

     IF UserMail <> '' THEN BEGIN

    AllUser := AllUser;

    MESSAGE('%1',AllUser); // I checked this as a result. The results are different from the blank. Return person by person. This is true!

    CLEAR(Report50112);

    Report50112.SetUserCode(AllUser); //I think that the function does not take the parameter.

    Report50112.RUNMODAL;

    UserCode := User."Full Name";

    SenderName := COMPANYNAME+'_'+'Time_Sheet_For_Approved';

    //---File

    ExcFilePath := '\\SF-MSNAV\NAV_Offer\';

    ExcFileName := SenderName +'_'+FORMAT(DATE2DMY(TODAY,1))+'.'+FORMAT(DATE2DMY(TODAY,2))+'.'+FORMAT(DATE2DMY(TODAY,3))+'.xls';

    Attachment:= ExcFilePath+ExcFileName;

    IF EXISTS(Attachment) THEN

      ERASE(Attachment);

    ExcCreated := REPORT.SAVEASEXCEL(50112,Attachment);

    END;

    END;

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans