DBxtra .NET Tips

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”

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.

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

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.

6. We added expressions to calculate on the Constant.

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

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.

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

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

Please check it out.

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

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:

First select a connection ad double click it.

Something like this will show on the New Object tab.

IMPORTANT!

Click on "Heterogeneous Query".

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

Select the second database connection.

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

Create relationship as usual.

Design the query as usual.

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

Important!

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

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.

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
• 

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.

Here is how the Connection window will look like:

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.

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:

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".

Design the report and chart

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

Add another data source

Select the second query and add it by double clicking.

Add a data source

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

Add relationship

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

Click "Add Relationship".

Click "Ok" when finished.

Add a new Group into the Report

Select "Product Category".

Insert a chart into the Group Header or Footer band.

Define the Chart as usual.

Assign a name for each series.

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".

Do so for each series.

Format the chart to your liking.

Format the report to your liking.

Here is how the finished chart looks like:

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.

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…

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:

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

The report design looks like this:

Note that we grouped the report by customer.

The report in preview looks like this:

Note that all records are shown for last week.

2. Design the Merge Catalogue

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.

Define all settings.

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

3.1 Create a new Job

Click "Add Job".

Select "E-mail - Merge DBxtra Object".

Enter a Job description.

Click "Settings".

Select the Merge Catalogue

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.

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

Define E-mail settings

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

Click the "To" button.

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.

Select a output format.

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.

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.

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".

Learn about running the Schedule automatically.

Here is how your customer will receive the message:

And  here is the merged report:

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

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:

Select a path and click "Create".

Ready!

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

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

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