Friday, February 03, 2006

Ginsu Reports...

It slices... It dices... It even cuts through cans... not to mention it provides a little reporting now and then.

I am going to take a break from talking specifically about the integration with Eclipse and use the next few posts to talk about more general topics. I know that a lot of people who read this blog have used reporting technologies in the past. Whether it’s been Crystal Reports, WebIntelligence, JasperReports, BIRT, or any other reporting solution, most developers find themselves having a need for formatting and outputting data at some point in their career. I have been working with the Crystal Reports SDKs for over 5 years now, and during this time I have built many applications that lend themselves to the traditional “BI” solution (e.g. charts and listed data), but every now and then I find myself using the SDK/Report Designer to do something that most people wouldn’t think of trying with a reporting engine.

Often, when a developer looks at a requirement, the first thought that jumps to through their head is how they can build the solution. They may google the web to see if there are any code snippets that exist or look into which packages will provide the functionality to make their jobs easier, but in the end the path usually leads them to building their own solution. A few years back, when I was on the Developer Support team, I started thinking “How could this be done with Crystal Reports?”. The result... I ended up with quite an array of unique sample reports/applications. I thought it would be cool to see what the rest of you may have done with reporting, it doesn’t have to have been built with Crystal Reports, just any time that you may have used a reporting tool outside its “normal” boundaries. I have included 3 stories of mine, plus 1 from a colleague that I saw recently.

The Script Generator
As I mentioned earlier, I used to work on the Developer Support team. Back in the CR 8.0 and CR 8.5 days we used to also handle installation issues. Every once in a while a customer would call up because they were having issues with missing PROGIDs from the registry. This usually pointed to unregistered DLL files, normally due to lack of permissions when the product was installed. The solution was to either re-install the product or manually register the specific DLLs. Manually registering was often time consuming and generally a trial and error process. One day, while playing around with Crystal Reports, I noticed it had a database driver that allowed me to report off of the File System. Using this driver to filter the report to return only files with .dll extensions, I created a formula that read essentially "regsvr32 " + filename. I placed the formula in the Details section and exported to a text file. "Voila!" I now had a batch file that cycled through every and attempted to manually register every DLL file in the folder. What essentially took less than a few minutes to create ended up saving me and my customers a lot of time in the long run.

The Custom Calendar Report
This actually started out with a call from a customer who worked for a hotel chain. He wanted to create a calendar to show how many rooms were free on a given day in any month. Unfortunately, Crystal Reports did not come with a built-in Calendar control which allowed users to modify data on a given day. However, I was still able to create a mock-up which the user ended up using to dynamically create a calendar. I used Crystal Reports to create a report that contained 7x6 grid of text boxes. Using the Crystal Reports API I was able to modify the text box values at runtime based on the month and year. The result was a report which displayed a calendar with the number of available rooms for each day. I have since modified the application for a company that wanted to color-code the sales range for a particular day (e.g under $5000 = Red, etc.). This was easy to do once I had the template report already created.

Ebert who???
For those people who know me, most will agree that I am slightly addicted to my DVD movies. I have grown a rather substantial collection over the years, to the point where I now catalog my DVDs in a database. Anyways, as a Product Manager, we are trained to listen to what the market is telling us, this helps us build better product requirements. I have found that I now use this philosophy to pretty much guide my entire decision-making process. Whether I am choosing a restaurant to eat out at, a hotel to stay at or my next digital camera, I always find myself researching the public opinion websites to see what the rest of you have to say. Well, the same holds true with my personal DVD collection. I wanted to have a way to dynamically retrieve the latest public opinion about all my movies. I was able to do this using a Custom User Function Library(UFL) within Crystal Reports. If you haven't had an opportunity to play with these yet, they have some pretty cool capabilities. In a nutshell, UFLs allow users to make call-outs to external logic (Java, COM, C++) from within a report. Anyways, to solve my DVD public opinion issue I built a UFL that will take in the name of each of my DVDs and access the movie's page on IMDB.com. For those of you unfamiliar with IMDB.com, it is an online database of movies which allows the community to rate movies between 1 and 10. My UFL is able to retrieve this rating and return it to the report. This was only the beginning, I actually modified the UFL to return the DVD cover image, the synopsis, and the movie's Genre to my report. Now, when I want to choose a movie I can simply filter the report to return what I feel like that night. For example, I can search for "All Comedies with an average rating of 7.0 or higher". The report will bring back the list of DVDs, it's IMDB average rating, a brief synopsis of each movie and the DVD cover image. Now if only IMDB would add the genre "Chick-flick" I could filter those out before my wife has an opportunity to see the list :) . When "Sisterhood of the Travelling Pants" shows up with an average score of 7.0 on my Comedy list, I will likely find myself hunkering down to watch a bunch of girls share a pair of jeans for the summer if my wife gets hold of the list before me...but that's another story.

