May 22, 2015 at 3:14 pm
Here's why:
I have a database that lists out all my company's sales representatives. Because some products can take more than a month to ship and because Sales quotas change month-to-month, I have multiple rows for the same Product ID for different months' quotas. Now, I've corrected for this fact in all my Report expressions, by forcing a CountDistinct on the Fields!RXFILL_ID.Value parameter, but I'm encountering problems when I try to logic my way through this one:
SalesID RxFill_ID Price QuotaYear QuotaMonth
16 123 200 2015 3
16 123 200 2015 4
16 123 200 2015 5
16 456 150 2015 3
16 456 150 2015 4
16 456 150 2015 5
Now, ordinarily I could go back to the query and force it to group by the RxFill_ID, but! I will eventually need to be able to get the QuotaYear and QuotaMonth columns' data as well.
How do I write an expression in the report that will, in the above example, give me the value 175 (i.e., the average of 200 & 150 for the two different orders)?
May 22, 2015 at 3:35 pm
DonFord81 (5/22/2015)
How do I write an expression in the report that will, in the above example, give me the value 175 (i.e., the average of 200 & 150 for the two different orders)?
This will give you the average for each of the orders for each sales id. It can be expanded to include other information if needed.
declare @t table
(
SalesID int
,RxFill_ID int
,Price int
,QuotaYear int
,QuotaMonth int
)
Insert @t (SalesID, RxFill_ID, Price, QuotaYear, QuotaMonth) values
(16, 123, 200, 2015, 3)
,(16, 123, 200, 2015, 4)
,(16, 123, 200, 2015, 5)
,(16, 456, 150, 2015, 3)
,(16, 456, 150, 2015, 4)
,(16, 456, 150, 2015, 5)
,(17, 456, 150, 2015, 5)
;with cte as
(
select SalesID, Price,
ROW_NUMBER() over(partition by SalesID, rxFill_ID order by (select null)) RowNum
from @t
)
select SalesID, AVG(price) from cte
where RowNum = 1
group by SalesID
Please note how I created DDL, data and insert statements. This makes it much easier and faster to come up with a solution!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 22, 2015 at 3:43 pm
LinksUp (5/22/2015)
DonFord81 (5/22/2015)
How do I write an expression in the report that will, in the above example, give me the value 175 (i.e., the average of 200 & 150 for the two different orders)?
This will give you the average for each of the orders for each sales id. It can be expanded to include other information if needed.
declare @t table
(
SalesID int
,RxFill_ID int
,Price int
,QuotaYear int
,QuotaMonth int
)
Insert @t (SalesID, RxFill_ID, Price, QuotaYear, QuotaMonth) values
(16, 123, 200, 2015, 3)
,(16, 123, 200, 2015, 4)
,(16, 123, 200, 2015, 5)
,(16, 456, 150, 2015, 3)
,(16, 456, 150, 2015, 4)
,(16, 456, 150, 2015, 5)
,(17, 456, 150, 2015, 5)
;with cte as
(
select SalesID, Price,
ROW_NUMBER() over(partition by SalesID, rxFill_ID order by (select null)) RowNum
from @t
)
select SalesID, AVG(price) from cte
where RowNum = 1
group by SalesID
Please note how I created DDL, data and insert statements. This makes it much easier and faster to come up with a solution!
Where in the report would I put this? I'm very new to SSRS and SQL.
May 22, 2015 at 3:51 pm
DonFord81 (5/22/2015)
SalesID RxFill_ID Price QuotaYear QuotaMonth
16 123 200 2015 3
16 123 200 2015 4
16 123 200 2015 5
16 456 150 2015 3
16 456 150 2015 4
16 456 150 2015 5
How do I write an expression in the report that will, in the above example, give me the value 175 (i.e., the average of 200 & 150 for the two different orders)?
In re-reading your question, it is not clear if you want the average of one row that is 200 and one row that is 150 to come up with 175. Or did you want a true arithmetic average. All the 200's + all the 150's which in this case is still 175. If the prices can be different and you need an average of them all, then my first post won't quite do it.
For an average of all the prices for a given SalesID, this seems to work just as well:
select SalesID, AVG(price) from @t
group by SalesID
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 22, 2015 at 3:54 pm
LinksUp (5/22/2015)
In re-reading your question, it is not clear if you want the average of one row that is 200 and one row that is 150 to come up with 175. Or did you want a true arithmetic average. All the 200's + all the 150's which in this case is still 175. If the prices can be different and you need an average of them all, then my first post won't quite do it.For an average of all the prices for a given SalesID, this seems to work just as well:
select SalesID, AVG(price) from @t
group by SalesID
The prices will never be different between rows.
May 22, 2015 at 4:06 pm
DonFord81 (5/22/2015)
LinksUp (5/22/2015)
For an average of all the prices for a given SalesID, this seems to work just as well:
select SalesID, AVG(price) from @t
group by SalesID
The prices will never be different between rows.
The query above will work for either case!
I have never had much use for SSRS so I really have no idea how to add the query. I just googled "mssql add query to ssrs" and came up with a ton of "how to" links.
Here is an older link that I am sure is still applicable:
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply