Problem displaying data in a table format

[Dynamo3209]

Hi,
I am using the following code

<cfquery name ="Company_Totals" datasource="ABC">
	SELECT Clients.ClientID, Clients.ClientName,
               Customers.ClientID, Customers.CustID,
               Clients.Location, count(Clients.ClientID) As TotalOrders,
               Orders.Status, OrderDetails.ProductID,
               Products.ProdName
       from    
               Clients, `Orders`, Customers, OrderDetails, Products
       Where   
               Clients.ClientID = Customers.ClientID And
               Customers.CustID = Orders.CustID AND
               OrderDetails.OrderID = Orders.OrderID AND
               OrderDetails.ProductID = Products.ProductID
       group by  
               Clients.ClientName, Clients.Location, Products.ProductID
</cfquery>

<cfquery name ="Order_Totals" datasource="ABC">
	SELECT Clients.ClientID, Clients.ClientName,
               Customers.ClientID, Customers.CustID,
               Clients.Location, count(Clients.ClientName) As TotalOrders,
               Orders.Status, OrderDetails.ProductID,
               Products.ProdName
       from    
               Clients, `Orders`, Customers, OrderDetails, Products
       Where   
               Clients.ClientID = Customers.ClientID And
               Customers.CustID = Orders.CustID AND
               OrderDetails.OrderID = Orders.OrderID AND
               OrderDetails.ProductID = Products.ProductID
       group by  
               Clients.ClientName, Clients.Location
</cfquery>
<cfoutput query="Order_Totals">
#TotalOrders#
</cfoutput>
<table border = 1><tr><td>ClientName</td><td>Location</td><td>TotalOrders</td><td>Products</td></tr>
<cfoutput query="Company_Totals" group="ClientName">
<cfoutput group="location">
<cfset products="">
<p>
<cfoutput>
<cfset products=ListAppend(products,#ProdName#)>
</cfoutput>

<cfset ordertotals="">
<p>
<cfoutput>
<cfset ordertotals=ListAppend(ordertotals,#TotalOrders#)>

</cfoutput>


<tr>
<td>#ClientName#</td><td> #Location#</td><td> #ordertotals#</td><td> #products#</td></tr>
</cfoutput>
</p>
</cfoutput>

and get the following output

2 6 
[u]ClientName Location TotalOrders Products [/u]

Ford Burlingame 1,1 8,10 


Triad Loss 3,1,1,1 1,2,7,10

however I want the following format

ClientName Location TotalOrders Product1 Product2 Product3 .
Ford       Burlingame 2           1        1
Triad      Loss       6           2        3       1 ..

I am unable to accomplish this, I would appreciate any suggestions and help.
Thanks,
Dynamo

 

[ecobb]

Waaayyyy too many cfoutputs...let's clean it up and try this to start with:

<table border = 1> 
 <tr>
  <td>ClientName</td>
  <td>Location</td>
  <td>TotalOrders</td>
  <td>Products</td>
 </tr>
<cfoutput query="Company_Totals" group="ClientName">
[blue]<cfoutput group="location">[/blue]--Not sure what this is for
<cfset products="">
<cfset products=ListAppend(products,#ProdName#)>
<cfset ordertotals="">
<cfset ordertotals=ListAppend(ordertotals,#TotalOrders#)>
 <tr>
  <td>#ClientName#</td>
  <td>#Location#</td>
  <td>#ordertotals#</td>
  <td>#products#</td>
</tr>
[blue]</cfoutput>[/blue]
</cfoutput>

Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[Dynamo3209]

Hi Ecobb,
I need to generate a report for total orders grouped by client and location, one client may have several locations, so the report needs to be grouped by client and location and one client from one location may order several products so I have to display the total orders from a client at one particular location and then break up that total into according to the number of orders for each product.
i.e. suppose Client1 Location1 has made a total of 6 orders and then break of the total orders is as follows
Product1 = 2 orders
Product2 = 3 orders
Product3 = 1 order
then I need to display this as

ClientName  Location TotOrder Prod1  Prod2  Prod3
Client1     Loc1      6           2      3        1
Client1     Loc2      5           0      3        2
Client2     Loc1      7           4      1        2

To get this I am using the <cfoutput group="location">
Hope this makes my requirements clearer.
Thanks for the prompt reply.
Dynamo.

 

[ecobb]

Gotcha. So did you try my above suggestion of only using 2 cfoutputs instead of 5 or 6?

Looking back over the code, I do see some problems.
To start with, this: ListAppend(products,#ProdName#)
will produce a list like this: 1,2,3,4,5
So when you output #products#, it's going to be the list of them (1,2,3,4,5) instead of the individual products.

It would actually be this:

[b]ClientName  Products[/b]
Ford         1,3,4,7,8,9

Instead of this:

[b]ClientName  Product1  Product2[/b]
Ford         1         3

Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher

 

[Dynamo3209]

Hi Ecobb,
I can loop through each element as follows

<cfset length1 = listlen(ordertotals)>

<cfloop index="element" from="1" to="#length1#">
<cfset currentorder = listGetAt(ordertotals, element)>

and get the value of each element
But I am stuck at how to display the results in the table format like

ClientName  Location TotOrder Prod1  Prod2  Prod3
Client1     Loc1      6           2      3        1
Client1     Loc2      5           0      3        2
Client2     Loc1      7           4      1        2

The display part is causing problems for me, I am not able to display the results in the desired format, like shown above.
Thanks,
Dynamo

 

[Dynamo3209]

Hi Ecobb,
Can you give me some ideas how to go about for accomplishing the desired results as I have mentioned in my last post.

Thanks,
Dynamo