Categories
Crystal Reports Printers Ripplestone Ripplestone Tips Web Viewer

Printer Setting for Crystal Reports and Ripplestone

When publishing a Crystal report to Ripplestone, it helps to change the printer settings for the report to use the No Printer option.

When you create a new Crystal Report you normally set the report to print on a local printer.  While the report will run great for this printer it might not run when you publish it to Ripplestone and run the report using the web viewer.

A common message would be:

The page size was not large enough to format the contents of an object in the report.

Failed to export the report.

The reason for this error is that the web viewer has different margin and sizes that the printer that was selected and when the report is run it’s not able to fit in the new web viewer size.

To fix this problem open the Page Setup dialog box from within Crystal Reports and select the “No Printer (optimize for screen display)” option at the top of the box.  Save the report and make sure that it can be run without an errors.  If you get display errors then you can either change the margins on the Page Setup dialog box and/or change the layout of the report to fit within the new report dimensions.  After you get everything working in the report you can re-publish the report to Ripplestone and it should be able to be displayed in the web viewer and the pdf viewer.

image

Categories
Crystal Reports new release Ripplestone

Ripplestone Release 2.9.10

With Ripplestone version 2.9.10 we have concentrated on improving the Report Parameters page and the Scheduler.  These two are some of the most used sections of Ripplestone and benefit the most of getting new features

Report Parameters

  • Changed the Format for Date Ranges – When a report parameter is a date range type, the display for the dates will be in a more user friendly format.
  • Changed the Format for Parameters with a Single Default Value – When a report has a parameter that only has one default value, the display will be more like the display in Crystal Reports XI.
  • Changed the Format for Discrete and Range Type Parameters – When a report has a parameter that is a discrete and range type, the parameter page will look more like the parameter page that is displayed by Crystal Reports XI.
  • Updated the JavaScript to Fix Changes in Internet Explorer 8 – Internet Explorer 8 is stricter in how it uses JavaScript so the JavaScript code was updated to allow Internet Explorer 8 to work better with the parameters.
  • Parameters That Have Commas in the Parameter Value – If the report parameter is a single discrete value and the value has a comma in it, Ripplestone will correctly use the comma and not split the value into two or more values.

Below is a screen shot of the new parameter page with examples of some of the new formats.

Improved Parameter Page

Example of our newly improved Parameter Page

 

Scheduler Changes

  • Added Daily Option to Select Which Days of the Week to Run – When a scheduled job is set to run daily, the scheduler can specify which days of the week they would like the job to run. For example, they could have the job only run Monday – Friday or they could have the job run on Saturday and Sunday.
  • Adding New Scheduled Jobs From the Home Page Made Easier – When adding a new scheduled job from the home, the scheduled job will select the report on the Folder & Parameters tab so you don’t have to find it within the tree. It will also have the parameters for the report displayed.
  • Changed Run As Role Dropdown to Show All Roles for the Super User – If the scheduler is a Super User, then they will see all the roles in the Run as Role dropdown list. If they are an administrator or Scheduler they will only see the Roles they are allowed to see.

New Daily Option – This allows the scheduler to select the days of the week that the job should run.

ScheduleSelectDays

 

Email System

  • Added an Option to use SMTP Authentication – Added a new option for systems that use a SMTP Authentication and need to supply a user name and password to send email.

Security

  • Add Correct User Security When Adding New Folders – Changed the adding of new folders to use the current user to determine if they are a Super User or an Administrator. If they are an Administrator then they will get access to the newly created folder. Before only the Super User would get access to the new folder.

Bug Fixes

  • Hide the Password Fields in the Edit User Page – If the user has been marked as an Integrated Security user, then the password fields will not be displayed since they are not needed.
  • Updated the Record Selection Formula for Alert Reports – In some cases adding the alert code to the report’s record selection would cause an error. The formula has been changed to allow the alert code to be correctly added in those cases.
Categories
Crystal Reports Ripplestone

Ripplestone Release 2.9.9 – Summary

Overview

With version 2.9.9, Ripplestone continues to improve the user experience with changes to the home page that make it easier for users to navigate and find the Crystal Reports and Office Documents that they use most often. For the administrator we have added many new features that allow them to provide more flexibility for their users.

We will continually strive to look for ways to make Ripplestone the easiest tool for managing Crystal Reports and Microsoft Office Documents.

Home Page Improvements

Added Search to the Home Page

Added a search function to the home page that will allow users to enter a value into a Search Box and any reports with the value in the Title will be displayed in a list on the right side of the Home page.

Added My Document Feature

Allow user to select documents or reports that they use often as favorites and these documents and reports will be listed on the home page for the user.

Display the published documents when a folder is clicked

On the home page, when a folder is clicked the right pane will display all the documents and reports for that folder and allow the user to run/open then from the list. This allows the user to quickly run the report without first clicking on the report in the left side tree and then clicking the Run Report link.

Right Click Support

On the Home page users can right click on the navigation tree and get context menus with common actions.

Scheduler Changes

Performance Tune the Scheduler

The time it takes for the scheduler to check for jobs to run will be faster.

Added New Export Options to the Scheduler

Added new export options that were made available in Crystal Reports XI.

Clean the list of scheduled documents in the history folder

Allow an Administrator or Super User to set a number of documents to keep in the scheduled history or to set the duration in days for documents to be kept in the scheduled history. This can be set globally and then over ridden for individual documents.

Added “First Day After” Option to the Scheduler

For scheduled jobs that run Monthly or Quarterly, there is a new option to run the report on the first day of the month or quarter.

Administrator Document And Home Page Changes

Add option to open a report in pdf format

Allow a report to be opened in a pdf viewer instead of the Crystal Reports Web Viewer. This will be an option on the Edit Document page.

Added a new tab called Published Folder on the Edit Document Page

On the edit document page, a new tab was added called Published Folder. This new folder is used to select the document or report to be published and to select the folder that the document or report will be published to. The folder select is now using a tree that makes finding the folder easier.

Move a published document or report from the Edit Document Page

Added the ability for a Publisher or Administrator to move a document or report to another folder from the Edit Document Page. Before the document would need to be deleted and then re-published to move the document or report.

Edit the Web Viewer Print and Export options from the Edit Document Page

If the report is using the Crystal Reports Web Viewer, allow the user to edit the print and export options from the Edit Document page. The list of reports that are using print and/or export restrictions can still be viewed from the Administration page, but the Adding and Editing is now done on the Edit Document page.

Allow the Title and Text for the right side of the Home page to be changed

Using the System Setup and Configuration page allow an Administrator or Super User to change the title and the text that is displayed on the right pane of the Home page when Ripplestone is started and when the root node of the tree is selected.

Security

Allow the administrator to define what a role can do

When an administrator creates a role, let them have a list of tasks that can be assigned to a role. For example, the administrator could create department administrators that can add users and folders but only to their defined list of folders.

Allow role security to be at a folder level

Allow the administrator to assign the role security (Admin, Publisher, and Scheduler) at a folder level. For example, a role might allow users to view 3 folders within Ripplestone, but only allow them to be a scheduler on one of those folders. On the other two folders they can only view the reports.

Check the user’s security for each folder

When a folder is selected the security is checked for the user to see if they can publish, schedule or only run the documents and reports published in that folder.

Bug Fixes

Fixed a bug for saving the Password

On the Edit User page, there was a bug that did not save the password when a password was entered and then the user moved to another tab and then saved the User page. This page now correctly saves the password.

Miscellaneous

Added a link to Delete All Trace Logs

On the Trace Logs page, a link was added to delete all trace logs.

Categories
Crystal Reports Ripplestone Ripplestone Tips

Truncating Fields in the Crystal Reports Web Viewer

The Problem

There are times when building a Crystal Report that you want a field to be short and if the data is too long for the field for it to be truncated.  This works fine when developing the report in the Crystal Reports Designer, but when you publish the report to Ripplestone and run the report the data in the field will overlap with the field(s) to the right. 

The screenshot below is from the Crystal Reports Web Viewer and the HTML that is generates allows the data to “spill-over” to the two fields to the right of the Title field.

FieldOverlapWebViewer

What you want it to look like is this.  The screen shot below is the same report, but the viewer in Ripplestone was changed to pdf.  When exporting to pdf Crystal Reports does the proper thing and truncates the data in the Title field to the length that was set at design time.

