help with a query to create a chart

I have the following sql:
***********************************************************
SELECT region.region_name, sale.sale_dts, SUM(sale.total_pt_of_sale_amt)
AS POS, SUM(sale.total_incr_rev_amt) AS [Incremental Revenue]
FROM region INNER JOIN
region_terr ON region.region_id = region_terr.region_id INNER JOIN
sale ON region_terr.sales_rep_user_id = sale.sales_rep_user_id
GROUP BY region.region_name, sale.sale_dts
HAVING (region.region_name = @.regionname) AND (sale.sale_dts BETWEEN
@.start AND @.end)
**********************************************************
What I want to show is one number for [Incremental Revenue] and one number
for POS by the region and month. Can someone lend me a hand to help?After creating your dataset with your query on the Data tab, add the chart
control on the Layout tab. Go to Properties of the chart.
on the General tab, select Column for Chart type, then for sub-type pick the
first one (default).
Click on the Data tab, then select your dataset name from the combo box.
Click the Add button to the right of the Values section. Type POS for the
Series label, and for the value, pick =Fields!POS.Value from the combo box.
Click OK.
Click the same Add button to the right of the Values section. Type
"Incremental Revenue" for the Series label, and for the value, pick =Fields![Incremental Revenue].Value from the combo box. Click OK.
Now click the Add to the right of the Category groups section. Under Group
on, in the Expression column, select =Fields!sale.sale_dts.Value from the
combo box, then extract Month from it by modifying the expression to:
=Month(Fields!sale.sale_dts). Click OK.
I am assuming that your chart will only show sales for one region at a time,
so it does not make sense to plot the region name on the X axis. You can
include it in the chart title.
On the General tab, type in your title as something like this:
="Incremental Sales and POS for " & Parameters!regionname.value
Then your selected region will show in the chart title. You can modify other
things about the chart yourself by looking round in the Properties dialog.
For example, you can choose to display or not to display a legend.
HTH
Charles Kangai, MCT, MCDBA
"eric_rs1" wrote:
> I have the following sql:
> ***********************************************************
> SELECT region.region_name, sale.sale_dts, SUM(sale.total_pt_of_sale_amt)
> AS POS, SUM(sale.total_incr_rev_amt) AS [Incremental Revenue]
> FROM region INNER JOIN
> region_terr ON region.region_id => region_terr.region_id INNER JOIN
> sale ON region_terr.sales_rep_user_id => sale.sales_rep_user_id
> GROUP BY region.region_name, sale.sale_dts
> HAVING (region.region_name = @.regionname) AND (sale.sale_dts BETWEEN
> @.start AND @.end)
> **********************************************************
> What I want to show is one number for [Incremental Revenue] and one number
> for POS by the region and month. Can someone lend me a hand to help?sql