Sorting based on selection criteria 1

[vega83]

Friends If i slected todays date my data should sortup as below, how can i achive this?
MOnth
July09
June09
May09
APR09
Mar09
Feb09
jan09
Dec08
Nov08
Oct08
Sep08
Aug08
jul08
Thanks so much,
V

 

[VKZIMM]

Add a Group based on your Date field and select Descending
for each month.

On the Options tab of this window select Use a formula as Group Name and enter the following formula replacing AP_InvoiceHistoryHeader.InvoiceDate) with your date field.

MonthName (Month({AP_InvoiceHistoryHeader.InvoiceDate}),true )
+
Right (Totext(Year({AP_InvoiceHistoryHeader.InvoiceDate}),0),2)

 

[vega83]

Thanks so much it works, but only thing is its repeating 3 times
julu09
jul09
jul09
june09
june09
june09 so on, do i need to do anything else?

 

[VKZIMM]

No, you should not have to do anything else, but it could be how you are linking your records. I do not know what files you are using or linking with. Also, do you have multiple groups in your report?

 

[vega83]

Hi,
I did group on my date and included your formula, my every time date changes with in same month its creating other group. Any way to prevent this?

 

[VKZIMM]

Are you sure you have this section will be printed for each month?

 

[vega83]

Sample data is like
added date money
2009/07/29 20
2009/07/11 4
2009/06/15 33
2009/07/01 12

When did group on added date and used your formula
it comes as
July09
July09
june09
june09

 

[vega83]

I tried this formula and did group on it still not working
//If MOnth({?Date}) = Month(Currentdate) then 1
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-1,{?Date}))then 2
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-2,{?Date}))then 3
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-3,{?Date}))then 4
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-4,{?Date}))then 5
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-5,{?Date}))then 6
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-6,{?Date}))then 7
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-7,{?Date}))then 8
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-8,{?Date}))then 9
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-9,{?Date}))then 10
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-10,{?Date}))then 11
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-11,{?Date}))then 12
//else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-12,{?Date}))then 13

 

[vega83]

Ok i got it using

WhileReadingRecords;
If MOnth({Command.ADDED_DT}) = Month({?Date}) then 1
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-1,{?Date}))then 2
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-2,{?Date}))then 3
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-3,{?Date}))then 4
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-4,{?Date}))then 5
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-5,{?Date}))then 6
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-6,{?Date}))then 7
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-7,{?Date}))then 8
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-8,{?Date}))then 9
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-9,{?Date}))then 10
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-10,{?Date}))then 11
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-11,{?Date}))then 12
else if MOnth({Command.ADDED_DT}) = Month(DateAdd("m",-12,{?Date}))then 13

 

[VKZIMM]

I do not see anything in your calculation that will handle the different years. Are you only doing one year?

You are calculating months only from what I see.

 

[vega83]

Hi I have a selection criteria
{Command.ADDED_DT} in CDate(CSTR(Month(DateAdd("m",-13,{?Date})),0,'')&"/1/"
&CSTR(Year(DateAdd("yyyy",-1,{?Date})),0,'')) to {?Date}

But your right I am not able to do by year, is there other way to achieve this?. Because I would be doing sum of amount based on Month and year.

 

[vega83]

One more question is for my selection criteria
{Command.ADDED_DT} in CDate(CSTR(Month(DateAdd("m",-13,{?Date})),0,'')&"/1/"
&CSTR(Year(DateAdd("yyyy",-1,{?Date})),0,'')) to {?Date}

I amgetting only 12 months, i,c if i slect may2009, i am getting data from May2009 to june 2008 not may 2009 to may2008, Any reason?

 

[lbass]

Change your record selection to:

{Command.ADDED_DT} in DateAdd("m",-13,{?Date}-day({?Date})+1) to DateAdd("m",1,{?Date}-day({?Date})+1) - 1

If you get an error message saying a date is required here, change it to:

{Command.ADDED_DT} in date(DateAdd("m",-13,{?Date}-day({?Date})+1)) to date(DateAdd("m",1,{?Date}-day({?Date})+1)) - 1

Then you should just insert a group on {Command.ADDED_DT} on change of month in descending order.

-LB

 

[vega83]

LB, works like a charm!, thanks so much!. Really appreciate your time on this.
Thanks a ton,
V

 

[vega83]

LB, is there a way that I can show this months in horizontal manner?
I want to show this way
june july aug spt
instead of
june
july
aug.
Thanks again
V

 

[vega83]

Friends,
One thing I came up is creating colum headers and created running totals for each month and then created grand summary in report footer. Is there any other easier way?
Vega.

 

[VKZIMM]

Have you tried creating a Cross tab report?

I have done this using the wizard.

Your Columns would be base on the date (Monthly)

 

[vega83]

I agree cross tab is one option, I have lot of other formulas, I have try that and see, but I was checking if there any other options. I aprreciate your time , I try cross tab and see if the business users will be okay with that format. But if you have any other ideas , let me know.
Thanks,
V

 

[lbass]

The crosstab might be the simplest solution.

-LB

 

[vega83]

Thanks LB, I will build solution on cross tab, also how do we achive the same sorting order in cross tab?
like july09 june09 apr09 so onn till july 08?
Thanks so much,
V