Ad Hoc and Embedded Reporting Solutions

Parameters

This post will discuss several uses of Parameters in eWebReports. Parameters serve as place holders for values that will be set at runtime by your application. In a previous post we detailed how parameters can be used in a multi-tenant environment to filter data. In this post we will start by demonstrating how administrators create dynamic SQL with hidden parameters. The second example will show how Report Designers and Power Users can utilize non-hidden parameters in filters and formulas. Hidden parameters are also utilized by Stored Procedures but that is a topic that for a future post.

Creating Parameters

Parameters are created in the data tab of the Administartion Console. Click the ‘New’ button and enter a name to create a parameter. You may specify the type of data tough this is more important for Stored Procedures. In the value column a parameter can be given a default value.  Using the check box you can make a parameter hidden or non-hidden. Non-hidden parameters can be seen by end users if added to any cell of a report. If you want a user to give a value when a report is run, write a prompt text for an unhidden parameter.

Dynamic SQL

Generally data objects are simple tables or views. However in some situations you may want eWebReports to create a data object from SQL. To allow these commands to be dynamic with you application you can insert parameters. To do this click ‘New’ in the Data Object Menu. A blank row will appear, click in it to open the data object pop-up and click ‘Create/Edit SQL Statement’.

In the SQL Data Object menu enter you SQL commands. To insert a parameter select it from the dropdown list and click ‘Insert’. Be sure to put the parameter inside single quotes if its used as a value in the SQL (see image below). When the sql is run eWebReports will fill in the parameter for its value.

Parameters in Reports

Parameters can also be used in filters, formulas or cells of a report. These parameters will only display their value if Hidden is unchecked in the Admin Console.

To put a parameter in a cell or formula type ‘=’ then enter parameter name inside of @ symbols (remember: parameters ARE case sensitive).

If the parameter was given a Prompt Text in the Admin Console a menu will appear when the report is run and prompt the user for a value.

Parameters can also be accessed by filters. Select the filter you want and type the parameter name between @ symbols instead of a specific value.

Conclusion

Wether you use them for dynamic SQL, multi-tenant databases, functions , filters or simply to display on a report, parameters are an important tool to integrate eWebReports into your application.

Happy Reporting!

In a previous post we detailed how roles control which reports a user can run and what data he/she can access. In that post we mentioned that while roles can be set up in the Admin Console they are designed to be used through code at run time. This post will give examples of the .NET API calls necessary to create, modify, and activate a Role when a user signs in.

Creating a Role

Roles can be created using the code:

Role role = api.Roles.NewRole();

The role will automatically be active. If you want to use a role you already created in the Admin Console you can use the method Get Role(‘role id’) and then activate it with the method Activate().

Controlling Folders and Reports

Initially users will have access to all the folders in the report path. You can hide all folders using the code:

role.Security.Folders.IncludeAll = false;

If IncludeAll is false then a specific folder can be displayed by the api call:

Folder folder = role.Security.Folders.NewFolder();
folder.Name = “Travis’ Reports“;

Alternatively if Include all is true the above code will hide that folder.

An individual folder can be made read only to prevent users from editing reports.

Folder folder = role.Security.Folders.NewFolder();
folder.Name = “Travis’ Reports“;
folder.ReadOnly = true;

To make all the folders read only use:

role.Security.Folders.ReadOnly = true;

Setting Data Access

Initially users will have access to all data objects. You can hide all of them using the code:

role.Security.DataObjects.IncludeAll = false;

To then give access to an individual data object use the call:

DataObject dataObject = role.Security.DataObjects.NewDataObject();
dataObject.Name = “<Data Object>“;

If IncludeAll is true then the above code will hide the data object instead.

A data object can be filtered on the row level using the call:

DataObjectRow dataObjectRow = role.Security.DataObjectRows.NewDataObjectRow();
dataObjectRow.ObjectName = “<Data Object>”;
dataObjectRow.FilterString =  ”“<Data Field>” = ’10,250′ “;

Summary

Using these code examples above, you can control each user’s access to data programatically. Because the role is created through your application the user experiences eWebReports as a single sign on.

Happy Reporting.

This post will cover how to set eWebReports so it balances execution across multiple servers. This assures performance and is done in two easy steps. This post will be very brief to emphasize how simple this change is .

Installing the Scheduler

For each Remote Execution Server download the installation file from Exágo support. Run the installer and you will see the menu shown below. Choose the Scheduler option and follow the prompts until the scheduler is installed.

Admin Console

In the General tab of the Admin Console set ‘Enable Remote Report Execution’ to True in the Admin Console

