Solved

Multiple Sorts in ColdFusion Report Builder

Posted on 2007-04-02
6
877 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
[X]
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
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
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
UPLOAD FILE TO Web API USING POST 5 136
listing all functions in JavaScript 19 253
Problem to go to Web page 2 140
cookies analysis tools 2 111
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
The purpose of this video is to demonstrate how to insert an Iframe into 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 : Open Page or Post…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. 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 : Go t…

739 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