Export (0) Print
Expand All

Walkthrough: Modifying the Customer - Top 10 List Report

Microsoft Dynamics Nav 2009

This walkthrough demonstrates how to create a new client report definition (RDLC) report layout for the Classic report 111, Customer - Top 10 List. It also demonstrates how to modify the report in Visual Studio Report Designer. After completing this walkthrough, the RDLC report layout that you create will look similar to the RDLC report layout for report 111 that is in the CRONUS International Ltd. demo database.

In Microsoft Dynamics NAV 2009, you create RDLC report layouts with Report Designer in the Classic client and Visual Studio Report Designer. You can take advantage of several features in Visual Studio Report Designer that are not available for Classic report layouts.

This walkthrough illustrates the following tasks:

  • Formatting text in Visual Studio Report Designer.

  • Adding color to an RDLC report layout.

  • Interactively sorting data in an RDLC report layout.

  • Adding a pie chart and a bar chart to a report.

Prerequisites

To complete this walkthrough, you will need:

Viktor is a Microsoft Certified Partner working for CRONUS International Ltd. He has been asked to improve the look and feel of the default RDLC report layout for the Customer - Top 10 List report. He plans to use the capabilities that are available in Visual Studio Report Designer to:

  • Format data in the report.

  • Add background colors to the header and data rows.

  • Add a pie chart and a bar chart to the report.

  • Save the report in PDF format so that users who do not have the RoleTailored client installed can view the report.

  • Add functionality to the report so that you can click a customer name and jump to the customer card page in the RoleTailored client for the selected customer.

In the following procedure, you format header and footer rows as bold, modify the font size of text boxes, and delete columns that are not necessary in the new report. In Visual Studio Report Designer, you can select multiple text boxes and set a property on all of them at the same time.

To format text in Visual Studio Report Designer

  1. In the Classic client, on the Tools menu, click Object Designer.

  2. In Object Designer, click Report, select report 111, and then click Design.

  3. On the View menu, click Layout.

  4. In Visual Studio, in the Body section of the report layout, right-click the first column, which displays the rank, and then click Delete Columns.

    Dd568717.Local_901453871_nav_top10report_deletecolumn1(en-us,NAV.60).png
  5. In the Body section of the report layout, right-click the fifth column, which displays the portion of balance, and then click Delete Columns.

    Dd568717.Local_901453867_nav_top10report_deletecolumn5(en-us,NAV.60).png
  6. In the Body section of the report layout, select the four remaining fields in the header row of the table data region.

  7. On the View menu, click Properties Window.

  8. In the Properties window, expand Font.

  9. In the FontSize field, enter 9pt. In the FontWeight field, select Bold from the drop-down list.

    Dd568717.Local_123515211_nav_top10report_headerbold(en-us,NAV.60).png
  10. In the Body of the report layout, select the three fields in the first footer row.

  11. In the Properties window, expand Font.

  12. In the FontSize field, enter 9pt. In the FontWeight field, select Bold from the drop-down list.

  13. For every text box in the report that is not hidden:

    1. Select the text box.

    2. On the View menu, click Properties Window.

    3. In the Properties window, expand Font. In the FontSize field, enter 9pt.

  14. On the File menu, click Save Report.rdlc.

    Dd568717.Local_-1095602410_nav_top10report_afterformat(en-us,NAV.60).png

In the following procedure, you add color to the report. Adding a color to a whole row is a new capability in Microsoft Dynamics NAV 2009 RDLC report layouts. In Microsoft Dynamics NAV 2009, you can also apply a property based on conditions. For example, you can specify that odd and even rows have different background colors.

To add color to an RDLC report layout

  1. In Microsoft Visual Studio, select the fields in the header row in the table data region of the layout.

  2. On the View menu, click Properties Window.

  3. In the Properties window, in the BackgroundColor field, select RoyalBlue.

  4. In the table data region of the layout, select each field in the data row.

  5. In the Properties window, in the BackgroundColor field, expand the drop-down list. On the Web tab, select <Expression>.

  6. In the Edit Expression window, enter the following expression, and then click OK.

    =IIF(RowNumber(Nothing) mod 2, "AliceBlue", "White")
    

    For more information, see Adding Style and Formatting to a ReportViewer Report.

    Dd568717.Local_-1578313758_nav_top10report_backgroundcolor(en-us,NAV.60).png
  7. On the File menu, click Save Report.rdlc.

