how to group by parameter field

[rrmcguire]

if I have a parameter field which is a date range, for instance 5/1/11..5/11/11, and want to group a section in my report by that parameter field is that possible to do in a formula?

 

[IanWaterman]

You do not need to group by the parameter, just group report by date and select whether to show day week or month etc.

Parameter is used within select expert to restrict data set to the date range entered

Ian

 

[rrmcguire]

the problem with that is I only want it to show for one month, whereas other sections/groups of my report will be for different date ranges.

 

[rrmcguire]

the one month being a specific month that the user running the report dictates.

 

[lbass]

You need to explain more about the varying uses of date ranges within your report.

-LB

 

[rrmcguire]

what I have is a field called "dispatch profit" which for a date field pickup date Im needing to find the total of this for the current month 5/1/11.5/11/11, the previous month 4/1/11.4/30/11 and the month before that 3/1/11.3/31/11, I figured the only way to do this would for parameter fields where the user enters in the date ranges, as these ranges will change for the next month, etc. but I don't know how to get my dispatch profit to total for the previous months is the problem.

 

[lbass]

I would just use a selection formula like this:

{table.date} in dateserial(year(currentdate),month(currentdate)-2,1) to currentdate

Then insert a group on {table.date} on change of month. Then place the field you want to total in the detail section, right click on it->insert summary->sum at the group level.

-LB

 

[rrmcguire]

I'm not sure if that will work, let me try and explain better. Dispatch Profit is a formula totaling 2 other formulas together. That value then needs to be displayed based on a date range selected by the user which is equal to the pickup date range i.e. User selects current date range(I thought a parameter field is the only way to do this) equal to the pickup date range, ex. 5/1/11..5/11/11 then do the same for April and March being the previous 2 months, but this will change as example next month when the report is run for current month(June) the previous 2, May, and April......these 3 values generated need to all be displayed in the same section as they are being shown in a table for comparison versu the users goals, the difference between, the 2 and the percent different which is all copmplete. One other question I have is when a parameter field is used in a main report will it have any effect on a subreport(which for what I'm doing here is where I believe this is going to have to be placed). Thanks for any help provided

 

[rrmcguire]

Well I got what lbass suggested to work with putting in:

{table.date} in dateserial(year(currentdate),month(currentdate)-2,1) to currentdate

in the select expert and then grouping by my {table.date} , {table.date} being the pickupdate field, then running a summary group on my field, dispatch profit.

Problem being I had three parameter fields, for comparison with my dispatch profit summary called, CurrentExpectation, PreviousExpectation, and PreviousExpectation2, which I entered in values of for example, March=40000, April=25000, May=35000, then they would automatically put those values in my report to compare with my summary of my dispatch profit

Basically is there a way to use a parameter field to put in for different months of my group?

thanks

 

[lbass]

Instead of describing things, please show the results you want to see.

You can create formulas like this for your expectation amounts:

//{@currentexpect}:
if {table.date} in dateserial(year(currentdate),month(currentdate),1) to currentdate then
{table.amount}

//{@prevexpect}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-1,1) to dateserial(year(currentdate),month(currentdate),1)-1 then
{table.amount}

//{@prevexpect2}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-2,1) to dateserial(year(currentdate),month(currentdate)-1,1)-1 then
{table.amount}

To get the totals you want to compare, place these in the detail section and then right click on each and insert sums on them at the report level. Or you can create summary formulas like this, e.g.:

sum({@currentexpect})

-LB

 

[rrmcguire]

ok, I see what you're trying to do but if I put the @currentexpect, @prevexpect, or @prevexpect2 in my grouping by month on my pickupdate, how does it know which field to display based on the month, and do I put these fields in the Group?

ex. I have for @currentexpect:

if {SHIPMENTS_TTX.PICKUPDATE} in dateserial(year(currentdate),month(currentdate),1) to currentdate then {?Pm-Current Expectation}

where the user running the report inputs the ?Pm-Current Expectation being 40000 or any value

 

[lbass]

As I said, those are detail level formulas (except for the summary example) and do not belong in a group section.

The way you changed the formula tells me I don't have a clue what you are trying to do. If you want help, you need to need to describe the structure of your main report, what fields you are grouping on, and also identify the purpose of the subreport and where it is located (what main report section). Then explain where your {?pm_Current Expectation} is coming from--I thought you wanted formulas to CREATE the current expectation, and you seem to be showing it as if it already exists as a field to link one or something. Without a clear understanding of what you are trying to do, I can't help.

Consider trying to show what you want by creating some mock data, too.

-LB

 

[rrmcguire]

here's my example:

my formula @dispatch profit is needing to be displayed for the current month and the previous 2 months.

Dispatch Profit is calculated by 2 other formulas added together:

@net total MLA + @mlt net total US(we have two companies, one MLA the other MLT)

@net total MLA is equal to the field net_total added up for every record for a particular user where company=MLA

@mlt net total US = basically the same thing where company=MLT

those 2 values are then added together, I then have a summary to sum the dispatch profit within my group, grouped by "pickup date" for each month, where pickup date is a date field on each record.

Each record has a net total, and either is company MLA or MLT for reference to the formulas above.

I added in the below to the select expert as you mentioned:

{SHIPMENTS_TTX.PICKUPDATE} in dateserial(year(currentdate), month(currentdate)-2,1) to currentdate

Which if I understand correctly pulls only the shipments where pickupdate is within 2 months prior to to the current month.

My expectations for the current month, previous month, and previous month to that, ex. May, April, March, I have set as ex.

/{@currentexpect}:
if {SHIPMENTS_TTX.PICKUPDATE} in dateserial(year(currentdate),month(currentdate),1) to currentdate then {?Pm-Current Expectation}

which if I understand correctly will display what I input for my parameter for current expectation, ex. 20000, if the pickupdate is in the current month.

This seems to work, but fills in "0" for the other months as my @currentexpect, @previousexpect, and @previous expected2 are all within that same grouping by pickupdate...

I've attached the pdf of the report when its ran

The other 2 things I will do when this is correct is calculate the % of goal(expected) reached, so Sum of dispatch profit/@current expected, etc.

Then over or under goal, Sum of Dispatch Profit - @current expected, etc.

Any help would be appreciated.

 

[lbass]

You have said a lot here without actually responding to my last requests. You continue to describe formulas when you should be showing their actual content. And your pdf is unavailable.

-LB

 

[rrmcguire]

sorry, the url is

http://www.meadowlarkco.com/months2.pdf.

I actually have gotten the majority of the report to work based off of this:

{SHIPMENTS_TTX.PICKUPDATE} in dateserial(year(currentdate), month(currentdate)-2,1) to currentdate

for my months current, previous, and previous 2(ex. May, April, March)

My expectations are coming out correct and my % goal reached is showing correctly.

I know thats a lot, its really hard to explain this report as its quite in depth, so I believe I have it running correctly except my other question is I'll have to import this into my main report as a subreport, when I run the main report will parameter fields which are run have any effect on my subreport, but Im assuming that depends on the parameter field itself and what it dictates.

thanks for all your help the "date serial" formula seems to have helped immensly, sorry for all the confusion

 

[rrmcguire]

what does

{table.date} in dateserial(year(currentdate),month(currentdate)-2,1) to currentdate

accomplish?

 

[lbass]

It returns dates in the period starting the first of the month two months ago through the currentdate--exactly what the formula says. If you used the formula today, the results would include any dates between 3/1/2011 and 5/16/2011.

-LB

 

[rrmcguire]

okay thanks lbass....thanks again for all your help

 

[rrmcguire]

is there any way you could help me decipher another formula:

@month




if {@Pickup Date} >= {@Min Date} and {@Pickup Date}<= {@Max Date} then

"'" &
Right(Left(ToText (Year ({@Pickup Date})),InStr (ToText (Year ({@Pickup Date})),'.')-1),2) &
" " &
{@Month Place}&Left(ToText (Month ({@Pickup Date})),InStr (ToText (Month ({@Pickup Date})),'.')-1) &
MonthName (Month ({@Pickup Date}))

else if {@Pickup Date} >= {@Min Date 2} and {@Pickup Date}<= {@Max Date 2} then

"'" &
Right(Left(ToText (Year ({@Pickup Date})),InStr (ToText (Year ({@Pickup Date})),'.')-1),2) &
" " &
{@Month Place}&Left(ToText (Month ({@Pickup Date})),InStr (ToText (Month ({@Pickup Date})),'.')-1) &
MonthName (Month ({@Pickup Date}))

else ''


@pickupdate= if IsNull({SHIPMENTS_TTX.PICKUPDATE}) = False then {SHIPMENTS_TTX.PICKUPDATE}
else if IsNull({SHIPMENTS_TTX.PICKUPDATE}) = True and IsNull({SHIPMENTS_TTX.SCHEDPU}) = False then {SHIPMENTS_TTX.SCHEDPU}
else {SHIPMENTS_TTX.ENTRYDATE}


@min date = Minimum ({?Pm-Date Range 1}) where Pm-Date Range 1 is a parameter date range

@max date = Maximum ({?Pm-Date Range 1}) where Pm-Date Range 1 is a parameter date range

@Month Place = if Month({@Pickup Date}) < 10 then '0'
else ''


@Min Date2 = nothing

@ Max Date2 = nothing



A colleague of mine created this and Im trying to decipher what it does but he isn't available to reach