FieldPDFViewer

 

The Solution

To fix the problem we need to head back to the Crystal Reports Designer and make a change to the field that is overflowing (the Title field in the example).  Open the Format Editor by right clicking on the field and selecting the Format Field option.  On the Common tab we want to check the Can Grow option and set the Maximum number of lines to 1.

CRFormatEditor

After making the change to the report you can re-publish the report to Ripplestone and the field should be truncated and will fit the set width.  Below is the report in the Crystal Reports web viewer after the Can Grow option has been checked.

FieldAfterCanGrow

 

Conclusion

If you have a field in a Crystal Report that needs to be a specified width and cannot flow over to other fields and you need to view the report in the Crystal Reports Web Viewer you will need to set the Can Grow option along with the Maximum number of lines option.  Setting these two options in the Crystal Reports designer and then publishing that report to Ripplestone will cause the field to be truncated when displayed with the Crystal Reports Web Viewer.

Thanks to Patrick Kansa for help with this solution.

Categories
Crystal Reports Ripplestone Ripplestone Tips

Using Wildcard Parameters with Crystal Reports and Ripplestone

I have been working on the next version of Ripplestone and a client had asked about if and how Ripplestone will handle wildcard parameters in a Crystal Report.  I had not used wildcard parameters for awhile so I decided to dig in and build some reports with wildcard parameters and see how they worked when published to Ripplestone.

Creating the Crystal Report

For the report, I made a simple report that would have a parameter for a last name.  The report would use this parameter in the record selection and allow the user to enter wildcard in the format of “LY*” or “LY??” and have the report retrieve records for all the last names that either begin with LY or have four characters in the last name and they begin with LY.

To begin, I created the parameter with the settings to Allow custom values and Allow discrete values set to True.

image

 

Next I created the selection using the Select Expert and using the field called BillingLastName set the selection type to be “is like” and used the parameter called LastName as the value.

image

 

Running the report within Crystal Reports the user will get a parameter prompt and I entered the value of “LY*”.

image

 

The results from this simple report display all the records from the table where the last name begins with “LY”

image

 

Publish and Run the Report in Ripplestone

After creating the report in Crystal Reports, it is time to publish the report in Ripplestone and see how the wildcard parameters work.

Publishing the report was the same as any other report, select the report and give it a title click the Publish button.  Once the report was I clicked the Run Report link and got the parameter page.  The page prompts for  the last name and once again I entered “LY*” for the value.  Clicking the Run Report button will then pass the value to the report and the report will refresh the data for the report (in this case the data source was SQL Server) and display the report to the user within their web browser.

image

The report is returned as a pdf in the web browser.  The Crystal Report uses the wildcard parameter the same when running from Ripplestone as it does when running directly from the Crystal Report designer.

image

Categories
Crystal Reports Crystal Reports Formulas Ripplestone Ripplestone Tips

Converting Numbers to Dates in Crystal Reports

While working with a Ripplestone client today that had a Crystal Reports report that had data that was a date but the database stored the date as a number in the format YYYYMMDD (20090924 for example).  They wanted the date to be displayed in Ripplestone as MM/DD/YYYY (09/24/2009).

In their copy of Crystal Reports Designer they had changed the default number format to only display the number with no formatting.  They then used the following formula to convert the number into a date format:

Mid (ToText ({Database Field}), 5,2) + “/” + Right ( ToText({Database Field}),2) + “/” + Left (ToText ({Database Field}), 4)

This worked great when the number was 20090924, the formula split the number into the following 3 numbers 09, 24 and 2009 and then created the date by adding the “/” between the numbers.

The problem started once the report was published to Ripplestone and run.  The dates on the report were displaying as 90/00/20,0

After a lot of looking at the formula we discovered that the default number for the Crystal Reports Runtime Engine is to add commas and 2 decimals to the number, so that it was displaying as 20,090,924.00

When this was run with the formula, the 3 number were 90, 00 and 20,0.

To fix this problem we needed to remove the formatting from the number before the Mid, Right and Left functions were applied.  To remove the formatting of the number I added extra attributes to the ToText function.  The first attribute was the “#” as the second value.  This removes the commas from the number.  The next attribute was a zero (0) as the third value.  This will set the decimals to be zero and remove the “.00” from the end of the number.  The revised formula is below, with the changes highlighted in red.

