sql server reporting services: parameters -- sql server reporting services

You're in the

sql server reporting services

group:

parameters

Landon
12/28/2005 8:45:13 AM

sql server reporting services:

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

Re: parameters

Landon
12/28/2005 10:09:51 AM

would i be able to use that data from the sub report in a graph with
the data from the other dataset?

Re: parameters

Landon
12/28/2005 10:28:38 AM

so in one graph i can not have the current month and last month totals?

Re: parameters

Landon
12/28/2005 11:11:59 AM

Thanks Bruce for your help!

Landon

Re: parameters

Bruce L-C [MVP]
12/28/2005 11:13:31 AM

What 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

[quoted text, click to view]

Re: parameters

Bruce L-C [MVP]
12/28/2005 12:21:38 PM

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

[quoted text, click to view]

Re: parameters

Bruce L-C [MVP]
12/28/2005 1:02:32 PM

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

[quoted text, click to view]

Re: parameters

William
12/29/2005 6:31:02 AM

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

Re: parameters

Sorcerdon
12/29/2005 7:13:39 AM

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

Re: parameters

chris_napoli2003 NO[at]SPAM yahoo.com
1/1/2006 9:42:21 PM

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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.

home · blog · groups

Questions? Comments? Contact the dn