| Author |
Message |
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
  
Topic: SQL Eexpression or ?
Posted: 10 Mar 2009 at 5:07pm |
Hi
I have a reprot which is designed to display book details plus loan transaction like:
Acc No. Book title ISBN Call number Author number of loan
XXXX XXXXXX XXXX XXXXXXX XXXX X
My current approach is to group Acc No. and move all other fields to group header or footer, and create a formula '@number_loan' where: if {OND.IRN} = 0 THEN
0
ELSE
DistinctCount ({OND.IRN}, {ITN.ID})
OND and ITN are linked where OND stores loan transction details and ITN stores Acc No for each book item.
The above approach works. However my customer wants sort Acc No. book title, and Call number on the fly. I know I can use parameters for control the sort on the fly when the records are displayed in the detail section of the report. Once I move the fields into the group header or footer. Sort on the fly won't work.
Is there any other way? Could I use SQL expression field to replace the formula? if so, how do I write?
I would appreicate very if if anyone could please advise. Thanks in advance.
John
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Mar 2009 at 5:37pm |
Hey John,
Does your customer want it to sort in one of these 3 ways or multiple ways using multiple options.
IF it is just sorting on the fly for one you could try creating a formula field that adds these 3 items togther into a string but changes the order they are added, then group on this formula rather the Acc No.
Suppress the groupname if need be but it will cahnge the sort order on the fly...
If para= 1 then account numer + title + call number else
if para = 2 then tital + all nmber + title else
if para = 3 ...
Just an idea. Edited by DBlank - 10 Mar 2009 at 5:38pm
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 10 Mar 2009 at 5:51pm |
Thank you for your quick reply and solution.
My customer wants sort by three ways-- Accession number, book title, and Call number.
I will try to use the approach you have advised then... and let you know how it works.
Thanks again.
John
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 10 Mar 2009 at 6:05pm |
Hi,
when I created a formula with:
if {?sort_fields} = "Accession Number" then
{ITN.ID}
else if {?sort_fields} = "Title" then
{HEADING.HEAD}
else if {?sort_fields} = "Call Number" then
{ITD.CALLNO}
and then change the group with the above formula.
when running this report, there is an error saying " there must be a group that matches this field" , click OK, and the following line is highlighted in @number_loan
DistinctCount ({OND.IRN}, {ITN.ID})
Please advise.
Thanks for your further help.
John
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Mar 2009 at 6:16pm |
|
DistinctCount ({OND.IRN}, {formula field you are groupin on here})
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Mar 2009 at 6:20pm |
Also you may want to change your formula field because if you use that to group on I am guessing there are titles that are the same but the ID # is differnet, that is why I was suggesting to use multiple fields in there to make sure you get the correct unique records but just swap the ordering. Something like (this is assuming that the ITN.ID is your true unique record #):
if {?sort_fields} = "Accession Number" then
{ITN.ID}
else if {?sort_fields} = "Title" then
{HEADING.HEAD} + " " + totext({ITN.ID},0)
else if {?sort_fields} = "Call Number" then
totext({ITD.CALLNO},0) + " " + totext({ITN.ID},0) Edited by DBlank - 10 Mar 2009 at 6:22pm
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 10 Mar 2009 at 6:27pm |
Cool! now it works after modifying the line with error. Thanks very much for that
Also your last suggestion is fine, I will follow that.
Thanks again for your help.
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 11 Mar 2009 at 6:28pm |
Hi
I would like to re-open this query. With the above report, I have also field such as Currency code(which SGD, USD, AUD etc ) which I need to sort .
Acc No. Book title ISBN Call number Author number of loan CODE
XXXX XXXXXX XXXX XXXXXXX XXXX X XXX
The sort on Accession Number, title, Call number is OK; however when I sort on Currency code, there is only one record displayed for each currency code.
When I unsupress the group header to have a look, there is:
AUD
XXXX XXXXX XXXXXXX XXXXXX XXXXX XXXX XX AUD XXX ....
USD
XXXX XXXXXX XXXXXXX XXXXXX XXXXX x USD XXXX ...
I don't know why. could you please advise. Thanks in advance.
|
| IP Logged |
|
johnwsun
Senior Member
Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
|
Posted: 11 Mar 2009 at 6:47pm |
Sorry, I found the solution--I forgot to put other fields together as:
else if {?sort_fields} = "Currency Code" then
{CCY.ANSI} + " " + {ITN.ID} + " " + {HEADING.HEAD} + " " + {ITD.CALLNO}
cannot use totext({CCY.ANSI},0), as there is an error " too many argument"
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Mar 2009 at 8:20pm |
|
Looks like you got it. I only had to text in my sample because I assumed some of those were numeric fields. Would need to convert them to text to include the title field. Seems like things are working well now
Edited by DBlank - 11 Mar 2009 at 8:21pm
|
| IP Logged |
|
|
|
