Crystal Reports How to sort on second pass formula

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: How to sort on second pass formula

Author	Message
lieveluc Newbie Joined: 27 Feb 2009 Location: Germany Online Status: Offline Posts: 2	Topic: How to sort on second pass formula Posted: 27 Feb 2009 at 6:30am
	What needs to be done to sort on a second pass formula? I do have a formula which i using a shared variable (the content of this variable is returned by a subreport). Actually it is the number of lines within the subreport. Now i want to sort the report on this formula. However this formula can`t be selected. Any workarounds? Thx
	IP Logged


lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 27 Feb 2009 at 6:42am
	I would have said to create another group and use the formula to group on, but have recently found that having shared variables seems to remove the ability to create a group. With that in mind, is there any way to replicate the data in the subreport in the main report? Maybe not in the same detail, but for the line number? If so, you can build a formula that would return the number of lines. That would be my first plan of attack. Are getting the data via a stored proc or joining to the tables? If from a stored proc, you might be able to retrieve the number of lines an store it as a field in the result set. What you are looking for is a value that can be directly accessed/calculated from your data w/o shared variables...I am guessing that they are not predicable enough so that on the first pass of reading the records Crystal can deterimine where a record is going to go. Hope this helps
	IP Logged

lieveluc Newbie Joined: 27 Feb 2009 Location: Germany Online Status: Offline Posts: 2	Posted: 27 Feb 2009 at 7:55am
	Actually i´m using the approach (chapter 9) as described in the Crystal Reports Encyclopedia from Brian Bischof. In my case all information resides in one table of a MySQL database. There do exist already 2 groups: first group is the email address and the following group is showing all sessionid for the email address. I use a subreport to show every activity with the same sessionid. Within this subreport i count the number of lines as i want to sort the global report based on the number of activities for each email address. The sum of lines for each email address is displayed correctly in the main report in the corresponding group footer. The only problem i have is here that this formula can`t be used as a sort criteria. I also tried to use a SQL expression to count the number of lines, however the problem i have here is that i can`t pass to a SQL statement a variable field for the sessionid. If i use a static sessionid the following SQL expression works fine: (select count(n.`adresse`) from database n where n.`sessionid` = '12345'). Any idea how to pass a field from CR as a variable to a SQL expression? Thanks for your support
	IP Logged

lockwelle Moderator Joined: 21 Dec 2007 Online Status: Offline Posts: 4374	Posted: 27 Feb 2009 at 8:34am
	OK, all the data is one table, which is good. You group by email address and then by session id. Now, you want to sort based on the number of lines of activity by an email address. Hopefully, I have gotten the requirements correct. Not to hard...I hope. I would create a formula that counts the activity based on the email address something really simple like: COUNT({table.emailaddress}) then I would create a group and use the formula as the field to group on. I found that Brian did this, and I have been using it ever since for dynamic group/sorting of data in my reports. Hope I got it right and that this helps.
	IP Logged

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum