Calculate with Scripts

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.

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.

The final result: