Consolidated User Input Parameters

Starting with DBxtra 9.2, if you have a report with parameters that contains a subreport with parameters, you can have them both use the same values, for that, both the parameters in the main report and the ones in the subreport, must have the same name and alias.

Select tbl_Sales_Person.[Sales Person],

tbl_Territory.Territory,

Sum(tbl_Invoice_Sub.Qty * tbl_Invoice_Sub.Price) As Revenue

From tbl_Sales_Person tbl_Sales_Person

Inner Join tbl_Invoice_Main tbl_Invoice_Main

On tbl_Sales_Person.[Id Sales Person] = tbl_Invoice_Main.[Id Sales Person]

Inner Join tbl_Customer tbl_Customer On tbl_Customer.[Id Customer] =

tbl_Invoice_Main.[Id Customer]

Inner Join tbl_Country tbl_Country On tbl_Country.[Id Country] =

tbl_Customer.[Id Country]

Inner Join tbl_Invoice_Sub tbl_Invoice_Sub On tbl_Invoice_Main.[Id Invoice] =

tbl_Invoice_Sub.[Id Invoice]

Inner Join tbl_Territory tbl_Territory On tbl_Territory.[Id Territory] =

tbl_Country.[Id Territory]

This query has two parameters, one for the Sales Person and the other for the Territory, in this report we also want to see the sales by every country, as a detail of every Territory, so we create a second report object to show such information, with the following query:

Select tbl_Sales_Person.[Sales Person],

tbl_Country.Country,

Sum(tbl_Invoice_Sub.Qty * tbl_Invoice_Sub.Price) As Revenue,

tbl_Territory.Territory

From tbl_Sales_Person tbl_Sales_Person

Inner Join tbl_Invoice_Main tbl_Invoice_Main

On tbl_Sales_Person.[Id Sales Person] = tbl_Invoice_Main.[Id Sales Person]

Inner Join tbl_Customer tbl_Customer On tbl_Customer.[Id Customer] =

tbl_Invoice_Main.[Id Customer]

Inner Join tbl_Country tbl_Country On tbl_Country.[Id Country] =

tbl_Customer.[Id Country]

Inner Join tbl_Invoice_Sub tbl_Invoice_Sub On tbl_Invoice_Main.[Id Invoice] =

tbl_Invoice_Sub.[Id Invoice]

Inner Join tbl_Territory tbl_Territory On tbl_Territory.[Id Territory] =

tbl_Country.[Id Territory]

Where tbl_Sales_Person.[Sales Person] = @Param1 And tbl_Territory.Territory =

@Param2

Group By tbl_Sales_Person.[Sales Person],

tbl_Country.Country,

tbl_Territory.Territory

Where tbl_Sales_Person.[Sales Person] = @Param1 And tbl_Territory.Territory =

@Param2

Group By tbl_Sales_Person.[Sales Person],

tbl_Territory.Territory

Since we want the information of a particular Territory and Sales Person, we add two parameters for that value and we ensure that it’s name (Param1 and Param2) is the same in both queries, we also customize every parameter so its alias is also the same in both parameters in both queries.

Now when you run the report, you’ll be asked once for the values of both parameters and both reports will be filtered accordingly.

Note! The DBxtra Sample Project of DBxtra version 9.2.0 + provides a sample report “Employees Sales by Territory” which shows how to do that.