Dynamic Report Creation At Runtime

[Former Member]

I am trying to create a dynamic crystal report at run time from Visual Studio 2013 using C#. My back end database is SQL Server 2005. The source for my crystal report is a SQL Server stored procedure which takes two parameters, a year (smallint) and a quarter (tinyint). The stored procedure uses dynamic SQL and creates a dynamic temp table. It returns the result set from a select on the temp table. The number of columns in the result set will be determined at run time. Right now it returns 40 columns. It can return additional columns in multiples of 6. All of these columns will need to be displayed on the crystal report at run time. The C# application will run on 64 bit Windows 7 machines and the SQL Server database is on a 32 bit server.

I have been successful in designing a crystal report within the Visual Studio IDE using this stored procedure and running it and displaying it in the crystal reports viewer from within the C# application. So when the appropriate application's menu item is clicked, the form with the crystal viewer on it opens and displays the report. So I know that I can connect to the database and display the report. However as stated in the preceding paragraph, I need to create the crystal report at run time as the number of columns on the report will be variable.

I have looked at the CrystalDecisions SDK and API and tried without success to do this. I am getting errors when trying to connect to the DB through the API and am not able to retrieve the data. I have played with the code for about a week with no success. I have tried the code in the discussion forums without success. The documentation for the SDK API's does not provide the level of detail that I need. The CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo class has an attributes property that needs to be set. I don't know what the proper settings should be.

I prefer to use the OLE DB native client for SQL Server.

Any help would be greatly appreciated.

Martin

[Former Member · Accepted solution]

I have made some changes to my code for database security. When I create a user in the SQL Server database to use my C# application, I only give them connect privilege to the database. In my log on form, I set a SQL Server database application role which has permission to execute two stored procedures. One procedure verifies the user's user name and password against an application table which I created in the database. Their password is stored encrypted in this table and so this stored procedure encrypts the password which is entered on the log on form and compares it to the encrypted password in the database table. If they match, the application proceeds. By the way, I am using integrated security to establish the connection to the database which is why I check the users table to validate the user. Once the user passes validation, the application retrieves the name of the database application role which has been assigned to each user. This role determines what privileges the user has in the database as well as what application menu items are enabled or disabled. The names of the database application roles as well as their encrypted passwords are stored in an application table as well. Each user is assigned a database application role. So for example one user may only run reports, another only do data entry, another only view data, or another may have administrative abilities over the application and can do all of these things. Since the database application role's password is stored encrypted in the user role table, the application decrypts the password and then sets this role after unsetting the initial role used in logging on.

Since Crystal Reports establishes a new connection when it runs a report, I created a special "reporting" user in the SQL Server database and gave it connect privilege and the privilege to execute the stored procedure which returns the result set for the report. As I add new reports to the application, I shall either grant execute on the stored procedure that returns the result set for that report to the "reporting" user or I shall grant select on the appropriate tables to the "reporting" user. I updated my code to change the user name and password which the Crystal Report uses to connect to the database. I also use do not use Windows authentication (integrated security) for this but SQL Server authentication.

I have attached my final code for the dynamic report to this reply as well as the code for the global variables class where I have the "reporting" user name and password. I am also pasting it into this reply below. I also attached an image of the report section form.

Here is the code for the global variables class:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

namespace Retail_Incentive_Plan

{

public class GlobalVariables

{

public static string connection_string;

public static string server;

public static SqlConnection scn = new SqlConnection();

public static bool logon_verified;

public static string crystal_report_userid;

public static string crystal_report_password;

}

}

Here is the code for the dynamic Crystal Report:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using CrystalDecisions.ReportAppServer.ClientDoc;

using CrystalDecisions.ReportAppServer.Controllers;

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.ReportAppServer.ReportDefModel;

using System.Data.SqlClient;

namespace Retail_Incentive_Plan

{

public partial class BranchRankingReport : Form

{

CrystalDecisions.ReportAppServer.ReportDefModel.SectionFormat crSectionFormat;

CrystalDecisions.ReportAppServer.ReportDefModel.Section boSection;

CrystalDecisions.ReportAppServer.ReportDefModel.Section BR_ReportHeader;

CrystalDecisions.ReportAppServer.ReportDefModel.Sections boSection1;

CrystalDecisions.ReportAppServer.ReportDefModel.Section boSection2;

CrystalDecisions.ReportAppServer.ReportDefModel.Section BR_ReportFooter;

CrystalDecisions.ReportAppServer.DataDefModel.Fields boFields;

CrystalDecisions.ReportAppServer.DataDefModel.Field boField;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField boFormulaField;

CrystalDecisions.ReportAppServer.Controllers.PrintOutputController boPrintOutputController;

CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

CrystalDecisions.ReportAppServer.ReportDefModel.PageMargins PageMargin;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject boTextObject;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject totalbssb;

CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs boParagraphs;

CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph boParagraph;

CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements boParagraphElements;

CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement boParagraphTextElement;

CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement boParagraphFieldElement;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor boFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font boFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor DetailLineFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font DetailLineFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor ColumnHeadingFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font ColumnHeadingFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor TotalLineFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font TotalLineFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject branch_name;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor bnFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font bnFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject reportperiod;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject branch_num;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject total_points1;

// Arrays of fieldobjects

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] category_name;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] goal;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] actual;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] needed;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] pct_of_goal;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] points;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_goal;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_actual;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_needed;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_percent;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumgoal;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumactual;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumneeded;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumpercent;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject rank_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject branchname_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject branchnumber_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] goal_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] actual_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] needed_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] pctgoal_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] points_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject total_points_heading;

int? iyear;

int? iqtr;

int icategories;

char output;

public BranchRankingReport(int inyear, int inquarter, char inoutput)

{

int neededstart;

int neededadd;

int categorynamestart;

int categorynameadd;

int goalstart;

int goaladd;

int actualstart;

int actualadd;

int percentstart;

int percentadd;

int pointsstart;

int pointsadd;

int totalpointsstart;

int goalheadingstart;

int goalheadingadd;

int actualheadingstart;

int actualheadingadd;

int neededheadingstart;

int neededheadingadd;

int percentgoalheadingstart;

int percentgoalheadingadd;

int pointsheadingstart;

int pointsheadingadd;

int totalpointsheadingstart;

int sumgoalstart;

int sumgoaladd;

int sumactualstart;

int sumactualadd;

int sumneededstart;

int sumneededadd;

int sumpercentstart;

int sumpercentadd;

int rptheight;

int rptwidth;

string sql_string;

string headingfontname;

string subheadingfontname;

string columnheadingfontname;

string detaillinefontname;

string totallinefontname;

int headingfontsize;

int subheadingfontsize;

int columnheadingfontsize;

int detaillinefontsize;

int totallinefontsize;

int boFieldIndex;

bool headingfontbold;

bool subheadingfontbold;

bool columnheadingfontbold;

bool detaillinefontbold;

bool totallinefontbold;

Color headingfontcolor;

Color subheadingfontcolor;

Color categoryheadingfontcolor;

Color columnheadingfontcolor;

Color detaillinefontcolor;

Color totallinefontcolor;

iyear = inyear;

iqtr = inquarter;

output = inoutput;

headingfontname = "Times New Roman";

headingfontsize = 18;

headingfontbold = true;

headingfontcolor = Color.Black;

subheadingfontname = "Times New Roman";

subheadingfontsize = 18;

subheadingfontbold = true;

subheadingfontcolor = Color.Black;

columnheadingfontname = "Times New Roman";

columnheadingfontsize = 12;

columnheadingfontbold = true;

columnheadingfontcolor = Color.Black;

categoryheadingfontcolor = Color.Black;

detaillinefontname = "Times New Roman";

detaillinefontsize = 12;

detaillinefontbold = false;

detaillinefontcolor = Color.Black;

totallinefontname = "Times New Roman";

totallinefontsize = 12;

totallinefontbold = true;

totallinefontcolor = Color.Black;

goalstart = 3600;

actualstart = 4500;

neededstart = 5350;

percentstart = 6350;

pointsstart = 7450;

categorynamestart = 3600;

goalheadingstart = 3600;

actualheadingstart = 4430;

neededheadingstart = 5300;

percentgoalheadingstart = 6300;

pointsheadingstart = 7500;

totalpointsheadingstart = 0;

totalpointsstart = 0;

sumgoalstart = 3500;

sumactualstart = 4400;

sumneededstart = 5350;

sumpercentstart = 6350;

rptheight = 10700;

// Get the number of categories that appear on the report.

// Each category has goal, actual, needed, % of goal, and points.

sql_string = "select count(distinct category_id) from branch_category_actual_points " +

"where year = " + iyear.ToString() + " and quarter = " + iqtr.ToString();

SqlCommand scmd = new SqlCommand(sql_string, GlobalVariables.scn);

SqlDataReader sdr;

try

{

sdr = scmd.ExecuteReader();

}

catch (SqlException sex)

{

MessageBox.Show(System.Convert.ToString(sex.Number) + " - " + sex.Message, "Database Error", MessageBoxButtons.OK,

MessageBoxIcon.Error);

return;

}

sdr.Read();

if (sdr.HasRows)

{

icategories = sdr.GetInt32(0);

}

else

{

MessageBox.Show("Unable to get number of categories.", "Get Number Of Categories", MessageBoxButtons.OK,

MessageBoxIcon.Error);

sdr.Close();

this.Close();

return;

}

sdr.Close();

/*

The formula for the report width is (the width of the headings before the first category's headings + (the number of categories * the width of all the headings

for a category) + the width of the total points heading - the adjustment for the width of the spaces between the last category's points and the total points)

3600 is the starting position of the first incentive category heading

icategories is the number of incentive categories for the year and quarter

4700 is the width of all the headings for a category

1250 is the width of the heading for total points

100 is subtracted from the final width since the width for the number of spaces after the last category of points is 50 rather than 150 as with the

other categories

*/

rptwidth = (3600 + (icategories * 4700) + 1250 - 100);

category_name = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

goal = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

actual = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

needed = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

pct_of_goal = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

points = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_goal = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_actual = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_needed = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_percent = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sumgoal = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

sumactual = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

sumneeded = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

sumpercent = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

goal_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

actual_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

needed_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

pctgoal_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

points_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

DetailLineFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

DetailLineFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

DetailLineFont.Bold = detaillinefontbold;

DetailLineFont.Name = detaillinefontname;

DetailLineFont.Size = detaillinefontsize;

DetailLineFontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

ColumnHeadingFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

ColumnHeadingFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

ColumnHeadingFont.Bold = columnheadingfontbold;

ColumnHeadingFont.Name = columnheadingfontname;

ColumnHeadingFont.Size = columnheadingfontsize;

ColumnHeadingFontColor.Color = uint.Parse(ColorTranslator.ToOle(columnheadingfontcolor).ToString());

TotalLineFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

TotalLineFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

TotalLineFont.Bold = totallinefontbold;

TotalLineFont.Name = totallinefontname;

TotalLineFont.Size = totallinefontsize;

TotalLineFontColor.Color = uint.Parse(ColorTranslator.ToOle(totallinefontcolor).ToString());

InitializeComponent();

crystalReportViewer1.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None;

crystalReportViewer1.ShowRefreshButton = false;

if (output == 'F')

crystalReportViewer1.ShowPrintButton = false;

else

crystalReportViewer1.ShowExportButton = false;

crystalReportViewer1.AutoSize = true;

PageMargin = new CrystalDecisions.ReportAppServer.ReportDefModel.PageMargins();

boPrintOutputController = new CrystalDecisions.ReportAppServer.Controllers.PrintOutputController();

//**EDIT** Change the path and report name to the report you want to change.

try

{

boReportDocument.Load("c:\\Retail_Incentive_Plan\\Reports\\DummySPWithParameters.rpt");

}

catch

{

MessageBox.Show("Branch Ranking Report Not Found", "Error Loading Report", MessageBoxButtons.OK,

MessageBoxIcon.Error);

this.Close();

return;

}

ISCDReportClientDocument boReportClientDocument;

boReportClientDocument = boReportDocument.ReportClientDocument;

boReportClientDocument.DatabaseController.CanExecuteSQL();

boReportClientDocument.PrintOutputController.ModifyPaperOrientation(CrPaperOrientationEnum.crPaperOrientationLandscape);

if (output == 'F')

{

boReportDocument.PrintOptions.NoPrinter = true;

boReportDocument.PrintOptions.DissociatePageSizeAndPrinterPaperSize = true;

boReportClientDocument.PrintOutputController.ModifyUserPaperSize(rptheight, rptwidth);

boReportDocument.ReportClientDocument.PrintOutputController.ModifyPageMargins(0, 0, 0, 0);

}

else

{

boReportClientDocument.PrintOutputController.ModifyUserPaperSize(rptheight, rptwidth);

boReportDocument.ReportClientDocument.PrintOutputController.ModifyPageMargins(0, 0, 0, 0);

boReportDocument.PrintOptions.NoPrinter = false;

boReportDocument.PrintOptions.PageMargins.topMargin.Equals(0);

boReportDocument.PrintOptions.PaperSize = PaperSize.PaperLegal;

}

boReportDocument.SetParameterValue("@year", iyear);

boReportDocument.SetParameterValue("@quarter", iqtr);

CrystalDecisions.Shared.TableLogOnInfo myLogOn;

myLogOn = new TableLogOnInfo();

foreach (CrystalDecisions.CrystalReports.Engine.Table myTable in boReportDocument.Database.Tables)

{

myLogOn = myTable.LogOnInfo;

myLogOn.ConnectionInfo.UserID = GlobalVariables.crystal_report_userid;

myLogOn.ConnectionInfo.Password = GlobalVariables.crystal_report_password;

myLogOn.ConnectionInfo.ServerName = GlobalVariables.server;

myLogOn.ConnectionInfo.IntegratedSecurity = false;

myLogOn.ConnectionInfo.DatabaseName = "Retail_Incentive_Plan";

myTable.ApplyLogOnInfo(myLogOn);

}

//Verify the database after adding substituting the new table.

//To ensure that the table updates properly when adding Command tables or Stored Procedures.

try

{

boReportDocument.VerifyDatabase();

}

catch (CrystalDecisions.CrystalReports.Engine.LogOnException)

{

MessageBox.Show("LogOnException Encountered", "Debug", MessageBoxButtons.OK, MessageBoxIcon.Error);

ParentForm.Close();

Application.Exit();

return;

}

BR_ReportHeader = boReportClientDocument.ReportDefController.ReportDefinition.ReportHeaderArea.Sections[0];

crSectionFormat = BR_ReportHeader.Format;

crSectionFormat.EnableSuppress = true;

// ****************************** Added For Text Object Heading **********************************************************************************************

//First determine which section to add the text field to - in this case the page header section

boSection = boReportClientDocument.ReportDefController.ReportDefinition.PageHeaderArea.Sections[0];

//Create the text object

boTextObject = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Retail Incentive Plan";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

//Explicitly set the default font

boFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

boFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

boFont.Bold = headingfontbold;

boFont.Name = headingfontname;

boFont.Size = headingfontsize;

boFontColor.Color = uint.Parse(ColorTranslator.ToOle(headingfontcolor).ToString());

boFontColor.Font = boFont;

boParagraphTextElement.FontColor = boFontColor;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

boTextObject.Paragraphs = boParagraphs;

//Now add it to the section

boTextObject.Width = 3911;

boTextObject.Left = ((rptwidth / 2) - (boTextObject.Width / 2));

boTextObject.Top = 1;

boTextObject.Height = 552;

boReportClientDocument.ReportDefController.ReportObjectController.Add(boTextObject, boSection, -1);

// Get Subheading

//First create the formula field

boFormulaField = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

boFormulaField.Text = "IIF (ToText ({?@quarter}, 0) = '1', '1st', IIF (ToText ({?@quarter}, 0) = '2', '2nd' , IIF (ToText ({?@quarter}, 0) = '3', '3rd' , IIF (ToText ({?@quarter}, 0) = '4', '4th', 'Invalid Quarter' ) ))) + ' Quarter ' + ToText ({?@year}, 0, '')";

boFormulaField.Name = "ReportPeriod";

boFormulaField.Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

boFormulaField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(boFormulaField);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Page Header section

boSection = boReportClientDocument.ReportDefController.ReportDefinition.PageHeaderArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@ReportPeriod}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

