Solved

Filter Data returned from a Stored Procedure

Posted on 2003-10-22
9
199 Views
Last Modified: 2013-12-24
Have a fairly straight forward problem.  I have a a dynamic data table in a cfm page that returns data from a SQL Server stored procedure.  All I want to do is have the client side filter the data that is already been returned.  I've looked at the dynamic menu answers on this site and frankly...not being an experienced coldfusion programmer,...couldn't apply it.  I'm just a poor ol' IT Manager who has been forced to reduce staff and am trying to do my best with the tutorials in Coldfusion and Dreamweaver.


My page datasource is

<CFSTOREDPROC procedure="userid.sales_by_week" datasource="Sales_SQL" username="userid" password="password">
</CFSTOREDPROC>
<CFSTOREDPROC procedure="userid.sales_by_week" datasource="Sales_SQL" username="userid" password="password">
  <CFPROCRESULT name="sls_by_week">
</CFSTOREDPROC>

The returned data is below and I want the end user to be able to filter the data based on the   #sls_by_week.SEG# column and possibly one more field.  
<table>
<cfoutput query="sls_by_week">
<tr>
 <td><div align="center">#sls_by_week.SEG#</div></td>
 <td><div align="center">#sls_by_week.YR#</div></td>
<td><div align="center">#sls_by_week.TTL#</div></td>
<td><div align="center">#sls_by_week.SAT#</div></td>                        
<td><div align="center">#sls_by_week.SUN#</div></td>                               
<td><div align="center">#sls_by_week.MON#</div></td>
<td><div align="center">#sls_by_week.TUE#</div></td>
<td><div align="center">#sls_by_week.THU#</div></td>
<td><div align="center">#sls_by_week.FRI#</div></td>                        
</tr>
</cfoutput>
</table>

I need an answer quickly
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 9598153
do u want to filter the data - at procedure level itself ???
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9598173
Assuming the user chooses teh filter [thru a drop down i presume & submits the form - then ...]

<CFSTOREDPROC procedure="userid.sales_by_week" datasource="Sales_SQL" username="userid" password="password">
      <cfprocparam CFSQLTYPE="CF_SQL_INTEGER" DBVARNAME="@db_seg" VALUE="#Form.seg#">

  <CFPROCRESULT name="sls_by_week">
</CFSTOREDPROC>

In ur procedure change ur query to :

-- ur existing sql -- add this where condition to it --
where seg = db_seg
0
 

Author Comment

by:lrbrister
ID: 9598194
No...there are three business segments that have sales.  I want the person browsing (This is an INTRANET page for the CEO...) to be able to filter the records based on the segment he chooses. since we are in the 43rd week of the year this data that's being returned serves up 129 records.  If the user could change or select one business segment he would see 43 records.

This way I don't have to build three segment specific procedures with the assorted ORDER BY and WHERE SEG='business1' in each
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 17

Accepted Solution

by:
anandkp earned 500 total points
ID: 9598243
give ur CEO a form to choose the segment from

something like this :
<!--- filter.cfm --->
<FORM NAME="frm" ACTION="results.cfm" METHOD="post">
      <SELECT NAME="seg" SIZE="1">
            <OPTION>-- pls select --</OPTION>
            <OPTION VALUE="1">1</OPTION>
            <OPTION VALUE="2">2</OPTION>
            <OPTION VALUE="3">3</OPTION>
      </SELECT>
      <INPUT TYPE="Submit" NAME="submit" VALUE="filter">
</FORM>      

<!--- results.cfm --->
<CFSTOREDPROC PROCEDURE="userid.sales_by_week" DATASOURCE="Sales_SQL" USERNAME="userid" PASSWORD="password">
      <CFPROCPARAM CFSQLTYPE="CF_SQL_INTEGER" DBVARNAME="@db_seg" VALUE="#Form.seg#">
      
      <CFPROCRESULT NAME="sls_by_week">
</CFSTOREDPROC>

<CFOUTPUT QUERY="sls_by_week">
      <!--- ur results here --->
</CFOUTPUT>

let me know if this helps

K'Rgds
Anand
0
 

Author Comment

by:lrbrister
ID: 9598562
Anand,

I'm getting results but with no filter.  I'm still getting all of the records.

  The segment names are text.  Example Comp1 or Comp2.
I changed the options to look like this

SELECT NAME="seg" SIZE="1">
          <OPTION>-- pls select --</OPTION>
          <OPTION VALUE="Comp1">Comp1</OPTION>
          <OPTION VALUE="Comp2">Comp2</OPTION>
          <OPTION VALUE="Comp3">Comp3</OPTION>

 I also changed the cfprocparam tag like below
<CFPROCPARAM CFSQLTYPE="CF_SQL_VARCHAR" DBVARNAME="@db_seg"

Am I supposed to substitute the @db_seg vaiiable in the output field somewhere? Or did I make a mistake in the Options section by changing it?

It's a pain to be under a deadline with minimal training.

Thanks
lrbrister

0
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9598807
if you want to do it client side. You might try something like this:

<cfhtmlhead text="function Toggle(an_id) {
            an_id.style.display = (an_id.style.display == 'none') ? 'block' : 'none';
            }">
<table>
<cfoutput query="sls_by_week">
<tr>
<td colspan="9" onClick="javascript:Toggle(A#sls_by_week.currentrow#)"><div align="center">#sls_by_week.SEG#</div></td>
</tr>
<tr id=A#sls_by_week.currentrow# style="display:none;">
      <td><div align="center">#sls_by_week.YR#</div></td>
      <td><div align="center">#sls_by_week.TTL#</div></td>
      <td><div align="center">#sls_by_week.SAT#</div></td>                        
      <td><div align="center">#sls_by_week.SUN#</div></td>                          
      <td><div align="center">#sls_by_week.MON#</div></td>
      <td><div align="center">#sls_by_week.TUE#</div></td>
      <td><div align="center">#sls_by_week.THU#</div></td>
      <td><div align="center">#sls_by_week.FRI#</div></td>                        
</tr>
</cfoutput>
</table>

That should, assuming I typed it right, display a table with all the segments the rest being hidden. When a segement is clicked the stuff under it will apear.
Let me know if that helped or you need something slightly different.
0
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9598816
well I noticed a problem already..

<cfhtmlhead text="
<script>
function Toggle(an_id) {
            an_id.style.display = (an_id.style.display == 'none') ? 'block' : 'none';
            }
</script>">

Use this instead for the htmljead, what this tag does is but the text into the header of the HTML file regarless of where it is executed in the CFM template.
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9598979
u need to use this "db_seg" inside ur procedure to filter ur records at teh DB level
0
 
LVL 2

Expert Comment

by:jonnygo55
ID: 9601684
if you are running cold fusion 5 or mx you could do a query of a query...

<<cfquery name="filteredQuery" dbtype="query">
     select * from sls_by_week where SEG="whatever"
</cfquery>

better to do it at the sp level but in a pinch maybe this is faster for you...
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question