PDF Export

July 4, 2017 hung-nb Web App 0

Crystal Report – Working with rpt file

How to display text on footer of last page only?

  • Format Object >> Supress Formula[html]if PageNumber = TotalPageCount then false else true[/html]

How to write Crystal Report formulas?

  • ubound, split, for, left, mid[js]stringvar sample := {Table.notesos};
    numbervar counter := ubound(split(sample,”<“))-1;
    numbervar i;for i := 1 to counter do(
    numbervar openbracket := instr(sample,”<“);
    numbervar closebracket := instr(sample,”>”);
    sample := left(sample,openbracket-1) & mid(sample,closebracket+1)
    );
    sample;[/js]
  • Line break in a string[js]’New line break’ + chr(10) + chr(13) + ‘testing'[/js]
  • Replace string 
    Replace '12,345.00' to '12345'

    [js]
    StringVar value:= ToText({Table.value});
    StringVar ReplaceString := Replace (value, “,”, “”);
    ReplaceString := Replace (ReplaceString, “.00”, “”);
    ReplaceString;[/js]

  • “If/Else”, “Case”, “isnull”[js]
    // Background Color
    if {Table2.columnname} = ‘ABC’ then
    select ToText({Table2.sample1})
    case ‘B’: crYellow
    case ‘C’: RGB(255,184,113)
    case ‘A’: crGreen
    case ‘X’: crRed
    default: crWhite
    else
    crWhite[/js][js]if not isnull({Table.field1}) then
    select ToText({Table.field1})
    case ‘B’: crYellow
    case ‘C’: RGB(255,184,113)
    case ‘A’: crGreen
    case ‘X’: crRed
    default: crWhite
    else
    crWhite[/js]

How to dynamically change font size of a Crystal Report field basing on the field length?

  • Apply a conditional format to the field based on the length of the data of the field
    • To change dymically the font size of a field:
      Select the required field(s)
    • From the Format menu select the Format Field option
      Click on the Font Tab
    • Press the [x+2] button to the right of the Size drop down
      Enter the following formula:[js]IF Len({Customer.Customer Name})>15 Then 8 else
      IF Len({Customer.Customer Name}) in 10 to 15 Then 9 else 10[/js]
    • Press the Save and Close button

How to display fields horizontally instead of vertical layout?

  • “Section Expert” > Select “Details” > Check on “Format with Multiple Columns”
  • > Select “Layout” tab > Set “Width” as 5.150 “cm” > Check on “Across then Down”, “Format Groups with multiple column”

Crystal Report – Working with DB model

How to create XSD for a report

  • Add .xsd file to project
    Right click on the project and select Add-> Add new item and select the Data Set
  • Set this XSD file to rpt file
    Open rpt file >> Database Expert
    Create New Connection >> ADO.NET (XML) >> Make New Connection
    File Path: Browse the XSD file >> Finish
    Back to rpt file >> Right click on “Database Fields” >> Set Datasource Location
    Select the new connection you have just created
  • Reference
    https://www.codeproject.com/Articles/18332/Crystal-Report-Basics-and-Integration-with-DataSet

How to set text color in HTML

Crystal Report can’t display HTML tag like this
[html]
require
[/html]
You need to change the HTML tag into
[html]
require
[/html]

Issue

  • Connection Information OLE DB (ADO) dialog appears everytime verifying DATABASE
  • Crystal Report doesn’t work when running on different server

Cause

This is because DB logon information is different at every server

How to Create Crystal Report using Visual Studio

Solution

Find out the way to dynamically change the DB logon information. Using “SetDatabaseLogon” does not worked, you have to use “ApplyLogOnInfo”. However, when using ApplyLogOnInfo, “missing parameter values” error may occur.
You have to change logon information of sub report first and use ConnectionInfo like this
[js]
{
…

TableLogOnInfo logOnInfo = new TableLogOnInfo();
ConnectionInfo connectionInfo =
CreateConnection(AppSettings.GetAppSetting(“ServerName”), AppSettings.GetAppSetting(“Database”), AppSettings.GetAppSetting(“UserName”), AppSettings.GetAppSetting(“Password”));

// Sub report
ReportDocument rptSub = new ReportDocument();
for (int i = 0; i < rpt.Subreports.Count; i++) { rptSub = rpt.OpenSubreport(rpt.Subreports[i].Name); for (int j = 0; j < rptSub.Database.Tables.Count; j++) { logOnInfo = rptSub.Database.Tables[j].LogOnInfo; logOnInfo.ConnectionInfo = connectionInfo; rptSub.Database.Tables[j].ApplyLogOnInfo(logOnInfo); } } // Main report for (int i = 0; i < rpt.Database.Tables.Count; i++) { logOnInfo = rpt.Database.Tables[i].LogOnInfo; logOnInfo.ConnectionInfo = connectionInfo; rpt.Database.Tables[i].ApplyLogOnInfo(logOnInfo); } rpt.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, Path.Combine(strPhysicalDirectory, FileName)); ... } private static ConnectionInfo CreateConnection(string Server, string Database, string Username, string Password) { ConnectionInfo connectionInfo = new ConnectionInfo(); string connString = "DRIVER={SQL Server};SERVER=" + Server + ";DATABASE=" + Database + ";UID=" + Username + ";PWD=" + Password; connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.CONNINFO_DATABASE_DLL, DbConnectionAttributes.DATABASE_DLL_CRDB_ODBC)); connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.QE_DATABASE_NAME, Database)); connectionInfo.Attributes.Collection.Add(new NameValuePair2("QE_DatabaseType", "ODBC (RDO)")); DbConnectionAttributes attributes = new DbConnectionAttributes(); attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.CONNINFO_CONNECTION_STRING, connString)); attributes.Collection.Add(new NameValuePair2("Server", Server)); attributes.Collection.Add(new NameValuePair2("UseDSNProperties", false)); connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.QE_LOGON_PROPERTIES, attributes)); connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.QE_SERVER_DESCRIPTION, Server)); connectionInfo.Attributes.Collection.Add(new NameValuePair2("QE_SQLDB", true)); connectionInfo.Attributes.Collection.Add(new NameValuePair2(DbConnectionAttributes.CONNINFO_SSO_ENABLED, false)); connectionInfo.IntegratedSecurity = false; connectionInfo.UserID = Username; connectionInfo.Password = Password; connectionInfo.ServerName = Server; connectionInfo.DatabaseName = Database; connectionInfo.Type = ConnectionInfoType.CRQE; return connectionInfo; } [/js]

Error: A document processed by the JRC engine cannot be opened in the C++ stack

This is because the rpt file is not found
1.Right click the Rpt file.
2.Choose the properties
3.Changed
a. Build Action as “Content”
b. Copy to Output Directory as “Do not Copy”
c. Put Empty for Custom Tool and Custom Tool Namespace

Create a Crystal Report – Concept

Connect directly to DB with embeded queries in rpt file

Crystal Report
1. Create rpt file
2. Create DB model (xml file or dataset file(.xsd)
3. Construct queries directly in rpt file after connecting to DB

C# – Backend code
1. ReportDocument.ExportToDisk()

Connect indirectly to DB by calling a stored procedure

Crystal Report
1. Create rpt file
2. Create DB model (xml file or dataset file(.xsd)

SQL Server
1. Create Stored Proc

C# – Backend code
1. Call stored procedure
2. ReportDocument.SetDataSource(DataSet)
3. ReportDocument.ExportToDisk()

HTML To PDF

Use Evo PDF Converter

How to merge pdf files?

Use PDFSharp

How to compress pdf file?

Use Neevia, bitmiracle PDF Compress

Be the first to comment

Leave a Reply

Your email address will not be published.


*