Ad Hoc and Embedded Reporting Solutions

In software terms, extensibility means a program‘s behavior can be changed without access to the source code. eWebReports makes this possible in four key ways. To assure control and data security, these modifications are controlled entirely by Administrators. This post will give a brief description of each way eWebReports is extensible and provide links to blog posts that detail these features.

Custom Data Access

Currently eWebReports supports Microsoft SQL server, MySQL, Oracle, PostgreSQL, XML and Excel files.  However if your database keeps all your information in proprietary code reports to end users may not be particularly useful. Also you may want to report data from an unsupported data source such as DB2 or Microsoft Access. With a little bit of programing a Web Service or .Net Assembly can access these databases or format the data as desired.

Custom Functions

Suppose you want to give users access to functions you have created to manipulate their data. eWebReports allows you to define custom functions in several high level languages. This feature gives users the tools they need to perform industry specific analytics.

Styling eWebReports

The default style of eWebReports looks great here at Exágo. It was designed to match our orange and yellow company theme. But your users should have a single experience and never suspect they are using a third party tool. To accomplish this eWebReports can be styled with the UI and custom CSS to match blend seamlessly into your product.

Single Sign on – API

Styling eWebReports is only the beginning of shaping a consistent user experience. All of the standard eWebReports is built on an API that you have access to. Using the API eWebReports can be integrated into your application.

Summary

Using code eWebReports can access unsupported data sources, make functions, change styles and integrate into your application. These features demonstrate eWebReports’ incredible extensibility.

Happy Reporting

eWebReports makes it easy to set up connections to data sources and join tables. As any system admin knows all too well that for best performance and results it is vital to make these joins carefully. This post will demonstrate how in eWebReports Administrators assign weights to joins to avoid Cartesian products, and how Report Designers and Power users, if given permission, can modify joins of a single report to reach only the desired data. This demonstration uses four tables holding information about students, classes, and professors of the non-existent Exágo University.

Changing Joins on a Single Report

At Exágo University most professors advise students but some don’t. Knowing this the Administrator correctly used a Left Outer join between these tables in the Admin Console.

However Dean Wormer (a power user) wants a report that shows only the professors that advise students. Though the Dean does not know it, he wants this report to use an inner join. Instead of making the Administrator waste time changing joins, eWebReports lets power users make these changes for individual reports. Power users and report designers only can affect joins if the permissions are set in the General Tab of the Admin Console (see below).

Now in the report designer Mr. Wormer can make the changes he wants. The Dean can make this change even though he may not know what joins are or how they differ.  He clicks ‘Advanced’ and is presented with an intuitive menu using plain English and simple check boxes.

Because he only wants Professors who do advise students he unchecks the box that says ‘include professors that don’t have a student’. eWebReports understands that by unchecking the box he wants an inner join.

For comparison here is the report before and after the Dean changed the join:

Before:
After:
Weights: Avoiding Cartesian Products

As Dean Wormer notices from report there are no human rights advisers. To fix this problem he runs a report listing all the students in Introduction to Human Rights (see below).

Each student is listed four times. This error is an example of a cartesian product caused by how the data was set up. The Administrator made the joins in a way that gives the student table two possible paths to join to the intro to human rights table (see below).

To fix this the Administrator can give a weight to the correct join in the Admin Console.

This weight tells eWebReports to use the more direct join path. Since the joins are kept in reports, the joins should be reset in the report designer. To do this we bring up the advanced menu and click on ‘More’.

Then click on ‘Recreate’.

Now when we run the report, eWebReports uses the correct join path and gives Dean Wormer the information he desires.

Summary

Using weights Administrators specify how eWebReports should join together tables. If the Admin chooses to give permission power users and report designers can make changes to these joins. The menus in the report designer use plain English so power users do not need any technical knowledge to make these changes.

Happy Reporting

Almost all ad hoc reports do much more than display raw data. Formulas take data as input for calculations, security and formatting and give reports real business value. These formulas can be as simple as displaying the month of a date element or quite complicated financial calculations. eWebReports provides an intuitive formula editor as well as the ability to create custom formulas.  This post, meant for Report Designers and Power Users, will discuss and show examples of five functions.

5 Functions

• If – Allows reports to build in some logic. The If function takes three parameters: a condition followed by two values. If the condition evaluates to True then the first value is used, if not the second value is used. This diagram below explains it nicely. In our example the if function checks to make sure the report does not write a blank line if the address does not have a second street: “if({Addresses.Street 2}=”,newline(), newline() & {Addresses.Street 2}& newline())”.

• Today – Returns the current date when the report is run. Does not take any parameters.
• NewLine – Jumps to the beginning of the next line in text. Does not take any parameters.
• DateDiff – Returns the amount of time between two dates. The DateDiff function takes three parameters: a string and two dates. The string tells the eWebReports what unit to measure the difference in. “yyyy” returns years, “d” days, “w” weeks, “m” months, “h” hours, “n” minutes, “s” seconds, “q” quarters.
• LoadImage – Loads an image from the connection string parameter. This allows you to keep images on a server and load them dynamically instead of keeping them in the report.

Example

The report designer show how these five functions  can be used to draft a reminder letter. The NewLine and If functions help format the address. Today and DateDiff calculate the number of days left to apply. Finally LoadImage places a picture into our letter.

To see what these formulas produce we run the report as a PDF.

Summary

This post highlighted just five out of the many formulas offered by eWebReports. Leveraging these formulas and those that you define will bring analytics and real business value to your data.

Happy Reporting.

eWebReports connects very easily to many data sources: MySQL, SQL, Oracle, PostreSQL, just to name a few. However suppose you have your data in SQLite, or Simple DB. eWebReports can still connect to your database by way of a web service. This post will demonstrate a Web Service that uses c# to create and populate an employee table. Because web services are setup outside of eWebReports and connected in the administration console, this post is meant for Administrators. It will detail the requirements for your code, show an example of a simple web service, then walk through how to connect it to eWebReports.

Benefits

Web services can do more than allow you to connect to nonstandard databases. With a web service you can use any high level language to filter, sort and manipulate data on the database before sending it to eWebReports. . To make web services more dynamic eWebReports can pass user specified filter and sort strings to the web service. So user specified manipulation can be done on the server to enhance performance.

Requirements

Like most code, web services consist of classes and methods. eWebReports interprets classes as data sources and methods as data objects. Each method must have a parameter whose name matches the Call Type Parameter Name in the general tab of the Admin Console.

eWebReports will expect the web service to return the schema if callType is 0, the full dataset and schema if callType is 1 and values for the filter drop down list if callType is 2. The data data/schema/filter values must be returned as a serialized XML string.

Example

using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data;
using System.Xml;
using System.IO;

namespace ExagoWebServiceExample

{
[WebService(Namespace = "http://localhost/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// The class will be interpreted as a Data Source
public class WebServiceExample : System.Web.Services.WebService
{
[WebMethod]
// This method will be interpreted as a Data Object
public string websvc_EmployeeHire(int callType)
{
// Setting up example data object schema
DataSet ds = new DataSet(“Header”);
DataTable dt = ds.Tables.Add(“SubHeader”);

             // Setting up example data object schema
dt.Columns.Add(“Last Name”, typeof(string));
dt.Columns.Add(“Employee Number”, typeof(int));
dt.Columns.Add(“Hire Date”, typeof(DateTime));

switch (callType)
{
case 0:
// returning the data object schema
return ds.GetXmlSchema();
case 1:
// adding some example data
dt.Rows.Add(“Consumer”, 249, “2010-10-18″);
dt.Rows.Add(“Keys”, 26, “2005-06-25″);
dt.Rows.Add(“Jones”, 151, “2009-12-31″);
dt.Rows.Add(“Smith”, 2, “2004-10-10″);
// returning data object rows
return ds.GetXml();
default:
return null;
}
}
}
}

Connecting a Web Service

Once the web service is created, eWebReports can connect to it much like a database. Open the Administration Console. In the Data Source menu click New. Give the web service a name then select ‘websvc’ from the drop down menu.

For the Connection String enter url= [the URL of your web service]. Click the green check mark to verify the connection.

Now that the web service is connected each method can be made into a data object. In the data object menu click New. A list of data sources will appear. Drop down the Web Service then the Methods and a select the method you want as an object, and then click OK.

Everything after this is the same as using a standard database as your source. For more information on Data Objects and Joins see Your First Ad Hoc Report.

Previously we have discussed how ad hoc reporting can be embedded into your application. In that post we showed the report designer as part of a webpage. The style of eWebReports had been changed to match the webpage. You can see the difference between that example and the default Exágo theme.

This post will go over how you can style eWebReports to perfectly match the look of your application. Our goal will be to create a very green style for a Park Management company. We will do this in two steps.

Step 1: The Administration Console
The administration console allows you to change the style of the major elements of eWebReports such as background, logo, text color, etc. To begin navigate to the Style section of the admin console.

Before changing the logo copy the new logo into the Config folder of eWebReports. It is important that the new logo is copied into the Config folder. The image will be lost if it is saved elsewhere in eWebReports and an upgraded or reinstall is run. With this done we can begin to modify the style. Click on the element you want to change and a menu bar will appear. Click on the logo ‘Welcome to eWebReports’ and add the file path of our new logo. Hit enter and our logo should appear as shown below.

Next we will change the header image by clicking the grey bar along the top of the sample screen. In this example we will replace it with a nearly identical gradient that is green instead of orange. So far we have this.

Now we want to change the background color and selected text color. When you click on either of these elements a color selection tool appears. Choose a new background color using this tool or by entering a RGB hex value or any standard css color name.

Finally we want to change the orange buttons to have white backgrounds. Click Save and navigate to the home page. Seeing this page it is clear that we want to make more changes. To make further changes we must move beyond the admin console.

Step 2: Customizing CSS
Important: Make sure you are using eWebReports 2011.1 build 18 or newer. The latest version of eWebReports can be found at our support site.

We have made all the changes we can in the Admin console but there are still style changes we want to make. To do this we utilize the cascading nature of Cascading Style Sheets (CSS). Tough CSS is fairly intuitive you can learn more about CSS here.
Create a text file and save it in the config folder as WebReportsCustom.css. This file will be loaded after the standard WebReports.css allowing for us to make changes to element classes or individual elements.
As an example we will change all the buttons to have green text. In WebReportsCustom.css enter

.button1a, .button1
{
     color:green;
}
Save this file and refresh the home page. Looking at the Create New Report button we see that our change has been made.

Inside the same brackets add ‘border: solid 1px green;’. Again the change can be seen by saving the CSS file and refreshing the page.

With this code in place all the buttons are green with green borders. However using CSS we can also modify individual elements. We may do this to call attention to a specific element like the Create New Report Button. Using a web developer tool find the ID of the button you want to change, in this case it is NewReportBtn. In the WebReportsCustom.css file add the text
#NewReportBtn
{
font-size:14pt;
border: solid 3px blue;
color:blue;
}.
Now we can see that the Create New Report Button pops out but all the other buttons are remain green.

Summary
To craft a single user experience, eWebReport must match the look of your application. The admin console makes changing the largest visual elements quick and simple. Spending a little time and creating custom CSS allows you to change the design down to individual details. With these two features end users can have access to all the functionality of ad hoc reporting without ever suspecting they are using something that is not entirely your creation.
Happy reporting.

In almost every company there are different levels of personnel. Personnel in various levels require different amounts of access to business information. As an example imagine a retail company with branches around the world. Salesmen should be able to get reports on her/his clients, products and quotas but certainly should not have access to the same information as the board of directors or CEO. Also a regional manager in Mumbai India will want to see different currency symbols, and date formats than a manager in Muncie Indiana. eWebReports offers a solution to such issues by allowing you to create roles for specific users or classes of users.

Creating Roles and Controlling Access

This post will utilize the Administration Console to create four roles and modify permissions and cultural settings. However, roles are typically created through the Application Programmer Interface (API) which allows for access to correspond with the permissions a user has in your application.

Navigate to the roles section of the Admin Console. Click the New button to create a new role.

A row will appear below. Click in that row to give the role an ID. Roles can be constructed to be used by a single user or by a group of users who all have the same permissions. In this example we will create three roles: Regional Managers – USA, Regional Managers – India, and CEO – Mr. Brody. The CEO role is specific for one individual (Mr. Brody) while the Regional Managers roles can be used applied to many users.

To the right of each role ID are five check boxes to make large permissions adjustments. For the CEO role check ‘Include All Folders’, ‘Allow Folder Management’ and ‘Include All Data Objects’.  For the Regional Manager Roles check ‘Folders All Read Only’ and ‘Allow Folder Management’.  The menu should appear as:

With these settings the CEO has full access to all the reports of the company while the Regional Managers cannot access or run any reports. To give regional managers the ability to access their respective folders we specify those folders in the Folders menus. With the Regional Manager – USA role selected, click the ‘New’ button above the folder menu. Click inside the row that appears and select the folder Sales Information – USA. We will leave ’Read Only’ unchecked as we want our regional managers to be able to edit their reports. The folders menu should now appear as:

Repeat this process for the Indian regional manager role. Note that for a role which has ‘Include All Folders’ putting a folder into the Folders menu will hide it from the user.

The Data Objects and Data Object Rows menus work in the same way as the Folders menu. For security a user must have access to every data object on a report in order to edit that report. The Data Object Rows menu allows data objects to be filtered so a user only sees information appropriate for her/him. In this example that means filtering employee information by country so the US and India managers only see their respective staff even though all the information is in the same table.

User Specific Cultural Settings

With these security measures set we can move on to specify cultural settings by role. Using the General menu we can overwrite the cultural settings to fit each role. In India the rupee (₹) is used as currency but in Indiana the US dollar ($) is used.  Similarly these locations write dates in different formats. So with the Regional Manager – USA role selected set the Global Date Format to dd-MMM-yy, the global time format to h:mm:ss AM/PM and the Currency symbol to $. For the Regional Manager- India role set MM/dd/yy, h:mm:ss, and ₹ for their respective parameters. For the USA role the General Menu should appear as:

Below is a sample of the same report run from each of the two roles. You can see the differences in the date and currency formats.

USA:

India:

Summary

Roles ensure your information is secure by only giving users access to the information you want them to have. This control can make reports executable but not editable or specify which rows in a data object a user can see. Roles can also be used to modify cultural settings for different users. Since most web-applications have users log in, roles are typically made by the API as they can leverage the log in information. As is true with all the administrative aspects of eWebReports, the roles feature is designed to be invisible to the end user, ensuring an easy reporting experience.

Happy reporting.

This week’s post will highlight two advanced features of eWebReports. First we will show how administrators can use Excel Files as a data source. Using the data from an excel file we will demonstrate how Report Designers and Power Users can utilize Group Min/Max filters to specify the most recent information available. Our sample data will be patient and cleaning information for the nonexistent dentistry office Smile Bright. Our final report will use the data from the excel worksheets to create a reminder letter telling each patient if it has been more than six months since his or her last cleaning.

Using Excel Files

eWebReports can read excel files as though each worksheet is a table.  Remember though that excel files are not databases. Simply put they do not offer the speed, performance, security or heavy lifting of a real database. Using excel files is recommended only if your data set is small or someone provides the information in this format.

Excel as a Data Source

Let’s start with the excel file. eWebReports uses the worksheet name as table titles, the top row as column headers, and the remaining cells as the data. Do not leave any blank rows or columns. Our Smile Bright example has two tables: patients and cleanings.

To add an excel file to your data sources you must edit the xml config file installed with eWebReports. After the </general> tag, add the following and replace the name and connection string with your own file path:

<datasource>
<id>0</id>
<name>xls_Smile Bright</name>
<dbtype>file</dbtype>
<dataconnstr>File=C:testexample.xls;Type=excel;</dataconnstr>
 </datasource>

Save this file and go to the admin console. You will see this.

You can click the green check mark to verify that your excel file is connected. eWebReports treats excel files just like any other data source.  You can add the data objects and create the appropriate joins in the admin console as you would with an oracle, SQL or other databases.

Building Our Dentist Letter

Now that we have the data in eWebReports we must set up the letter to send each patient. For reasons that will become clear later, as we go through the New Report Wizard we sort on the field Patient ID. Also here we can filter the patients who have come in in the last six months. In the filter tab drag and drop the field Days since App, choose greater than or equal to and then key in 180. Click finish and the report designer will appear.

For brevity we will skip over formatting the letter and simply say that it makes extensive use of the Concatenate( ) function. It also uses a group header section on our sorted field PatientID. For details click on the image below. The report is fairly basic so you should be able to recreate it.

Our Problem

So it looks like we should be done. We’ve got all our data from the excel file and we’ve formatted our letter. However when we hit run we get this:

The letter tells us that Janet Chambers has had three most recent visits. This isn’t possible and needs to be fixed.

The Solution

The text for each patient repeats multiple times. This happens because most patients have had several past cleanings and it has been more than six months since every cleaning. We want to restrict our focus to the MOST RECENT cleaning and ignore all the others. To do this we can use Min/Max filters.

Before we try to use these filters we need they must be enabled in the general tab of the administration console.

Once this is set to True we notice a change in the Filter Menu of the report designer.

Clicking this link will bring up a min/max filter menu. These filters find the minimum or maximum value of a sorted data field. Min/max filters require a sorted field, thus why we sorted on PatientID when creating the report.

Since we want the most recent appointment each patient had, we use a maximum filter  and specify the field PatientID (see below). If we were working with a text field instead of a datetime field, a maximum filter would leave the text closest to Z in alphabetic order. At the bottom of the menu the summary makes it very clear what the min/max filter will produce.

With this filter set, we execute the report and see that the letter is fixed and ready to remind people of their somewhat neglected teeth.

Happy reporting.

eWebReports offers a very intuitive and easy to use report designer. However it is not uncommon to want information from your database to be added onto forms or invoices other companies, governments or clients provide. It would be a huge waste of time to try and recreate these forms yourself when all you want to do is fill them in.This problem can be solved with the Document Template tool in the report designer. This post will cover how to make a receipt template with a watermarked background and have eWebReports build a PDF that has been filled in with sales information from our database.

If you are working with a template that has already been created put it in your report folder and skip the Set Up section of this post.

Set Up

Because we are not starting with a form provided by an outside source we must create a receipt template. We do this in MS Word leaving the spaces we want filled in with as __________’s. In our word document we can place a water mark image. Since we are making a receipt we will have it say “PAID” in large translucent letters behind the text. Our word document will look like this:

Using MS Word we save this file as a PDF. Although you can do the next step in any editor you like, we recommend using Adobe Acrobat Pro. Open the PDF file and click ‘Forms’ and ‘Add or Edit Fields’. Acrobat Pro will prompt to automatically make fields for most underlined spaces. Whether you specify the fields yourself or let Acrobat do the work it is important to give the fields intelligible names. If a field will be mapped to a repeating item (in this example Price will map to OrderDetails.Price) you must make several fields and use the naming convention name.0, name.1, etc. Once the fields are made, save the template to your Reports folder (the path specified in the Admin console).

Using Document Template Tool

Create a report with all the information you will want to have appear on your template. As shown in our example the report can include formulas. The result of the formula will be used to populate your template.

Our receipt contains order information such as the id and date, customer contact and shipping details, and which employee handled the transaction. In the Details Section (which repeats for each item in the order) we list the item, price, quantity, discount and total (using some simple formulas). Because it is possible to execute this report as a format other than  PDF we added in a logo and titles. For information on how to include all these elements into a report see our previous post on creating your first report.

Now we open the Document Template tool. Simply hover over the advanced tools and select the document template tool (pictured below).

This will bring up a menu to map the fields of the form to cells on your report. At the top of the menu use the drop down to select the template we want to use. Below this drop down are two columns. When you select a template, the left column will populate with the fields of the form. Each cell in the right column is a drop down menu we can use to select the cell from our report we want to map to the corresponding field on the left.  We then click ‘OK’. Now when we execute this report as a PDF it will populate the template instead of executing as a normal report would. Our finished receipt looks like this:

More Uses of the Document Template Tool

Though we created a PDF, the Document Template tool can be used to complete Excel or RTF templates. Other common uses of the Document Template tool are completing government forms such as W-2’s, W-4’s, 1099’s or placing data into precise locations of a blank template as to print on top of paper forms.

Happy Reporting.

So far this blog has focused on the features of eWebReports that involve building reports. This week we turn our attention to blending eWebReports into your application. This can be as simple as changing the color of the provided User Interface (UI) to match your company style. However with the Application Program Interface (API) your development team can embed dashboards, report buttons, and the report designer so power users can edit reports. All these additions can be made so users have a single experience while reporting with eWebReports.

We have made a small website for the imaginary company International Resellers to demonstrate how eWebReports can be integrated. This post will not cover the technical details of using the API. Instead we will discuss four ways to seamlessly integrate eWebReports into your web application. Note our website uses the .NET API but the Web Service API could be used instead to make the exact same website.

User Specific Reporting

Many common web applications have users log in to their program. If you have users log in the API can set permissions specific to each user. These permissions control which reports a user sees, edits, and specify what information a user can access at the row level. Setting user specific permissions is a very important feature of the API and can be used in conjunction with the other methods of integration.

Dashboards

Some of your end users just want the information a report contains. They aren’t interested in setting filters or saving the report in one of various formats. These users are looking for a dashboard.  The homepage of the International Reseller mockup contains four dashboards displaying summary graphs and tables for the company.

Each dashboard is a report that is executed and placed in a div when the page is loaded. That means the display is dynamic and shows the most up to date information to your end user.  You create the report and the div it goes into. So you have complete control over the style of the dashboard and can make it match your application perfectly.

Selecting From a List

While dashboards suffice for some, many end users will want to save reports as a pdf, excel or rtf file.  In such a situation you can create a standard set of report and make them available either in a dropdown menu or as buttons on a page. Both these techniques are show below.

In our demo website we make employee reports available as two dropdown menus. Users pick the report they want in the first then the format in the second menu. When they click the execute button the report is downloaded or if they chose HTML the report appears on the webpage.

If a user would like a list of products offered by International Resellers all they have to do is click the format they want under the list they are looking for. Nothing on this page would is specific to eWebReports so an end user would not know they aren’t using your application.

Customized Report Builder

While many end users will be happy with dashboards and buttons, power users will want to use all the features of eWebReports. The eWebReports UI can be styled and customized to blend naturally into your application. This can be done in the Administration Console and then the UI can be loaded into a iframe on your webpage. As pictured below both the eWebReports home page and the report designer can be blended into your application.

Advanced Integration

This mock up website is just meant to spark your imagination as to how eWebReports can be embedded into your application. With the API it is possible for an end user to get the reports they want while maintaining a single experience on your webpage.

Happy reporting.

This week’s post discusses a fundamental element of the report designer: sections. Sections are made up of sets of rows. Placing data in a section specifies to eWebReports where on the report that data should appear and how it should be interpreted. By design most of the sections are extremely intuitive. For example the Report Header appears once at the beginning of the report. In this post though, we will focus on Repeating Group Sections (RGS) to show how advanced reports can be created. This post is meant for report designers, power users and administrators who have a hand in creating reports.

Why use Repeating Group Sections?

Repeating Group Sections are used when you have multiple one-to-many relationships in your data. In this example each Order can have multiple Products, Discounts, Suppliers and Categories. Repeating Group Sections allow us to completely display all the product information for each order then all the discount information then the supplier information etc. In addition to letting us display the information in the way we want, repeating groups provide an easy way to analyze on very specific pieces of information. In our example we calculate the total revenue, the total discount, the average unit price and the average discount for each order.

Our Report

As we go through the new report wizard add the categories ‘Orders, Order Detail, Products, Suppliers, and Categories’. Repeating Group Sections require a sort, so we will create sort on Orders.OrderID. In the Layout tab we only add OrderID to the report. Initially the report designer will look like this:

We add four columns to provide space for the details of our report. Now we add a Repeating Group Section on OrderID as pictured below.

The first Repeating Group Section shows product information. In the details of the RGS we drag and drop the product name, unit price and quantity. In column E use the formula editor to multiply unit price by quantity to get revenue. This information should be labeled in the header row of the RGS. We are using repeating group sections to be able to calculate sub totals and by order averages. So in the footer of the RGS we insert these formulas. Now our report looks this:

For brevity we add repeating group sections for supplier, discounts, and categories without going into detail. Now the report looks as follows:

Though we have calculations for each order we also may want to get information about the entire report. To do this we will add a report footer section as pictured below.

Now that all the information is in place we format cells and add color to make the report aesthetically pleasing.

In the previous posts we have generated the reports as html. For a change of pace lets execute our report as an excel file.

Now that you have a better understanding of sections you can make very complex and intricate reports with ease.

Happy reporting.