Solved

Multiple Sorts in ColdFusion Report Builder

Posted on 2007-04-02
6
874 Views
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.

Thanks.
0
Comment
Question by:CDL2
  • 3
  • 3
6 Comments
 
LVL 7

Expert Comment

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

Ben
www.ScheduleForce.net
0
 

Author Comment

by:CDL2
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">
SELECT    FY,  EP, EN, LS, LC, FJON, TJON, HOURS, AMOUNT
FROM      table
ORDER BY FY, LS, EP, #calc.sortempID#, #calc.sortName#, LC, FJON
</cfquery>

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.

Thanks.
0
 
LVL 7

Expert Comment

by:bwasyliuk
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.?

Ben
www.ScheduleForce.net
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:CDL2
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
                        query.EN
              else
                           " "
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?
0
 
LVL 7

Accepted Solution

by:
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...

Ben
www.ScheduleForce.net
0
 

Author Comment

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

Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

810 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