Report Design

How do i make a sub-total in CR9 like the one in the attached file?

Hi,

Add a group section to the report and compute for the sub-total. Be sure you have an identifying column so that you can group them.

Hope this help.

i can get the sub-total for every client but may problem is how can i group the all client that start with SOCO and get the subtotal?

Create a formula that returns True/False by checking if the first 4 characters of the client field is equal to "SOCO". Then create a Group based on the formula.

Quote:

---

Originally Posted by brucevde

Create a formula that returns True/False by checking if the first 4 characters of the client field is equal to "SOCO". Then create a Group based on the formula.

---

this is in CR? where can i find that in Cr?

Yes in CR. I don't have CR9 but under the Insert menu there is probably an item that gets you to the Formula editor screen.

if you have CR8.5 or 11 can you please show how it is done?

This is what i have done so far. I have an SQL query to get the total by client and save it in a temporary table.

vb Code:

sSQL = "DELETE FROM tblTempMonthly"
  oConn.Execute sSQL
 
  sSQL1 = "INSERT INTO tblTempMonthly " & _
            "(ClientName, " & _
            "Budget, " & _
            "Purchases, " & _
            "Selling)" & _
  "SELECT tblClient.ClientName, " & _
            "tblClient.ClientBudget,  " & _
            "SUM(tblClient_Consume_Products.Purchases * tblClient_Consume_Products.Quantity) AS Purchases, " & _
            "SUM(tblClient_Consume_Products.SalesPrice * tblClient_Consume_Products.Quantity) As Selling " & _
  "FROM tblClient INNER JOIN " & _
        "tblClient_Consume_Products ON  " & _
        "tblClient.ClientID = tblClient_Consume_Products.ClientID " & _
  "WHERE (tblClient_Consume_Products.DateOut BETWEEN '" & Format(DTPStart.Value, "mm-dd-yyyy") & "' AND '" & Format(DTPEnd.Value, "mm-dd-yyyy") & "' ) " & _
  "GROUP BY tblClient.ClientName, tblClient.ClientBudget"
  oConn.Execute sSQL1

Result of the above query:
ClientName Budget Purchase Selling
SOCO-Apopong 960.00 55.50 63.82
SOCO-Calumpang 960.00 56.25 64.68
Doctor's Hospital 3000.00 72.75 83.66
SOCO-Main 5000.00 147.75 169.90

I used the records of temporary table in Crystal Report 9. I used grouped by clientname and here is the result:
ClientName Budget Purchase Selling
Doctor's Hospital 3000.00 72.75 83.66
SOCO-Apopong 960.00 55.50 63.82
SOCO-Calumpang 960.00 56.25 64.68
SOCO-Main 5000.00 147.75 169.90
Total 9920.00 332.25 382.06

i want it to be like this:
SOCO-Apopong 960.00 55.50 63.82
SOCO-Calumpang 960.00 56.25 64.68
SOCO-Main 5000.00 147.75 169.90
Sub-Total 6920.00 259.50 298.40

Doctor's Hospital 3000.00 72.75 83.66
Sub-Total 3000.00 72.75 83.66
Grand Total 9920.00 332.25 382.06

anyone who has an idea to share on how to solve the problem please?

i think u need some sort of Parent Table that hold 'smaller Company' so u can JOIN from there

something like

Quote:

---

ParentTable = @parentID - ParentName
@1 - SOCO
@2 - Bank

---

and at ur Current Table add new column that reference to ParentTable

Quote:

---

urCurrentTable = ... + ParentID
... + 1
... + 2

---

so at the end u can say i want the total Consumption for SOCO, Bank, and so on

PS: perhaps others can explain it better

The following are the contents of my tables:

Under tblClient:
ClientID ClientName Budget
Client1 SOCO-Main 5000.00
Client2 SOCO-Apopong 960.00
Client3 SOCO-Calumpang 960.00
Client4 Doctor's Hospital 3000.00

Under tblClient_Consume_Products:
ProductID ClientID Qty Purchases SalesPrice
Prod0001 Client1 5.00 18.75 21.56
Prod0002 Client1 3.00 18.00 20.70
Prod0002 Client4 2.00 18.00 20.70
Prod0001 Client3 3.00 18.75 21.56
Prod0001 Client2 2.00 18.75 21.56
Prod0002 Client2 1.00 18.00 20.70
Prod0001 Client4 1.00 18.75 21.56
Prod0002 Client4 1.00 18.00 20.70

My query above came from two tables--tblClient and tblClient_Consume_Products and i created a temptable to save the result of my query. In CR I used temptable for my report.
My tblClient has already ClientID column.

Quote:

---

Originally Posted by erickwidya

i think u need some sort of Parent Table that hold 'smaller Company' so u can JOIN from there

---

Is the parent table different from my tblClient?

Quote:

---

Originally Posted by erickwidya

at ur Current Table add new column that reference to ParentTable

---

are you referring to temptable?

Quote:

---

Is the parent table different from my tblClient?

---

of course, otherwise i wouldn't say new table, i think urCurrentTable is referring to ClientTable

if u change it..just make sure u SELECT parentID column(or whatever name u use) so that in ur temptable u also have parentID and u can use JOIN from there

like

Code:

---

SELECT ... from myTempTable INNER JOIN [ParentTable] ...
WHERE [ParentTable].[Name] = 'SOCO' --or Hospital, Bank

---

and in CR u can use GROUP by [ParentID] and get the result u want