Use Of Crystal Reports Shared Variables

Shared variables are used within Crystal Reports to pass information between various sections of a report and its subreports.

There are lots of "gotchas" when using shared variables, so you must be sure to follow the rules. The most important one to remember is that you cannot use a shared variable before it has been calculated and used on the report.

For example, if you create in the main report:

Shared NumberVar x := 1776;

You will not be able to see the value in the subreport until you actually put the value in the main report,above the subreport. To test this, try putting the value in the report header versus the report footer. Each "band" of the report processes in a particular order, this order must be followed for the shared variable to initialize and display properly in a subsequent report section.

For example: report header, page header, group header 1, group header 2, details, group footer 2, group footer 1 containing subreport, report footer. In this example, you could initialize the variable anywhere in any main-report section except the last two.

In this example, you would have to initialize your variable in the report header for it to appear in the subreport: report header, page header, group header 1 containing subreport, details, group footer 1, report footer.

Record Selection Formula Error

Today I will discuss  very Important Topic related to Your Record selection Formula or Group Selection formula.Today I faced different type of Problem in my record selection from which every time I tried report not open and errors occurs. I checked  my Record Selection formula but I did’t find.I wrote the Formula like this :

//{vDynaCube_EnquiryBkgSalesRpt.userId} = {?Staff_Id} and

//{vDynaCube_EnquiryBkgSalesRpt.EnquiryDate} in {?FrmEnquiryDate} to {?ToEnquirydate}

IF {?Report For}='ENQUIRY' THEN

({vDynaCube_EnquiryBkgSalesRpt.EnquiryDate} in ({?FrmEnquiryDate} to {?ToEnquirydate})) and

(if {?Staff_Id}=0 then ({vDynaCube_EnquiryBkgSalesRpt.EnquiryDate} in ({?FrmEnquiryDate} to {?ToEnquirydate})) else {vDynaCube_EnquiryBkgSalesRpt.userId}={?Staff_Id} )

ELSE IF {?Report For}='JOURNEY' THEN

({vDynaCube_EnquiryBkgSalesRpt.JourneyDate} in ({?FrmEnquiryDate} to {?ToEnquirydate}))and

(if {?Staff_Id}=0 then ({vDynaCube_EnquiryBkgSalesRpt.JourneyDate} in ({?FrmEnquiryDate} to {?ToEnquirydate})) else {vDynaCube_EnquiryBkgSalesRpt.userId}={?Staff_Id} )

In this Formula there is a Parameter Which name is Report for .And In this formula I fixed two Constant Value which was ‘Enquiry’ & ‘Journey’.It means if user will select the Enquiry  then data will come according to between   enquiry date and Staff_id.

Similarly for the else Case. All is ok But Error msg was Coming  which was :

//{vDynaCube_EnquiryBkgSalesRpt.userId} = {?Staff_Id} and   fields not found.

I Confused that where I m wrong. After that I Deleted the Commented Statement which was :

//{vDynaCube_EnquiryBkgSalesRpt.userId} = {?Staff_Id} and

//{vDynaCube_EnquiryBkgSalesRpt.EnquiryDate} in {?FrmEnquiryDate} to {?ToEnquirydate}

First two lines of the Record selection but it was commented then I again run my report now it run properly without any error.

Then I got that “Every Line whether it is Commented Or Not gives their Effect On the Record Selection Formula.”

Tips Of the Days : Not Leave any Commented Statement On Your Record Selection formula.

Display/ Passing Subreport Value into Main Report in Crystal Report

When using a sub report, you may wish to pass a result (such as a computed field, summary, or running total) back to the main report. A shared variable can be used to do this as long as you understand the order in which the results are calculated.

To use a subreport value in the main report, you must calculate it first - that is, the subreport must appear "above" its use in the main report. If this does not work with your report structure, you may have to put an extra copy of your subreport in a higher section.

For example, place this in the main report header section:

Computed item:

 whileprintingrecords;

 Shared NumberVar x := 0;

Subreport:

 whileprintingrecords;

 Shared Numbervar x:= sum({yourtable.yourcolumn})

Color each of these items with white text and shrink to the smallest size possible. Do not use the "suppress" option, as this prevents the item from being calculated.

Adding a Hyperlink in Crystal Report

One of the most valuable features of Web-based content is the capability to quickly move between pages by using hyperlinks. By clicking a hyperlink associated with a word, a phrase, or an image, you can instantly display a new page that provides more detail.

Crystal Reports enables you to add hyperlinks to your reports without your having to become a HTML scripter. Here's how to do it:

1. In your report, select the object that you want to turn into a hyperlink and then click the Insert Hyperlink icon on the Expert Tools toolbar. (Or right-click the selected object and choose Format Field.)

The Hyperlink tab of Format Editor appears. If the Hyperlink icon on the Expert Tools toolbar appears dimmed, you can't use the object you selected as a hyperlink.

2. Select the type of hyperlink that you want.

3. To link to a Web site, type a Web site address in the Hyperlink Information box.

4. Click OK.

You can link to several places other than Web sites. Here's a brief description of the options available:

·         No Hyperlink: Removes a hyperlink from the selected object.

·         A Website on the Internet: Links to a Web site.

·         Current Website Field Value: Select this when you want to link to the URL contained as a value in the selected object. This option appears dimmed if no URL is contained as a value in the selected object.

·         An E-mail Address: Enables users to send e-mail messages to a recipient that you specify.

·         A File: Links to a file on the user's computer.

·         Current E-mail Field Value: Select this when you want to send an e-mail message to the address contained as a value in the selected object. This option appears dimmed if no address is contained as a value in the selected object.

