SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sum for year within Tablix


Sum for year within Tablix

Author
Message
cmw 66135
cmw 66135
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 128
Hi

I have a report that I am trying to do that is filtered by what period the user enters. What I would like to show on the tablix is the total for the year to date even though it has been filtered by period to show current billing, is there a way I can do this by a sum command or something similar





































































































Thanks
Chris
Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82231 Visits: 21076
Rather than filtering the data at SP level, or Tablix, filter the Row Group. Add the Totals row to your tablix first, then in your Row Groups Pane put the filter on the Details_Group. The Total will still have the full total for the data that was returned to the report, where as the tablix will only display those you filtered to.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
cmw 66135
cmw 66135
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 128
Hi Thom

Thanks for the reply, I would have done that but one of the items in the data is a total pulled from the server and it multiples this total when filtered like this so I thought the only way around it was to do this calculation on the tablix itself. Sorry im still new to this and self trained so I dont always know the best answer to it.

Is there a way I can do the above without totalling a vital piece of information that must show as per how it should be pulled in?

Thanks
Chris
Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82231 Visits: 21076
It sounds like your describing that you have totals already in your data as well as the non-aggragated data itself. Are you having to display those sub-totals as well? I'd suggest that, if so, you do your grouping and sub-totaling in SSRS, not in your dataset. Then you would have to issue of double counted data.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
cmw 66135
cmw 66135
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 128
The total for the year to date is data. When I do grouping to show totals the grouping doesnt show the year target total which is why i thought it needed to be done at tablix level
Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82231 Visits: 21076
cmw 66135 - Tuesday, November 14, 2017 10:04 AM
The total for the year to date is data. When I do grouping to show totals the grouping doesnt show the year target total which is why i thought it needed to be done at tablix level


I don't really understand what you're saying here.

Perhaps you could provide some sample data (including data you don't want to include) and your expected results in SSRS.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
cmw 66135
cmw 66135
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 128
Hi Thom

This is below what I am trying to achieve with my report. This is a spreadsheet that we currently update manually but I am trying to automate a report and make it, idiot proof for the people who will us it.






























































































































































































































This is what I have done so far, I feel im on the right track but as I say I am still very new to this and self taught so I dont always know if I have explained things correctly
Thom A
Thom A
SSC Guru
SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)SSC Guru (82K reputation)

Group: General Forum Members
Points: 82231 Visits: 21076
That dataset doesn't look anything like what you originally posted, hw does it relate? Also, those screenshots are tiny (I very much doubt you have a screen that is only 271 pixels tall!).

Have a look at the link in my signature on how to post sample data. If you can provide some consumable sample data, along with what you want your SSRS report to look like, I'll be more than happy to try to guide you through.

Cheers.


Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does :-P

Please always remember to encapsulate your code in IFCode Markup. For example [code=sql] [/code].
Click here to read Jeffs Guide on how to post SQL questions, and get swift and helpful answers from the community
HappyGeek
HappyGeek
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14761 Visits: 7218

Looking at the detail you have posted it is clear that this is a law firm, and I should think that the data you are looking for is already calculated within the PMS system as this would be a normal reporting requirement, so not entirely sure why you need a manual form of entry onto a spreadsheet; also the data you have posted above is not obfuscated and can lead to the easy identification of the individuals and the law firm you represent, please take this as a piece of advice on being careful of what you post in future.
You may wish to edit your post!!

...
cmw 66135
cmw 66135
SSC Eights!
SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)SSC Eights! (869 reputation)

Group: General Forum Members
Points: 869 Visits: 128
Point taken Happygeek. Thanks for the advice.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search