Formulas for page headers 1

[youngcougar]

I have page headers that are made with date formulas. There are 3 groups, and the formulas are 'accumulated' in the details section. Problem is that the page headers only appear correctly on the second page of a 2 page report. The only header that works on the first page is the one for the current fiscal period. Any ideas? If you need more info.....lemme know

 

[synapsevampire]

Page headers aren't made with any sort of formulas, do you mean that you're displaying dates? What does that have to do with aggregates in the details?

Typically successful posts include:

Crystal version
Database/connectivity used
Example data (show actual data, fields)
Expecte output, and where in the report

-k

 

[youngcougar]

I've created formulas in the page header section for Profit and Loss statements. I have the report grouped to 3 levels. In each formula, I 'print out' the fiscal period, and fiscal year. When you run the report, I want to see the prior 2 fiscal periods to the current one. The only page header that appears (formula) is the one with the current period. All of the formulas work on page 2, as the formulas in the page headers are working. I grab the values with a different formula (shared vars) in the detail section of the report. This has something to do with evaluation time, I think??

 

[lbass]

You should share the contents of all formulas and state in what report sections they are placed.

-LB

 

[youngcougar]

Page header formula(called ShowCp2) =

shared stringvar CP2name;
global numbervar cp2year;
CP2name & " F" & right(totext(cp2year,0,"",""),2)

Detail Section Formula=

shared stringvar CPname;
shared stringvar CP1name;
shared stringvar CP2name;
global numbervar cp1period;
global numbervar cp2period;

if {Periods.Period_Num} = {?Fiscal Period} then
CPname := {Periods.Period_Name};

if {Periods.Period_Num} = cp1period then
CP1name := {Periods.Period_Name};

if {Periods.Period_Num} = cp2period then
CP2name := {Periods.Period_Name};

3 levels of grouping

Any help would be appreciated! - The global values show up

 

[lbass]

This approach won't work. Please explain your group structure--what are each of your group fields, and also show samples of your period_num field and Period_name field, and identify the datatypes for each.

Also explain what the page headers are supposed to display.

-LB

 

[youngcougar]

Keep in mind that they work fine on the second page. There are 3 levels of grouping pertaining to P & L statements. First group is broken into 2 categories (straight from table structure) the categories are: Gross Margin and Total Expenses. Group 2 (straight from table structure) are subcategories of Group 1 For example: Total Sales, Total COS, ect. Group 3 are the accounts that belong to each of the Group 2 Categories. Values for each account is grabbed at this level (and are correct) and summed in each of the other grouping levels. The details section of this report contains the following formula:

shared stringvar CPname;
shared stringvar CP1name;
shared stringvar CP2name;
global numbervar cp1period;
global numbervar cp2period;

if {Periods.Period_Num} = {?Fiscal Period} then
CPname := {Periods.Period_Name};

if {Periods.Period_Num} = cp1period then
CP1name := {Periods.Period_Name};

if {Periods.Period_Num} = cp2period then
CP2name := {Periods.Period_Name};

The CPname, CP1name, and CP2name are the names for the relevant periods - like 'January'. The {Periods.Period_Name} field is in this format straight from the tables.

the periods.period_num data is a regular integer number between 1 and 12. The parameter {?Fiscal Period} is also a integer number between 1 and 12.

In the page header of the report, I have several headings that consist of the following formula (substituting CP2name, cp2year with relevant vars from the details):

shared stringvar CP2name;
global numbervar cp2year;
CP2name & " F" & right(totext(cp2year,0,"",""),2)

The only page header that works on the first page, is the one that consists of the period that the user selected. For example if I selected period 2 (which for us is 1 month ago) Then I get the correct header (on the first page) which reads:

'October F'06'

On the second page, I get all headers? So if the user selects fiscal year = 6, fiscal period = 2, then 2 headers should read backward from this parameter,

'August F'05' 'September F'05' then the current one (which works on page 1) 'October F'06'. If I make the font small enough for the entire report to fit on one page, then the only heading that works is the parameter heading. When the headings don't work, I get:

