Building An Inexpensive Customer Document Portal Solution With Crystal Reports, Logicity & OneDrive

There are so many benefits to having a customer portal for your company - from better customer service to less expense around staffing incoming calls; every company could benefit. And while we would love to build you a beautiful, full-featured customer portal solution, we recognize that not all companies have the budget for that sort of endeavor. A client recently asked us to come up with a portal where there was a very limited budget - but there was also a straightforward task, just a place where a customer could download PDFs of their invoices and orders. Never ones to give up easily, we came up with the following solution involving Crystal Reports, Logicity, and OneDrive for Business.

At a high level, Crystal Reports is used to design the output for all of the PDFs we will ultimately be stored in OneDrive for Business. So there will be Crystal Reports for invoices, orders, packlists, etc. Then Logicity is used to hack together a sort of “bursting” of these reports into individual PDFs stored in customer subfolders in OneDrive for Business. Then lastly, customers are given share links to their appropriate folders. Voilá — an inexpensive online document portal! Let’s dig into the details:

Crystal Reports

This part should be pretty simple - you might even already have these done. Simply create Crystal Reports for each of the types of PDFs you wish to store out in the portal. So, for example, you may make an “invoice” Crystal Report that accepts an invoice # as a parameter and returns back a beautiful PDF that gives your customer the data they wish to see. This wasn’t the actual report we used for the project but is an example of how something like this might look:

Enter an order number and the details of that order are returned.

OneDrive for Business

OneDrive for Business is offered as part of Office365 and is a pretty solid deal at $5 / month for 1 TB of storage. It is a cloud file system with a desktop component that keeps everything in sync, much like DropBox or Google Drive. Why we are calling it out specifically here is because it allows subfolder permissions to be assigned to external users which is neat. Feel free to substitute in DropBox here as well, though, if you prefer as both will get the job done. Within the structure I have created a folder called ‘Customer Portals’ and then within that folder I have a subfolder for every customer.

Logicity

Now for the fun part. If you have not used Logicity before, it is a tool that can automate viewing, emailing, printing, and saving Crystal Reports. It does so by creating these resource files called Report Runtime Definitions (RRD) using a bundled tool called Logicity Solution Builder. So the next step is to simply create a RRD for this report that outputs the resulting PDF into the appropriate subfolder in this OneDrive structure:

Just hard code in the parameter value for now.

Also hard code in all of the path information.

Now save the RRD file and open it up in a text editor. You will see something that looks like this:

;WORKSPACE_MODE=FALSE

SaveReport[Dummy]=C:\Actual Report.rpt?DiscardSavedData=True&saveas=C:\Users\aellis\OneDrive - GingerHelp\Customers\Customer Portals\Riders Company\Order Status 44772.pdf&export=Adobe Acrobat (PDF)&pdfPassword=&SaveSupressIfNoRecords=True&param[Order Number]=44132&

The trick now is that we want to make a brand new Crystal Report that pulls up all of the rows of PDFs we want to generate and make the details line output exactly what we have on line 2 of this RRD - just replacing out the customer name and order number with dynamic data. To do so first tweak the page setup on your new report and set it for No Printer with a really wide landscape page width:

We have it set for 30 inches wide here - depending on your paths you may need even more than that.

Now wire the report up to the appropriate datasource with the correct filters and put the text from the RRD into a single large text field, dragging and dropping field values for where it needs to be dynamic:

Now save this report and create a new RRD that runs THIS report and exports the result out to a RRD file:

So now if you run this RRD (and schedule it in the future) it will output a new RRD file named ‘Generator Result.rrd’ with contents that look something like this:

And if you take ‘Generator Result.rrd’ and put it on a schedule as well (just make sure you run it after the first RRD) you will see individual PDFs bursted into the appropriate customer folders:

And with the PDFs being regularly scheduled for output one can now invite customers to individual folders:

Install OneDrive for Business on the desktop PC of your customer service folks so they can easily maintain sharing on a per folder basis.

When the customers log in they will just see the contents of their folder including any subfolders.

And that is basically it. All of this said, a few final thoughts:

• If you have a ton of lines in your ‘Generator’ RRD it may take a VERY long time before it pops up and lets you know it is actually running.

• You should consider trying to limit how many reports you burst out. So as opposed to regenerating every order since forever, do something like orders that have been created or updated today.

• You give up a lot of management and branding with this approach. You may find your customer service folks are constantly being asked to resend share links which would defeat the whole purpose of this process.

Anyway, hope this was helpful to somebody out there. Here is a link to all of the files used in this solution. If you came up with any interesting solutions using this article please let us know in the comments!