Link to home
Create AccountLog in
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?
Avatar of aplusexpert
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);
            }
        }
Avatar of 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?

Avatar of 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
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
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?
As I stated you can't.

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

mlmcc
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.

By the way, is it just a typo that you have the "=true" in both parts of the if condition?
Yes.    This was a typo.  Thanks for pointing it out.   However my code is correct.
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
To avoid the new page after the last record
In the formula for New Page After
{?NewPageAfterParameter} = True
AND
Not OnLastRecord

mlmcc
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
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
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account