Exporting in Excel with PrintOnlyifDetails
sunnyk
Member Posts: 280
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
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
0
Comments
-
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,
Sophia0 -
Where to write this code???0
-
I noticed that this problem come only when Grouping is done on certain field of a Dataitem D1.0
-
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).0 -
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.
](*,)0 -
On PreReport trigger
and On PostReport
xlApp.Visible :=TRUE;//write it 0 -
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...Sophia Singh wrote:On PreReport trigger
and On PostReport
xlApp.Visible :=TRUE;//write it
)can you post your txt object please? 0 -
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. } }0 -
First of all, a little suggestion:
change thisEnterCell(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 thisINTCOLNO += 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 thisIF NOT LotNoInformation.FINDFIRST THEN CurrReport.SKIP
to thisCurrReport.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 thisIF ExportToExcel THEN BEGIN EnterCell(RowNo,1,FORMAT("Item Ledger Entry"."Source No."),TRUE,FALSE,FALSE); RowNo := RowNo + 1; END;
to thisIF 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 0 -
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.0 -
Just noticed i wrote this...
CurrReport.SKIP(ISEMPTY)
Excuse me, it's totally wrong, what i meant wasif ISEMPTY then currreport.skip;
:oops:
It was friday, afterall... #-o
anyway, doesn't the "currreport.showoutput = true" do the trick for you?0 -
Nopes Belias its Not working ](*,).It was friday, afterall...
anyway, doesn't the "currreport.showoutput = true" do the trick for you?0 -
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();
0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.7K Microsoft Dynamics NAV
- 18.8K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 328 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions