I have a Crystal Report getting data from SQL Server. I want to generate GroupReport Total and also Generate Grand Total Report
I have been able to generate the report from SQL, the following are the fields i have: Item_Name, qty, Price, Amount
I have created a Formular to sum all the amount which the total will give me the GrandTotal
But for each subreport, i want to get the total amount too but this seems to be operating in a running total form.
Below is what i am getting now.
CashierA
OrderDate Item qty Price Amount
2019-07-24 rice 2 3,000 6,000
2019-07-24 beans 2 5,000 10,000
2019-07-24 fish 2 3,000 6,000
sub_Total 22,000
CashierB
OrderDate Item qty Price Amount
2019-07-24 rice 2 3,000 6,000
2019-07-24 beans 2 5,000 10,000
2019-07-24 fish 2 2,000 4,000
sub_Total 42,000
Day_Total 42,000
CashierA
OrderDate Item qty Price Amount
2019-07-25 rice 2 3,000 6,000
2019-07-25 beans 2 4,000 8,000
2019-07-25 fish 2 3,000 6,000
sub_Total 62,000
CashierB
OrderDate Item qty Price Amount
2019-07-25 rice 2 3,000 6,000
2019-07-25 beans 2 5,000 10,000
2019-07-25 fish 2 4,000 8,000
sub_Total 86,000
Day_Total 86,000
GrandTotal 86,000
tonumber({DTtablesales.Amount})
The Below is what I want
CashierA
OrderDate Item qty Price Amount
2019-07-24 rice 2 3,000 6,000
2019-07-24 beans 2 5,000 10,000
2019-07-24 fish 2 3,000 6,000
sub_Total 22,000
CashierB
OrderDate Item qty Price Amount
2019-07-24 rice 2 3,000 6,000
2019-07-24 beans 2 5,000 10,000
2019-07-24 fish 2 2,000 4,000
sub_Total 20,000
Day_Total 42,000
CashierA
OrderDate Item qty Price Amount
2019-07-25 rice 2 3,000 6,000
2019-07-25 beans 2 4,000 8,000
2019-07-25 fish 2 3,000 6,000
sub_Total 20,000
CashierB
OrderDate Item qty Price Amount
2019-07-25 rice 2 3,000 6,000
2019-07-25 beans 2 5,000 10,000
2019-07-25 fish 2 4,000 8,000
sub_Total 24,000
Day_Total 44,000
GrandTotal 86,000
Note that the GrandTotal is a TotalRunning Field calculating sum(Amount)
My problem is getting the sub-total for each Cashier, get it reset when calling another cashier details for that particular date and the total should be calculated for that date.
You can achieve the results desired by using the appropriate grouping levels and summary fields as well.
The top most grouping level would be "Order Date". And then below that you would group by "Cashier".
Then insert the "Amount" field into the Details section of your report. Right click the field for "Amount" in the report designer and click on "Insert > Summary". You should then see the Insert Summary Dialog Box on the screen. Check the check box for "Add to all group levels" and this will insert summary fields into each Group Footer section and your Report Footer section. The summary fields in each Cashier group footer should reflect the total sales for each cashier for a single order date. The Order Date group footer will reflect the total sales for that date across all cashiers, and the Report Footer section will reflect the total sales across all order dates included in the report's database/dataset.
I would also recommend you put a Record Selection formula on the report to allow the user to choose a date range or a single date using the Order Date field. This can be built into the report using either your SQL code or Parameter fields in the report itself.