·         Report Part Drilldown: Specifies which detail object is displayed when the user drills down on a report part. (Report parts are explained in the next section.) This type of hyperlink works only with DHTML viewers such as Internet Explorer 4.0 and above or Netscape Navigator 4.72 and above.

·         Another Report Object: Links directly to the object that the user specifies. The destination object may be in this report or in another report. Details are up next.

To link to a Web site on the Internet, follow these steps:

1. Open the source report and select the object that you want as the originator of the hyperlink.

2. Click the Insert Hyperlink icon.

The Hyperlink tab of Format Editor appears.

3. In the Hyperlink Type area, select A Website on the Internet.

4. In the Hyperlink Information area, fill in the URL of the Web site to which you want to link.

5. Click OK to establish the link.

Now when the cursor hovers over the source object, it turns into the hyperlink hand. Clicking the source object launches your default browser and displays the target Web site.

Connect Crystal Report with MS SQL DB

Environment details:

=====================================

Crystal Reports 2008 Sp3.

SQL server 2005.

Steps:

=====================================

A) Creating a DSN:

--------------------------------------

          1. Go to 'Administrative tools' -> 'Data Sources(ODBC)'.

          2. Select the 'System DSN' tab. Click 'Add'.

          3. Select 'SQL Server' driver and click 'Finish'.

          4. Provide the DSN name, say ProjectVegas. Select the database from the dropdown list.

          5. Click on 'Next' and provide authentication.

          6. Click 'Next' and 'Finish'.

B) In the Crystal Reports 2008 designer:

--------------------------------------

          1. Open the report in Design mode.

          2. Go to the 'Field Explorer' -> right click on the 'Database Fields' -> 'Set Datasource Location'.

          3. Create a new connection with the DSN created in Section B.

          4. Update the main report connection.

          5. Preview the report.

          6. Pass the parameter value and click on Apply.

Connecting to MySQL with Crystal Reports XI

CrystalReports is not too into the open source scene, consequently it doesn’t work with MySQL out of the box. But you can easily get the Crystal Reports Designer to work against a MySQL database by using JDBC.

1. Download the MySQL Connector J jar file. That download should contain a jar file that looks something like:mysql-connector-java-3.1.14-bin.jar
2. Add the location of your newly downloaded jar file to the Classpath, as defined in CrystalReports CRConfig.xml file. On a Windows machine, the config file will be located somewhere like:C:\Program Files\Business Objects\Common\3.5\java\CRConfig.xml
3. Once you have altered your CRConfig.xml, close and reopen Crystal Reports.
4. From the menu: File -> New -> Standard Report
5. In the “Available Data Sources” list, double-click to expand “Create New Connection”
6. Double-click to expand “JDBC (JNDI)”
7. Double-click “Make New Connection”
8. Connection URL: “jdbc:mysql://db.example.com/dbname” (Use your own database host name and db name).
9. Database Classname: “com.mysql.jdbc.Driver”
10. Click “Next”
11. Enter a database user/password combination when prompted.
12. You should now be able to inspect the tables/columns in the database to begin reporting.

If the connection is not allowed, make sure your granted access settings in MySQL allow you to connect from wherever you are. Try connecting using the vanilla MySQL command line client.

"Next" and "Previous" Functions in Crystal Report

Today  I am going to write about  the "Next" and  "Previous" Functions available with Crystal Reports.  As their names describe the intent of these functions is to provide you a value of your selected field for either  the Next or the Previous record as shown in your report.

For example if I wanted to determine the number of days it has been since a History record has been written against an Account in SalesLogix,  I could use these two functions along with the DateDiff function  to organize our data.  Let me give you an idea of how!

First, create a SalesLogix report with the History table.  Make sure to create two groups, group 1 is for the Account Name - sorted ascending, group 2 is by History Complete Date - sorted ascending by second.  The group level fields are used in the report.  Use your Section Expert to have group1 overlay group2.  Set the fields in the two groups so they would line up into columns if they were next to each other.  Also,  I  suggest suppressing all group footers and the detail section in your report.

Now create a formula field called "Next" and add the following formula:

IF {HISTORY.ACCOUNTNAME} = Next ({HISTORY.ACCOUNTNAME}) then CSTR(Next ({HISTORY.COMPLETEDDATE})) else ""

This formula states if the current records Account Name does not equal the next records Account name then do not show the next records Complete date.   If you do not include the account comparison in the formula then "Next" will display a value for the next record even if it is not from the same account. 

Add this field anywhere in History Group 2.  If you put this field by the complete date you will see that the field displays the date of the next records complete date.  The last record under the group for the Account will be blank.  What I want is this last History record that was added for this Account and none of the others so I will use this blank field to my advantage.  My group 2 suppression formula looks something like this.

{@Next}<>""

This should bring you down to just one record per account.  Since none of the data showing has a "Next" value so remove that field from the report.

Let's go ahead and create another field called "Previous" using the following formula:

IF {HISTORY.ACCOUNTNAME} = Previous ({HISTORY.ACCOUNTNAME}) then CSTR(CDATE(Previous ({HISTORY.COMPLETEDDATE}))) else ""

This field is very similar to the "Next".  Add this field to the report and you will see that you now have the date of the previous History record.  If the value is blank then there is not a previous record in the system

So you now know the Complete date of  the last History record along with the Complete Date of  record prior to the last History record, but how long has it been since the last History record?  Create one final formula field called "Days Since" using this formula:

DateDiff ("d",{HISTORY.COMPLETEDDATE},CurrentDate)

Add this to your group 2 and you will have all the information you need to see what Accounts are in need of some attention.