In the following procedure, you enable dynamic sorting for each column in the report table. After you design the report to include dynamic sorting, a user can preview the report and change the data sort order before printing the report.

To add interactive sorting to an RDLC report layout

  1. In Visual Studio, in the Body section of the report layout, right-click the first field in the first column, which displays the customer number header, and then click Properties.

  2. In the Textbox Properties window, click the Interactive Sort tab.

  3. On the Interactive Sort tab, select Add an interactive sort action to this textbox.

    Dd568717.Local_1125331171_nav_top10report_interactivesort(en-us,NAV.60).png
  4. In the Sort expression field, select =Fields!Customer__No__.Value from the drop-down list, and then click OK.

  5. Repeat steps 1 through 4 for each column. Select the following fields for the Sort expression field:

    • =Fields!Customer_Name.Value

    • =Fields!Customer__Sales__LCY__.Value

    • =Fields!Customer__Balance__LCY__.Value

    Dd568717.Local_753215208_nav_top10report_aftercolor(en-us,NAV.60).png

In the following procedure, you add a pie chart to display either the sales data or the balance data. For more information about using Visual Studio Report Designer to add charts to a report, see Adding Charts to a Report (Visual Studio Report Designer).

NoteNote

The request page for report 111 allows you to specify whether to display sales data or balance data and whether to display a pie chart or a bar chart. Creating this request page is out of the scope of this walkthrough. You can use the request page for report 111 that is in the CRONUS International Ltd. demo database. This request page is not modified when you modify the RDLC report layout.

To add a pie chart

  1. In Visual Studio, on the View menu, click Toolbox.

  2. Drag a chart from the toolbox to the Body of the report layout below the table data region. Resize and place the chart.

  3. Right-click the chart, point to Chart Type, point to Pie, and then click Simple Pie.

  4. Right-click the chart, and then click Properties.

  5. In the Chart Properties window, on the Data tab, in the Dataset name field, select DataSet_Result from the drop-down list.

  6. Next to Values, click Add.

  7. In the Edit Chart Value window, click the Values tab.

    Dd568717.Local_-708558518_nav_top10report_editchartvalue(en-us,NAV.60).png
  8. In the Edit Chart Value window, next to the Series label field, click the Function button to add an expression.

  9. In the Edit Expression window, add the following expression.

    =IIF(Fields!ShowTypeNo.Value = 0,Fields!Customer__Sales__LCY__Caption.Value,Fields!Customer__Balance__LCY__Caption.Value)
    

    This expression specifies whether the label displayed in the legend of the pie chart contains the caption for Sales data or Balance data.

    NoteNote

    In the request page, the user specifies whether to display sales or balance data in the chart. The ShowTypeNo variable is set by code on the request page. Transforming the request page is out of the scope of this walkthrough.

  10. In the Edit Chart Value window, next to the Value field, click the Function button to add an expression.

  11. In the Edit Expression window, add the following expression.

    =IIF(Fields!ShowTypeNo.Value = 0,Fields!Customer__Sales__LCY__.Value,Fields!Customer__Balance__LCY__.Value)
    

    This expression specifies whether the data displayed in the pie chart is Sales data or Balance data.

  12. In the Edit Chart Value window, click OK.

  13. In the Chart Properties window, next to the Category groups field, click Add to group the data by customer so that each customer in the report is grouped in a unique section of the pie chart.

  14. In the Grouping and Sorting Properties window, on the General tab, under Group on, select the following value from the drop-down list.

    =Fields!Customer__No__.Value
    
    Dd568717.Local_1661142687_nav_top10report_groupingandsorting(en-us,NAV.60).png
  15. In the Label field, select the following value for the label that is displayed in the legend.

    =Fields! Customer__No__.Value
    

    This expression specifies that the customer number is displayed to show which customer is associated with a given color in the pie chart.

  16. In the Chart Properties window, on the Legend tab, under Layout, select Table. Under Position, change the position of the legend to the bottom middle position.

  17. In the body of the report layout, select the pie chart.

  18. On the View menu, click Properties Window.

  19. In the Properties window, expand Visibility.

  20. In the Hidden field, select <Expression> from the drop-down list.

  21. In the Edit Expression window, add the following expression.

    =IIF(Fields!ChartTypeNo.Value = 1,FALSE,TRUE)
    

    This expression specifies whether to display the pie chart.

    NoteNote

    In the request page, the user specifies whether to display a pie chart or a bar chart. The ChartTypeNo variable is set by code on the request page. Transforming the request page is out of the scope of this walkthrough.

  22. On the File menu, click Save Report.rdlc.

    Dd568717.Local_-1005033207_nav_top10report_afterpie(en-us,NAV.60).png

