Multiple Sorts in ColdFusion Report Builder

Posted on 2007-04-02
Last Modified: 2013-12-20
I'm trying to build a report that allows the user to select the sort order. I was able to do this in Crystal Reports by using several sort fields and moving data into them as needed to satisfy the requested sort. I would always sort on all the fields, but sometimes the fields would be blank and therefore basically ignored. For example, I want to allow the user to sort by year, location and SSN, or by year, location, and name. Year and location were database fields and always populated, whereas SSN and name were calculated fields. I would populate either SSN or name only if that was actually to be used in the sort, otherwise it would be blank.

I tried this in Report Builder using the advanced option in the query builder. I entered the 2 calculated fields into the order by statement, it was even nice enough to show the calculated fields in a dropdown box. However, when I tried to preview the report I got an error "Table or alias not found: calc -- Location of error in the SQL statement is: 119 ".

Should this approach have worked? Is there another way to accomplish this?

Also, is there a way to look at the code that the preview is trying to execute? The only code I've found is the code snippet, and the code fragments in the fields and parameters.

Question by:CDL2
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
  • 3
  • 3

Expert Comment

ID: 18844461
Can you show the query format - it might have to do with the appropriate use of ## signs...


Author Comment

ID: 18845658
I think you're right about the #signs.  I've tried a couple of different things, but haven't found the right thing yet.  I think it should look something like this:

<cfquery name="CFReportDataQuery" datasource="databasename">
FROM      table
ORDER BY FY, LS, EP, #calc.sortempID#, #calc.sortName#, LC, FJON

The latest error I've been getting is "Element SORTEMPID is undefined in CALC."  That's a strange error, since the query builder actually showed it in a dropdown once I entered calc.


Expert Comment

ID: 18845686
The trouble here might be that the Calc option is not yet defined at the point in which this query runs.  The query runs early in the instantiation of the report, and if these calculated variables are created after the query runs, you are out of luck.

What about just passing in the sort option as a PARAM - then you could to PARAM.SortEmpID.?

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 18845760
The parameter being passed just tells me which sort the user wants.  Depending on the sort they want EmpID or Name may be blank or may have the value from the database query.  For example, calc.sortName has a formula in it

               if param.SortOpt = '2' then
                           " "
This is how I was able to do multiple sorts with one report in Crystal.  Is there another way to do it in CF Report Builder?

Accepted Solution

bwasyliuk earned 125 total points
ID: 18846474
If it is telling you that the calculated variable does not exist, then i think you are stuck in this path - and need to choose a different one.

For what it is worth - I have found the query wizard in the report builder doesnt meet the needs in more complicated scenarios, and I have taken the path of building the query (always using a stored procedure as well) outside (in CFML) and then passing the query variable to the report in the <CFREPORT tag.

Let me know if you want me to expand on this...


Author Comment

ID: 18850534
I've come to the same conclusion and am working on the <cfreport> tag now.


Featured Post

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: :…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

622 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