Report Design

[Simply Me]

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

[tsungik]

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.

[Simply Me]

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?

[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.

[Simply Me]

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?

[brucevde]

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.

[Simply Me]

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

[Simply Me]

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.

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

[Simply Me]

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

[erickwidya]

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

something like

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

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

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

[Simply Me]

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.

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?

at ur Current Table add new column that reference to ParentTable

are you referring to temptable?

[erickwidya]

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

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