In  ‘Remote Execution Remoting Host’ list the servers you want to use separated by commas (ex. http://MyHttpServer1:2001,tcp://MyTcpServer:2001).

That’s it. It’s really that easy.

Happy Reporting.

eWebReports makes it easy for end users to create reports that follow your business logic. This post will cover how Filter Dependencies make sure Report Designers and Power Users follow your logic when creating filters.

Problem

In the Filter menu below there are two filters; States and Cities. The user has set the filters to exclude everything that is not in the State of New York and not the city  Bridge Port. However we know that Bridgeport is not in New York, or at least it wasn’t the last time we checked!

This means when the report is run no data will qualify. Sure enough when we try and execute the report we see this.

Solution

To solve this dilemma we use a feature called Filter Dependency. In the General Tab of the Admin console set ‘Allow Filter Dependencies’ to True.

This setting changes the drop down values of filters. Now users will only be able to select values that have not been excluded by the previous filters.

As you can see from the image below we set the first Filter on State  to ‘NY’.

Now drag and drop Cities.City into the filter list and select the drop down. Only cities in New York will appear because the filter is now dependent on the state set above it.

Conclusion

Filter dependency are just one of the many ways eWebReports helps assure that end users create valid logical reports without knowing how your data is set up.

Happy Reporting.

Yesterday a user sent me a question as a follow up to this blog post on Auto-Filling Templates. He had successfully made a report that fills in a PDF template but wanted to avoid using Adobe Acrobat. His request was for an explanation of how to fill in a Word template. As such this blog post will cover the elements used to set up both a PDF and Word template.

Directing the Data
Before you can tell eWebReports which cell goes where on the report, you must set up targets on the template. In PDF templates these targets are called fields. Acrobat Pro can make them for you very easily and PDFs provided by outside agencies probably already have them. In Word however these targets are called bookmarks. To create a bookmark select the area where you want data to go, click Insert then click bookmark (see picture below). Targets in word do not have to be blank. eWebReports will replace bookmarked text so we recommend writing labels to show what type of data will go there later.

Repeating Data

The other key step in setting up templates is using proper naming convention. For fields/bookmarks that will only appear once on the template the only consideration is how understandable the name will be to the end user. However for data that repeats must follow a naming convention. Generally repeating data means the data that will be mapped to cells in the Details section of the Report Designer. For PDF  such fields are named name.0, name.1 etc. In Word templates the bookmarks are named name_0, name_1, name_2 etc.

Conclusion

The image above shows a good example of a Word Template being set up. Because there may be multiple discounts on each receipt the Discount bookmarks are named Discount_0, Discount_1, and Discount_2 etc. Once these bookmarks are in place the template gets saved in the Report Path and it can be filled in by eWebReports.

Happy Reporting!

Many of our clients maintain multi-tenant data, where a common data store (such as a set of database tables) houses data for many of their clients.  Each of their clients is considered a “tenant” of the data store.  For reporting purposes, it is of course necessary to restrict access to the data by client.  There are many ways to architect this…we commonly see three:

1) Tenant marshaling is handled by a stored procedure.

2) Mapping parameters to tenant keys in eWebReports.

3) Each client has a separate schema, accessed by changing the connection string.

Number 2 is the most common case, is very simple to implement and is covered in this post.  Numbers 1 and 3 are also fairly straightforward and will be covered in future posts.

Example Data

Consider the following example. A single database houses information for both Exágo University and the School of Reporting Technology (SoRT). The ‘University’ column is used to specify which row belongs to which fictitious college.

Whither eWebReports?

To enable tenant-based access by the ‘University’ column we use an eWebReports parameter.  Parameter values are used fairly frequently in eWebReports for things like runtime filtering, dynamic SQL statements and of course multi-tenancy.  They are defined either in the Administration Console or at runtime using the eWebReports API. In this case we’ll use the Administration Console. We create a parameter “collegeID” and set its initial value to “Exago U”.

For each Data Object listed in the administration console, there is a ‘Tenants’ field in the row for that Data Object.  Clicking in the ‘Tenants’ field for a Data Object brings up a dialog for matching Parameters to Data Columns.  You can add as many as you need to reflect the tenant keys (only one in this case).

Once we complete our actions in the dialog, the Data Object row reflects the new tenant/key matching.

Note: for this example you can ignore the ‘Parameters’ column next to the ‘Tenants’ column; it is used for associating parameter values with programmable data objects like stored procedures or MSSQL table-valued functions.

We save the configuration and eWebReports is ready to report for Exágo U.

Results

Below is an example report design to produce a list of students. Because the parameter value is set to “Exágo U”, only data for Exágo University will appear in the report.

Changing the value of the parameter to “School of Reporting Technology” will cause the report to display only data for students of SoRT.

In an embedded reporting scenario your application can set the parameter value on-the-fly through the eWebReports API, enabling you to base the tenant information on whatever authentication method your application is already using.

Happy Reporting!

As every database administrator knows sometimes data is uncertain. For example some people write their middle names and some have middle names but do not write them and some do not have middle names. Almost all databases allow such values to be Null. While Null data works well with business logic it is not obvious to most end users. To help end-users avoid mistakes common to Null data eWebReports treats null values as blank. This post will show how to handle situations when Null values raise issues in a formula.

Example

For our data we will return to the non-existent Exágo University. Dean Wormer would like a list of students that displays their age. From the student data we can see that there is not an age column but instead birthdays. Also it seems that some students did not provide a birthday (null data).

Using the function DateDiff we can find the age of each student. DateDiff takes three inputs; the desired unit of measurement (in this case ‘yyyy’ for years), and the two dates in question. For our example the final formula will be ‘ =DateDiff(‘yyyy’,{Students.Birthday}, today()) ’. Using this formula we see there is an issue with our report.

The formula is displays the error #NUM! for students that did not write their birthday because it is trying to take the difference of a time and a blank string.

Solution

Because eWebReports converts Null data to blank strings we can handle this case in one of two ways. Either we can use {Students.Birthday} = ‘’ or isBlank({Students.Birthday}). Both statements will return True if the birthday is Null and False if there is a date. We can use either check as the condition of an IF function to handle the error. Click HERE for more help with the IF function. The full formula will be:

=if({Students.Birthday}=”, ‘NA’, DateDiff(‘yyyy’ ,{Students.Birthday}, today()))

Using this formula the report looks much better.

Filtering Null Values

Null values could also require attention in the filter menu. In this case filter on the empty string and eWebReports will understand to exclude null values.  Below is a picture showing the filter and the filtered report.

Conclusion

eWebReport is designed to benefit end users by separating ad-hoc reporting from database logic. To achieve this eWebReports makes it easy for end users to filter and check for null values.

Happy Reporting.

eWebReports runs atop an Application Programmer Interface (API). This means through code developers can utilize the capabilities of eWebReports inside your application. Programmers can use either the .NET API or the Web Service API to integrate into your application.

.NET API vs. Web Service

Developers working in C# use the .NET API. The standard user interface is built upon the .NET API. This means all the functionality of eWebReports is available as code. If your developers do not work in C# you can utilize the web service API. This option offers the flexibility to integrate eWebReports using any high level language. As a tradeoff for the flexibility, the web service offers slightly less functionality and a small dip in performance.

Web Service Example

Here is a sample of code that uses the web service API. This PHP program sets a role (for user specific security) and then executes a report. The file could be called when a user clicks a link or button.

Conclusion

The example above is a very simple demonstration and could be extended to set report types, filters, sorts and much more. All of these features can be dynamic to work with the sign on and user information that already exists within your application. Using either API, eWebReports blends into your application to provide powerful reporting to your end users with a single user experience.

Happy Reporting.

Suppose you have a report like the one below showing customers and how many purchases they have made in the past year.

This report may be simple and easy to understand but by no means has it extracted real business value from your data. For instance it would certainly help to know that B’s Beverages makes all its purchases in the beginning of spring or that last year there were no purchases in February.

This post will walk through modifying the report to show sales broken down by month. In the process we will also highlight a few tips for using the report designer.

Adding Columns and Formatting

The image below shows the report designer for the original report.

Normally to add a column we would click at the top of a column and select ‘Insert Columns Before’ (see below). This command would add a singular column between A and B.

To save time we can add multiple columns at once. Click on A, then while holding shift click the last column (E).  Now when you select ‘Insert columns After’ five columns will be added. Repeat this until there are twelve columns between the customer name and the number of purchases.

To fit all the columns on a single page we can re-size each column by click and dragging on the right side. Don’t worry about matching the width each column we will do that shortly.

Next we want the title to spread from columns A to N. Shift click to select all of row one and then click the ‘Merge selected button.

Type the abbreviations of each month in the appropriate column. Select columns B – M and click ‘Set All Width Identical’.

As a final touch of formatting use the Cell Format menu (below) to add borders to the report.

With all these modifications done the report looks ready to have the data added.

Putting Data into Rows

Since we are already in a group footer that sorts by Customer the relevant data is the date of each order. For each Date we want to check if the month corresponds to the column we are in then add 1 to the number of purchases if it does. This can be done in the formula editor. Since we are embedding functions, start with the Sum function to keep the total. Drag in the If then the Month function to check the month of the order. The resulting formula is “=Sum( If(Month({Orders.OrderDate})= 01, 1, 0))”. For more help with functions click here.

Clicking OK will add the formula to the selected cell. Instead of using the formula editor eleven more times hold CTRL click the formula and drag it to each of the other cells.

Now each cell checks if the month is January and does the sum accordingly. To fix this click on each cell and change the if condition to the appropriate month.

Running the report now we see that we are almost done, but the chart has a lot of zeros that make it look cluttered. Also we would like to see the totals for each month at the bottom of the report.

Add a report footer by clicking in the left most column. Using the CTRL drag  copy row three into the new section.

To remove the zeros hold shift and select the formulas in the Company Name Footer. Click the format cells menu. In the number tab check the ‘Blank When Zero’ and make sure the decimal places are set to 0.

Clicking ok and run the report.

It is clear from the output that with just a few formulas and a little formatting we extracted much more value from our data.

Happy Reporting!

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