Highlighted

Building parameters for the query builder

Explorer ,
Oct 23, 2015

Copy link to clipboard

Copied

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!

Views

169

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Building parameters for the query builder

Explorer ,
Oct 23, 2015

Copy link to clipboard

Copied

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!

Views

170

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Oct 23, 2015 0
Advocate ,
Oct 26, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 26, 2015 0
Advocate ,
Oct 26, 2015

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Oct 26, 2015 0