asked on

COLDFUSION DRILL DOWN REPORT

I need to know how to create a report to generate a drill down in coldfusion.

Example:

LAST_NAME | FIRST_NAME
JORDAN MICHAEL
CRUISE TONY

Click on Last Name and then it drills down to the next level of data which per say consist of address information.

How can I do this in Coldfusion?

Thank

erikTsomik

Well first you need a query to pull people names
<cfquery name="people" datasource="#name#">
select last_name,first_name, emloyeeid from employee
</cfquery>
Now you can start outputing the data
<table border="1">
<tr>
<td>Last Name</td>
<td>First_name</td>
<td>
</tr>

<cfoutput query="people">
<tr>
<td><a href="people_detail.cfm?employee=#employeeID#">#Last_name#</a></td>
<td>#First_name#</td>
</tr>
</cfoutput>
</table>

And noe you can go to the processng page

<cfif isDefined("url.employee")>
<cfquery name="peopleDetail" datasource="#name#">
select phone,email,address from employee where employeeid=#url.employee#
</cfquery>

<table border="1">
<tr>
<td> phone </td>
<td>email</td>
<td>address</td>

</tr>

<cfoutput query="peopleDetail">
<tr>
<td>#phone#</td>
<td>#email#</td>
<td>#address#</td>
</tr>
</cfoutput>
</table>
</cfif>

OK the logic here is correct if you have more question please post more . but thata should work for you

psychic_zero

My idea is like this code below. But I'm not sure how to retrieve data from previous page using URL get method in Coldfusion as last time I'm using Coldfusion is about 5 years ago, but hope this can give you some idea.

// 1st Page
 
<CFQUERY DATASOURCE = "users" NAME = "MAIN">
SELECT user_id, first_name, last_name
FROM users
ORDER BY last_name
</CFQUERY> 
 
<table border = "1">
<tr>
   <td>LAST_NAME</td>
   <td>FIRST_NAME</td>
</tr>
<tr>
   <CFOUTPUT QUERY = "MAIN"> 
   <td><a href="detail.cfm?user_id=#user_id#">#first_name#</a></td>
   <td>#last_name#</td>
   </CFOUTPUT> 
</tr>
</table> 
 
// 2nd Page - detail.cfm
// Get the user_id parameter from the previous page
 
<CFQUERY DATASOURCE = "users" NAME = "MAIN">
SELECT user_id, first_name, last_name, address1, address2, state, zipcode
FROM users
WHERE user_id=#user_id#
</CFQUERY> 
 
<table border = "1">
<tr>
   <td>LAST_NAME</td>
   <td>#last_name#</td>
</tr>
<tr>
   <td>FIRST_NAME</td>
   <td>#first_name#</td>
</tr>
<tr>
   <td>ADDRESS 1</td>
   <td>#address1#</td>
</tr>
<tr>
   <td>ADDRESS 2</td>
   <td>#address2#</td>
</tr>
<tr>
   <td>STATE</td>
   <td>#state#</td>
</tr>
<tr>
   <td>ZIP CODE</td>
   <td>#zipcode#</td>
</tr>
</table>

Open in new window

erikTsomik

psychic_zero:
you sample is identical as mine

psychic_zero

Yeah, the different is I forgot how to retrieve data from previous page in Coldfusion

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial