Grouping and Formulas 2

[spiego]

I have a report that groups based on the following formula:

If year({Command.invdte}) in year(currentdate) - 2 to year(currentdate) -1 Then
    ToText(year(currentdate) - 2,0,"") & "/" & ToText(year(currentdate) -1,0,"")
else
   If year({Command.invdte}) in year(currentdate) - 1 to year(currentdate) Then
       ToText(year(currentdate) - 1,0,"") & "/" & ToText(year(currentdate),0,"")

When I run the report, the group header display as:
2002/2003
2003/2004

For each group, I need to display the total commission for a month. I have placed a formula in the group header that looks like this:

If GroupName ({@Years}) = ToText(year(currentdate) - 2,0,"") & "/" & ToText(year(currentdate) -1,0,"") Then
Sum ({@Month_1_Top}, {@Years})
Else
If GroupName ({@Years}) = ToText(year(currentdate) - 1,0,"") & "/" & ToText(year(currentdate),0,"") then
Sum ({@Month_1_Bottom}, {@Years})

When I run the report I only get numbers for 2002/2003. Why doesn't it sum for 2003/2004 also?

 

[spiego]

So, if the first period (01) of 2002 actually starts in December of 2001, that will throw the report off?

 

[spiego]

Here is what I see is going on in the detail section of the report. I just looked at one broker. In period 11 (Nov), the report shows that the broker made $2164.53 in commission. I know that is incorrect when I compare it against a query of the table. The correct amount should be $1657.38.

When I look a the detail in the report, I notice that it was showing commission starting on 11/1/2002. The broker did have a commission sale on 10/30/2002, which is considered still period 11. When I scrolled down to 2003 for period 11, I noticed that it was calculating commission starting on 10/27/03, which was the first week of period 11. When I add up the detail from 2002 and 2003, I get $2164.53.

Why does it not pick up the commission on 10/30/2002, but it does for Oct. of 2003?

 

[lbass]

We are going around in circles here. You need to understand how your command periods work. Start a new report. Insert a group on command period then put the date field in the detail section. Insert a second group on the datefield and choose "print on change of year". The specific dates in each command period will probably change from year to year since you are using the 4-4-5 accounting method, and I'm assuming the command periods somehow relate to that.

You must understand the command periods before writing these reports. If each record as a command period associated with it, you should probably be creating your manual crosstab columns by referring to these without any reference to the date field, and your yearly formula should also be based on command periods not on specific dates (or the formula should reflect the start date of the first period to the end date of the last period under consideration, instead of using month end dates). This is assuming that command periods are either four or five-week periods, although you haven't explained that so far.

But I don't know how your database is set up, so I cannot be more specific than that.

-LB

 

[spiego]

Sorry if it seems like we are going in circles.

I created the report you suggested and yes, the dates in each period does change. Also, your assumption about the periods from the table being either four or five-week periods is correct.

As for the manual crosstab columns, here is a sample of the code I am using:

{@FistMonth}
ToText(currentdate, "MMM")

{@SecondMonth}
ToText(DateAdd("m", 1, currentdate), "MMM")

Each record has the fields - year, period, invoice date. So what you are saying is to use the period field for the manual crosstab instead of the code I mentioned above?

If needed, I can supply you the SQL that I am using to create the command object.

 

[lbass]

You have to make a decision. Either you do a crosstab based on actual dates and months of the year, or you do a crosstab based on your company's 4-4-5 accounting periods. I'm guessing that your company would prefer it based on accounting periods.

Let me make a wild guess. You said in your last post that there is a year field (let's call it {command.periodyear}). If this is, in fact, an accounting year that is based on the actual start of Period1 and the end of Period12, e.g.,
12/30/01 - 12/28/02 (assuming that week 1 starts on the Sunday of the week containing January 1), then you can create your crosstab in the following way:

Create a formula:
if month(currentdate) <> 12 then
(
if (cdbl({command.periodyear}) = year(currentdate)-1 and
cdbl({command.period}) in month(dateadd("m",1,currentdate)) to 12) or
(cdbl({command.periodyear}) = year(currentdate) and
cdbl({command.period}) in 1 to month(currentdate) then totext(year(currentdate)-1,0,"")+"/"+
totext(year(currentdate),0,"") else
if (cdbl({command.periodyear}) = year(currentdate)-2 and
cdbl({command.period}) in month(dateadd("m",1,currentdate)) to 12) or
(cdbl({command.periodyear}) = year(currentdate)-1 and
cdbl({command.period}) in 1 to month(currentdate) then totext(year(currentdate)-2,0,"")+"/"+
totext(year(currentdate)-1,0,"")
) else
if month(currentdate) = 12 then
(
if (cdbl({command.periodyear}) = year(currentdate) and
cdbl({command.period}) in 1 to month(currentdate) then totext(year(currentdate),0,"") else
if (cdbl({command.periodyear}) = year(currentdate)-1 and
cdbl({command.period}) in 1 to month(currentdate) then totext(year(currentdate)-1,0,"")
)

Insert a group on this formula and then create a series of formulas like the following:

//{@currmo}:
if cdbl({command.period}) = month(currentdate) then {command.commission}

//{@currmo-1}:
if cdbl({command.period}) = month(dateadd("m",-1, currentdate)) then {command.commission}

//{@currmo-2}:
if cdbl({command.period}) = month(dateadd("m",-2, currentdate)) then {command.commission}

For your column labels, use formulas like:

//{@currmolabel}:
"Period " + totext(month(currentdate),0,"")

Then insert summaries on each detail formula and suppress the details section. This approach uses the currentdate only to establish what period will be the first column.

-LB

 

[spiego]

I created a new report and used your formula for the grouping (BTW - It was missing a ')' before the then statement). It works, but for some reason I also get a group that is blank. Any idea on why?

 

[lbass]

Yes, see my previous explanation (above) when this happened to you before .

-LB

 

[spiego]

LB,

Now that I'm filled up on turkey, I'm ready to get back into this.

The year in the table does tie into the accounting year (e.g., 2002 starts on 12/30/01 and ends on 12/28/02). So, your assumptions are correct. I'm still not sure why the blank group though after following your code. Please forgive my ignorance.

 

[lbass]

You need to make sure that the time period in your record selection formula matches the time period covered by the manual crosstab formulas.

-LB

 

[spiego]

When you say record selection formula, are you referring to the SQL that I use in the command object?

 

[lbass]

Well, I wasn't, because I didn't know you were using a SQL command. I'm not familiar with how that works, as I don't have CR 9.0. What I am saying is that you seem to be pulling records into your report that fall outside of the group formula. You need to make your record selection match the outer bounds of the criteria for your group formula. Then you won't get an unnamed group, although I guess this could also occur if your data contained nulls for a field in your group formula.

-LB

 

[spiego]

Here is the SQL command statement that I use to fetch the records for the report:

SELECT Sal_ep_sbt.year, Sal_ep_sbt.period, Sal_ep_sbt.invno,
  sal_ep_sbt.invdte, Sal_ep_sbt.lineno, Sal_ep_sbt.item, Sal_ep_sbt.descrip,
  Sal_ep_sbt.qtyshp, Sal_ep_sbt.price, Sal_ep_sbt.cost, 
  Sal_ep_sbt.custno, Sal_ep_sbt.company, Sal_ep_sbt.brokerpaid, Broker.c_brokerage_company,
  Sal_ep_sbt.commission, Sal_ep_sbt.commrate
FROM  sales_ep_sbt!sal_ep_sbt INNER JOIN          sales_ep_sbt!broker 
   ON  Sal_ep_sbt.brokerpaid = Broker.c_key_id_broker_number
WHERE Sal_ep_sbt.year > AllTrim(STR(YEAR(DATE( )) - 3))
     AND Sal_ep_sbt.brokerpaid <> " "
 ORDER BY Sal_ep_sbt.year, Sal_ep_sbt.period

As for null values, the only fields that could have null values are year and period and they seem to be fine.

 

[spiego]

LB,

Something else that I noticed is that the correct commission amount for period 11 of 2002 shows up in the grouping that is blank. The amount that shows up for 2002/2003 is for period 11 of 2003 and should be displayed in the group 2003/2004. The last amount is for 2004. It shows this way for all of the brokers.

Period 11
Broker 01
"Blank Group" 1,657
2002/2003 2,335
2003/2004 2,636

 

[lbass]

I'm sorry, but I don't think I can be of any more assistance, as I don't really have any more ideas to offer on this. Perhaps some fresh eyes would help, and you might fare better (get more responses) if you start a new thread (without directing your responses just to me).

-LB

 

[spiego]

Thanks LB for your assistance. I will try a new thread and see what happens. I also created a thread on the Business Objects site as well.