Report Software for Easy Use by Anyone

DBxtra Tips

Here we will post tips on how to get the most out of DBxtra .NET.


Constants in DBxtra Queries

July 29th, 2008

One of our customers recently needed to use Constants – which may vary from time to time – in his DBxtra Queries.

We built a sample query object explaining how to do that - CONSTANTS Sample - Invoices Detail {MXN to EURO, USD & YEN}.

1.-   We created a new query object (Constants Table) which points to the data connection and where we declare a list of constants.

The Query “Constants Table”

image

2. We selected a table – any table is good – from the data connection and assigned a TOP 1 for it.

Right click on the Diagram Pane and select Edit.

image

3. We created an expression with Constant values and assigned an Alias name.

image

4. We saved the object as “Constants Table”.

5. We created a new Query named “Invoices Detail {MXN to EURO, USD & YEN}” adding all required tables  and also the DBxtra query “Constants Table”. The object “Constants Table” does not have any relationship to any other table.

image 

6. We added expressions to calculate on the Constant.

image

image

The Expression:

((tbl_Invoice_Sub.Unit_Price - tbl_Invoice_Sub.Discount) * tbl_Invoice_Sub.Qty) / [Constants Table].EURO

The Result shown in the Data Grid

image

You can download the sample project from here:

www.dbxtra.com//Download/SampleProjects/CONSTANTS SAMPLE - SQL WebServer.zip

To import the project download it select Import Project on the DBxtra Designer Utils menu.

Note! this sample project is connected to a MS SQL Server database on our Web Server.
If you can’t open the connection then please check that your firewall does not block port 1433.


The DBxtra .NET Parameter Report

March 13th, 2008

End users can specify parameters at the time of running a report. This helps end-users to get only relevant data that they want to see in the reports.

Watch the video.

You can learn more here: http://www.dbxtra.com/helpfile/Parameter_Query.htm


Quick Start Guide was updated

March 13th, 2008

The DBxtra .NET Report Designer Quick Start Guide was updated.

Please check it out.


New Quick Chart Wizard

March 12th, 2008

Check out the new Quick Chart Wizard in DBxtra .NET version 1.0.0.4.

Now it is much easier to create a chart.

You still have access to the Advanced Chart wizard but the Quick Chart Wizard allows setting basic chart functionality in less time.

Check out the video.



Online Videos by Veoh.com


Create a Query from two (or more) different databases

February 29th, 2008

One of the nice features of DBxtra .NET is that you can create a query from 2 or more different databases.

You can join any kind of databases like MS SQL Server with Oracle; Access with
MySQL and so on.

Example:

Let’s suppose we have all our customer data in a SQ Server database but the contact data is located in an Excel sheet.

First we need to create the connection to both the SQL Server database and the Excel sheet.

Here are our connections:

image 

First select a connection ad double click it.

Something like this will show on the New Object tab.

image

IMPORTANT!

Click on "Heterogeneous Query".

image

Drag and drop a table / view from the first database.

image

Select the second database connection.

Drag and drop the table / view from the second database.

image

Create relationship as usual.

image

Design the query as usual.

image

Here is a preview of the query in the data grid:

image

Important!

Whenever you create a query from two or more different databases then the MS Access SQL syntax is applied.


Oracle Connection Option

February 28th, 2008

One of our customers asked if he need to have the TNSNAMES.ORA file
configured on the user’s box for them to use DBxtra?

Answer:

There are three ways to connect to Oracle.

clip_image002

1. Direct Connection to Oracle

You need to provide the Service name; which is the TNSName – TNSNAMES.ORA (Network Configuration File) which is generated by the Oracle Configuration Tool.

To use the direct Oracle connection you need to have the Oracle client installed on each PC using DBxtra .NET and also configured each PC’s TNSName.ora.

2. Create a OLEDB connection to Oracle

  • Use the Data Link properties by clicking the button “…” – Connection String
  • Select the Provider: Oracle Provider for OLEDB
  • clip_image002[4]

Enter the following on the Connection tab:

- Data Source: Connection string to Oracle Server in order to NOT need the TNSNames.ora on each PC.

(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = DBxtra2005)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = DBOra9i)))

Note: This way the TNSName.ora does not have to be configured on each PC; but each PC needs the Oracle client installed.

clip_image002[6]

Here is how the Connection window will look like:

clip_image002[8]

Note: Remember to assign the “SQL Dialect” to Oracle.

  1. Connect through ODBC

You can also connect through ODBC but that requires having the ODBC configured on each PC.


Chart on detail level - a chart for each detail

February 20th, 2008

One of our customer asked the following question:

Is it possible to generate one report with different charts using the
group by expression ?

Since we have a huge number of equipments we would avoid to generate one
report/chart per equipment manually (filtering by equipment_id) but we
would like to have one chart per page for each equipment_id (i.e group by
equipment).

Here is the solution:

In this example we suppose that we want to show the amount of currency sales and qty for each product category by month.

Create the query for the report

