October 2, 2017 at 12:48 pm
Select TOP 1000 SUM(UnitPrice),SUM(ExtendedAmount)
FROM dbo.FactInternetSales
where SalesTerritoryKey IN (6,7,1,9,4)
How can I write expression in SSRS to achive this kind of aggregation.
October 2, 2017 at 12:58 pm
wweraw25 - Monday, October 2, 2017 12:48 PMSelect TOP 1000 SUM(UnitPrice),SUM(ExtendedAmount)
FROM dbo.FactInternetSales
where SalesTerritoryKey IN (6,7,1,9,4)How can I write expression in SSRS to achive this kind of aggregation.
Without your existing report and data source(s?) in hand, it's impossible to know if this is practical or not. Maybe the first question is why you can't integrate this into your dataset. Also, the query you show wants a TOP 1000 to be applied to a query that will only ever return one row anyway, as there's no GROUP BY involved, so it's going to compute a SUM across all the records that meet the WHERE clause condition. You could also make this a separate dataset by providing a column name for each of the sums, but then again, how, EXACTLY, would you use this? Especially within the context of your existing report?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 3, 2017 at 2:54 am
Like Steve said, we don't have a lot to work on here. Is the non-aggregated data also in your report as a dataset? Are you looking to put these in text boxes, at the bottom of your table? You have two fields there, so are you wanting to return both of these as a single value?
You need to elaborate more on what your requirements are.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 4, 2017 at 3:06 pm
Thom A - Tuesday, October 3, 2017 2:54 AMLike Steve said, we don't have a lot to work on here. Is the non-aggregated data also in your report as a dataset? Are you looking to put these in text boxes, at the bottom of your table? You have two fields there, so are you wanting to return both of these as a single value?You need to elaborate more on what your requirements are.
| Entertainment | Jan | Feb |
| Cable TV | 52.98 | 52.98 |
| Video Rentals | 7.98 | 11.97 |
| Movies | 16 | 32 |
| CDS | 18.99 | 29.99 |
if I calculate sum of my expenses in Jan for Cable TV and Video Rentals it would be something like 52.98+7.98 (using excel its easy sum(col)+Sum(Col))
how the same can be achieved using ssrs.
October 5, 2017 at 2:00 am
wweraw25 - Wednesday, October 4, 2017 3:06 PM
Entertainment Jan Feb Cable TV 52.98 52.98 Video Rentals 7.98 11.97 Movies 16 32 CDS 18.99 29.99 if I calculate sum of my expenses in Jan for Cable TV and Video Rentals it would be something like 52.98+7.98 (using excel its easy sum(col)+Sum(Col))
how the same can be achieved using ssrs.
This looks more like what you're after isn't a tablix but a matrix. You can then put your entertainment as your row heading, and your Month as the column heading. then an expression of =SUM(Fields!YourColumn.Value) will be alll you need.
This is still based on assumptions I've made on the little information we have.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 5, 2017 at 7:07 am
Thom A - Thursday, October 5, 2017 2:00 AMwweraw25 - Wednesday, October 4, 2017 3:06 PM
Entertainment Jan Feb Cable TV 52.98 52.98 Video Rentals 7.98 11.97 Movies 16 32 CDS 18.99 29.99 if I calculate sum of my expenses in Jan for Cable TV and Video Rentals it would be something like 52.98+7.98 (using excel its easy sum(col)+Sum(Col))
how the same can be achieved using ssrs.This looks more like what you're after isn't a tablix but a matrix. You can then put your entertainment as your row heading, and your Month as the column heading. then an expression of =SUM(Fields!YourColumn.Value) will be alll you need.
This is still based on assumptions I've made on the little information we have.
Thanks for the reply Thom. For full aggregation of column I can use the expression you suggested, but as I mentioned i need only aggregation of Cable TV and Video rentals for the month of Jan.
October 5, 2017 at 7:13 am
wweraw25 - Thursday, October 5, 2017 7:07 AMThom A - Thursday, October 5, 2017 2:00 AMwweraw25 - Wednesday, October 4, 2017 3:06 PM
Entertainment Jan Feb Cable TV 52.98 52.98 Video Rentals 7.98 11.97 Movies 16 32 CDS 18.99 29.99 if I calculate sum of my expenses in Jan for Cable TV and Video Rentals it would be something like 52.98+7.98 (using excel its easy sum(col)+Sum(Col))
how the same can be achieved using ssrs.This looks more like what you're after isn't a tablix but a matrix. You can then put your entertainment as your row heading, and your Month as the column heading. then an expression of =SUM(Fields!YourColumn.Value) will be alll you need.
This is still based on assumptions I've made on the little information we have.
Thanks for the reply Thom. For full aggregation of column I can use the expression you suggested, but as I mentioned i need only aggregation of Cable TV and Video rentals for the month of Jan.
When it comes to using a matrix or a tablix, you generally do the same thing for all values, so unless you provide some indicator in your data that would only appear on the rows for those two categories, you have no way to do the grouping needed. That's why I always recommend that you get your groups straightened out in your dataset, and that way, any SSRS grouping is fairly easy. Waiting until you have the report in SSRS is kinda too late.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply