Exporting in Excel with PrintOnlyifDetails

sunnyk · 2009-05-21

Dear All,
Suppose i have 2 Dataitems on report say D1 and D2 with D2 indented under D1. I set the property PrintOnlyIfDetail of D1.
With Report output Its working fine. Only those records of D1 are printing which have data in D2.But while exporting to excel all D1 receords are printing irrespective they have corresponding data in D2.How come?
Am i missing something while writing Export to Excel code?

Waiting for your Valuable Comments

Sophia_Singh · 2009-05-21

Hi!!!!!!!!!!

I tried the same thing as u have asked for.....It worked fine.

IF NOT(CREATE(xlApp)) THEN
ERROR('Error creating excel file');
xlWorkBook := xlApp.Workbooks.Add(-4167);
xlWorkSheet := xlApp.ActiveSheet;
xlWorkSheet.Name := 'Name';
Row:=1;

try this code.........

regards,
Sophia

sunnyk · 2009-05-21

Where to write this code???

sunnyk · 2009-05-21

I noticed that this problem come only when Grouping is done on certain field of a Dataitem D1.

Belias · 2009-05-21

I think you're writing the excel file in the onaftergetrecord trigger of D1. Onaftergetrecord is triggered for each record in the filter set, even if you set the printonlyifdetail property. This property manages only the visibility in the printout.
Move your code in the onaftergetrecord trigger of D2, and call it only the first time you enter the trigger (a simple boolean condition).

sunnyk · 2009-05-21

Dear Belias,
My problem is that i Have Group Header for D1, and i m writing code on the OnPreSection for export to excel the data in this Section. Lets take an example of Item Ledger Entry Table and Lot Information Table. I have indented Lot Information Table under ILE. ILE is grouped on Source No. Now, If Lot Information table has Data based on the filters with ILE Then only it will Print Source No. But for export to excel it is printing all the source No.
](*,)

Sophia_Singh · 2009-05-21

On PreReport trigger

and On PostReport
xlApp.Visible :=TRUE;//write it

Belias · 2009-05-21

Sophia Singh wrote:

On PreReport trigger

and On PostReport
xlApp.Visible :=TRUE;//write it

i don't think he has problems in showing the excel file, anyway, i can't understand the whole problem (it's a lot difficult to ask and receive support for reports...

)can you post your txt object please?

sunnyk · 2009-05-22

OBJECT Report 50418 Lot  Information
{
  OBJECT-PROPERTIES
  {
    Date=05/22/09;
    Time=[ 9:18:19 AM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    TopMargin=1000;
    BottomMargin=1000;
    OnPreReport=BEGIN
                  IF ExportToExcel = TRUE THEN BEGIN
                    TempExcelBuffer.DELETEALL;
                    CLEAR(TempExcelBuffer);
                    RowNo := 1;
                  END;
                END;

    OnPostReport=BEGIN
                   IF ExportToExcel = TRUE THEN BEGIN
                     TempExcelBuffer.CreateBook;
                     TempExcelBuffer.CreateSheet('Lot Quality Information','',COMPANYNAME,USERID);
                     TempExcelBuffer.GiveUserControl;
                   END;
                 END;

    Orientation=Landscape;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table32;
        DataItemTableView=SORTING(Source Type,Source No.,Item No.,Variant Code,Posting Date)
                          ORDER(Ascending)
                          WHERE(Source Type=CONST(Vendor));
        PrintOnlyIfDetail=Yes;
        OnAfterGetRecord=BEGIN
                           LotNoInformation.RESET;
                           LotNoInformation.SETRANGE(LotNoInformation."Item No.","Item Ledger Entry"."Item No.");
                           LotNoInformation.SETRANGE(LotNoInformation."Variant Code","Item Ledger Entry"."Variant Code");
                           LotNoInformation.SETRANGE(LotNoInformation."Lot No.","Item Ledger Entry"."Lot No.");
                           IF NOT LotNoInformation.FINDFIRST THEN
                             CurrReport.SKIP
                         END;

        ReqFilterFields=Item No.,Entry Type,Source Type,Source No.;
        GroupTotalFields=Source No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            SectionWidth=22500;
            SectionHeight=2115;
            OnPreSection=BEGIN
                           IF (ExportToExcel AND (CurrReport.PAGENO = 1)) THEN BEGIN
                             EnterCell(RowNo,10, FORMAT(TODAY,0,4),TRUE,FALSE,FALSE);
                             RowNo := RowNo + 1;
                             EnterCell(RowNo,10, FORMAT(USERID),TRUE,FALSE,FALSE);
                             RowNo := RowNo + 1;
                             EnterCell(RowNo,1, 'Item No.',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,2, 'Mfg. Part No.',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,3, 'Manufacturer Code',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,4, 'Lot No.',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,5, 'Description',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,6, 'Quantity On Hand',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,7, 'Test Quality',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,8, 'Certificate No.',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,9, 'Comment',TRUE,FALSE,FALSE);
                             EnterCell(RowNo,10, 'QA Code',TRUE,FALSE,FALSE);
                             RowNo := RowNo + 1;
                           END;
                         END;

          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;1269 ;1800 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=ENU=Item No. }
            { 1000000002;Label  ;1800 ;1269 ;1800 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Mfg. Part No. }
            { 1000000003;Label  ;3600 ;1269 ;2100 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Manufacturer Code }
            { 1000000005;Label  ;5700 ;1269 ;1500 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Lot No. }
            { 1000000007;Label  ;7200 ;1269 ;5400 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Description }
            { 1000000009;Label  ;12600;1269 ;1350 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Quantity on Hand }
            { 1000000011;Label  ;13950;1269 ;1350 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Test Quality }
            { 1000000013;Label  ;15300;1269 ;1800 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Certificate No. }
            { 1000000015;Label  ;17100;1269 ;4050 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=Comment }
            { 1000000017;Label  ;21150;1269 ;1350 ;846  ;HorzAlign=Center;
                                                         FontSize=8;
                                                         FontBold=Yes;
                                                         MultiLine=Yes;
                                                         CaptionML=ENU=QA Code }
            { 1000000034;TextBox;20100;0    ;2400 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000035;TextBox;20100;423  ;2400 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
            { 1000000036;TextBox;21900;846  ;600  ;423  ;HorzAlign=Right;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000037;Label  ;21000;846  ;900  ;423  ;CaptionML=ENU=Page }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=22500;
            SectionHeight=423;
            OnPreSection=BEGIN
                           IF ExportToExcel  THEN BEGIN
                             EnterCell(RowNo,1,FORMAT("Item Ledger Entry"."Source No."),TRUE,FALSE,FALSE);
                             RowNo := RowNo + 1;
                           END;
                         END;

          }
          CONTROLS
          {
            { 1000000000;TextBox;300  ;0    ;1500 ;423  ;SourceExpr="Source No." }
          }
           }
      }
       }
    { PROPERTIES
      {
        DataItemIndent=1;
        DataItemTable=Table6505;
        DataItemTableView=SORTING(Item No.,Variant Code,Lot No.)
                          ORDER(Ascending);
        CalcFields=Inventory;
        DataItemLink=Item No.=FIELD(Item No.),
                     Variant Code=FIELD(Variant Code),
                     Lot No.=FIELD(Lot No.);
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=22500;
            SectionHeight=423;
            OnPreSection=BEGIN

                           IF ExportToExcel  THEN BEGIN
                             EnterCell(RowNo,1, FORMAT("Lot No. Information"."Item No."),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,2, FORMAT("Lot No. Information"."Mfg. Part No."),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,3, FORMAT("Lot No. Information"."Manufacturer Code"),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,4, FORMAT("Lot No. Information"."Lot No."),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,5, FORMAT("Lot No. Information".Description),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,6, FORMAT("Lot No. Information".Inventory),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,7, FORMAT("Lot No. Information"."Test Quality"),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,8, FORMAT("Lot No. Information"."Certificate Number"),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,9, FORMAT("Lot No. Information".Comment),FALSE,FALSE,FALSE);
                             EnterCell(RowNo,10, FORMAT("Lot No. Information"."QA Code"),FALSE,FALSE,FALSE);
                             RowNo := RowNo + 1;
                           END;
                         END;

          }
          CONTROLS
          {
            { 1000000004;TextBox;0    ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Item No." }
            { 1000000006;TextBox;3600 ;0    ;2100 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Manufacturer Code" }
            { 1000000008;TextBox;1800 ;0    ;1800 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Mfg. Part No." }
            { 1000000010;TextBox;5700 ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Lot No." }
            { 1000000012;TextBox;7200 ;0    ;5400 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Description }
            { 1000000014;TextBox;12600;0    ;1350 ;423  ;HorzAlign=Right;
                                                         SourceExpr=Inventory }
            { 1000000016;TextBox;13950;0    ;1350 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Test Quality" }
            { 1000000018;TextBox;15300;0    ;1800 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Certificate Number" }
            { 1000000020;TextBox;17100;0    ;4050 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Comment }
            { 1000000022;TextBox;21150;0    ;1350 ;423  ;HorzAlign=Left;
                                                         SourceExpr="QA Code" }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
      { 1000000000;CheckBox;3410;0    ;440  ;440  ;InPage=-1;
                                                   ShowCaption=No;
                                                   SourceExpr=ExportToExcel }
      { 1000000001;Label  ;0    ;0    ;3300 ;440  ;ParentControl=1000000000;
                                                   InPage=-1;
                                                   CaptionML=ENU=Export to EXCEL }
    }
  }
  CODE
  {
    VAR
      ExportToExcel@1000000008 : Boolean;
      TempExcelBuffer@1000000007 : TEMPORARY Record 370;
      RowNo@1000000006 : Integer;
      ColumnNo@1000000005 : Integer;
      LotNoInformation@1000000009 : Record 6505;
      DetailsExist@1000000010 : Boolean;

    PROCEDURE EnterCell@1000000000(lvRowNo@1000000000 : Integer;lvColumnNo@1000000001 : Integer;lvCellValue@1000000002 : Text[250];lvBold@1000000003 : Boolean;lvItalic@1000000004 : Boolean;lvUnderLine@1000000005 : Boolean);
    BEGIN
      TempExcelBuffer.INIT;
      TempExcelBuffer.VALIDATE("Row No.",lvRowNo);
      TempExcelBuffer.VALIDATE("Column No.",lvColumnNo);
      TempExcelBuffer."Cell Value as Text" := lvCellValue;
      TempExcelBuffer.Formula := '';
      TempExcelBuffer.Bold := lvBold;
      TempExcelBuffer.Italic := lvItalic;
      TempExcelBuffer.Underline := lvUnderLine;
      TempExcelBuffer.INSERT;
    END;

    BEGIN
    END.
  }
}

Belias · 2009-05-22

First of all, a little suggestion:
change this

  EnterCell(RowNo,1, 'Item No.',TRUE,FALSE,FALSE);
  EnterCell(RowNo,2, 'Mfg. Part No.',TRUE,FALSE,FALSE);
  EnterCell(RowNo,3, 'Manufacturer Code',TRUE,FALSE,FALSE);
  EnterCell(RowNo,4, 'Lot No.',TRUE,FALSE,FALSE);
  EnterCell(RowNo,5, 'Description',TRUE,FALSE,FALSE);
  EnterCell(RowNo,6, 'Quantity On Hand',TRUE,FALSE,FALSE);
  EnterCell(RowNo,7, 'Test Quality',TRUE,FALSE,FALSE);
  EnterCell(RowNo,8, 'Certificate No.',TRUE,FALSE,FALSE);
  EnterCell(RowNo,9, 'Comment',TRUE,FALSE,FALSE);
  EnterCell(RowNo,10, 'QA Code',TRUE,FALSE,FALSE);

to this

  INTCOLNO += 1;
  EnterCell(RowNo,INTCOLNO, 'Item No.',TRUE,FALSE,FALSE);
  INTCOLNO += 1;
  EnterCell(RowNo,INTCOLNO, 'Mfg. Part No.',TRUE,FALSE,FALSE);
  INTCOLNO += 1;
  EnterCell(RowNo,INTCOLNO, 'Manufacturer Code',TRUE,FALSE,FALSE);
  INTCOLNO += 1;
  EnterCell(RowNo,INTCOLNO, 'Lot No.',TRUE,FALSE,FALSE);

This makes it easier to insert a new column between "A" and "B" for example...and some customers ask it :?
anyway, i don't have a lot of data to process and i got only the bold lines in excel...i'll look through the code...maybe a screenshot of your excel file can help...

OK, first of all, you can change this

IF NOT LotNoInformation.FINDFIRST THEN
  CurrReport.SKIP

to this

CurrReport.SKIP(ISEMPTY)

as you don't need the value of the first record...it's a little faster for SQL.
to solve your problem, try to change this

IF ExportToExcel  THEN BEGIN
  EnterCell(RowNo,1,FORMAT("Item Ledger Entry"."Source No."),TRUE,FALSE,FALSE);
  RowNo := RowNo + 1;
END;

to this

IF Currreport.showoutput = true then begin
  IF ExportToExcel and THEN BEGIN
    EnterCell(RowNo,1,FORMAT("Item Ledger Entry"."Source No."),TRUE,FALSE,FALSE);
    RowNo := RowNo + 1;
  END;
END;

Check this on all your sections

sunnyk · 2009-05-25

Hi Belias Thanks a lot for ur valuable tips.
Attached is the two report output. The Second file have Navision report Data. This is correct because it is printing only those Source No. From Item Ledger Entry Table which has some data in Lot No. Information Table.
The otherone Excel Output is showing all the Source No. from Item Ledger Entry Table whther they have data in Lot No. Information Table.
My Query is That if i put Printonlydetail property yes it works fine for navision report print, but when i export this data to excel how can i use this property in code to export only those records which have details in indented dataitem.

Belias · 2009-05-25

Just noticed i wrote this...

CurrReport.SKIP(ISEMPTY)

Excuse me, it's totally wrong, what i meant was

if ISEMPTY then
  currreport.skip;

:oops:

It was friday, afterall... #-o

anyway, doesn't the "currreport.showoutput = true" do the trick for you?

sunnyk · 2009-05-25

It was friday, afterall...

anyway, doesn't the "currreport.showoutput = true" do the trick for you?

Nopes Belias its Not working ](*,).

mabl4367 · 2011-03-17

Maby it's not the moast effective way to solve this but you could check the number of indented data items manually in the top data item and if there are none skip the top data item.

In the top data items OnAfterGetRecord trigger do the folowing:

recIndentedItem.RESET();
recIndentedItem.SETRANGE(relatedField,recTopItem."relatedField");
IF recIndentedItem.ISEMPTY() THEN
  CurrReport.SKIPP();

Howdy, Stranger!

Quick Links

Categories

Exporting in Excel with PrintOnlyifDetails

Comments