parameters

I am trying to make a report that will select the sales people who have
made 10 sales or more during the current month and show what those
sales are with the price..., and then show what those same sales
persons totals were for the previous month. my problem is to get the
totals from the previous month. example of the report is below. I am
trying to use two datasets one to get the first name, last name,
sales_id, product sold, price, and quantity. the other dataset will
get the last month total. I want to pass the sales person id to the
other dataset for each sales person. is there a way to do this?
Thanks, Landon
sales people who sold 10 or more:
first name, last name, sale_id, product sold, price, quantity
last month total: total
< -- this is where my problem is
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: total
first name, last name, sale_id, product sold, price, quantity
last month total: totalWhat works best in this is to have a sub report. Create a normal report that
you pass the sales person id as a parameter. Make sure the report works.
Drag and drop the report onto the first report. Right mouse click on report,
parameters. Set the parameter for the sub report to the field which has the
sales person id.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135788313.801129.98180@.g49g2000cwa.googlegroups.com...
>I am trying to make a report that will select the sales people who have
> made 10 sales or more during the current month and show what those
> sales are with the price..., and then show what those same sales
> persons totals were for the previous month. my problem is to get the
> totals from the previous month. example of the report is below. I am
> trying to use two datasets one to get the first name, last name,
> sales_id, product sold, price, and quantity. the other dataset will
> get the last month total. I want to pass the sales person id to the
> other dataset for each sales person. is there a way to do this?
> Thanks, Landon
> sales people who sold 10 or more:
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> < -- this is where my problem is
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
> first name, last name, sale_id, product sold, price, quantity
> last month total: total
>|||would i be able to use that data from the sub report in a graph with
the data from the other dataset?|||You can't join two datasets regardless of whether or not it is in a
subreport or not. You need to join the data into a single dataset if you
want to do this. Your subreport can have a graph, that isn't the problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135793391.283032.284030@.z14g2000cwz.googlegroups.com...
> would i be able to use that data from the sub report in a graph with
> the data from the other dataset?
>|||so in one graph i can not have the current month and last month totals?|||Not that I am aware of. A graph is based on a dataset, not on two datasets.
You can't join datasets. So if you want the data from two datasets on the
graph then you need to join the data at the source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Landon" <landonb@.gmail.com> wrote in message
news:1135794518.867450.152820@.g49g2000cwa.googlegroups.com...
> so in one graph i can not have the current month and last month totals?
>|||Thanks Bruce for your help!
Landon|||You should fetch your data as a subquery in your data stream. You would then
have access to both current and prior month values. Example:
select first_name, last_name, sale_id, product_sold, price,
quantity,last_month_total
,(SELECT SUM(amount)
FROM sales
WHERE first_name = s.first_name and last_name = s.last_name
and sales_date in <month>
) AS prior_month_total
FROM sales s
where ...|||If they are on different databases you can link them in the query like
so:
Lets say 1 data set db is DB1 and the second is DB2
Place the query in DB1(and the dataset) and then you can say:
SELECT first_name, last_name, sale_id, product_sold, price,
quantity,DB2.tablename.last_month_total|||I would make a stored procedure and use a temp table based on the
months needed by the user. That way you can have both months in the
same dataset.