Archive

Archive for June, 2009

Running Ripplestone on Vista and Windows Server 2008

June 16th, 2009 No comments

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.

Crystal Report Performance Tip – Stored Procedures

June 5th, 2009 No comments

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.

Change the Crystal Reports Set Datasource Location for Ripplestone

June 4th, 2009 No comments

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.

Crystal Reports Set Database Location

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.

Welcome to All About Reports

June 2nd, 2009 No comments

Welcome to the All About Report site.  This site is devoted to writing, training, discussing and anything else about reports.  We will be concentrating on Crystal Reports, Microsoft Access, SQL Reporting Services, DevExpress Xtra Reports and any other reporting packages that come across the desk.

We look forword to hearing comments and suggestions.

Categories: General Tags:

Crystal Reports Group Ranges with Specified Order feature

June 1st, 2009 No comments

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.

 GroupExpert

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.