reportperiod = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set the datasource of this field object to the formula of the above Database Field Object

reportperiod.DataSourceName = boFormulaField.FormulaForm;

reportperiod.FieldValueType = boFormulaField.Type;

//Now set the co-ordinates of where the field will go

reportperiod.Width = 3911;

reportperiod.Left = ((rptwidth / 2) - (reportperiod.Width / 2));

reportperiod.Top = 500;

reportperiod.Height = 552;

boFont.Name = subheadingfontname;

boFont.Size = subheadingfontsize;

boFont.Bold = subheadingfontbold;

boFontColor.Color = uint.Parse(ColorTranslator.ToOle(subheadingfontcolor).ToString());

reportperiod.FontColor = boFontColor;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(reportperiod, boSection, -1);

// ****************************** End Added For Text Object Heading **********************************************************************************************

//First determine which section to add the field to - in this case the details section

boSection2 = boReportClientDocument.ReportDefController.ReportDefinition.DetailArea.Sections[0];

//Create the database field object

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject rank_num;

rank_num = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

rank_num.DataSourceName = "{Command.rank}";

rank_num.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

rank_num.FontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

rank_num.FontColor.Font.Name = detaillinefontname;

rank_num.FontColor.Font.Size = detaillinefontsize;

rank_num.FontColor.Font.Bold = detaillinefontbold;

rank_num.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

rank_num.Left = 0;

rank_num.Top = 1;

rank_num.Width = 350;

rank_num.Height = 226;

rank_num.FontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

try

{

boReportClientDocument.ReportDefController.ReportObjectController.Add(rank_num, boSection2, -1);

}

catch (Exception ex)

{

System.Windows.Forms.MessageBox.Show(ex.Message, "Debug", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Information);

}

// ****************************** Added For Database Field Object Detail Band **********************************************************************************************

//Create the database field object

branch_name = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

branch_name.DataSourceName = "{Command.branch_name}";

branch_name.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

branch_name.Width = 3 * 1440;

branch_name.FontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

branch_name.FontColor.Font.Name = detaillinefontname;

branch_name.FontColor.Font.Size = detaillinefontsize;

branch_name.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

//explicitly set the default font

bnFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

bnFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

bnFont.Bold = detaillinefontbold;

branch_name.FontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

branch_name.FontColor.Font.Bold = detaillinefontbold;

bnFontColor.Font = bnFont;

//Now add it to the section

branch_name.Left = 450; // 1440 twips per inch

branch_name.Top = 1;

branch_name.Width = 2500;

branch_name.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branch_name, boSection2, -1);

// ****************************** Added For Database Field (Branch Number) Object Detail Band **********************************************************************************************

//First determine which section to add the text field to - in this case the details section

boSection2 = boReportClientDocument.ReportDefController.ReportDefinition.DetailArea.Sections[0];

//Create the database field object

branch_num = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

branch_num.DataSourceName = "{Command.branch_number}";

branch_num.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt8uField;

branch_num.FontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

branch_num.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

branch_num.FontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

branch_num.FontColor.Font.Bold = detaillinefontbold;

//Now add it to the section

branch_num.Left = 3000;

branch_num.Top = 1;

branch_num.Width = 370;

branch_num.Height = 226;

branch_num.FontColor.Font.Name = detaillinefontname;

branch_num.FontColor.Font.Size = detaillinefontsize;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branch_num, boSection2, -1);

// ****************************** Added For Database Field (Goal) Object Detail Band **********************************************************************************************

//First determine which section to add the text field to - in this case the details section

boSection2 = boReportClientDocument.ReportDefController.ReportDefinition.DetailArea.Sections[0];

goaladd = 0;

for (int j = 0; j < icategories; j++)

