RD Tutorial: Report IC Product Sales with Percent of Total

This tutorial will show you how to create a report to show the IC Product Sales for a period, and for each product, the percentage of the total sales.

1. Go to Navigator > Setup > Report Designer to open the Report Designer.
2. First, create a SQL Query to return the Products sold, Descriptions and Sales Value for a particular period. Click Edit Query (Alt+Q).
3. Select the ICPROD (IC Products) and the ICBAL (IC Balances) tables. These will be joined by the ProductCode fields.
4. Select the following fields:
   • ICPROD.ProductCode
   • ICPROD.Description
   • ICBAL.SalesValue
   • ICBAL.PeriodID
5. Set ICBAL.PeriodID to be hidden, by un-ticking the Show column.
6. Set the ICBAL.PeriodID to have a parameter for the period required, by setting the Criteria column to:

   = :AsAtPeriod

7. Set the Criteria for the ICBAL.SalesValue to not include zero value sales:

   <> 0

8. Group the results by the Product Code, by selecting the Group column.
9. Click Check Query (Alt+C) to check the query for errors.
10. Click on the Results tab. For the AsAtPeriod Param set the Type to Number and Value to the current Period ID, or just use 101.
11. Click Accept (F9).
12. In the Report Designer, click the Report Wizard button, or go to Accredo > File > Report Wizard.
13. In the Report Design Wizard form, set the following:
    • Report Title: Product Sales
    • Selections Title: Report Selections
    • Retain BeforeReport Code: Unselected (Clear)
    • Retain AfterQuery Code: Selected (Ticked)
14. Leave the defaults in the Columns tab.
15. In the Selections tab, set the following for AsAtPeriod:
    • Caption: As at
    • Input Type: Period
    • Style: Equal
    • If Blank: Required
    • Default Value: CurrentPeriod("IC")
16. Click Generate (F9). The Wizard will generate a report.
17. Select the Report, then click the AfterQuery property. Enter the following code to sum the total sales after the query is executed:

    Dim tblSales as Object
    tblSales = CloneQuery
    tblSales.First
    Do Until tblSales.EOF
      TotalSales = TotalSales + tblSales.SalesValue
      tblSales.Next
    Loop
    tblSales = nothing

18. Select the Report, then click the BeforeReport property. Add the following as the first line, before the existing code:

    TotalSales = 0

19. Select the bandStandard Detail Band. Enter the following in the BeforeBand section:

    PercentOfTotalSales = Percent(Query.SalesValue,TotalSales)

20. Select the bandTotal Summary Band. Enter the following in the BeforeBand section:

    PercentOfTotalSales = Percent(TotalSalesValue,TotalSales)

21. Add a new label , next to the Sales Value label in the Column Header Band. You could copy the Sales Value label then resize it. Change the Text to:

    % Of Total Sales

22. Add a new expression , next to the Query.SalesValue expression in the Detail band. You could copy the Query.SalesValue expression then resize it. Set the Format to Percent. Change the Expression to:

    PercentOfTotalSales

23. Add a new expression , next to the TotalSalesValue expression in the Summary band. You could copy the TotalSalesValue expression then resize it. Set the Format to Percent. Change the Expression to:

    PercentOfTotalSales

24. Save the report, then click Execute Report (Ctrl+R) to run the report.

Note: This report is provided with Accredo. See the ProductSalesWithPercentOfTotalSales.pfd report.