Finally, I recently had an opportunity to see something a co-worker was working on where he was using Web Services and our reporting technology to report off of places for sale on Craigslist.com. He was then using another web service to connect to the GoogleMaps API and pass in the address of each place on the market. The end result was a dynamic report which filtered the Craigslist listings and displayed the results on a map of the area. It was really cool.

Anyways, I am sure this is just the tip of the iceberg of what people have done with reporting technologies. I would love to hear what the rest of you have been able to accomplish so don't be afraid to post a reply.

Until next time.

Sean

3 Comments:

At 8:07 AM, February 06, 2006, Blogger Adam Ellis said...

Ok, I have one. The output is still a pretty standard report, but what it has to go though to arrive at it's output is border-line rediculous. I have a customer that wanted a full-on MRP report done in Crystal. It starts with the quantity on hand in all of the "good" warehouses and models the projected quantity on hand into the future taking into account:

- Customer orders consuming that part.
- Purchase orders buying that part.
- Work Order making that part.
- Work orders consuming that part as a material requirement.
- Planned customer orders.
- Planned work orders.

Now it would be easy to take the quantity on hand, add in the supply, and subtract the demand, but a true MRP needs to know on a per-day basis (for the window selected) what the projected balance is going to be and point out to the user when a PO should be expedited to overcome a shortage that occurs on a day in the middle of the window, for example. I chose, for better or worse, to use an array to overcome this problem. The main report is for the part and a subreport is used for each of the elements of the MRP. The array is structured like so:

[EVENT DATE] [TYPE] [ORDER ID] [CUSTOMER/VENDOR NAME] [ORDER DATE] [REQ QTY] [PROJ BALANCE] [CURRENT OP] [STATUS]

Since Crystal does not support multi-demensional arrays, I just seperate the elements with a '|'. So each subreport adds rows to this array. When all of the subreports are completed, it is then sorted by Event Date and the 'PROJ BALANCE' column is calculated. Then I go into another subreport that has a record count equivalent to the size of my array and spit out the results like so (for one element of the array - repeat for each):

Shared StringVar Array arySortedTimePhase;
StringVar Array aryBreakout;

If {@Output}<>"" Then
(aryBreakout := Split(arySortedTimePhase[{#Line Number}], "|");
Mid (aryBreakout[3], 1, 3);)
Else
""

It is crazy, but it works. These kind of reports always make me wish for a better way to "pre-process" my results before they hit Crystal. If I am reporting against a SQL Server, I know I could probably use a stored procedure to do this kind of work. Back to the topic of CR for Eclipse, though, any thoughts on how I could pre-process there?

Take care,
AE

 
At 7:01 AM, February 07, 2006, Blogger Sean said...

Hi Adam,
With CR XI we introduced Java User Function Libraries, which are essentially external java classes that can be called via a Crystal formula. When the Beta of Crystal Reports for Eclipse is released there will be a wizard to assist users in creating the UFL stubs and ensuring that they are visible to the Designer. From what I can tell a UFL should be able to solve your issue as it will allow you to do as much logic as required to generate the result before passing back to the report. It also allows for arguments so if you need to pass in specific data from the current record you can do so via the arguments of the function. In your example, you could pass in the Part information and then do all of the MRP logic in the UFL. This would remove any of the limitations our existing formula language may have (such as multi-dimension arrays). If you want more information on Java UFLs you can check out the following whitepaper:

Using Java UFLs

Do you think this will work for you?

Note: I will have to do some research with the Engineering team to see if there is a way to get the Tech Preview to recognize UFLs, otherwise you may have to download our 30-day evaluation of Crystal Reports XI in the meantime.

Sean

 
At 7:01 AM, December 14, 2006, Anonymous Anonymous said...

I love straight to the point receipts. Thanks for this one!
Ana

 

Post a Comment

<< Home