Prophet21 Reporting:

Have you ever wondered what makes up one of those values you see in a P21 report?  Often, the information you are given is a summation of a lot of different values in the Commerce Center database.  For instance, in order to come up with sales for a given period, P21 may sum all of the values for the field "total_amount" in the table "invoice_hdr" for that period and year.

Behind the scenes, there is a "query" being run against the database in "Structured Query Language" (SQL).  This is called a "SQL statement" (either pronounced "ess-que-ell" or "sequel" depending on your db professor).  This SQL statement may look something like this:

SELECT SUM(p21_view_invoice_hdr.total_amount) FROM p21_view_invoice_hdr WHERE p21_view_invoice_hdr.period = 9 AND p21_view_invoice_hdr.year_for_period = 2006

Most of the time, however, the SQL is a lot more complicated that this.  In fact, it is possible to "trace" the actual SQL that is being run behind the scenes when you run a built in P21 report.  Looking at the trace file can be extremely confusing.  There can be an enourmous amount of text in these queries.

The reason for this is that usually we want more information than just the total sales for a given period and year.  We want to know what "makes up" total sales for that period and year.  To add to this, we may also want to know what the outstanding balances are for the period and year.  The result is that the reports (and the SQL statements) can get to be quite lengthy.

Custom Reports

Even with these lengthy reports, sometimes we can't get quite what we're looking for with the built-in P21 reports.  Because of this, many Prophet 21 users spend a lot of time developing custom reports developed directly against the database - usually written in Crystal Reports or in Microsoft Excel.

Custom reporting is a great option if you need specific views of your data that are not provided by P21 directly.  For instance, what if you want to see all sales in 2008 for a specific customer by a specific order taker with your top three product groups?  Although it may be possible to compile that data from a built-in report, it would probably be a lot more simple to create an Excel spreadsheet and generat the report in that way.

There are problems with using Crystal Reports and/or Microsoft Excel for your custom reporting options, however.  For one thing, there is usually not an infrastructure in place to manage these reports.  By managing the reports I mean storing them in such a way that the correct users are able to access the correct reports in some kind of logical way.

In addition to this, often we end up creating several different reports in order to show various "levels" of summary data.  We may create one report that shows total sales by Sales Rep for a period.  Then we may create a second report that lets us see the detail of each sales rep's  sales at the customer level.  Then we may create a third level of reporting that allows us to see the individual sales made to each customer.

What we end up with is usually a directory full of customer Excel spreadsheets, or Crystal Reports.  Usually these custom reports end up with strange and lengthy names like "My Sales By Sales Rep 2 Period 4 for Accounting.xls".  This is not the easiest thing to read through when you're trying to find that one report you wrote for sales that the owner wants to see "right now".

Web-Based, Drillable Reports Using ASP.Net

Wouldn't it be nice if we could create a manageable set of reports that would allow us to drill down on our data from summary information?  Wouldn't it be even better if we could assign these reports to a set of menu uptions that allowed us to get back to a report quickly?

That's exactly what is possible with the ASP.Net 2.0 framework from Microsoft.  The best part is that the process can usually be achieved relatively quickly.

Activant's Prophet21 is written with a SQL Server DB as the back-end.  This is exactly what the .Net framework is optimized for.  In fact, if you are working with Visual Web Developer (or Visual Web Develop Express which is free), you will have access to a pretty nice visual representation of your data.

The trick is finding out what you want to report on, what levels you want to be able to drill down on and to and where exactly you can find that in the enormous P21 DB!

In my next installment of this article series, I will go over setting up the web project in Visual Studio, Connecting to the database (Play of course!), and creating our first report!

Come back soon for the next article.

Andy