DBxtra .NET Tips

New video tutorial: How to define Pivot Grid Views.

Watch the Video

One of our customers asked if it is possible that a DBxtra user can view report data belonging only to his assigned customers.

Example: The user should only be allowed to see records for his assigned customers when opening a report.

Here is how to set that up.

There must be a link between the table/view and the user id.

Here is how the query is setup

The query is done from 3 tables:

• Orders
• Order Details
• Users

The table “Users” has a field named “user” and is joined to the “Orders” table – field “EmployeeId”.

We added a Criteria by double clicking the “Criteria” field.

We clicked the “login Parameter” button.

This will insert a criteria like that:

Now depending on the user who logs on DBxtra – either the Designer or Report Web Service  viewer - he will see only data from the query or report which coincides between the user id of the logged in user and the “EmployeeId” of the “User” table.

Let’s log on as the user “Eric Clapton”.

Let’s open the query with the data grid.

Now let’s log on as “Fats Domino”.

Let’s open the query with the data grid.

Each user gets his own related data.

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…