{

goal[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

goal[j].DataSourceName = "{Command.goal" + (j + 1).ToString() + "}";

goal[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

goal[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Now add it to the section

goal[j].FontColor = DetailLineFontColor;

goal[j].FontColor.Font = DetailLineFont;

goal[j].Left = goalstart + goaladd;

goal[j].Top = 1;

goal[j].Width = 500;

goal[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(goal[j], boSection2, -1);

goaladd = goaladd + 4700;

}

// ****************************** Added For Database Field (Actual) Object Detail Band **********************************************************************************************

actualadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

actual[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

actual[j].DataSourceName = "{Command.actual" + (j+ 1).ToString() + "}";

actual[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

actual[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

actual[j].FontColor = DetailLineFontColor;

actual[j].FontColor.Font = DetailLineFont;

actual[j].Left = actualstart + actualadd;

actual[j].Top = 1;

actual[j].Width = 600;

actual[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(actual[j], boSection2, -1);

actualadd = actualadd + 4700;

}

// ****************************** Added For Database Field (Needed) Object Detail Band **********************************************************************************************

neededadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

needed[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

needed[j].DataSourceName = "{Command.needed" + (j + 1).ToString() + "}";

needed[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

needed[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

needed[j].FontColor = DetailLineFontColor;

needed[j].FontColor.Font = DetailLineFont;

needed[j].Left = neededstart + neededadd;

needed[j].Top = 1;

needed[j].Width = 700;

needed[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(needed[j], boSection2, -1);

neededadd = neededadd + 4700;

}

// ****************************** Added For Database Field (Pct_Of_Goal) Object Detail Band **********************************************************************************************

percentadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

pct_of_goal[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

pct_of_goal[j].DataSourceName = "{Command.pct_of_goal" + (j + 1).ToString() + "}";

pct_of_goal[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeNumberField;

pct_of_goal[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

pct_of_goal[j].FontColor = DetailLineFontColor;

pct_of_goal[j].FontColor.Font = DetailLineFont;

pct_of_goal[j].Left = percentstart + percentadd;

pct_of_goal[j].Top = 1;

pct_of_goal[j].Width = 950;

pct_of_goal[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(pct_of_goal[j], boSection2, -1);

percentadd = percentadd + 4700;

}

// ****************************** Added For Database Field (Points) Object Detail Band **********************************************************************************************

pointsadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

points[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

points[j].DataSourceName = "{Command.points" + (j + 1).ToString() + "}";

points[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt32sField;

points[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

points[j].FontColor = DetailLineFontColor;

points[j].FontColor.Font = DetailLineFont;

points[j].Left = pointsstart + pointsadd;

points[j].Top = 1;

points[j].Width = 700;

points[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(points[j], boSection2, -1);

pointsadd = pointsadd + 4700;

totalpointsstart = points[j].Left + points[j].Width + 100;

}

// ****************************** Added For Database Field (Total_Points1) Object Detail Band **********************************************************************************************

//Create the database field object

total_points1 = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

total_points1.DataSourceName = "{Command.total_points1}";

total_points1.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt32sField;

total_points1.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

total_points1.FontColor = DetailLineFontColor;

total_points1.FontColor.Font = DetailLineFont;

total_points1.Left = totalpointsstart;

total_points1.Top = 1;

total_points1.Width = 750;

total_points1.Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(total_points1, boSection2, -1);

// ****************************** Added For Database Field (category_name) Object Detail Band **********************************************************************************************

categorynameadd = 0;

for (int j = 0; j < icategories; j++)

{

//First determine which section to add the text field to - in this case the details section

boSection1 = boReportClientDocument.ReportDefController.ReportDefinition.PageHeaderArea.Sections;

//Create the database field object

category_name[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

category_name[j].DataSourceName = "{Command.category_name" + (j + 1).ToString() + "}";

category_name[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

category_name[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

//explicitly set the default font

category_name[j].FontColor = ColumnHeadingFontColor;

category_name[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

category_name[j].Left = categorynamestart + categorynameadd;

category_name[j].Top = 1500;

category_name[j].Width = 4200;

category_name[j].Height = 300;

boReportClientDocument.ReportDefController.ReportObjectController.Add(category_name[j], boSection, -1);

categorynameadd = categorynameadd + 4700;

}

// ****************************** Added For Database Field (rank_heading) Object Detail Band **********************************************************************************************

//Create the text object

rank_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Rank";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

rank_heading.Paragraphs = boParagraphs;

rank_heading.FontColor = ColumnHeadingFontColor;

rank_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

rank_heading.Left = 0;

rank_heading.Top = 1800;

rank_heading.Width = 580;

rank_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(rank_heading, boSection, -1);

// ****************************** Added For Database Field (branchname_heading) Object Detail Band **********************************************************************************************

//Create the text object

branchname_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Branch Name";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

branchname_heading.Paragraphs = boParagraphs;

branchname_heading.FontColor = ColumnHeadingFontColor;

branchname_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

branchname_heading.Left = 870;

branchname_heading.Top = 1800;

branchname_heading.Width = 1500;

branchname_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branchname_heading, boSection, -1);

// ****************************** Added For Database Field (branchnumber_heading) Object Detail Band **********************************************************************************************

//Create the text object

branchnumber_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Num";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

branchnumber_heading.Paragraphs = boParagraphs;

branchnumber_heading.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

branchnumber_heading.FontColor = ColumnHeadingFontColor;

branchnumber_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

branchnumber_heading.Left = 2900;

branchnumber_heading.Top = 1800;

branchnumber_heading.Width = 550;

branchnumber_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branchnumber_heading, boSection, -1);

// ****************************** Added For Database Field (goal_heading) Object Detail Band **********************************************************************************************

goalheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

goal_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Goal";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

goal_heading[j].Paragraphs = boParagraphs;

goal_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

goal_heading[j].FontColor = ColumnHeadingFontColor;

goal_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

goal_heading[j].Left = goalheadingstart + goalheadingadd;

goal_heading[j].Top = 1800;

goal_heading[j].Width = 550;

goal_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(goal_heading[j], boSection, -1);

goalheadingadd = goalheadingadd + 4700;

}

// ****************************** Added For Database Field (actual_heading) Object Detail Band **********************************************************************************************

actualheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

actual_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Actual";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

actual_heading[j].Paragraphs = boParagraphs;

actual_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

actual_heading[j].FontColor = ColumnHeadingFontColor;

actual_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

actual_heading[j].Left = actualheadingstart + actualheadingadd;

actual_heading[j].Top = 1800;

actual_heading[j].Width = 700;

actual_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(actual_heading[j], boSection, -1);

actualheadingadd = actualheadingadd + 4700;

}

// ****************************** Added For Database Field (needed_heading) Object Detail Band **********************************************************************************************

neededheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

needed_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Needed";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

needed_heading[j].Paragraphs = boParagraphs;

needed_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

needed_heading[j].FontColor = ColumnHeadingFontColor;

needed_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

needed_heading[j].Left = neededheadingstart + neededheadingadd;

needed_heading[j].Top = 1800;

needed_heading[j].Width = 850;

needed_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(needed_heading[j], boSection, -1);

neededheadingadd = neededheadingadd + 4700;

}

// ****************************** Added For Database Field (pctgoal_heading) Object Detail Band **********************************************************************************************

percentgoalheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

pctgoal_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "% of Goal";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

pctgoal_heading[j].Paragraphs = boParagraphs;

pctgoal_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

pctgoal_heading[j].FontColor = ColumnHeadingFontColor;

pctgoal_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

pctgoal_heading[j].Left = percentgoalheadingstart + percentgoalheadingadd;

pctgoal_heading[j].Top = 1800;

pctgoal_heading[j].Width = 1100;

pctgoal_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(pctgoal_heading[j], boSection, -1);

percentgoalheadingadd = percentgoalheadingadd + 4700;

}

// ****************************** Added For Database Field (points_heading) Object Detail Band **********************************************************************************************

pointsheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

points_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Points";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

points_heading[j].Paragraphs = boParagraphs;

points_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

points_heading[j].FontColor = ColumnHeadingFontColor;

points_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

points_heading[j].Left = pointsheadingstart + pointsheadingadd;

points_heading[j].Top = 1800;

points_heading[j].Width = 650;

points_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(points_heading[j], boSection, -1);

pointsheadingadd = pointsheadingadd + 4700;

totalpointsheadingstart = points_heading[j].Left + points_heading[j].Width + 50;

}

boReportClientDocument.ReportDefController.ReportSectionController.SetProperty(BR_ReportHeader, CrystalDecisions.ReportAppServer.Controllers.CrReportSectionPropertyEnum.crReportSectionPropertyFormat, crSectionFormat);

// ****************************** Added For Database Field (total_points_heading) Object Detail Band **********************************************************************************************

//Create the text object

total_points_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Total Points";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

total_points_heading.Paragraphs = boParagraphs;

total_points_heading.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

total_points_heading.FontColor = ColumnHeadingFontColor;

total_points_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

total_points_heading.Left = totalpointsheadingstart;

total_points_heading.Top = 1800;

total_points_heading.Width = 1250;

total_points_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(total_points_heading, boSection, -1);

// ****************************** Added For Formula Field (sumgoal) Object Report Footer Band **********************************************************************************************

sumgoaladd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumgoal[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

sumgoal[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumgoal[j].Text = "ToText (Sum ({Command.goal" + (j + 1).ToString() + "}), 0)";

sumgoal[j].Name = "sumgoal" + (j + 1).ToString();

sumgoal[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumgoal[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumgoal[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumgoal" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_goal[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_goal[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_goal[j].DataSourceName = sumgoal[j].FormulaForm;

sum_goal[j].FieldValueType = sumgoal[j].Type;

//Now set the co-ordinates of where the field will go

sum_goal[j].Left = sumgoalstart + sumgoaladd;

sum_goal[j].Width = 600;

sum_goal[j].Top = 500;

sum_goal[j].Height = 226;

sum_goal[j].FontColor = TotalLineFontColor;

sum_goal[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_goal[j], BR_ReportFooter, -1);

sumgoaladd = sumgoaladd + 4700;

}

// ****************************** Added For Formula Field (sumactual) Object Report Footer Band **********************************************************************************************

sumactualadd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumactual[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumactual[j].Text = "ToText (Sum ({Command.actual" + (j + 1).ToString() + "}), 0)";

sumactual[j].Name = "sumactual" + (j + 1).ToString();

sumactual[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumactual[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumactual[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumactual" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_actual[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_actual[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_actual[j].DataSourceName = sumactual[j].FormulaForm;

sum_actual[j].FieldValueType = sumactual[j].Type;

//Now set the co-ordinates of where the field will go

sum_actual[j].Left = sumactualstart + sumactualadd;

sum_actual[j].Width = 700;

sum_actual[j].Top = 500;

sum_actual[j].Height = 226;

sum_actual[j].FontColor = TotalLineFontColor;

sum_actual[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_actual[j], BR_ReportFooter, -1);

sumactualadd = sumactualadd + 4700;

}

// ****************************** Added For Formula Field (sumneeded) Object Report Footer Band **********************************************************************************************

sumneededadd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumneeded[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumneeded[j].Text = "ToText (Sum ({Command.needed" + (j + 1).ToString() + "}), 0)";

sumneeded[j].Name = "sumneeded" + (j + 1).ToString();

sumneeded[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumneeded[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumneeded[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumneeded" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_needed[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_needed[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_needed[j].DataSourceName = sumneeded[j].FormulaForm;

sum_needed[j].FieldValueType = sumneeded[j].Type;

//Now set the co-ordinates of where the field will go

sum_needed[j].Left = sumneededstart + sumneededadd;

sum_needed[j].Width = 700;

sum_needed[j].Top = 500;

sum_needed[j].Height = 226;

sum_needed[j].FontColor = TotalLineFontColor;

sum_needed[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_needed[j], BR_ReportFooter, -1);

sumneededadd = sumneededadd + 4700;

}

// ****************************** Added For Formula Field (sumpercent) Object Report Footer Band **********************************************************************************************

sumpercentadd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumpercent[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumpercent[j].Text = "ToText (CDbl({@sumactual" + (j + 1).ToString() + "}) / CDbl({@sumgoal" + (j + 1).ToString() + "}) * 100, 2, '.' )";

sumpercent[j].Name = "sumpercent" + (j + 1).ToString();

sumpercent[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumpercent[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumpercent[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumpercent" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_percent[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_percent[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_percent[j].DataSourceName = sumpercent[j].FormulaForm;

sum_percent[j].FieldValueType = sumpercent[j].Type;

//Now set the co-ordinates of where the field will go

sum_percent[j].Left = sumpercentstart + sumpercentadd;

sum_percent[j].Width = 950;

sum_percent[j].Top = 500;

sum_percent[j].Height = 226;

sum_percent[j].FontColor = TotalLineFontColor;

sum_percent[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_percent[j], BR_ReportFooter, -1);

sumpercentadd = sumpercentadd + 4700;

}

//Create the text object

totalbssb = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Total BSSB";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

//Explicitly set the default font

boFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

boFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

boFont.Bold = totallinefontbold;

boFont.Name = totallinefontname;

boFont.Size = totallinefontsize;

boFontColor.Color = uint.Parse(ColorTranslator.ToOle(totallinefontcolor).ToString());

boFontColor.Font = boFont;

boParagraphTextElement.FontColor = boFontColor;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

totalbssb.Paragraphs = boParagraphs;

//Now add it to the section

totalbssb.Left = 450;

totalbssb.Top = 500;

totalbssb.Width = 2500;

totalbssb.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(totalbssb, BR_ReportFooter, -1);

crystalReportViewer1.ReportSource = boReportDocument;

crystalReportViewer1.ShowLogo = false;

crystalReportViewer1.ParentForm.WindowState = FormWindowState.Maximized;

}

private void BranchRankingReport_Load(object sender, EventArgs e)

{

boReportDocument.SetParameterValue("@year", iyear);

boReportDocument.SetParameterValue("@quarter", iqtr);

this.WindowState = FormWindowState.Maximized;

}

}

}

Martin

BranchRankingReport.txt.zip

Global_Variables.txt.zip

[former_member183750]

Hi Martin

The requirement can be achieved using the InProc RA SDK and you are on the right track there. More on that below. However, report creation APis are not trivial and if all you need to do is add or remove columns then perhaps there is an easier way that does not involve RCAPI;

Create a report with some x number of formulas. Say 10 formulas. Call these formula1, formula2, etc., etc. At runtime, the user chooses what database fields they need to show and you simply populate the formulas with the database field. Thus if the user chooses 5 fields, you populate formula1 to formula5, the rest of the formulas will be blank. The code would be something like what is in KBA: 1214187 - Modifying report formula fields at runtime using the Crystal Reports .NET SDK

Now, if report creation apis are your choice, there is a number of samples you can see here:

The document will also be a great resource.

The Developer Help Files are here:

Report Application Server .NET SDK Developer Guide

Report Application Server .NET API Guide

There is also a utility attached to this KBA, that will write out the database logon code for you.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[Former Member]

Hi Ludek,

I am having difficulty in accessing many of the links on SAP's site. The bank that I work for has blocked much of your site. I had to go to a public library to go to your links and download the material. I also have to use a library computer to respond to this discussion. Is your server where these links are located in Germany? If so, our network guy told me that Germany is blocked by our ISP. I am trying to get them to get me access but I don't know if that will happen.

I did spend today looking at the utility to write out the database logon code. It does not run on my machine. Is this 32 bit or 64 bit? I'm running Windows 7 64 bit. I opened the project and form1 in Visual Studio 2013 and tried to fix things to get it to run but as of now I have not been successful. I was able to build it, however now Form1 has no controls on it. I did look at the VB code in the form and I did not see where or how it populates the connection attributes. The app looks to me like it displays what references to use, what libraries to use, what functions to use and I think it probably displays them in the two text boxes on the form depending on VB or C#. But it doesn't appear to show what the attribute values should be depending on connection type. Am I correct?

I don't know if you can do this, probably not, but in light of the problems above, it would be faster for me to communicate with you by email than through posting here. I can only post here after work at a library.

Martin

[former_member183750]

Hi Martin

Yes, the servers are hosted in Germany - for the most part. E.g.; no guarantees.

As for the utility not running on your computer. It is 32 bit, but so is VS so the OS "bitness" is not relevant. However maybe we need to touch on the 32 / 64 bit "stuff". If your datasource is 64 bit, the utility may need to be recompiled as 64 bit and run via a compiled exe. But I can't say much as I am not sure what actually happens. E.g.; are you getting errors? What is the error? Etc.

Also, depending on what path you decided to take (RCAPI vs. Formulas), you may not need the RAS ADK at all and use the CR SDK which is much, much simpler. E.g. mo need to get the logon code from the utility as it spits out RAS code.

As for how the app works. All it display is the code that would need to be used - either VB or C#. All you'd need to do is copy and paste into your app.

Re. Email. We do not communicate via email. It's either here, or phones only.

- Ludek

[Former Member]

Hi Ludek,

I have made some small progress. I can now login from work as well as get to links. But it seems I am still blocked for zip files.

I also have the utility working. But whenever I try to add my dummy crystal report (dummyreport.rpt) to the project, visual studio 2013 hangs up and I have to kill VS.

I added the following code to my form and ran it but I get the following error:

System.Runtime.InteropServices.COMException was unhandled

HResult=-2147352565

Message=Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

Source=CrystalDecisions.ReportAppServer.DataDefModel

ErrorCode=-2147352565

StackTrace:

at CrystalDecisions.ReportAppServer.DataDefModel.TablesClass.get_Item(Int32 Index)

at Retail_Incentive_Plan.ReportForm3..ctor() in u:\Incentive\Front_End\Visual_Studio_2013\Projects\Retail_Incentive_Plan - Copy\Retail_Incentive_Plan\ReportForm3.cs:line 123

at Retail_Incentive_Plan.MDIParent.ShowNewForm(Object sender, EventArgs e) in u:\Incentive\Front_End\Visual_Studio_2013\Projects\Retail_Incentive_Plan - Copy\Retail_Incentive_Plan\MDIParent.cs:line 27

at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)

at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)

at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)

at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)

at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ToolStrip.WndProc(Message& m)

at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)

at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)

at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)

at Retail_Incentive_Plan.Program.Main() in u:\Incentive\Front_End\Visual_Studio_2013\Projects\Retail_Incentive_Plan - Copy\Retail_Incentive_Plan\Program.cs:line 18

InnerException:

The error occurs at this line of code:

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables[0], boTable);

I'm sure it must be something with the attributes that I am setting. But I'm not sure of what the correct values for some of these attributes should be. By the way, I am using the RCAPI approach rather than the formulas approach (for now).

Here is my code that is in the form:

	public ReportForm3()
	{

//************************** ChangeConnectionInfo ******************************************************************************************************************

	ReportDocument boReportDocument = new ReportDocument();
	//**EDIT** Change the path and report name to the report you want to change.
	boReportDocument.Load(@"c:\Retail_Incentive_Plan\DummyReport.rpt", OpenReportMethod.OpenReportByTempCopy);
	//Create a new Command Table to replace the reports current table.
	CrystalDecisions.ReportAppServer.DataDefModel.CommandTable boTable =
	new CrystalDecisions.ReportAppServer.DataDefModel.CommandTable();

	//boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
	PropertyBag boMainPropertyBag = new PropertyBag();
	//boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
	//In the main property bag (boMainPropertyBag)
	PropertyBag boInnerPropertyBag = new PropertyBag();

	//Set the attributes for the boInnerPropertyBag
	boInnerPropertyBag.Add("Auto Translate", "-1");
	boInnerPropertyBag.Add("Server", "MyServerName");
	boInnerPropertyBag.Add("Connect Timeout", "15");
	boInnerPropertyBag.Add("Data Source", "vancsdb02");
	boInnerPropertyBag.Add("General Timeout", "0");
	boInnerPropertyBag.Add("Initial Catalog", "Retail_Incentive_Plan");
	boInnerPropertyBag.Add("Integrated Security", "True");
	boInnerPropertyBag.Add("Locale Identifier", "1033");
	boInnerPropertyBag.Add("OLE DB Services", "-5");
	boInnerPropertyBag.Add("Provider", "SQLOLEDB");
	boInnerPropertyBag.Add("Tag with column collation when possible", "0");
	boInnerPropertyBag.Add("Use DSN Default Properties", "False");
	boInnerPropertyBag.Add("Use Encryption for Data", "0");

	//Set the attributes for the boMainPropertyBag
	boMainPropertyBag.Add("Database DLL", "crdb_ado.dll");
	boMainPropertyBag.Add("QE_DatabaseName", "Initial Catalog=Retail_Incentive_Plan");
	boMainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)");
	//Add the QE_LogonProperties we set in the boInnerPropertyBag Object
	boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);
	boMainPropertyBag.Add("QE_ServerDescription", "MyServerName");
	boMainPropertyBag.Add("QE_SQLDB", "True");
	boMainPropertyBag.Add("SSO Enabled", "False");

	//Create a new ConnectionInfo object
	CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =
	new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
	//Pass the database properties to a connection info object
	boConnectionInfo.Attributes = boMainPropertyBag;
	//Set the connection kind
	boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
	//**EDIT** Set the User Name and Password if required.
	boConnectionInfo.UserName = "myUserName";
	boConnectionInfo.Password = "MyPassword";
	//Pass the connection information to the table
	boTable.ConnectionInfo = boConnectionInfo;

	//Get the Database Tables Collection for your report
	CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;
	boTables = boReportDocument.ReportClientDocument.DatabaseController.Database.Tables;

	//For each table in the report:
	// - Set the Table Name properties.
	// - Set the Command table's command text.
	// - Set the table location in the report to use the new modified table
	boTable.Name = "Command";
	boTable.QualifiedName = "Command";
	boTable.Alias = "Command";

	//boTable.CommandText = "select country, region, city from customer where country='usa'";
	boTable.CommandText = "exec get_branch_ranking_data, 2015, 1";

boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables[0], boTable);

	//Verify the database after adding substituting the new table.
	//To ensure that the table updates properly when adding Command tables or Stored Procedures.
	boReportDocument.VerifyDatabase();

//return boReportDocument;

//************************** End ChangeConnectionInfo ***************************************************************************************************************

I'm sure that "vancsdb02" is incorrect in the Data Source in the code above but I'm not sure what goes there.

All of my assemblies are set in my project as well as in the top of my code. I just didn't show it here for brevity.

Will pick up again with this on Monday.

Martin

[former_member183750]

Hi Martin

I think this will be the answer for vancsdb02;

boInnerPropertyBag.Add("Data Source", "MyServerName");

E.g.; it's the same as boInnerPropertyBag.Add("Server", "");

Now, if you are using MS SQL 2008 or higher see this:

MS SQL 2005 - OLE DB Provider

MS SQL 2008 - SQL Native 10

MS SQL 2012 - SQL Native 11

MS SQL 2013 - SQL Native 11

You appear to be using SQLOLEDB. E.g.;

boInnerPropertyBag.Add("Provider", "SQLOLEDB");

and that is for MS SQL 2005 and lower only.

Also, at the top of your code I see what I believe is the creation of a command table:

CrystalDecisions.ReportAppServer.DataDefModel.CommandTable boTable = new CrystalDecisions.ReportAppServer.DataDefModel.CommandTable();

but closer to the bottom of the code it looks like you're using a stored proc:

boTable.CommandText = "exec get_branch_ranking_data, 2015, 1";

Have a peek at the attached txt file. It's from an old RAS app that I have not run in years, but it used to work and may provide some clues.

- Ludek

rascode.txt.zip

[Former Member]

Hi Ludek,

I shall remove the line of code for vancsdb02 since I have the line for "Server" and you said that "Server" is the same as "Data Source".

My backend database is MS SQL Server 2005 which is why I am using SQLOLEDB for "Provider".

The reason that I am creating a Command Table is because when I created the Crystal Report manually through the Visual Studio tool, I used a Command Table rather than a Database Table since my data source is a MS SQL Server 2005 Stored Procedure rather than a table or a sql string. I set the text of the command to my stored procedure call. The Crystal Report that I created this way manually works fine and brings back all my data. So when I saw in the CrystalDecisions.ReportAppServer API an object called a Command Table as well as an object called Table I thought that I should be using the Command Table. By the way, what is the difference between setting a Command Table's CommandText to the code to execute the stored procedure versus setting a Table's CommandText to the code to execute the stored procedure? The code being "exec get_branch_ranking_data, 2015, 1". What is the difference between command tables and tables? When do you use one or the other? This could be part of my problem.

Perhaps I need to fix the code in my last post to use one or the other and not mix the two as I now am doing. My error message "Invalid index" refers to the Tables collection and I believe Tables(0). Obviously I have no tables in the collection coming back because my connection is not made or because I'm mixing command tables with tables.

I am using some of the RAS code from your old RAS app with the above code. I just didn't show it in my last post. It comes after the code shown above.

Is there any documentation for what values to put into the attributes of the ConnectionInfo object based on what type of connection you are trying to make, whether OLEDB, ODBC, ADO, etc for a MS SQL Server 2005 database? The API documentation doesn't give this level of detail.

Martin

[Former Member]

Hi Ludek,

I have made quite a bit of progress since I last posted on February 21, 2015.

Since then I have been able to establish a connection to the SQL Server database (2005) using Microsoft integrated security.

I created a dummy crystal report from within Visual Studio 2013, set the source to a stored procedure which takes 2 parameters (year and quarter), and created a parameter for year and for quarter. I saved this dummy crystal report in my project. I also copied the dummy crystal report to my hard drive.

So far I have done the following in C# code:

1) Created a text field and placed it on the page header.

2) Created a formula field and placed it on the page header. The formula takes the parameters for year and quarter passed in and creates a string like "1st Quarter 2015".

3) Created database fields and placed them in the detail section. The fields created so far are Rank, Branch Name, Branch Number, Goal1, Actual1, Needed1, Percent Of Goal1, Points1. They correspond to one category, New Checking Accounts. I placed the category name (in this case "New Checking Accounts") in the page header just above the fields after branch number. For first quarter of 2015, there are a total of 6 categories. So there will be Goal1 through Goal6, Actual1 through Actual6, etc. As of now I created variables for just the first category "New Checking Accounts" and have done a lot of hard coding. I shall go back and look to generate the fields for the remaining categories using a loop and possibly arrays.

4) Created a formula field to sum Goal1 for all the branches. Placed it on the report footer section. I'll go back and create formulas to sum the other 5 fields (Actual1 through Points1). Eventually the sums will be for all categories.

Needless to say I have also changed the font name, size, and weight. I have changed other properties of the fields.

Once I have completed the last sentence in step 3 above and have gotten it to work, I shall attempt to do all the same except without the dummy crystal report, that is, to take it a step further. When I am finally done with this, I shall post my code here.

You are quite correct, the InProc RA SDK API's are not trivial.

I have only gotten this far by reading what I can find on the SAP web site, reading the sample code, the API documentation, and using logic to try different things. When I was stuck I tried to break the problem down to a simpler problem and try that. Sometimes trying something in the designer helps solve a problem, especially in debugging formula issues.

Martin

[former_member183750]

You have more guts and perseverance than I do

I did a few basic apps using the report creation APIs and that was good enough for me. After that, like you it would be copy paste from existing apps and what ever else, depending on people smarter than me.

I am sure any code you want to post, will be very, very appreciated by the whole community.

- Ludek

[Former Member]

Hi Ludek,

Sorry to be so late in getting back to you. I had to spend the last few weeks working on other things but now I am back to working on this problem. I have made a lot of progress on the above but am still not ready for prime time. Perhaps in another week or two if not interrupted.

I did have another question though.

It seems I can create a dummy crystal report file (*.rpt) and use the RAS API to make extensive modifications to it. But I don't seem to be able to create the crystal report file itself from scratch.

We do not have Business Objects Enterprise, so I guess I'm working with the single-tiered RAS. I believe it is also called the In Process Unmanaged RAS. With this RAS, is it even possible to create the crystal report including the crystal report file (.rpt) from scratch at runtime? Or can I only modify an existing crystal report .rpt file at runtime? I don't want to spend anymore time trying to do something that is not possible. Or is there a way to create the rpt file using Visual Studio C# and it's crystal reports object and somehow save it to disk and then load it and modify it? I really wanted to bypass saving the brand new crystal report file to disk. I wanted to create the report in memory only so that the user could display it on a form, print it, or export it to Excel. But if this is not possible without BO Enterprise, I can live with this. I just don't want to spend more time trying to do something that is impossible.

Awaiting your reply.

Martin

[former_member183750]

Hi Martin

With this RAS, is it even possible to create the crystal report including the crystal report file (.rpt) from scratch at runtime?

Yes, absolutely.

See KBA 1300982 - How to create a new report using the Crystal Reports .NET inproc RAS SDK

Here is a complete code for creating a report from scratch, hooking it to a database and adding a db field to the report:

**********************************************************

//File Name: CS_CreateReport_inproc.sln

//Created: March 14, 2008

//Author ID: FLI

//

//Purpose: This C# .NET sample Windows application demonstrates

// how to create a report, add several databasefields

// and display this report using unmanaged inproc RAS.

//

// Note this is available without a dedicated RAS with SP2 for XI R2

//********************************************************************

using CrystalDecisions.ReportAppServer.ClientDoc;

using CrystalDecisions.ReportAppServer.Controllers;

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.CrystalReports.Engine;

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace CS_CreateReport_inproc

{

public partial class Form1 : Form

{

// CR variables

ReportDocument m_boReportDocument;

ISCDReportClientDocument m_boReportClientDocument; // report client document

PropertyBag m_boLogonInfo; // logon info

PropertyBag m_boAttributes; // logon attributes

ConnectionInfo m_boConnectionInfo; // connection info

CrystalDecisions.ReportAppServer.DataDefModel.Table m_boTable; // table

int iField;

Field m_boFieldCustomer; // customer field

Field m_boFieldLastYSale; // last year's sale

Field m_boFieldCity; // city field

Field m_boFieldRegion; // region field

Field m_boFieldCountry; // country field

public Form1()

{

InitializeComponent();

//*****************************

// create a report from scratch

//*****************************

// -> no RAS server available

//Create a new ReportDocument

m_boReportDocument = new ReportDocument();

// load the RPT file

m_boReportDocument.Load("..

..

dummy.rpt");

//Access the ReportClientDocument in the ReportDocument (EROM bridge)

m_boReportClientDocument = m_boReportDocument.ReportClientDocument;

// <- no RAS server available

// -> RAS server available

/*

// create report client document

m_boReportClientDocument = new ReportClientDocument();

// new report document

m_boReportClientDocument.New();

*/

// <- RAS server available

// create logon property

m_boLogonInfo = new PropertyBag();

/* ODBC

// create logon attributes

m_boAttributes = new PropertyBag();

m_boAttributes\"Database DLL\" = "crdb_odbc.dll";

m_boAttributes\"QE_DatabaseType\" = "ODBC (RDO)";

m_boAttributes\"QE_ServerDescription\" = "ODBC - Xtreme Sample Database 11.5";

m_boAttributes\"QE_SQLDB\" = true;

m_boAttributes\"Server Name\" = "Xtreme Sample Database 11.5";

*/

// DAO

// create logon attributes

m_boAttributes = new PropertyBag();

m_boAttributes\"Database DLL\" = "crdb_dao.dll";

m_boAttributes\"QE_DatabaseType\" = "Access";

// m_boAttributes\"QE_ServerDescription\" = "ODBC - Xtreme Sample Database 11.5";

//m_boAttributes\"QE_SQLDB\" = true;

m_boAttributes\"Server Name\" = "C:

Program Files

Business Objects

Common

3.5

Samples

En

Databases

xtreme.mdb";

// create connection info

m_boConnectionInfo = new ConnectionInfo();

m_boConnectionInfo.Attributes = m_boAttributes;

m_boConnectionInfo.UserName = "Admin";

m_boConnectionInfo.Password = "";

m_boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindSQL;

// create a table

m_boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();

m_boTable.ConnectionInfo = m_boConnectionInfo;

m_boTable.Name = "Customer";

m_boTable.Alias = "Customer";

// add a table

m_boReportClientDocument.DatabaseController.AddTable(m_boTable, null);

// grab customer name

iField = m_boReportClientDocument.Database.Tables[0].DataFields.Find("Customer Name", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);

m_boFieldCustomer = (Field)m_boReportClientDocument.Database.Tables[0].DataFieldsiField;

// grab Last Years's Sale

iField = m_boReportClientDocument.Database.Tables[0].DataFields.Find("Last Year's Sales", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);

m_boFieldLastYSale = (Field)m_boReportClientDocument.Database.Tables[0].DataFieldsiField;

// grab city field

iField = m_boReportClientDocument.Database.Tables[0].DataFields.Find("City", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);

m_boFieldCity = (Field)m_boReportClientDocument.Database.Tables[0].DataFieldsiField;

// grab region field

iField = m_boReportClientDocument.Database.Tables[0].DataFields.Find("Region", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);

m_boFieldRegion = (Field)m_boReportClientDocument.Database.Tables[0].DataFieldsiField;

// grab country field

iField = m_boReportClientDocument.Database.Tables[0].DataFields.Find("Country", CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, CeLocale.ceLocaleUserDefault);

m_boFieldCountry = (Field)m_boReportClientDocument.Database.Tables[0].DataFieldsiField;

// add database fields to report

m_boReportClientDocument.DataDefController.ResultFieldController.Add(-1, m_boFieldCustomer);

m_boReportClientDocument.DataDefController.ResultFieldController.Add(-1, m_boFieldLastYSale);

m_boReportClientDocument.DataDefController.ResultFieldController.Add(-1, m_boFieldCity);

m_boReportClientDocument.DataDefController.ResultFieldController.Add(-1, m_boFieldRegion);

m_boReportClientDocument.DataDefController.ResultFieldController.Add(-1, m_boFieldCountry);

// show in reportviewer

// -> no RAS server available

crystalReportViewer1.ReportSource = m_boReportDocument;

// -> RAS server available

//crystalReportViewer1.ReportSource = m_boReportClientDocument;

}

private void button1_Click(object sender, EventArgs e)

{

m_boReportDocument.SaveAs("C:

Test.rpt");

}

}

}

Message was edited by: Ludek Uher

[Former Member]

Dear Ludek,

I'm finally getting back. After spending many days on trying to create a crystal report from scratch without the use of the report server, I was unable to create a report from scratch. I did notice in the code that you sent me, it opens an existing Crystal Reports .rpt file and modifies it. I was hoping to be able to use Visual Studio to create the actual Crystal Report .rpt file at run time from scratch. No matter what I tried, I was unable to do it and somewhere I came across something that said that with the in process RAS report server, you could modify an existing report. So I guess that I would need the Business Objects Report Server in order to do this which I do not have. However I was able to create a truly dynamic Crystal Report from scratch at run time using a dummy Crystal Report .rpt file. Here is my solution:

1. I created a dummy Crystal Report file that has a connection to a Microsoft SQL Server Database. I also added a Command and set the command to 'exec dbo.Get_Branch_Ranking_Data;1'. This stored procedure takes 2 paramaters, a smallint for year and a tinyint for quarter. Both are Input parameters. I created two parameter fields in the dummy Crystal Report, @year and @quarter. Both are defined as Static Number. For the page setup I checked No Printer (optimize for screen display). I also checked Dissociate Formatting Page Size and Printer Paper Size. I selected User Defined Size for my paper size. I selected inches for Unit, set Horizontal to 100.000 and Vertical to 8.501. I set Orientation to Landscape. I set the margins as follows: Left: 0.000 Right: 43.166 Top: 0.166 and Bottom: 0.167. I also checked Adjust Automatically. I then saved the dummy Crystal Report within Visual Studio where I created it and I also copied it to my hard drive.

2. The SQL Server stored procedure Get_Branch_Ranking_Data uses dynamic SQL and a temp table to return a result set with the number of columns returned determined at run time. The number of columns returned is determined by the number of incentive categories which can vary from year to year. I designed the database so that it could vary from quarter to quarter as well. The result set contains Rank, Branch Name, and Branch Number. For each category, right now there are six categories but that can change, it returns the category name, Goal, Actual, Needed, % Of Goal, and Points. So for each category there are 5 columns of data associated with it. The stored procedure also returns Total Points. These columns of data are returned for each Branch.

3. I created a c# class called GlobalVariables which contains the connection_string, the server name, the SQL Connection, and logon_verified. Server is stored in the Windows registry and there is an entry for production and for test. The logon form populates these global variables. I shall attach the c# class file and the c# form code for the report in addition to pasting it here. Here is the GlobalVariables Class:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

namespace Retail_Incentive_Plan

{

public class GlobalVariables

{

public static string connection_string;

public static string server;

public static SqlConnection scn = new SqlConnection();

public static bool logon_verified;

}

}

4. Here is the c# code for the windows form which includes all the code to dynamically create the report at run time:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using CrystalDecisions.ReportAppServer.ClientDoc;

using CrystalDecisions.ReportAppServer.Controllers;

using CrystalDecisions.ReportAppServer.DataDefModel;

using CrystalDecisions.ReportAppServer.ReportDefModel;

using System.Data.SqlClient;

namespace Retail_Incentive_Plan

{

public partial class BranchRankingReport : Form

{

CrystalDecisions.ReportAppServer.ReportDefModel.SectionFormat crSectionFormat;

CrystalDecisions.ReportAppServer.ReportDefModel.Section boSection;

CrystalDecisions.ReportAppServer.ReportDefModel.Section BR_ReportHeader;

CrystalDecisions.ReportAppServer.ReportDefModel.Sections boSection1;

CrystalDecisions.ReportAppServer.ReportDefModel.Section boSection2;

CrystalDecisions.ReportAppServer.ReportDefModel.Section BR_ReportFooter;

CrystalDecisions.ReportAppServer.DataDefModel.Fields boFields;

CrystalDecisions.ReportAppServer.DataDefModel.Field boField;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField boFormulaField;

CrystalDecisions.ReportAppServer.Controllers.PrintOutputController boPrintOutputController;

CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument = new CrystalDecisions.CrystalReports.Engine.ReportDocument();

CrystalDecisions.ReportAppServer.ReportDefModel.PageMargins PageMargin;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject boTextObject;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject totalbssb;

CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs boParagraphs;

CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph boParagraph;

CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements boParagraphElements;

CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement boParagraphTextElement;

CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement boParagraphFieldElement;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor boFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font boFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor DetailLineFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font DetailLineFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor ColumnHeadingFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font ColumnHeadingFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor TotalLineFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font TotalLineFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject branch_name;

CrystalDecisions.ReportAppServer.ReportDefModel.FontColor bnFontColor;

CrystalDecisions.ReportAppServer.ReportDefModel.Font bnFont;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject reportperiod;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject branch_num;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject total_points1;

// Arrays of fieldobjects

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] category_name;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] goal;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] actual;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] needed;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] pct_of_goal;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] points;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_goal;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_actual;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_needed;

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[] sum_percent;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumgoal;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumactual;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumneeded;

CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[] sumpercent;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject rank_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject branchname_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject branchnumber_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] goal_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] actual_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] needed_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] pctgoal_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[] points_heading;

CrystalDecisions.ReportAppServer.ReportDefModel.TextObject total_points_heading;

int? iyear;

int? iqtr;

int icategories;

char output;

public BranchRankingReport(int inyear, int inquarter, char inoutput)

{

int i;

int neededstart;

int neededadd;

int categorynamestart;

int categorynameadd;

int goalstart;

int goaladd;

int actualstart;

int actualadd;

int percentstart;

int percentadd;

int pointsstart;

int pointsadd;

int totalpointsstart;

int goalheadingstart;

int goalheadingadd;

int actualheadingstart;

int actualheadingadd;

int neededheadingstart;

int neededheadingadd;

int percentgoalheadingstart;

int percentgoalheadingadd;

int pointsheadingstart;

int pointsheadingadd;

int totalpointsheadingstart;

int sumgoalstart;

int sumgoaladd;

int sumactualstart;

int sumactualadd;

int sumneededstart;

int sumneededadd;

int sumpercentstart;

int sumpercentadd;

int rptheight;

int rptwidth;

string sql_string;

string headingfontname;

string subheadingfontname;

string columnheadingfontname;

string detaillinefontname;

string totallinefontname;

int headingfontsize;

int subheadingfontsize;

int columnheadingfontsize;

int detaillinefontsize;

int totallinefontsize;

int boFieldIndex;

bool headingfontbold;

bool subheadingfontbold;

bool columnheadingfontbold;

bool detaillinefontbold;

bool totallinefontbold;

Color headingfontcolor;

Color subheadingfontcolor;

Color categoryheadingfontcolor;

Color columnheadingfontcolor;

Color detaillinefontcolor;

Color totallinefontcolor;

iyear = inyear;

iqtr = inquarter;

output = inoutput;

headingfontname = "Times New Roman";

headingfontsize = 18;

headingfontbold = true;

headingfontcolor = Color.Black;

subheadingfontname = "Times New Roman";

subheadingfontsize = 18;

subheadingfontbold = true;

subheadingfontcolor = Color.Black;

columnheadingfontname = "Times New Roman";

columnheadingfontsize = 12;

columnheadingfontbold = true;

columnheadingfontcolor = Color.Black;

categoryheadingfontcolor = Color.Black;

detaillinefontname = "Times New Roman";

detaillinefontsize = 12;

detaillinefontbold = false;

detaillinefontcolor = Color.Black;

totallinefontname = "Times New Roman";

totallinefontsize = 12;

totallinefontbold = true;

totallinefontcolor = Color.Black;

goalstart = 3600;

actualstart = 4500;

neededstart = 5350;

percentstart = 6350;

pointsstart = 7450;

categorynamestart = 3600;

goalheadingstart = 3600;

actualheadingstart = 4430;

neededheadingstart = 5300;

percentgoalheadingstart = 6300;

pointsheadingstart = 7500;

totalpointsheadingstart = 0;

totalpointsstart = 0;

sumgoalstart = 3500;

sumactualstart = 4400;

sumneededstart = 5350;

sumpercentstart = 6350;

rptheight = 10700;

// Get the number of categories that appear on the report.

// Each category has goal, actual, needed, % of goal, and points.

sql_string = "select count(distinct category_id) from branch_category_actual_points " +

"where year = " + iyear.ToString() + " and quarter = " + iqtr.ToString();

SqlCommand scmd = new SqlCommand(sql_string, GlobalVariables.scn);

SqlDataReader sdr = scmd.ExecuteReader();

sdr.Read();

if (sdr.HasRows)

{

icategories = sdr.GetInt32(0);

}

else

{

MessageBox.Show("Unable to get number of categories.", "Get Number Of Categories", MessageBoxButtons.OK,

MessageBoxIcon.Error);

sdr.Close();

this.Close();

return;

}

sdr.Close();

/*

The formula for the report width is (the width of the headings before the first category's headings + (the number of categories * the width of all the headings

for a category) + the width of the total points heading - the adjustment for the width of the spaces between the last category's points and the total points)

3600 is the starting position of the first incentive category heading

icategories is the number of incentive categories for the year and quarter

4700 is the width of all the headings for a category

1250 is the width of the heading for total points

100 is subtracted from the final width since the width for the number of spaces after the last category of points is 50 rather than 150 as with the

other categories

*/

rptwidth = (3600 + (icategories * 4700) + 1250 - 100);

category_name = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

goal = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

actual = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

needed = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

pct_of_goal = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

points = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_goal = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_actual = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_needed = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sum_percent = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject[icategories];

sumgoal = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

sumactual = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

sumneeded = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

sumpercent = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField[icategories];

goal_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

actual_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

needed_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

pctgoal_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

points_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject[icategories];

DetailLineFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

DetailLineFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

DetailLineFont.Bold = detaillinefontbold;

DetailLineFont.Name = detaillinefontname;

DetailLineFont.Size = detaillinefontsize;

DetailLineFontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

ColumnHeadingFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

ColumnHeadingFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

ColumnHeadingFont.Bold = columnheadingfontbold;

ColumnHeadingFont.Name = columnheadingfontname;

ColumnHeadingFont.Size = columnheadingfontsize;

ColumnHeadingFontColor.Color = uint.Parse(ColorTranslator.ToOle(columnheadingfontcolor).ToString());

TotalLineFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

TotalLineFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

TotalLineFont.Bold = totallinefontbold;

TotalLineFont.Name = totallinefontname;

TotalLineFont.Size = totallinefontsize;

TotalLineFontColor.Color = uint.Parse(ColorTranslator.ToOle(totallinefontcolor).ToString());

InitializeComponent();

crystalReportViewer1.ToolPanelView = CrystalDecisions.Windows.Forms.ToolPanelViewType.None;

crystalReportViewer1.ShowRefreshButton = false;

if (output == 'F')

crystalReportViewer1.ShowPrintButton = false;

else

crystalReportViewer1.ShowExportButton = false;

crystalReportViewer1.AutoSize = true;

PageMargin = new CrystalDecisions.ReportAppServer.ReportDefModel.PageMargins();

boPrintOutputController = new CrystalDecisions.ReportAppServer.Controllers.PrintOutputController();

//**EDIT** Change the path and report name to the report you want to change.

try

{

boReportDocument.Load("c:\\Retail_Incentive_Plan\\Reports\\DummySPWithParameters.rpt");

}

catch

{

MessageBox.Show("Branch Ranking Report Not Found", "Error Loading Report", MessageBoxButtons.OK,

MessageBoxIcon.Error);

this.Close();

return;

}

ISCDReportClientDocument boReportClientDocument;

boReportClientDocument = boReportDocument.ReportClientDocument;

boReportClientDocument.DatabaseController.CanExecuteSQL();

boReportClientDocument.PrintOutputController.ModifyPaperOrientation(CrPaperOrientationEnum.crPaperOrientationLandscape);

if (output == 'F')

{

boReportDocument.PrintOptions.NoPrinter = true;

boReportDocument.PrintOptions.DissociatePageSizeAndPrinterPaperSize = true;

boReportClientDocument.PrintOutputController.ModifyUserPaperSize(rptheight, rptwidth);

boReportDocument.ReportClientDocument.PrintOutputController.ModifyPageMargins(0, 0, 0, 0);

}

else

{

boReportClientDocument.PrintOutputController.ModifyUserPaperSize(rptheight, rptwidth);

boReportDocument.ReportClientDocument.PrintOutputController.ModifyPageMargins(0, 0, 0, 0);

boReportDocument.PrintOptions.NoPrinter = false;

boReportDocument.PrintOptions.PageMargins.topMargin.Equals(0);

boReportDocument.PrintOptions.PaperSize = PaperSize.PaperLegal;

}

boReportDocument.DataSourceConnections[0].SetConnection(GlobalVariables.server, "Retail_Incentive_Plan", true);

boReportDocument.SetParameterValue("@year", iyear);

boReportDocument.SetParameterValue("@quarter", iqtr);

PropertyBag boInnerPropertyBag = new PropertyBag();

//Set the attributes for the boInnerPropertyBag

boInnerPropertyBag.Add("Auto Translate", "-1");

boInnerPropertyBag.Add("Server", GlobalVariables.server);

boInnerPropertyBag.Add("Connect Timeout", "15");

boInnerPropertyBag.Add("Data Source", GlobalVariables.server);

boInnerPropertyBag.Add("Trusted_Connection", "yes");

boInnerPropertyBag.Add("General Timeout", "0");

boInnerPropertyBag.Add("Initial Catalog", "Retail_Incentive_Plan");

boInnerPropertyBag.Add("Integrated Security", "True");

boInnerPropertyBag.Add("Locale Identifier", "1033");

boInnerPropertyBag.Add("OLE DB Services", "-5");

boInnerPropertyBag.Add("Provider", "SQLOLEDB");

boInnerPropertyBag.Add("Tag with column collation when possible", "0");

boInnerPropertyBag.Add("Use DSN Default Properties", "False");

boInnerPropertyBag.Add("Use Encryption for Data", "0");

//Create a new ConnectionInfo object

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo =

new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

//Pass the database properties to a connection info object

boConnectionInfo.Attributes = boInnerPropertyBag;

//Set the connection kind

boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindSQL;

//Verify the database after adding substituting the new table.

//To ensure that the table updates properly when adding Command tables or Stored Procedures.

boReportDocument.VerifyDatabase();

BR_ReportHeader = boReportClientDocument.ReportDefController.ReportDefinition.ReportHeaderArea.Sections[0];

crSectionFormat = BR_ReportHeader.Format;

crSectionFormat.EnableSuppress = true;

// ****************************** Added For Text Object Heading **********************************************************************************************

//First determine which section to add the text field to - in this case the page header section

boSection = boReportClientDocument.ReportDefController.ReportDefinition.PageHeaderArea.Sections[0];

//Create the text object

boTextObject = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Retail Incentive Plan";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

//Explicitly set the default font

boFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

boFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

boFont.Bold = headingfontbold;

boFont.Name = headingfontname;

boFont.Size = headingfontsize;

boFontColor.Color = uint.Parse(ColorTranslator.ToOle(headingfontcolor).ToString());

boFontColor.Font = boFont;

boParagraphTextElement.FontColor = boFontColor;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

boTextObject.Paragraphs = boParagraphs;

//Now add it to the section

boTextObject.Width = 3911;

boTextObject.Left = ((rptwidth / 2) - (boTextObject.Width / 2));

boTextObject.Top = 1;

boTextObject.Height = 552;

boReportClientDocument.ReportDefController.ReportObjectController.Add(boTextObject, boSection, -1);

// Get Subheading

//First create the formula field

boFormulaField = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

boFormulaField.Text = "IIF (ToText ({?@quarter}, 0) = '1', '1st', IIF (ToText ({?@quarter}, 0) = '2', '2nd' , IIF (ToText ({?@quarter}, 0) = '3', '3rd' , IIF (ToText ({?@quarter}, 0) = '4', '4th', 'Invalid Quarter' ) ))) + ' Quarter ' + ToText ({?@year}, 0, '')";

boFormulaField.Name = "ReportPeriod";

boFormulaField.Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

boFormulaField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(boFormulaField);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Page Header section

boSection = boReportClientDocument.ReportDefController.ReportDefinition.PageHeaderArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@ReportPeriod}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

reportperiod = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set the datasource of this field object to the formula of the above Database Field Object

reportperiod.DataSourceName = boFormulaField.FormulaForm;

reportperiod.FieldValueType = boFormulaField.Type;

//Now set the co-ordinates of where the field will go

reportperiod.Width = 3911;

reportperiod.Left = ((rptwidth / 2) - (reportperiod.Width / 2));

reportperiod.Top = 500;

reportperiod.Height = 552;

boFont.Name = subheadingfontname;

boFont.Size = subheadingfontsize;

boFont.Bold = subheadingfontbold;

boFontColor.Color = uint.Parse(ColorTranslator.ToOle(subheadingfontcolor).ToString());

reportperiod.FontColor = boFontColor;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(reportperiod, boSection, -1);

// ****************************** End Added For Text Object Heading **********************************************************************************************

//First determine which section to add the field to - in this case the details section

boSection2 = boReportClientDocument.ReportDefController.ReportDefinition.DetailArea.Sections[0];

//Create the database field object

CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject rank_num;

rank_num = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

rank_num.DataSourceName = "{Command.rank}";

rank_num.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

rank_num.FontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

rank_num.FontColor.Font.Name = detaillinefontname;

rank_num.FontColor.Font.Size = detaillinefontsize;

rank_num.FontColor.Font.Bold = detaillinefontbold;

rank_num.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

rank_num.Left = 0;

rank_num.Top = 1;

rank_num.Width = 350;

rank_num.Height = 226;

rank_num.FontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

boReportClientDocument.ReportDefController.ReportObjectController.Add(rank_num, boSection2, -1);

// ****************************** Added For Database Field Object Detail Band **********************************************************************************************

//Create the database field object

branch_name = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

branch_name.DataSourceName = "{Command.branch_name}";

branch_name.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

branch_name.Width = 3 * 1440;

branch_name.FontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

branch_name.FontColor.Font.Name = detaillinefontname;

branch_name.FontColor.Font.Size = detaillinefontsize;

branch_name.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

//explicitly set the default font

bnFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

bnFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

bnFont.Bold = detaillinefontbold;

branch_name.FontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

branch_name.FontColor.Font.Bold = detaillinefontbold;

bnFontColor.Font = bnFont;

//Now add it to the section

branch_name.Left = 450; // 1440 twips per inch

branch_name.Top = 1;

branch_name.Width = 2500;

branch_name.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branch_name, boSection2, -1);

// ****************************** Added For Database Field (Branch Number) Object Detail Band **********************************************************************************************

//First determine which section to add the text field to - in this case the details section

boSection2 = boReportClientDocument.ReportDefController.ReportDefinition.DetailArea.Sections[0];

//Create the database field object

branch_num = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

branch_num.DataSourceName = "{Command.branch_number}";

branch_num.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt8uField;

branch_num.FontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

branch_num.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

branch_num.FontColor.Color = uint.Parse(ColorTranslator.ToOle(detaillinefontcolor).ToString());

branch_num.FontColor.Font.Bold = detaillinefontbold;

//Now add it to the section

branch_num.Left = 3000;

branch_num.Top = 1;

branch_num.Width = 370;

branch_num.Height = 226;

branch_num.FontColor.Font.Name = detaillinefontname;

branch_num.FontColor.Font.Size = detaillinefontsize;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branch_num, boSection2, -1);

// ****************************** Added For Database Field (Goal) Object Detail Band **********************************************************************************************

i = 1;

//First determine which section to add the text field to - in this case the details section

boSection2 = boReportClientDocument.ReportDefController.ReportDefinition.DetailArea.Sections[0];

goaladd = 0;

for (int j = 0; j < icategories; j++)

{

goal[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

goal[j].DataSourceName = "{Command.goal" + (j + 1).ToString() + "}";

goal[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

goal[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Now add it to the section

goal[j].FontColor = DetailLineFontColor;

goal[j].FontColor.Font = DetailLineFont;

goal[j].Left = goalstart + goaladd;

goal[j].Top = 1;

goal[j].Width = 500;

goal[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(goal[j], boSection2, -1);

goaladd = goaladd + 4700;

}

// ****************************** Added For Database Field (Actual) Object Detail Band **********************************************************************************************

actualadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

actual[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

actual[j].DataSourceName = "{Command.actual" + (j+ 1).ToString() + "}";

actual[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

actual[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

actual[j].FontColor = DetailLineFontColor;

actual[j].FontColor.Font = DetailLineFont;

actual[j].Left = actualstart + actualadd;

actual[j].Top = 1;

actual[j].Width = 600;

actual[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(actual[j], boSection2, -1);

actualadd = actualadd + 4700;

}

// ****************************** Added For Database Field (Needed) Object Detail Band **********************************************************************************************

neededadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

needed[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

needed[j].DataSourceName = "{Command.needed" + (j + 1).ToString() + "}";

needed[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt16sField;

needed[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

needed[j].FontColor = DetailLineFontColor;

needed[j].FontColor.Font = DetailLineFont;

needed[j].Left = neededstart + neededadd;

needed[j].Top = 1;

needed[j].Width = 700;

needed[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(needed[j], boSection2, -1);

neededadd = neededadd + 4700;

}

// ****************************** Added For Database Field (Pct_Of_Goal) Object Detail Band **********************************************************************************************

percentadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

pct_of_goal[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

pct_of_goal[j].DataSourceName = "{Command.pct_of_goal" + (j + 1).ToString() + "}";

pct_of_goal[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeNumberField;

pct_of_goal[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

pct_of_goal[j].FontColor = DetailLineFontColor;

pct_of_goal[j].FontColor.Font = DetailLineFont;

pct_of_goal[j].Left = percentstart + percentadd;

pct_of_goal[j].Top = 1;

pct_of_goal[j].Width = 950;

pct_of_goal[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(pct_of_goal[j], boSection2, -1);

percentadd = percentadd + 4700;

}

// ****************************** Added For Database Field (Points) Object Detail Band **********************************************************************************************

pointsadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the database field object

points[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

points[j].DataSourceName = "{Command.points" + (j + 1).ToString() + "}";

points[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt32sField;

points[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

points[j].FontColor = DetailLineFontColor;

points[j].FontColor.Font = DetailLineFont;

points[j].Left = pointsstart + pointsadd;

points[j].Top = 1;

points[j].Width = 700;

points[j].Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(points[j], boSection2, -1);

pointsadd = pointsadd + 4700;

totalpointsstart = points[j].Left + points[j].Width + 100;

}

// ****************************** Added For Database Field (Total_Points1) Object Detail Band **********************************************************************************************

//Create the database field object

total_points1 = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

total_points1.DataSourceName = "{Command.total_points1}";

total_points1.FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeInt32sField;

total_points1.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//explicitly set the default font

total_points1.FontColor = DetailLineFontColor;

total_points1.FontColor.Font = DetailLineFont;

total_points1.Left = totalpointsstart;

total_points1.Top = 1;

total_points1.Width = 750;

total_points1.Height = 226;

boReportClientDocument.ReportDefController.ReportObjectController.Add(total_points1, boSection2, -1);

// ****************************** Added For Database Field (category_name) Object Detail Band **********************************************************************************************

categorynameadd = 0;

for (int j = 0; j < icategories; j++)

{

//First determine which section to add the text field to - in this case the details section

boSection1 = boReportClientDocument.ReportDefController.ReportDefinition.PageHeaderArea.Sections;

//Create the database field object

category_name[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

//Set which field to use for the data to be displayed

category_name[j].DataSourceName = "{Command.category_name" + (j + 1).ToString() + "}";

category_name[j].FieldValueType = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

category_name[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

//explicitly set the default font

category_name[j].FontColor = ColumnHeadingFontColor;

category_name[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

category_name[j].Left = categorynamestart + categorynameadd;

category_name[j].Top = 1500;

category_name[j].Width = 4200;

category_name[j].Height = 300;

boReportClientDocument.ReportDefController.ReportObjectController.Add(category_name[j], boSection, -1);

categorynameadd = categorynameadd + 4700;

}

// ****************************** Added For Database Field (rank_heading) Object Detail Band **********************************************************************************************

//Create the text object

rank_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Rank";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

rank_heading.Paragraphs = boParagraphs;

rank_heading.FontColor = ColumnHeadingFontColor;

rank_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

rank_heading.Left = 0;

rank_heading.Top = 1800;

rank_heading.Width = 580;

rank_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(rank_heading, boSection, -1);

// ****************************** Added For Database Field (branchname_heading) Object Detail Band **********************************************************************************************

//Create the text object

branchname_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Branch Name";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

branchname_heading.Paragraphs = boParagraphs;

branchname_heading.FontColor = ColumnHeadingFontColor;

branchname_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

branchname_heading.Left = 870;

branchname_heading.Top = 1800;

branchname_heading.Width = 1500;

branchname_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branchname_heading, boSection, -1);

// ****************************** Added For Database Field (branchnumber_heading) Object Detail Band **********************************************************************************************

//Create the text object

branchnumber_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Num";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

branchnumber_heading.Paragraphs = boParagraphs;

branchnumber_heading.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

branchnumber_heading.FontColor = ColumnHeadingFontColor;

branchnumber_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

branchnumber_heading.Left = 2900;

branchnumber_heading.Top = 1800;

branchnumber_heading.Width = 550;

branchnumber_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(branchnumber_heading, boSection, -1);

// ****************************** Added For Database Field (goal_heading) Object Detail Band **********************************************************************************************

goalheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

goal_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Goal";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

goal_heading[j].Paragraphs = boParagraphs;

goal_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

goal_heading[j].FontColor = ColumnHeadingFontColor;

goal_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

goal_heading[j].Left = goalheadingstart + goalheadingadd;

goal_heading[j].Top = 1800;

goal_heading[j].Width = 550;

goal_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(goal_heading[j], boSection, -1);

goalheadingadd = goalheadingadd + 4700;

}

// ****************************** Added For Database Field (actual_heading) Object Detail Band **********************************************************************************************

actualheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

actual_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Actual";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

actual_heading[j].Paragraphs = boParagraphs;

actual_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

actual_heading[j].FontColor = ColumnHeadingFontColor;

actual_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

actual_heading[j].Left = actualheadingstart + actualheadingadd;

actual_heading[j].Top = 1800;

actual_heading[j].Width = 700;

actual_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(actual_heading[j], boSection, -1);

actualheadingadd = actualheadingadd + 4700;

}

// ****************************** Added For Database Field (needed_heading) Object Detail Band **********************************************************************************************

neededheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

needed_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Needed";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

needed_heading[j].Paragraphs = boParagraphs;

needed_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

needed_heading[j].FontColor = ColumnHeadingFontColor;

needed_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

needed_heading[j].Left = neededheadingstart + neededheadingadd;

needed_heading[j].Top = 1800;

needed_heading[j].Width = 850;

needed_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(needed_heading[j], boSection, -1);

neededheadingadd = neededheadingadd + 4700;

}

// ****************************** Added For Database Field (pctgoal_heading) Object Detail Band **********************************************************************************************

percentgoalheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

pctgoal_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "% of Goal";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

pctgoal_heading[j].Paragraphs = boParagraphs;

pctgoal_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

pctgoal_heading[j].FontColor = ColumnHeadingFontColor;

pctgoal_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

pctgoal_heading[j].Left = percentgoalheadingstart + percentgoalheadingadd;

pctgoal_heading[j].Top = 1800;

pctgoal_heading[j].Width = 1100;

pctgoal_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(pctgoal_heading[j], boSection, -1);

percentgoalheadingadd = percentgoalheadingadd + 4700;

}

// ****************************** Added For Database Field (points_heading) Object Detail Band **********************************************************************************************

pointsheadingadd = 0;

for (int j = 0; j < icategories; j++)

{

//Create the text object

points_heading[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Points";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

points_heading[j].Paragraphs = boParagraphs;

points_heading[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentLeft;

points_heading[j].FontColor = ColumnHeadingFontColor;

points_heading[j].FontColor.Font = ColumnHeadingFont;

//Now add it to the section

points_heading[j].Left = pointsheadingstart + pointsheadingadd;

points_heading[j].Top = 1800;

points_heading[j].Width = 650;

points_heading[j].Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(points_heading[j], boSection, -1);

pointsheadingadd = pointsheadingadd + 4700;

totalpointsheadingstart = points_heading[j].Left + points_heading[j].Width + 50;

}

boReportClientDocument.ReportDefController.ReportSectionController.SetProperty(BR_ReportHeader, CrystalDecisions.ReportAppServer.Controllers.CrReportSectionPropertyEnum.crReportSectionPropertyFormat, crSectionFormat);

// ****************************** Added For Database Field (total_points_heading) Object Detail Band **********************************************************************************************

//Create the text object

total_points_heading = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Total Points";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

total_points_heading.Paragraphs = boParagraphs;

total_points_heading.Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

total_points_heading.FontColor = ColumnHeadingFontColor;

total_points_heading.FontColor.Font = ColumnHeadingFont;

//Now add it to the section

total_points_heading.Left = totalpointsheadingstart;

total_points_heading.Top = 1800;

total_points_heading.Width = 1250;

total_points_heading.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(total_points_heading, boSection, -1);

// ****************************** Added For Formula Field (sumgoal) Object Report Footer Band **********************************************************************************************

sumgoaladd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumgoal[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

sumgoal[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumgoal[j].Text = "ToText (Sum ({Command.goal" + (j + 1).ToString() + "}), 0)";

sumgoal[j].Name = "sumgoal" + (j + 1).ToString();

sumgoal[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumgoal[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumgoal[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumgoal" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_goal[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_goal[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_goal[j].DataSourceName = sumgoal[j].FormulaForm;

sum_goal[j].FieldValueType = sumgoal[j].Type;

//Now set the co-ordinates of where the field will go

sum_goal[j].Left = sumgoalstart + sumgoaladd;

sum_goal[j].Width = 600;

sum_goal[j].Top = 500;

sum_goal[j].Height = 226;

sum_goal[j].FontColor = TotalLineFontColor;

sum_goal[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_goal[j], BR_ReportFooter, -1);

sumgoaladd = sumgoaladd + 4700;

}

// ****************************** Added For Formula Field (sumactual) Object Report Footer Band **********************************************************************************************

sumactualadd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumactual[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumactual[j].Text = "ToText (Sum ({Command.actual" + (j + 1).ToString() + "}), 0)";

sumactual[j].Name = "sumactual" + (j + 1).ToString();

sumactual[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumactual[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumactual[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumactual" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_actual[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_actual[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_actual[j].DataSourceName = sumactual[j].FormulaForm;

sum_actual[j].FieldValueType = sumactual[j].Type;

//Now set the co-ordinates of where the field will go

sum_actual[j].Left = sumactualstart + sumactualadd;

sum_actual[j].Width = 700;

sum_actual[j].Top = 500;

sum_actual[j].Height = 226;

sum_actual[j].FontColor = TotalLineFontColor;

sum_actual[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_actual[j], BR_ReportFooter, -1);

sumactualadd = sumactualadd + 4700;

}

// ****************************** Added For Formula Field (sumneeded) Object Report Footer Band **********************************************************************************************

sumneededadd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumneeded[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumneeded[j].Text = "ToText (Sum ({Command.needed" + (j + 1).ToString() + "}), 0)";

sumneeded[j].Name = "sumneeded" + (j + 1).ToString();

sumneeded[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumneeded[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumneeded[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumneeded" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_needed[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_needed[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_needed[j].DataSourceName = sumneeded[j].FormulaForm;

sum_needed[j].FieldValueType = sumneeded[j].Type;

//Now set the co-ordinates of where the field will go

sum_needed[j].Left = sumneededstart + sumneededadd;

sum_needed[j].Width = 700;

sum_needed[j].Top = 500;

sum_needed[j].Height = 226;

sum_needed[j].FontColor = TotalLineFontColor;

sum_needed[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_needed[j], BR_ReportFooter, -1);

sumneededadd = sumneededadd + 4700;

}

// ****************************** Added For Formula Field (sumpercent) Object Report Footer Band **********************************************************************************************

sumpercentadd = 0;

for (int j = 0; j < icategories; j++)

{

//First create the formula field

sumpercent[j] = new CrystalDecisions.ReportAppServer.DataDefModel.FormulaField();

//Set the parameters (assume it's a string and Crystal Syntax)

sumpercent[j].Text = "ToText (CDbl({@sumactual" + (j + 1).ToString() + "}) / CDbl({@sumgoal" + (j + 1).ToString() + "}) * 100, 2, '.' )";

sumpercent[j].Name = "sumpercent" + (j + 1).ToString();

sumpercent[j].Syntax = CrystalDecisions.ReportAppServer.DataDefModel.CrFormulaSyntaxEnum.crFormulaSyntaxCrystal;

sumpercent[j].Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now add it to the report

boReportClientDocument.DataDefController.FormulaFieldController.Add(sumpercent[j]);

//Now that the formula has been created, add the newly created formula to the report

//First determine which section to add the formula field to - in this case the Report Footer section

BR_ReportFooter = boReportClientDocument.ReportDefController.ReportDefinition.ReportFooterArea.Sections[0];

//Get back all the formulafields in the report

boFields = boReportClientDocument.DataDefController.DataDefinition.FormulaFields;

boFieldIndex = boFields.Find("{@sumpercent" + (j + 1).ToString() + "}", CrystalDecisions.ReportAppServer.DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameFormula,

CrystalDecisions.ReportAppServer.DataDefModel.CeLocale.ceLocaleUserDefault);

boField = (CrystalDecisions.ReportAppServer.DataDefModel.Field)boFields[boFieldIndex];

//Set the type of field this is

boField.Type = CrystalDecisions.ReportAppServer.DataDefModel.CrFieldValueTypeEnum.crFieldValueTypeStringField;

//Now create a new Field object which will be added to the report

sum_percent[j] = new CrystalDecisions.ReportAppServer.ReportDefModel.FieldObject();

sum_percent[j].Format.HorizontalAlignment = CrystalDecisions.ReportAppServer.ReportDefModel.CrAlignmentEnum.crAlignmentRight;

//Set the datasource of this field object to the formula form of the above Database Field Object

sum_percent[j].DataSourceName = sumpercent[j].FormulaForm;

sum_percent[j].FieldValueType = sumpercent[j].Type;

//Now set the co-ordinates of where the field will go

sum_percent[j].Left = sumpercentstart + sumpercentadd;

sum_percent[j].Width = 950;

sum_percent[j].Top = 500;

sum_percent[j].Height = 226;

sum_percent[j].FontColor = TotalLineFontColor;

sum_percent[j].FontColor.Font = TotalLineFont;

//And finally Add it to the report

boReportClientDocument.ReportDefController.ReportObjectController.Add(sum_percent[j], BR_ReportFooter, -1);

sumpercentadd = sumpercentadd + 4700;

}

//Create the text object

totalbssb = new CrystalDecisions.ReportAppServer.ReportDefModel.TextObject();

//Create the paragraph elements for the text Object

boParagraphs = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraphs();

boParagraph = new CrystalDecisions.ReportAppServer.ReportDefModel.Paragraph();

boParagraphElements = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphElements();

boParagraphTextElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphTextElement();

boParagraphFieldElement = new CrystalDecisions.ReportAppServer.ReportDefModel.ParagraphFieldElement();

//Set the text value for the text field

boParagraphTextElement.Text = "Total BSSB";

boParagraphTextElement.Kind = CrystalDecisions.ReportAppServer.ReportDefModel.CrParagraphElementKindEnum.crParagraphElementKindText;

//Explicitly set the default font

boFontColor = new CrystalDecisions.ReportAppServer.ReportDefModel.FontColor();

boFont = new CrystalDecisions.ReportAppServer.ReportDefModel.Font();

boFont.Bold = totallinefontbold;

boFont.Name = totallinefontname;

boFont.Size = totallinefontsize;

boFontColor.Color = uint.Parse(ColorTranslator.ToOle(totallinefontcolor).ToString());

boFontColor.Font = boFont;

boParagraphTextElement.FontColor = boFontColor;

boParagraphElements.Add(boParagraphTextElement);

boParagraph.ParagraphElements = boParagraphElements;

boParagraphs.Add(boParagraph);

totalbssb.Paragraphs = boParagraphs;

//Now add it to the section

totalbssb.Left = 450;

totalbssb.Top = 500;

totalbssb.Width = 2500;

totalbssb.Height = 350;

boReportClientDocument.ReportDefController.ReportObjectController.Add(totalbssb, BR_ReportFooter, -1);

crystalReportViewer1.ReportSource = boReportDocument;

crystalReportViewer1.ShowLogo = false;

}

private void BranchRankingReport_Load(object sender, EventArgs e)

{

boReportDocument.SetParameterValue("@year", iyear);

boReportDocument.SetParameterValue("@quarter", iqtr);

}

}

}

5. I built a form to enter selection criteria for the Crystal Report rather than use Crystal's default prompt screen for the parameters. It gave me more control. The selection screen has a combobox for year and a combobox for quarter and a combobox for quarter. It also has a groupbox with two radio buttons in it, one labelled Printer and one labelled File. I populate the year combobox with available years of data. I populate the quarter combobox with available quarters of data for the selected year. If the user selects Printer, I present the Crystal Report with the print button in the Crystal Report Viewer showing by the export button hidden. If the user selects File, I present the Crystal Report with the export button showing but the print button hidden. I default the report to legal size paper. I have attached a picture of the selection criteria form.

6. Since the report can have x number of columns, I noticed by experiment that Crystal automatically sizes the report to fit the legal size paper by shrinking the font size. By experiment I found that the limit was 38 categories which we'll never hit.

I hope that this solution can be of help to the community.

Martin

Global_Variables.txt.zip

BranchRankingReport.txt.zip