EasyCFM.COM - The ColdFusion Community! About Us!

New Member
Posts: 6
Joined: Jan 2008

I am writing a dynamic report generator that will allow me to specify the filters and selectable fields for generating a report in tables.  These tables house metadata that I will then use to determine what shows on the screen and eventually in the reports.  I am specifically having problems getting the output from one query based on a field I determine from a previous query.  What I need is for the output of query 1, which is the field name I want from query 2, to be used to get the query results from that field when I output query 2.  In "qryGetMetaData" the value of txtDisplayField will be used to determine what output I need to get from "qryGetFilterData".

******************************************************

<cfquery name="qryGetMetaData" datasource="twmreport">
    SELECT f.txtDataSource,
                    f.numFilterTypeID,
                    f.txtParameters,
                    f.txtParameter2,
                    f.txtParameter3,
                    f.txtParameter4,
                    f.txtDisplayField,
                    f.txtIDField
    FROM TWMReport.dbo.mFilter f
    INNER JOIN TWMReport.dbo.mFilterToDataSet ftd
    ON f.numFilterID = ftd.numFilterID
    WHERE ftd.numDataSetID = #DataSetID#
</cfquery>

<cfquery datasource="twmreport" name="qryGetFilterData">
     #qryGetMetaData.txtDataSource# <cfif LEN(qryGetMetaData.txtParameters) GT 0>#qryGetMetaData.txtParameters#</cfif> = #Param1# </cfquery>

<cfset DisplayField = #qryGetMetaData.txtDisplayField#>

<cfoutput query="qryGetFilterData">
     #numClientID# #DisplayField#<br/>
</cfoutput>
*****************************************************

Thanks for looking.

dlackey

01-08-2008 @ 10:40 AM

Moderator
Posts: 935
Joined: Jun 2007

Hi Stevevo and welcome to the forums.

Can you...
post the value of #queryGetMetaData.txtDataSource#?
post the value of #len(qryGetMetaDta.txtParameteres)#?
post the value of #qryGetMetaData.txtDisplayField#?
Also, your <cfset displayField = #qryGetMetaData.txtDisplayField# is setup like a numeric since it doesn't have "" around it but based on the name, I'm assuming it is a text variable instead of a numeric.

Cheers,
Chip

stevevo

01-08-2008 @ 10:51 AM

New Member
Posts: 6
Joined: Jan 2008

Thanks Chip - I'm glad to join.  I hope to be able to give some answers in the near future.

**************

txtDataSource is "pr_getAliasForClient" - this is a stored procedure.

txtParameters is a list of parameters to be passed to the SP.  The specific value in this case is "@ClientID".

The combination of these two works great.

txtDisplayField returns a value of "txtAlias".  "txtAlias" is a field in the result of "qryGetFilterData".  The hope when outputing "qryGetFilterData" is to use "txtDisplayField" to tell the CFOUTPUT that I really want the results from query qryGetFitlerData.txtAlias.  The key is that I must be able to do this dynamically.

Thanks again.

dlackey

01-08-2008 @ 11:31 AM

Moderator
Posts: 935
Joined: Jun 2007

what happens if you hard code the (e.g. @ClientID) along with the name of the SP? Does the query produce the expected results?

Cheers,
Chip

stevevo

01-08-2008 @ 11:55 AM

New Member
Posts: 6
Joined: Jan 2008

If I hard code my values like so, all returns as I'd want it.

<cfoutput query="qryGetFilterData">
#numClientID# #txtAlias#<br/>
</cfoutput>

dlackey

01-08-2008 @ 12:00 PM

Moderator
Posts: 935
Joined: Jun 2007

Are you developing locally or on a network or by a hosting provider?  If hosting provider, is there a way you can confirm that what you are wanting to do is allowed?  You may have to get a feature enabled or soemthing.

If locally, are you running IIS?  If so, do you have the necessary permissions enabled for the SP to run (they are executed right?).

Umm.. otherwise, I'm stuck at the moment.  The hard coding works as expected so some value isn't making it the server or from the server.  Can you dump your vars before and after the query to see where the values are stopping (if stopping is the operative word).

Cheers,
Chip

stevevo

01-08-2008 @ 1:37 PM

New Member
Posts: 6
Joined: Jan 2008

I'm developing on a server that is in a colo, but I have full access.  My problem isn't permissions or anything like that.  I can run SPs fine and everything else is good.

The problem I have it that I want to use #DisplayField# (value of txtAlias) to tell CFOUTPUT to get #txtAlias# from my CFQUERY (qryGetFilterData).  So, my output looks like this:

1 txtAlias
1 txtAlias
1 txtAlias
1 txtAlias
1 txtAlias

I hope this makes more sense now.  Thanks

dlackey

01-08-2008 @ 1:42 PM

Moderator
Posts: 935
Joined: Jun 2007

what if you double pounded ## your ##displayfiend##? OR maybe you can write do the following:

<cfsavecontent variable="varDisplayText">#displaytext#</cfsavecontent>

As long as that isn't wrapped with <cfoutput></cfoutput> the text should remain as #displaytext#. put #varDisplayText# when you are ready for the var to be evaluated???

Cheers,
Chip

stevevo

01-08-2008 @ 1:57 PM

New Member
Posts: 6
Joined: Jan 2008

I'm not sure I'm getting my overall intention communicated well. I don't ever want to see the text "DisplayText" in my output. Rather, I want to see the results of the query "qryGetFilterData" for the field that is represented by #DisplayText#. So, in this case, if we were to hard code what I'm looking for it would be:

<cfoutput query="qryGetFilterData">
#numClientID# #txtAlias#<br/>
</cfoutput>

The trick for me is that I want that second value to always be dynamic so I can return data from different tables without having to write code for each unique table.

thanks again

stevevo

01-08-2008 @ 3:43 PM