Filter Data returned from a Stored Procedure

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
Larry Bristersr. DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anandkpCommented:
do u want to filter the data - at procedure level itself ???
anandkpCommented:
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
Larry Bristersr. DeveloperAuthor Commented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

anandkpCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry Bristersr. DeveloperAuthor Commented:
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

PE_CF_DEVCommented:
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.
PE_CF_DEVCommented:
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.
anandkpCommented:
u need to use this "db_seg" inside ur procedure to filter ur records at teh DB level
jonnygo55Commented:
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...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.