Mid (ToText ({Database Field}, “#”, 0), 5,2) + “/” + Right ( ToText({Database Field}, “#”, 0),2) + “/” + Left (ToText ({Database Field}, “#”, 0), 4)

This allowed the number to be formatted correct and when the report was run or scheduled from Ripplestone the dates are displayed in the correct format.

Categories
Crystal Reports IIS Ripplestone Vista Windows Server 2008

Running Ripplestone on Vista and Windows Server 2008

I was working with a Ripplestone client to get the software installed on a Vista PC and had a do a few things different than if this was XP Pro or Windows Server 2003.  The main differences were that Vista and Windows Server 2008 use IIS 7 and the .NET framework 1.1 is not installed by default.

I started with the following article on setting up Vista and Windows 2008 to run an ASP.NET 1.1 application.

Install ASP.NET 1.1 with IIS7

Once I got ASP.NET 1.1 installed and configured, I need to make sure that Crystal Reports was installed correctly with the Ripplestone installer.  This was just a matter of checking the permissions and testing the running of a report within Ripplestone.

All together it took an extra 30 minutes to get everything installed and configured correctly.

Categories
Crystal Reports Performance Tips Ripplestone Stored Procedures

Crystal Report Performance Tip – Stored Procedures

 [ad name=”Adsense-Blog Post”]

As I work with clients and their Crystal Reports, one of the items that will come up sooner than later is how to increase the performance of their reports.  The performance issue becomes more noticeable once the reports are published into Ripplestone and the users are interactively running the reports.  If the report takes 5-10 minutes to run the users will let you know that the reports are “taking forever” to run.

One of the suggestions I usually have is to look into using stored procedures to do a lot of the work and have the stored procedure return a table that is easy to report from.  What I mean by “easy to report from” is that the table returned by the stored procedure has as much processing completed within the stored procedure so the report will not need to do the processing.

One of the techniques that I have used often is to have the stored procedure create a temporary table that I will fill with the data needed for the report and return the temporary table as the output of the stored procedure.

To create the temporary table, you declare the table name and the fields that the table will contain.  The example below is for a financial report for profit and loss.

Declare @tblProfitAndLossSummary Table
    (LocationName varchar(50),
    ActualExpense smallmoney,
    ActualRevenue smallmoney,
    NonProductivityPercentage decimal(9,1),
    PaidProductivity decimal(9,1),
    WorkedProductivity decimal(9,1),
    HourlyRate smallmoney)

If the data that will be going into the table will be coming from multiple locations or tables then you might need to do some type of calculation within the stored procedure.  In the above Profit and Loss example I needed to create a SQL Server cursor and then loop through the records and insert a record into the temporary table for each record within the cursor.

        /* Initialize the variables */
        SELECT @ActualRevenue = 0
        SELECT @RegularHours = 0
        SELECT @ActualExpense = 0
        SELECT @NonProductivityPercentage = 0
        SELECT @PaidProductivity = 0
        SELECT @WorkedProductivity = 0

To hold the data for each record, I created a variable for each of the fields in the temporary table and then would insert the data into the temporary table with the following code.

INSERT INTO @tblProfitAndLossSummary
(LocationName,
ActualExpense,
ActualRevenue,
NonProductivityPercentage,
PaidProductivity,
WorkedProductivity,
HourlyRate)
VALUES
(@LocationName,
@ActualExpense,
@ActualRevenue,
@NonProductivityPercentage,
@PaidProductivity,
@WorkedProductivity,
@HourlyRate)

I was then able to run calculations within the Cursor loop such as

/* This gets the Revenue amount for a store */
SELECT @ActualRevenue = SUM(BillingCharges.ChargeAmount)
FROM BillingCharges INNER JOIN BillTo ON BillingCharges.BillNumber = BillTo.BillNumber
WHERE (BillTo.StoreNumber = @StoreNumber)
AND (BillingCharges.PeriodFrom >= CONVERT(DATETIME, @StartDate, 102)) AND (BillingCharges.PeriodTo <= CONVERT(DATETIME, @EndDate, 102))

/* Calculate the Regular Hours */
SELECT @RegularHours = ((@RegularTime + @Category1Time) / 60)

In the above example there were many additional queries and calculations needed to get the values for the 7 fields being inserted into the temporary table.

In the end all the data that the report needs is contained in the temporary table and the last step would be to return the data with a final SELECT statement.

SELECT LocationName,
    SUM(ActualExpense) AS Expense,
    ActualRevenue,
    AVG(NonProductivityPercentage) AS AvgNonProdPercent,
    AVG(PaidProductivity) AS AvgPaidProd,
    AVG(WorkedProductivity) AS AvgWorkedProd,
    AVG(HourlyRate) AS AvgHourlyRate
FROM @tblProfitAndLossSummary
GROUP BY LocationName, ActualRevenue
ORDER BY LocationName

RETURN

The crystal report has the added bonus of being easy to format since most of the data is ready for the report.

In this example the report before the stored procedure was very slow with many formulas and sub-reports.  After the report was re-written with the stored procedure it runs from within Ripplestone in under 5 seconds.

Categories
Crystal Reports Microsoft Access Ripplestone

Change the Crystal Reports Set Datasource Location for Ripplestone

One of the more common questions I get from Ripplestone users is how to change where the Crystal Report is getting the data from for the report.  Or a related question is that after a report is published to Ripplestone it gets an error stating that the report can no longer find the data source.  This is very common when the data source is a file based data source such as Microsoft Access, Excel or a dBase database.

First, let me step back an explain why Ripplestone will display the error when the report is published to Ripplestone.  Then we can fix the report to make it work within Ripplestone.

When creating a report that uses a file base database or a file like Excel, the report will use a hard coded path to the file.  In the screen shot below, the report is getting the Access database from a network share.  This report will work great as long as it’s run from the local network and the user running the report has permissions to the network share.  This is the location used to develop the report.

The problem is that when this report is published to Ripplestone, the Ripplestone server will also need to be able to access the database to refresh the report with current data.  The Ripplestone web application also runs under the Network Service local account and that account will need permission granted to access the database.

In the following example, the report was published to a external web server that did not have access to the network share, so it would display an error within Ripplestone stating that the database could not be found.

To solve the problem the database location needed to be changed within the Crystal Report using the Set Database Location dialog box.  The dialog box allows the report to be updated to a different database at a different location.  In the above example the database was moved to the report server at D:\Data\TMS\Applications2009.mdb and the TMS folder was given permission to the Network Service user to read from the folder.

Crystal Reports Set Database Location

As you can see in the about screen shot I create a new database connection in the bottom half with the new database location and then pressed the Update button to update the main report and the sub-reports with the new database.  I was then able to publish the updated Crystal Report to Ripplestone and the report will be able to location and report from the database in the D:\Data\TMS folder.

Categories
Crystal Reports Ripplestone

Crystal Reports Group Ranges with Specified Order feature

While working on a report for a Ripplestone client I was able to use the Crystal Report Specified Order option of the Group Expert.  I thought I’d share the process of using the Specified Order feature and how I used it to solve a problem that I was having with the report.

The report was a Crystal Report that would display a list of students for a class and needed to be grouped by the students age.  It was easy enough to group the age by using the Group Expert, but that creates a group for each age.  I needed to have the children grouped by a age range and not by the exact age of the children.

To create a range of ages I clicked on the Options button to open the Change Group Options and then selected “in specified order” from the group by options dropdown.

ChangeGroupOptions_tab1

This activated the Specified Order tab on the dialog box.  On this tab I was able to type the group names in the combo box at the top and enter the 3 groups that I needed.  This created 3 empty groups, so the next step would be to assign values to each of the groups.  This would be the range of ages that I wanted for each of the three groups.

ChangeGroupOptions_tab3

To create the age ranges for each of the 3 groups I clicked on the Edit button to open the Define Named Group dialog box and selected “is between” from the list of options.  Then needed to enter the values for the range of ages.  In the example below the report will groups all the children between the ages of 7 and 9 into the groups called “Ages 7-9”.

DefineNamedGroup

After running the records were grouped into the 3 age ranges with totals for each group.

ReportPreview

If you have questions or comments, please let me know.