The query "Chart by Category" for the report looks like this:

image

Create another identical query and save it with another name.

We need to create another identical query for the second data source.
So we will save it with another name - "Chart by Category - Data".

image

image

Design the report and chart

Lets load the "Chart by Category" query and open the report designer.

image

Add another data source

image

Select the second query and add it by double clicking.

Add a data source

image

Select the query "Chart by Category - Data" as data source.

image

Add relationship

image

Important!
First you need to select the data source "Data" as parent query - table and then the child query - table.

image

Click "Add Relationship".

Click "Ok" when finished.

Add a new Group into the Report

image

Select "Product Category".

image

image

Insert a chart into the Group Header or Footer band.

image

Define the Chart as usual.

Assign a name for each series.

image

Define Series Binding.

IMPORTANT!
You need to bind the series to the added data source; in this case to YYYY-MM - "Chart by Category Data".

image

image

image

image

Do so for each series.

Format the chart to your liking.

Format the report to your liking.

image

Here is how the finished chart looks like:

image

Each product category is charted with its values.

Download the sample project

You can download this sample project by clicking here.

See how to import the project.


Excel Pivot Tables

February 19th, 2008

Since DBxtra .NET allows easy creation of linked Excel pivot tables you might find the following link interesting.

Pivot tables are an Excel feature that you should learn how to use. Instead of analyzing rows upon rows of records, a pivot table can aggregate your data and show a new perspective with few clicks. You can also move columns to rows or vice versa. The problem is people believe creating a pivot table is too difficult to learn

 More…


Schedule Server - Create a Schedule - with a merged report

February 19th, 2008

Let’s see how easy it is to create a Schedule to automatically send merged reports to business contacts.

Ins this example we’ll setup a Schedule to deliver a weekly Invoice Statement to our customers.

1. Design the Invoice Statement report

The query for the report looks like this:

image

Note that we included a parameter to show only records for last week.

The report design looks like this:

image

Note that we grouped the report by customer.

The report in preview looks like this:

image

Note that all records are shown for last week.

2. Design the Merge Catalogue

image

It is important that the Merge Catalogue query  has a unique key field to merge on.

And of course it also needs to hold an E-mail address.

In this case we’ll use the "Id_Customer field" as key merge field.

3. Create the Schedule

We recommend that you take a look at the DBxtra .NET Schedule Server Quick Start Guide.

Open the  Schedule Server, select the project and click "New" on the menu bar.

image

Define all settings.

Here we set the schedule to be running ever week on Monday.

image

3.1 Create a new Job

Click "Add Job".

image

Select "E-mail - Merge DBxtra Object".

Enter a Job description.

Click "Settings".

Select the Merge Catalogue

image

Important!

The Marge Catalogue Settings will by default be taken form the Project Configuration.

If you haven’t configured it yet you can enter required settings manually.

Select the DBxtra object as Merge Catalogue. (The one we designed in step 2.)

Very Important!

Select the "Key" field for to merge.
This field will tell DBxtra on which field to merge the report.

image

Also select the E-mail, Company and Contact field.

Define E-mail settings

image

Now that you’ve setup the Merge Catalogue  you can assign contacts.

Click the "To" button.

image

Select all contacts to receive the E-mail merged report.

Select "Always pill all records from the merge Catalogue" if you want to send the E-mail to all contacts from the catalogue.

Select the merged report to be sent

Click "Attach".

Select the DBxtra report.

In this case we’ll select the report we designed in step 1.

image

Select a output format.

image

Optionally you can attach any other file and define if to run the report only if it holds any data.

Also select if to zip the report for smaller size.

If you want to add the date and hour to the file name - report - to be sent select "Add date to file name".

When finished click "Ok".

Select the object merge field.

image

This is the field on which DBxtra .NET will merge report data for each merge catalogue contact.

In this case we’ll select the field "Id_ Customer".

Write the subject and message of your E-mail.

image

You can enter merged fields into the subject and bodY by selecting and double clicking it from the right merge fields bar.

This way each of your customer will receive a personalized message.

You can send yourself an E-mail test to see how the E-mail will arrive.

When finished click "Ok."

Click "Save".

image

Learn about running the Schedule automatically.

Here is how your customer will receive the message:

image

And  here is the merged report:

image

As you can see the contact receives the report only with data belonging to his company.

Learn more about the DBxtra .NET Schedule Server:

Quick Start Guide

Help file


Backup your DBxtra .NET Server Database

February 8th, 2008

Imagine that for any reasons your DBxtra .NET Server Database becomes inaccessible.
All your objects like queries; charts and report might be lost. Maybe Forever.
Of course you can re-design them but…..

That’s why backing up your DBxtra .NET Server database is SO IMPORTANT!

And its easy to do too.

Go to the "Utilities" menu and select:

image

Select a path and click "Create".

image

Ready!

To recover a priory backed up DBxtra .NET Server database click

image

Assign the path to the backup and DBxtra .NET will restore it.

image

Be save; backup your DBxtra .NET Server database regularly.