How to build a NAV 2013 report using a query object
davmac1
Member Posts: 1,283
Much to my disappointment, I discovered that reports did not allow using query as a data item. So I started thinking on how to get around it.
Then I remembered our old friend, the integer table.
If you build a report using the integer as the data item, you can then build code to process a query one row at a time for each integer. After the query has read the last row, you simply execute a CurrReport.BREAK.
I have attached a simple sample query and report that show how easy this is to do.
Step 1: choose or design a query
Step 2: design new report
Step 3: Add DataItem Integer - change the name to something meaningful (CustomerOrderTotals)
Step 4: define global variables to be used for each query field you will be using in the report and the Query Name
Step 5: under DataItem define your columns using the above defined global variables
Step 6: View C/AL Code (F9)
Step 7: OnPreDataItem trigger add code - QueryName.OPEN;
Step 8: OnAfterGetRecord trigger - add code
IF NOT QueryName.READ THE CurrReport.BREAK;
Variable1:=QueryName.Variable1;
Variable1:=QueryName.Variable2;
repeat lines for remaining fields
Step 9: exit code and view layout
Step 10: design your report in the Visual Layout editor, exit, and save, then save and compile your report and run.
Then I remembered our old friend, the integer table.
If you build a report using the integer as the data item, you can then build code to process a query one row at a time for each integer. After the query has read the last row, you simply execute a CurrReport.BREAK.
I have attached a simple sample query and report that show how easy this is to do.
Step 1: choose or design a query
Step 2: design new report
Step 3: Add DataItem Integer - change the name to something meaningful (CustomerOrderTotals)
Step 4: define global variables to be used for each query field you will be using in the report and the Query Name
Step 5: under DataItem define your columns using the above defined global variables
Step 6: View C/AL Code (F9)
Step 7: OnPreDataItem trigger add code - QueryName.OPEN;
Step 8: OnAfterGetRecord trigger - add code
IF NOT QueryName.READ THE CurrReport.BREAK;
Variable1:=QueryName.Variable1;
Variable1:=QueryName.Variable2;
repeat lines for remaining fields
Step 9: exit code and view layout
Step 10: design your report in the Visual Layout editor, exit, and save, then save and compile your report and run.
OBJECT Report 50005 CustomerOrdersFromQuery
{
OBJECT-PROPERTIES
{
Date=12/20/12;
Time=[ 7:35:06 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
DATASET
{
{ 1000000000;;DataItem;CustomerTotals ;
DataItemTable=Table2000000026;
OnPreDataItem=BEGIN
CustomerOrders.OPEN;
END;
OnAfterGetRecord=BEGIN
IF NOT CustomerOrders.READ THEN CurrReport.BREAK;
CustNo:=CustomerOrders.CustomerNo;
CustomerName:=CustomerOrders.Name;
PhoneNo:=CustomerOrders.Phone_No;
City:=CustomerOrders.City;
OrderTotal:=CustomerOrders.Sum_Amount;
END;
}
{ 1000000001;1;Column;CustNo ;
SourceExpr=CustNo }
{ 1000000002;1;Column;CustomerName ;
SourceExpr=CustomerName }
{ 1000000003;1;Column;PhoneNo ;
SourceExpr=PhoneNo }
{ 1000000004;1;Column;City ;
SourceExpr=City }
{ 1000000005;1;Column;OrderTotal ;
SourceExpr=OrderTotal }
}
REQUESTPAGE
{
PROPERTIES
{
}
CONTROLS
{
}
}
LABELS
{
}
CODE
{
VAR
CustomerOrders@1000000000 : Query 50000;
CustNo@1000000001 : Code[20];
CustomerName@1000000002 : Text[50];
PhoneNo@1000000003 : Text[20];
City@1000000004 : Text[50];
OrderTotal@1000000005 : Decimal;
BEGIN
END.
}
RDLDATA
{
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
<DataSources>
<DataSource Name="DataSource">
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString />
</ConnectionProperties>
<rd:DataSourceID>9c183416-3c53-4717-91d8-ec93bd015f72</rd:DataSourceID>
</DataSource>
</DataSources>
<DataSets>
<DataSet Name="DataSet_Result">
<Fields>
<Field Name="CustNo">
<DataField>CustNo</DataField>
</Field>
<Field Name="CustomerName">
<DataField>CustomerName</DataField>
</Field>
<Field Name="PhoneNo">
<DataField>PhoneNo</DataField>
</Field>
<Field Name="City">
<DataField>City</DataField>
</Field>
<Field Name="OrderTotal">
<DataField>OrderTotal</DataField>
</Field>
<Field Name="OrderTotalFormat">
<DataField>OrderTotalFormat</DataField>
</Field>
</Fields>
<Query>
<DataSourceName>DataSource</DataSourceName>
<CommandText />
</Query>
<rd:DataSetInfo>
<rd:DataSetName>DataSet</rd:DataSetName>
<rd:SchemaPath>Report.xsd</rd:SchemaPath>
<rd:TableName>Result</rd:TableName>
</rd:DataSetInfo>
</DataSet>
</DataSets>
<Body>
<ReportItems>
<Tablix Name="Tablix1">
<TablixBody>
<TablixColumns>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
<TablixColumn>
<Width>1in</Width>
</TablixColumn>
</TablixColumns>
<TablixRows>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="Textbox1">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Cust No</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox1</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox3">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Customer Name</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox3</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox5">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Phone No</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox5</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox2">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>City</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox2</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="Textbox6">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>Order Total</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Textbox6</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
<TablixRow>
<Height>0.25in</Height>
<TablixCells>
<TablixCell>
<CellContents>
<Textbox Name="CustNo">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!CustNo.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>CustNo</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="CustomerName">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!CustomerName.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>CustomerName</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="PhoneNo">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!PhoneNo.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>PhoneNo</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="City">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!City.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>City</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
<TablixCell>
<CellContents>
<Textbox Name="OrderTotal">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
<Paragraphs>
<Paragraph>
<TextRuns>
<TextRun>
<Value>=Fields!OrderTotal.Value</Value>
<Style />
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>OrderTotal</rd:DefaultName>
<Style>
<Border>
<Color>LightGrey</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
</Textbox>
</CellContents>
</TablixCell>
</TablixCells>
</TablixRow>
</TablixRows>
</TablixBody>
<TablixColumnHierarchy>
<TablixMembers>
<TablixMember />
<TablixMember />
<TablixMember />
<TablixMember />
<TablixMember />
</TablixMembers>
</TablixColumnHierarchy>
<TablixRowHierarchy>
<TablixMembers>
<TablixMember>
<KeepWithGroup>After</KeepWithGroup>
</TablixMember>
<TablixMember>
<Group Name="Details" />
</TablixMember>
</TablixMembers>
</TablixRowHierarchy>
<DataSetName>DataSet_Result</DataSetName>
<Top>0.2675in</Top>
<Left>0.28833in</Left>
<Height>0.5in</Height>
<Width>5in</Width>
<Style>
<Border>
<Style>None</Style>
</Border>
</Style>
</Tablix>
</ReportItems>
<Height>2in</Height>
<Style />
</Body>
<Code>Public Function BlankZero(ByVal Value As Decimal)
if Value = 0 then
Return ""
end if
Return Value
End Function
Public Function BlankPos(ByVal Value As Decimal)
if Value > 0 then
Return ""
end if
Return Value
End Function
Public Function BlankZeroAndPos(ByVal Value As Decimal)
if Value >= 0 then
Return ""
end if
Return Value
End Function
Public Function BlankNeg(ByVal Value As Decimal)
if Value < 0 then
Return ""
end if
Return Value
End Function
Public Function BlankNegAndZero(ByVal Value As Decimal)
if Value <= 0 then
Return ""
end if
Return Value
End Function
</Code>
<Width>6.5in</Width>
<Page>
<Style />
</Page>
<Language>=User!Language</Language>
<ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
<rd:ReportID>0eeb6585-38ae-40f1-885b-8d50088d51b4</rd:ReportID>
<rd:ReportUnitType>Inch</rd:ReportUnitType>
</Report>
END_OF_RDLDATA
}
}
OBJECT Query 50000 Customer Order Totals
{
OBJECT-PROPERTIES
{
Date=12/19/12;
Time=[ 8:33:32 PM];
Modified=Yes;
Version List=;
}
PROPERTIES
{
}
ELEMENTS
{
{ 1000000000;;DataItem; ;
DataItemTable=Table18 }
{ 1000000001;1;Column;CustomerNo ;
DataSource=No. }
{ 1000000002;1;Column; ;
DataSource=Name }
{ 1000000003;1;Column; ;
DataSource=Phone No. }
{ 1000000004;1;Column; ;
DataSource=City }
{ 1000000005;1;DataItem; ;
DataItemTable=Table36;
DataItemTableFilter=Document Type=CONST(Order);
DataItemLink=Sell-to Customer No.=Customer."No." }
{ 1000000009;2;Column; ;
DataSource=Amount;
MethodType=Totals;
Method=Sum }
{ 1000000010;2;Column; ;
DataSource=Amount Including VAT;
MethodType=Totals;
Method=Sum }
}
CODE
{
BEGIN
END.
}
}
David Machanick
http://mibuso.com/blogs/davidmachanick/
http://mibuso.com/blogs/davidmachanick/
0
Comments
-
Thanks David!
I don't have a NAV2013 installation yet because it crashes my laptop, so out of curiosity, do you have to use a fixed query variable, or you can still use the Query equivalent of the old, dynamic FORM.RUN(IntegerNumber), REPORT.RUN(IntegerNumber) - because if yes, you could have one report layout for multiple but similarly structured data sources! They typical case would be a Sales per Customers / Purchase per Vendors report. It would be a step towards separating logic from presentation, like the MVC principle.0 -
I don't see why not.
I am going to wait until Directions and find out what improvements they have given us in R2 - maybe they have made the queries more flexible.David Machanick
http://mibuso.com/blogs/davidmachanick/0 -
Congratulations for your code, it's very useful.
I have added a Navision Timer object to "Report Usage Mgt." Codeunit to save report usage periodically instead waiting till user Logoff Navision.
To active the timer ReportUsageMgt.RUN must be included to LogInStart() function in Codeunit 1neha0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K 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
- 320 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