FRD files do not open in Drill Down Viewer on WinXP IE6/7

>This problem took me 6 hours to figure out so I thought it was worth a post….

My organization needed a way to distribute FRX reports via a website. Since we are a SharePoint 2007 shop, we decided to place all of the reports in a document library. Our thought was that anyone with the FRX drill down viewer could navigate to the doc lib and click on the report file and it would open in its native application. It turns out this was not the case.
We originally tested on Win Vista SP1 and IE7. Uploading the .frd file to the doc lib and opening it from IE worked great. After the first demonstration meeting we discovered that machines running Windows XP had to first save the file to their computer rather than opening directly from the website. For some reason on XP IE would show the .frd file as a zip folder rather than a frx file.
To further troubleshoot we discovered that browsing with Firefox allowed the file to open directly without saving first.
After requesting help from a trusted college we determined that it had to be an IE / XP issue and had nothing to do with SharePoint. We were wrong.
After several hours of research, I came across a few articles in which this happens to office 2007 documents on a SharePoint 2003 Implementation. The resolution to that problem has to do with registering all of the office 2007 MIME types. This led met to ms KB article 326965 “IIS 6 does not serve unknown MIME types” Following the instructions I tried deifining a MIME type for specific application and using /application/octet-stream. This didn’t work either. Then I stumbeled across a google post that suggested defining a MIME type and using /application/???.
I am not an IIS expert and I do not know what the ??? actually does except that it resolved my issue. If someone can explain this, I would love to know.
The only reason I can come up with for it not affecting vista has to do with the way IE7 on vista handles file associations. If you select tools, internet options, programs, you get a set programs button in Vista.
if you do the same on IE7 on XP you get a significantly different choice with no way to manage “internet programs”
Again, if anyone has an explanation on the differences in IE handling of file extensions on XP vs vista I would love to hear what you have to say.

Comparing Last Year Period to This Year in Crystal Reports

>This was one of the toughest things I had to figure out in Crystal because there are very few examples out there on the internet on how to have columns in a report that compare the previous year values to this year.

The challenge:
My CFO wanted a report that showed waste receipts (we are a landfill) that had several columns that looked something like this:

Last Year to date
This year to date
Variance in tons
Variance in %
Previous Year Last 30 Days
This Year Last 30 Days
Variance in Tons
Variance in Percent
Total Receipts Last Year
Forecast Receipts for this year

So I thought how in the world do you do this because crystal only has one detail section and in my head I couldn’t figure out how you would compare two years values with only one query. Here is what I found.

Crystal has built in functions to calculate what period a date is in. Which is great but the logic on how to apply it was escaping me. What I ended up doing is a detail section that looks like this:

I only needed a couple of fields from the database record, the date of the Transaction [TimeOut] the Transaction number [Ticket] and the Tons Received [Tons]

I had to hide the Tons field because we are going to use this for calculations but since a total on tons would be a total for all query results there is no need to use it. Then for each of the period fields I created formula fields using the build in period functions of crystal for example Last Year to Date Tons [LYTD_Tons] is

If {Ticket.TimeOut} in LastYearYTD Then

So now I have a tons field that will only display a value when the date of the transaction is in last year to date. Otherwise this value will be a zero. By applying this same logic to the other period fields I can create summary fields that sum all of the period based fields. I placed this summary in the group for each customer. So what I get is a row on the report that shows Customer Name, LYTD, PYMTD, MTD, LY Total. Now that I have these fields I can create additional summaries that calucate the variance from last year.

The end result looks something like this:

In my humble opinion this is truly a work of art. If you have questions or would like more detail please add comments

Crystal Reports Date Difference


I have searched and searched over the internet many times trying to perfomr the simple task fo calculating the difference between two dates in Crystal Reports. The result I was looking for had to be formatted in DD:HH:MM:SS. After hours of searching here is how I did it.

Create a formula field that gets the total number of seconds between the start date and completed date:

DATEDIFF(“s”, {Incidents.Start_Date},{Incidents.Completed} )

I then used a second formula field to perform the arithmetic to calculate the remainders and format the output:

NumberVar TotalSec := {@TotalSeconds};
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec , 86400) / 3600) ;
NumberVar Minutes := Truncate (Remainder ( TotalSec , 3600) / 60) ;
NumberVar Seconds := Remainder (TotalSec , 60) ;
Totext ( Days , ‘##’ ) + ‘:’ +
Totext ( Hours , ’00’ ) + ‘:’ +
Totext ( Minutes , ’00’ ) + ‘:’ +
Totext ( Seconds , ’00’ )

There that works!! It seemed like it took forever to find the answer to this problem!
Up Next is how to do this based on a work calendar!