Solved

Multiple Sorts in ColdFusion Report Builder

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

 

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

Suggested Solutions

Title # Comments Views Activity
JKS to store upstart data 2 107
Web Site Hosting 10 92
site launch date and last modified date 3 99
Widget to get customer remakrs in our website. 3 89
In this short web based tutorial, I wanted to show users how they can still use the powers of FrontPage in conjunction with Expression Web 3.  Even though Microsoft eliminated the use of Web components, we can still use them with FrontPage and edit …
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…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to Import and export files in WordPress. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Click on Too…

820 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