Let’s suppose you have a Report where you have a Gran Total and need a summary percentage by year.

1.We have the following data sources.

The first data-source – CUSTOMER PURCHASES TOTAL BY YEAR.

Select

DatePart(Year, Orders.OrderDate) As Year,

Orders.CustomerID,

Sum(([Order Details].UnitPrice – [Order Details].Discount) *

[Order Details].Quantity) As Total

From

[Order Details] Inner Join

Orders On Orders.OrderID = [Order Details].OrderID

Group By

DatePart(Year, Orders.OrderDate),

Orders.CustomerID

The second data-source – CUSTOMER PURCHASES TOTAL.

Select

Orders.CustomerID,

Sum(([Order Details].UnitPrice – [Order Details].Discount) * [Order Details].Quantity) As [Grand Total]

From

[Order Details] Inner Join

Orders On Orders.OrderID = [Order Details].OrderID

Group By

Orders.CustomerID

2. We create the report from the data-source CUSTOMER PURCHASES TOTAL BY YEAR.

3. We add the second data-source to the Report.

Select "Data Source" from the "Insert" menu.

We need to relate both Reports on the CUSTOMERID field.

4. Add a DETAIL REPORT band for the second data-source we just added.

5.Add required fields by the Report and a LABEL which we’ll name CALCULATED.

6. On ONBEFOREPRINT we add the following code:

For C#:

Calculated.Text = String.Format("{0:##.00}", (System.Convert.ToDouble(GetCurrentColumnValue("Total")) * 100) / System.Convert.ToDouble(DetailReport.GetCurrentColumnValue("Grand Total")));

For VB:

Calculated.Text = String.Format("{0:##.00}", (System.Convert.ToDouble(GetCurrentColumnValue("Total")) * 100) / System.Convert.ToDouble(DetailReport.GetCurrentColumnValue("Grand Total")))

Note!  Set the script language for the Report.

Select the Report.

Select the SCRIPT LANGUAGE.

Let’s take a preview of the Report.