In the following procedure, you add a bar chart to display either the sales data or the balance data.

To add a bar chart

  1. In Visual Studio, on the View menu, click Toolbox.

  2. Drag a chart from the toolbox to the Body section of the report layout below the pie chart. Resize and place the chart.

  3. Right-click the chart, point to Chart Type, point to Bar, and then click Simple Bar.

  4. Right-click the chart, and then click Properties.

  5. In the Chart Properties window, on the Data tab, in the Dataset name field, select DataSet_Result from the drop-down list.

  6. Next to Values, click Add.

  7. In the Edit Chart Value window, click the Values tab.

    Dd568717.Local_1393735899_nav_top10report_editchartvaluebar(en-us,NAV.60).png
  8. In the Edit Chart Value window, next to the Series label field, click the Function button to add an expression.

  9. In the Edit Expression window, add the following expression.

    =IIF(Fields!ShowTypeNo.Value = 0,Fields!Customer__Sales__LCY__Caption.Value,Fields!Customer__Balance__LCY__Caption.Value)
    

    This expression specifies whether the label that is displayed in the legend of the bar chart is the Sales label or Balance label.

    NoteNote

    In the request page, the user specifies whether to display sales or balance data in the chart. The ShowTypeNo variable is set by code on the request page. Transforming the request page is out of the scope of this walkthrough.

  10. In the Edit Chart Value window, next to the Value field, click the Function button to add an expression.

  11. In the Edit Expression window, add the following expression.

    =IIF(Fields!ShowTypeNo.Value = 0,Fields!Customer__Sales__LCY__.Value,Fields!Customer__Balance__LCY__.Value)
    

    This expression specifies whether the data displayed in the bar chart is Sales data or Balance data.

  12. In the Edit Chart Value window, click OK.

  13. In the Chart Properties window, next to the Category groups field, click Add to group the data by customer so that each customer in the report is grouped in a separate bar in the chart.

  14. In the Grouping and Sorting Properties window, on the General tab, under Group on, select the following value from the drop-down list.

    =Fields!Customer__No__.Value
    
    Dd568717.Local_734189870_nav_top10report_groupingandsortingbar(en-us,NAV.60).png
  15. In the Label field, select the following value for the label that is displayed next to each bar in the chart.

    = Fields!Customer__No__.Value
    

    This expression specifies that the customer number is displayed to show which customer is associated with a given bar in the bar chart.

  16. In the Grouping and Sorting Properties window, on the Sorting tab, under Sort on, select <Expression> from the drop-down list.

  17. In the Edit Expression window, add the following expression.

    =IIF(Fields!ShowTypeNo.Value = 0,Fields!Customer__Sales__LCY__.Value,Fields!Customer__Balance__LCY__.Value)
    
  18. In the Grouping and Sorting Properties window, under Direction, select Ascending from the drop-down list, and then click OK.

  19. In the Chart Properties window, on the Legend tab, under Position, change the position of the legend to the bottom middle position.

  20. In the body of the report layout, select the bar chart.

  21. On the View menu, click Properties Window.

  22. In the Properties window, expand Visibility.

  23. In the Hidden field, select <Expression> from the drop-down list.

  24. In the Edit Expression window, add the following expression.

    =IIF(Fields!ChartTypeNo.Value = 0,FALSE,TRUE)
    

    This expression specifies whether to display the bar chart.

    NoteNote

    In the request page, the user specifies whether to display a pie chart or a bar chart. The ChartTypeNo variable is set by code on the request page. Transforming the request page is out of the scope of this walkthrough.

  25. On the File menu, click Save Report.rdlc.

    Dd568717.Local_-1005018108_nav_top10report_afterbar(en-us,NAV.60).png

Now that you have completed this walkthrough, your Customer - Top 10 List report has an RDLC report layout that takes advantage of features in Visual Studio Report Designer. Your RDLC report layout includes color, dynamic sorting, and charts. The next steps are to:

Community Additions

ADD
Show:
© 2014 Microsoft