Building parameters for the query builder

[kevomac12]

So here's a Query I need to build a subreport around...

<cfquery name = "ac" datasource= #ODBC_DataSource#>

  SELECT tblArea.OIDEMSMaster, tblAreaStructure.OIDStructureTypeRef, Count(tblAreaStructure.OID) AS CountOfOID,

  SUM(tblAreaStructure.SF) AS TotalSF, SUM(tblAreaStructure.ReplacementCost) AS TotalRC, SUM(tblAreaStructure.LSI) AS TotalLSI

  FROM (tblArea INNER JOIN tblAreaLocation ON tblArea.OID = tblAreaLocation.OIDArea)

  INNER JOIN tblAreaStructure ON tblAreaLocation.OID = tblAreaStructure.OIDAreaLocation

  GROUP BY tblArea.OIDEMSMaster, tblAreaStructure.OIDStructureTypeRef

  HAVING (((tblArea.OIDEMSMaster)=#URL.OIDEMS#));

</cfquery>

And this is what I did in the 'builder':

So I did the join from tblArea (primary key) to tblAreaLocation (foreign key), and then tblAreaLocation (primary key) tblAreaStructure (foreign key).

I think I got this on right (options/suggestions...)!

So I'm trying to build this: SUM(tblAreaStructure.SF) AS TotalSF, SUM(tblAreaStructure.ReplacementCost) AS TotalRC, SUM(tblAreaStructure.LSI) AS TotalLSI

In the Paramenters I set TotalSF as the name and the default values to: SUM(tblAreaStructure.SF) IS TotalSF (via the query builder), and the same set for TotalRC & TotalLSI...now where in the query do I set up TotalSF, TotalLSI, TotalRC? I think I didn't use the Parameters correctly!

[EddieLotter]

Kevin, I strongly recommend creating a view in your database engine and then building your query in the Query Builder against that view.

This will greatly simplify things for you in the Query Builder.

Cheers

Eddie

[EddieLotter]

kevomac12 wrote:

I don't understand "create a view" in my DB file and build a query in that view via the builder?

In your database you can save the text of a query as a "view" or "query" object. What it is called is specific to your database engine, but conceptually it is a "saved query."

When you save a "select" query in this way you can treat it as a read-only table. The query object returns a result set as if it were data in a single table. Using this approach will greatly simplify what you need to do in the Query Builder in the ColdFusion Report Builder.

In the list on the left of the Query Builder window, collapse the "Table" node and you will see that the next node is called "Views". Expand the "Views" node to see the available saved queries in your database. You can use these views the same way as you used your tables previously. It's very convenient.

Cheers

Eddie