crsytal report query and "group by" clause

[renju76]

Hi,

I need your help my friends.

I have a table with the fields include

Invoice_date,InvoiceNo,Cardno,name etc

for example

cardno Invoice_Date name InvoiceNo

oig/me/8777/462 12/10/2002 renjith 66666
oig/me/8777/464 12/10/2002 bnhbh 436346
oig/me/8733/555 10/10/2002 dfsdfsdf 56460
oig/me/8733/46343 09/10/2002 gjuyuy 663445
oig/me/8777/468 09/10/2002 zxcxcx 454545
oig/me/8777/462 09/10/2002 bbbbb 45560
oic/me/8777/462 09/10/2002 dssfsdf 45450
oic/me/8777/464 08/10/2002 nnnnnn 676756
oic/me/8777/465 08/10/2002 bbbbbb 456656
oig/me/8678/462 08/10/2002 fxdfdg 3455

I got a problem with sorting these records.I was trying to make the report using Crystal Report in sorted order by cardno,Invoice_Date,InvoiceNo.

for example,i needed the above records to be printed like this


oig/me/8678/462 08/10/2002 fxdfdg 3455


oig/me/8733/46343 09/10/2002 gjuyuy 663445

oig/me/8777/462 09/10/2002 bbbbb 45560
oig/me/8777/468 09/10/2002 zxcxcx 454545


oig/me/8733/555 10/10/2002 dfsdfsdf 56460


oig/me/8777/462 12/10/2002 renjith 66666
oig/me/8777/464 12/10/2002 bnhbh 436346


oic/me/8777/464 08/10/2002 nnnnnn 676756
oic/me/8777/465 08/10/2002 bbbbbb 456656

oic/me/8777/462 09/10/2002 dssfsdf 45450


i tried my best.

If you have any idea of getting these in the above order,please post your answer.

Thank you

 

[goranm]

Hi !

I can´t follow the pattern in your sort order.

For example why does "oic/me/8777/464 08/10/2002"
come after "oig/me/8777/468 09/10/2002".

Can you explain little more what you want to achieve.

Then we will try to help you again.

/Goran

 

[renju76]

Hi Goran

oig/me/8777/462 12/10/2002 renjith 66666
oig/me/8777/464 12/10/2002 bnhbh 436346
oig/me/8733/555 10/10/2002 dfsdfsdf 56460
oig/me/8733/46343 09/10/2002 gjuyuy 663445
oig/me/8777/468 09/10/2002 zxcxcx 454545
oig/me/8777/462 09/10/2002 bbbbb 45560
oic/me/8777/462 09/10/2002 dssfsdf 45450
oic/me/8777/464 08/10/2002 nnnnnn 676756
oic/me/8777/465 08/10/2002 bbbbbb 456656
oig/me/8678/462 08/10/2002 fxdfdg 3455


The above records are unsorted .

And the records must be printed like the following,


oig/me/8678/462 08/10/2002 fxdfdg 3455


oig/me/8733/46343 09/10/2002 gjuyuy 663445

oig/me/8777/462 09/10/2002 bbbbb 45560
oig/me/8777/468 09/10/2002 zxcxcx 454545


oig/me/8733/555 10/10/2002 dfsdfsdf 56460


oig/me/8777/462 12/10/2002 renjith 66666
oig/me/8777/464 12/10/2002 bnhbh 436346


oic/me/8777/464 08/10/2002 nnnnnn 676756
oic/me/8777/465 08/10/2002 bbbbbb 456656

oic/me/8777/462 09/10/2002 dssfsdf 45450

Hope you understood

waiting for your help

Thanks

 

[goranm]

Hi again !

Well I understand how you wiil have your record printed, but as I said in my previous post I don´t understand the criteria for the sorting.
Is it something in the cardno that is significant for how the sorting shall be done ?

Please explain a little bit more.

/Goran

 

[renju76]

Hi Goran

My sort critieria is,

The record must be sorted by three fields

these are cardno,invoice_date,invoiceNo

In the case of cardno

for example "oig/me/8777/462"

we will sort it by the part "8777"

so for each sorted date we have to group the records on the basis of this "8777"

I know its pretty confusing and complicated

Hope you got an Idea

Thanks

 

[goranm]

Ok !

But in your example you have "8777" both for the prefix "oic" and "oig".
And "oic" is coming after "oig".

Is that wright ?
And if it is, shall all the records be sorted descending on the first three letters in the cardno?

And is it the whole part "oig/me/8678" that shall be "treated" together ?

/Goran

 

[renju76]

yes...we have to consider the "oig/me/8678" as whole part

 

[goranm]

How about my first question ?

Why is "oic" coming after "oig" ?

/Goran

 

[stormtrooper]

If this "oig/me/8733/555" is one field, then it will always come before this "oig/me/8777/462" and any other "oig/me/8777/...". Are you sorting ascending or descending? Like goranm says, why is "oig/..." coing before "oic/..."? You still have to elaborate more on what you want because just looking at what you want, it looks as though that has no order whatsoever. For example, how does this happen?

...
oig/me/8733/46343 09/10/2002 gjuyuy 663445

oig/me/8777/462 09/10/2002 bbbbb 45560
oig/me/8777/468 09/10/2002 zxcxcx 454545


oig/me/8733/555 10/10/2002 dfsdfsdf 56460
...

You have "oig/me/8733..." and in between you have "oig/me/8777..." x 2 then it ends with "oig/me/8733..." again.

That's not how sorting is supposed to work.