Avatar of CHUG1008
CHUG1008
 asked on

How do I programatically change the Crystal reports formula for a Groupfooters "New Page After" property?

I want the user to be able to select a checkbox if they want a page break after each group of information.   The following code works fine if I don't have a formula in the "New Page After" formula.  But as soon as I put "formula = Not OnLastRecord" in at design time setting the property to true or false has no effect.   It always puts in a page break.

        If cboxPageBreak.Checked Then
            objReport.GroupFooterSection2.SectionFormat.EnableNewPageAfter = True
        Else
            objReport.GroupFooterSection2.SectionFormat.EnableNewPageAfter = True
        End If

How can access the formula section of "NewPageAfter" to remove the formula at run time?
.NET ProgrammingVisual Basic.NETCrystal Reports

Avatar of undefined
Last Comment
James0628

8/22/2022 - Mon
aplusexpert

Load report in Report document object and get the formulas for that report and update it.

Like this

        private void SetDatabaseLogon(ref ReportDocument _pReportDocument)
        {
            try
            {
//if (_ReportDocument.Name.Contains("rptSubESICForm6EmployeeMonthESICBranchwise.rpt - 1"))
                    //{
                    //    _ReportDocument.RecordSelectionFormula = "({sp_rpt_GetESICStatement;1.Month} = {?@SalaryMonth})\r\nand\r\n({sp_rpt_GetESICStatement;1.Year} = {?@SalaryYear})\r\nand\r\n({sp_rpt_GetESICStatement;1.ESICBranchID} = {?@ESICBranchID})\r\nAND {tblEmployee.ESICYesNo} = true\r\nAND {tblEmployee.ESICBranchID} <> 1\r\nAND {tblEmployee.JoinDate} <  DateAdd(\"M\",1,DateValue ({?@SalaryYear},{?@SalaryMonth} ,1 )) \r\nAND ({tblEmployee.ResignDate} >= DateValue ({?@SalaryYear},{?@SalaryMonth} ,1 )\r\n    OR {tblEmployee.ResignDate} = DateValue (1900,1 ,1 ))\r\nand(IF UpperCase({?EmployeeCategory} ) = UpperCase(\"employee\") then\r\n{tblEmployee.EmployeeCategoryID} <> 2 \r\nElse IF UpperCase({?EmployeeCategory} ) = UpperCase(\"contractor\") then\r\n{tblEmployee.EmployeeCategoryID} = 2  \r\nelse\r\n{tblEmployee.ESICYesNo} = true\r\n)\r\n";
                    //}
                    //if (_ReportDocument.Name.Contains("rptSubESICForm6EmployeeMonthESICBranchwise.rpt - 2"))
                    //{
                    //    _ReportDocument.RecordSelectionFormula = "({sp_rpt_GetESICStatementGRandTotal;1.Month} = {?@SalaryMonth})\r\nand\r\n({sp_rpt_GetESICStatementGRandTotal;1.Year} = {?@SalaryYear})\r\nAND {tblEmployee.ESICYesNo} = true\r\nAND {tblEmployee.ESICBranchID} <> 1\r\nand(IF UpperCase({?EmployeeCategory} ) = UpperCase(\"employee\") then\r\n{tblEmployee.EmployeeCategoryID} <> 2 \r\nElse IF UpperCase({?EmployeeCategory} ) = UpperCase(\"contractor\") then\r\n{tblEmployee.EmployeeCategoryID} = 2  \r\nelse\r\n{tblEmployee.ESICYesNo} = true\r\n)\r\n";
                    //}
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString(), clsCommon._sMessageboxCaption.ToString(), MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
CHUG1008

ASKER
I have the report document object and I'm able to program the "RecordSelectionFormula" and I'm also able to access the boolean value of "EnableNewPageAfter" as in my example:

objReport.GroupFooterSection2.SectionFormat.EnableNewPageAfter = True

But there is a place in design mode for setting a formula associated with "NewPageAfter" that allows you to control things like suppressing the page break on the last page, etc.   I can't figure out how to access this formula at runtime.   Do you have any example of code for this property?

Mike McCracken

When you use the formula it overrides the check mark in the selection.

When do you want a new page after?

Are you trying to prevvent a blank last page?

mlmcc
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Mike McCracken

I don't believve you can access those formula at runtime.  You could use a formula in the NEW PAGE AFTER formula and then control that formula through code.

mlmcc
CHUG1008

ASKER
I have the formula "formula = Not OnLastRecord" in the NEW PAGE AFTER formula, so how do I control that formula through code?   If I can't access those formulas at runtime how do I control it?  What am I missing?
Mike McCracken

As I stated you can't.

What are you trying to accomplish with the NEW PAGE AFTER formula?

mlmcc
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CHUG1008

ASKER
I want to provide the user the option of inserting page breaks after each group or not.   So I give them a checkbox control to enable or disable page breaks.

Nasir Razzaq

By the way, is it just a typo that you have the "=true" in both parts of the if condition?
CHUG1008

ASKER
Yes.    This was a typo.  Thanks for pointing it out.   However my code is correct.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mike McCracken

Do you know how to pass a parameter to the report?

If so
Add a parameter to teh report (boolean)

In the formula for New Page After
{?NewPageAfterParameter} = True

mlmcc
Mike McCracken

To avoid the new page after the last record
In the formula for New Page After
{?NewPageAfterParameter} = True
AND
Not OnLastRecord

mlmcc
James0628

I may be wrong, but it seems like you missed part of what mlmcc was saying, so, putting on my interpreter's hat:

 I don't run CR reports from code, but I think what mlmcc was saying was that you can't change the "New Page After" formula through code, but what you can do is create a separate formula, use that formula in the "New Page After" formula, and then alter the separate formula through code.

 For example, create a formula named @newpage that just says "formula = True", or maybe "formula = not OnLastRecord".  Whatever.  Then your "New Page After" formula would just be "formula = {@newpage}".  If you alter the @newpage formula using code, that will determine whether or not "New Page After" is true.

 James
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
CHUG1008

ASKER
James,

Thanks for that clarification, unfortunately I can't seem to get anywhere with this approach.   If I create a formula @newpage that says just "formula = not OnLastRecord" and then put "formula = {@newpage}" this would translate to "formula = formula = not onLastRecord".   So if I modify either formula to eliminate one "formula =" Crystal Reports doesn't like the formula.   It allows me to save it anyways, but when I execute my code the CR viewer gets upset before my code even has a chance to modify the formula.   Not sure how to work around that.   Any ideas?   I'm going to try to use parameters as mlmcc has suggested in the previous post.
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
James0628

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.