'F'05', 'F'05', 'October F'06'

Does this make things more clear?

 

[lbass]

So your fiscal year 2006 runs from October 05 through September 06?

I'm still unclear what you want on each page header--just one month/year? Do you have a group in the report based on year and period with a new page after on the group footer? Why couldn't you just use a group header with the groupnames?

-LB

 

[youngcougar]

yes, that's our fiscal year system. On the report there are 3 columns of P & L's so we can track if our Gross Margin is going up and expenses are going down for each either profit center or cost center. So the first column is 2 periods back from the period we are running the report, the second column is one period back from the user input parameter and the third column is the user input parameter. If I want to view the report for F' 06, period 2, which is 'November F' 06' then the page header formulas for the prior 2 periods(columns) must display: 'September F'05' and 'October F'06' If I group by dates, then p & L descriptions, ect ect, I can't run the report for all 'profit' centers with a comparison to the prior fiscal periods. What confuses me is that the headers work on page 2 ???

 

[lbass]

Oh, now I see about the headers, but what are you using for the column field under each header? How do you know that the column relates to that fiscal period? You might be able to use the column field in a formula to create the corresponding header, depending upon how the columns are created.

Your formula will only will only fully work on following pages. Also nowhere did you actually set the values of cp1period and cp2period. The parameter value, however, is available anywhere in the report.

-LB

 

[youngcougar]

The column values are also formulas, and they are correct for the period, so those ones work. I set the cp1period, cp2period vars in the report header, I know the vars are grabbing the right period because the formulas in the details, and grouping all bring the correct values for that period. I've suppressed the details, and to debug, I've unsuppressed. I've created debug formulas and place them in the details. I can see the correct period names in the details, and period numbers? One thing, in the 'Record Selection' I have a 'formula' that selects only values where the

fiscal year = ?fiscal year and periodnum <= ?period num OR fiscal year in ?fiscalyear to ?fiscalyear -1 and period num >= ?period num

when I suppress the period portion of the formula, and grab just the last 2 fiscal years, all of the headers work except for CP2, which is 2 periods backwards from now??

 

[lbass]

It seems to me that your record selection formula should be:

(
if {?Period} >= 3 then
(
{@fiscal year} = {?Fiscal Year} and
{@period} in {?Period}-2 to {?Period}
) else
if {?Period} = 2 then
(
(
{@fiscal year} = {?Fiscal Year}-1 and
{@period} = 12
) or
(
{@fiscal year} = {?Fiscal Year} and
{@period} <= 2
)
) else
if {?Period} = 1 then
(
(
{@fiscal year} = {?Fiscal Year}-1 and
{@period} >= 11
) or
(
{@fiscal year} = {?Fiscal Year} and
{@period} = 1
)
)
)

You can create your headers by using:

//{@two periods ago}:
if {?Period} in 3 to 5 then
monthname(9+{?Period}-2) + " F' " + totext({?Fiscal Year},"00") else
if {?Period} in 6 to 12 then
monthname({?Period}-5) + " F' " + totext({?Fiscal Year},"00") else
monthname(9+int({?Period}-2)) + " F'"+
totext({?Fiscal Year}-1,"00")

//{@One Period Ago}:
if {?Period} in 2 to 4 then
monthname(9+{?Period}-1) + " F' " + totext({?Fiscal Year},"00") else
if {?Period} in 5 to 12 then
monthname({?Period}-4) + " F' " + totext({?Fiscal Year},"00") else
monthname(9+int({?Period}-1)) + " F'"+
totext({?Fiscal Year}-1,"00")

//{@selected period}:
if {?Period} <= 3 then
monthname(9+{?Period}) + " F' " + totext({?Fiscal Year},"00") else
monthname({?Period}-3) + " F' " + totext({?Fiscal Year},"00")

For future reference, you should have included all relevant formulas in your original post--including the one that established the values for in the variables.

-LB

 

[youngcougar]

Thank you it works great!