Howdy, Stranger!
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
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
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
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.
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.
http://mibuso.com/blogs/davidmachanick/
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 1