How to build a NAV 2013 report using a query object

davmac1davmac1 Member Posts: 1,283
edited 2013-12-04 in NAV Tips & Tricks
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.
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.
  }
}

Comments

  • Miklos_HollenderMiklos_Hollender Member Posts: 1,598
    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.
  • davmac1davmac1 Member Posts: 1,283
    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.
  • nehaalinehaali Member Posts: 1
    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 1
    neha
Sign In or Register to comment.