Crystal report Crosstab percentage Rounding Problem Fix

When we use Integrated Cystal report of VS 2008 for report generation, it works fine except some special place. In my previous post in this blog some of those problem  were identified and i tried to give solution. Now i discuss another new point that was discovered and fix recently.

In crosstab report, if we use percentage based on count, there is rounding problem. Total sum of a row is not equal to 100. The Crystal report truncate the value after floating point automatically. I have searched in the net to find the reason of this behavior, unfortunately can’t get acceptable answer(may be i am not good googler !). So to save my self from client bullet, i have to think alternative.

A new custom field is created(Right click formula Field>new) which only count the record using blow formula,
————
WhileReadingRecords;
1
————–

Now this custom filed is used in cross tab. In this time, at first the sum is made of the custom field and then the show as percentage option of crosstab expert is checked.

This problem only occur only in the integrated Cystal report of VS 2008. So i was able to duck the client bullet .

………………………….
Khayer
GIS Programmer,
CEGIS

Notes: Special thank to Kamal Pasha my college to help me to resolve the problem.

Advertisements

Deploy VS 2008 Window Project That use Crystal Report

I have to pass a terrible day for deploying a project where Cystal Report is used. I have used Install Shield 15 to make the installer of the project.  Project is installed to client pc hopefully, But when the user click the report generate button, System triger following error,

<code>Could not load file or assembly ‘CrystalDecisions.Windows.Forms, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304’ or one of its dependencies. The system cannot find the file specified.

</code>

Program does not find the assembly for generating report in the client PC. When i build the installer, Version =10.2.3600 is used which wrong. You can check it go to below directories,

C:/Windows/Assembly/

Now Solution, I have to spend the whole day for the solution, though i am experience in deploying web project of .NET 2.0  which use Crystal Report. Bussiness solution web site is one the complecated site in the web to me. It seems to me useless. Furtuantlly Microsoft forum help to solve the problem. I have to  install CRRedist2008_x86.msi in the Client PC to get desires assemblies. i have got the file in my development pc in following dirrectory,

C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bootstrapper\Packages\CrystalReports10_5

Then, at the end of the day i feel very relieve. Thanks Mirosoft foum.

Please Check these Link

Thanks
Khayer
GIS Programmer, CEGIS
Bangladesh

Create Crystal Report from DataSet using ASP.NET and MySql

Web based reporting is very necessary tools. Now popular reporting tools Crystal Report is integrated with Visual Studio 2005 and later. It makes programmers life little bit easier. But there are some complexity when you want to generate web report in VS2005. I am totally failure to generate web report from design view created DataSet. May be there exists some bugs.

Now i can generate any kind of web report using Crystal Report including cross tab reporting just changing the strategy. Here i try to describe my strategy of report generation.

Step 1: Create dataSet of the whole database. Then save this dataset as XML format in the disk.

public void CreateDS()
{
MySqlDataReader reader = null;
DataSet rptDS = new DataSet();
string tblName;
MySqlConnection myCon;

//Used previous crated Connection
myCon = this.dbCon;

MySqlCommand cmd = new MySqlCommand("SHOW TABLES", dbCon);
try
{
reader = cmd.ExecuteReader();

while (reader.Read())
{
tblName = reader.GetString(0);
//Fill DataSet
this.FillDSWithTable("select * from " + tblName, tblName, ref rptDS, myCon);

}
// Write Dataset to disk AS XML
rptDS.WriteXml("E:/MyDataSet.XML", XmlWriteMode.WriteSchema);
lblMsg.Text = "Completed ....";
}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine("Failed to populate table list: " + ex.Message);
}
finally
{
if (reader != null) reader.Close();
}

public void FillDSWithTable(string sqlStr, string dsTblName,ref DataSet rptDS, MySqlConnection dbCon)
{
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, dbCon);
da.Fill(rptDS, dsTblName);

}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
}//end FillDSWithTable

Step 2: Now design Report using newly crated XML. I want to use XML data source.
a. Add Crystal report in your ASP.NET project.
b. Select Standard option
c. Create New ADO.NET Connection

d. Select Table

e. Select Fields

f. Press Finish. Then Report is generated

Step 3: Now Display this report in ASP.NET page. This time you need to change the data source of the Report file in the run time by Creating new data set. you also need Crystal Report Viewer to display the report.
a. Add a New aspx page
b. Add Crystal report Viewer in that page
c. Write below code in code behind page

protected void Page_Load(object sender, EventArgs e)
{
dbCon = DALMysql.Connection;
CreateReport();
}
public void CreateReport()
{
ReportDocument myReport = new ReportDocument();</code>

DataSet rptDS = new DataSet();
MySqlConnection temCon;
temCon = this.dbCon;
//load Report
myReport.Load(MapPath("~/" + "CrystalReport.rpt"));

//Create Dataset
//here we use single table. if there mutiple table we need to use loop for every table
this.FillDSWithTable("select * from MyTable", MyTable, ref rptDS, temCon);

//Set Report Data Source
//here we use single table. if there mutiple table we need to use loop for every table
myReport.Database.Tables[0].SetDataSource(rptDS.Tables[myReport.Database.Tables[0].Name]);

CrystalReportViewer1.ReportSource = myReport;
}//end Create Report

public void FillDSWithTable(string sqlStr, string dsTblName,ref DataSet rptDS, MySqlConnection dbCon)
{
try
{
MySqlDataAdapter da = new MySqlDataAdapter(sqlStr, dbCon);
da.Fill(rptDS, dsTblName);

}
catch (MySqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
}
}//end Func

Add two reference at top the page

using MySql.Data.MySqlClient;
using CrystalDecisions.CrystalReports.Engine;

d. Run the page.

You can create report using ODBC connection following same approach. But, it will create many unwanted problem specially when you want to use cross tab reporting.
You can check following links for more help,
http://dev.mysql.com/doc/refman/5.0/en/myodbc-examples-tools-with-crystalreports.html
http://www.codeproject.com/KB/database/Le_Quang_Anh.aspx

If this post do any help, please comments.

Thanks
Khayer
GIS Programmer, CEGIS
Bangladesh