Solved

Multiple Sorts in ColdFusion Report Builder

Posted on 2007-04-02
6
865 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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.

Join & Write a Comment

Suggested Solutions

When deciding to adopt any help desk solutions many factors should be explored before taking decisions. This will change from business to another but in general there are some kind of rule of thumb. Here are some quick tips: Do we need only ticket…
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
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 properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp…

746 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now