SSRS - Sort a report column based on the parameter value
I need to sort the columns based on the date parameter entered to be the prior year month first and increase up to the date entered, for the period of 12 months. For example, parameter value entered '03/31/2018'
, it would first display 'Apr 2017'
, then 'May 2017'
, ...so on..., last column 'Mar 2018'`. Thank you!
reporting-services
add a comment |
I need to sort the columns based on the date parameter entered to be the prior year month first and increase up to the date entered, for the period of 12 months. For example, parameter value entered '03/31/2018'
, it would first display 'Apr 2017'
, then 'May 2017'
, ...so on..., last column 'Mar 2018'`. Thank you!
reporting-services
add a comment |
I need to sort the columns based on the date parameter entered to be the prior year month first and increase up to the date entered, for the period of 12 months. For example, parameter value entered '03/31/2018'
, it would first display 'Apr 2017'
, then 'May 2017'
, ...so on..., last column 'Mar 2018'`. Thank you!
reporting-services
I need to sort the columns based on the date parameter entered to be the prior year month first and increase up to the date entered, for the period of 12 months. For example, parameter value entered '03/31/2018'
, it would first display 'Apr 2017'
, then 'May 2017'
, ...so on..., last column 'Mar 2018'`. Thank you!
reporting-services
reporting-services
asked Nov 13 '18 at 19:19
user10581977user10581977
43
43
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
First of all, next time be sure to post what you tried so far and why it is not working. This way people will see that you put some effort into it and they will be happy to help you.
Second go to your Tablix Properties > Sorting > Add and use the sort expression:
=CDate(Fields!YourDate.Value)
Then go to your Tablix Properties > Filters > Add and add the following two filters:
'Expression1
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(Parameters!YourParameter.Value)), "1").AddMonths(1)
'Format1
=Date/Time
'Operator1
>
'Value1
=CDate(Fields!YourDate.Value)
And the second filter:
'Expression2
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(CDate(Parameters!YourParameter.Value))), "1").AddMonths(13)
'Format2
=Date/Time
'Operator2
<
'Value2
=CDate(Fields!YourDate.Value)
add a comment |
I have modified my dataset to include one more field "IndexColumn", which sorts in a specified order, starting with "A" for a prior year month (April 2017):
=SWITCH(Fields!Indexcolumn.Value = “A”, Format(DateAdd("m", -11, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “B”, Format(DateAdd("m", -10, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “C”, Format(DateAdd("m", -9, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “D”, Format(DateAdd("m", -8, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “E”, Format(DateAdd("m", -7, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “F”, Format(DateAdd("m", -6, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “G”, Format(DateAdd("m", -5, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “H”, Format(DateAdd("m", -4, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “I”, Format(DateAdd("m", -3, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “J”, Format(DateAdd("m", -2, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “L”, Format(DateAdd("m", -1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “M”, Format(DateAdd("m", 1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy")
Cannot forget the error, which might pop up, which needs to be addressed: "The Value expression for the text box ‘Textbox9’ refers directly to the field ‘Indexcolumn’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
."
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53288089%2fssrs-sort-a-report-column-based-on-the-parameter-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
First of all, next time be sure to post what you tried so far and why it is not working. This way people will see that you put some effort into it and they will be happy to help you.
Second go to your Tablix Properties > Sorting > Add and use the sort expression:
=CDate(Fields!YourDate.Value)
Then go to your Tablix Properties > Filters > Add and add the following two filters:
'Expression1
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(Parameters!YourParameter.Value)), "1").AddMonths(1)
'Format1
=Date/Time
'Operator1
>
'Value1
=CDate(Fields!YourDate.Value)
And the second filter:
'Expression2
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(CDate(Parameters!YourParameter.Value))), "1").AddMonths(13)
'Format2
=Date/Time
'Operator2
<
'Value2
=CDate(Fields!YourDate.Value)
add a comment |
First of all, next time be sure to post what you tried so far and why it is not working. This way people will see that you put some effort into it and they will be happy to help you.
Second go to your Tablix Properties > Sorting > Add and use the sort expression:
=CDate(Fields!YourDate.Value)
Then go to your Tablix Properties > Filters > Add and add the following two filters:
'Expression1
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(Parameters!YourParameter.Value)), "1").AddMonths(1)
'Format1
=Date/Time
'Operator1
>
'Value1
=CDate(Fields!YourDate.Value)
And the second filter:
'Expression2
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(CDate(Parameters!YourParameter.Value))), "1").AddMonths(13)
'Format2
=Date/Time
'Operator2
<
'Value2
=CDate(Fields!YourDate.Value)
add a comment |
First of all, next time be sure to post what you tried so far and why it is not working. This way people will see that you put some effort into it and they will be happy to help you.
Second go to your Tablix Properties > Sorting > Add and use the sort expression:
=CDate(Fields!YourDate.Value)
Then go to your Tablix Properties > Filters > Add and add the following two filters:
'Expression1
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(Parameters!YourParameter.Value)), "1").AddMonths(1)
'Format1
=Date/Time
'Operator1
>
'Value1
=CDate(Fields!YourDate.Value)
And the second filter:
'Expression2
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(CDate(Parameters!YourParameter.Value))), "1").AddMonths(13)
'Format2
=Date/Time
'Operator2
<
'Value2
=CDate(Fields!YourDate.Value)
First of all, next time be sure to post what you tried so far and why it is not working. This way people will see that you put some effort into it and they will be happy to help you.
Second go to your Tablix Properties > Sorting > Add and use the sort expression:
=CDate(Fields!YourDate.Value)
Then go to your Tablix Properties > Filters > Add and add the following two filters:
'Expression1
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(Parameters!YourParameter.Value)), "1").AddMonths(1)
'Format1
=Date/Time
'Operator1
>
'Value1
=CDate(Fields!YourDate.Value)
And the second filter:
'Expression2
=DateSerial(Year(DateAdd("yyyy", -1, CDate(Parameters!YourParameter.Value))), Month(CDate(CDate(Parameters!YourParameter.Value))), "1").AddMonths(13)
'Format2
=Date/Time
'Operator2
<
'Value2
=CDate(Fields!YourDate.Value)
answered Nov 14 '18 at 6:26
StrawberryshrubStrawberryshrub
1,1621316
1,1621316
add a comment |
add a comment |
I have modified my dataset to include one more field "IndexColumn", which sorts in a specified order, starting with "A" for a prior year month (April 2017):
=SWITCH(Fields!Indexcolumn.Value = “A”, Format(DateAdd("m", -11, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “B”, Format(DateAdd("m", -10, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “C”, Format(DateAdd("m", -9, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “D”, Format(DateAdd("m", -8, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “E”, Format(DateAdd("m", -7, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “F”, Format(DateAdd("m", -6, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “G”, Format(DateAdd("m", -5, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “H”, Format(DateAdd("m", -4, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “I”, Format(DateAdd("m", -3, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “J”, Format(DateAdd("m", -2, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “L”, Format(DateAdd("m", -1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “M”, Format(DateAdd("m", 1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy")
Cannot forget the error, which might pop up, which needs to be addressed: "The Value expression for the text box ‘Textbox9’ refers directly to the field ‘Indexcolumn’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
."
add a comment |
I have modified my dataset to include one more field "IndexColumn", which sorts in a specified order, starting with "A" for a prior year month (April 2017):
=SWITCH(Fields!Indexcolumn.Value = “A”, Format(DateAdd("m", -11, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “B”, Format(DateAdd("m", -10, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “C”, Format(DateAdd("m", -9, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “D”, Format(DateAdd("m", -8, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “E”, Format(DateAdd("m", -7, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “F”, Format(DateAdd("m", -6, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “G”, Format(DateAdd("m", -5, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “H”, Format(DateAdd("m", -4, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “I”, Format(DateAdd("m", -3, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “J”, Format(DateAdd("m", -2, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “L”, Format(DateAdd("m", -1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “M”, Format(DateAdd("m", 1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy")
Cannot forget the error, which might pop up, which needs to be addressed: "The Value expression for the text box ‘Textbox9’ refers directly to the field ‘Indexcolumn’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
."
add a comment |
I have modified my dataset to include one more field "IndexColumn", which sorts in a specified order, starting with "A" for a prior year month (April 2017):
=SWITCH(Fields!Indexcolumn.Value = “A”, Format(DateAdd("m", -11, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “B”, Format(DateAdd("m", -10, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “C”, Format(DateAdd("m", -9, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “D”, Format(DateAdd("m", -8, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “E”, Format(DateAdd("m", -7, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “F”, Format(DateAdd("m", -6, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “G”, Format(DateAdd("m", -5, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “H”, Format(DateAdd("m", -4, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “I”, Format(DateAdd("m", -3, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “J”, Format(DateAdd("m", -2, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “L”, Format(DateAdd("m", -1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “M”, Format(DateAdd("m", 1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy")
Cannot forget the error, which might pop up, which needs to be addressed: "The Value expression for the text box ‘Textbox9’ refers directly to the field ‘Indexcolumn’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
."
I have modified my dataset to include one more field "IndexColumn", which sorts in a specified order, starting with "A" for a prior year month (April 2017):
=SWITCH(Fields!Indexcolumn.Value = “A”, Format(DateAdd("m", -11, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “B”, Format(DateAdd("m", -10, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “C”, Format(DateAdd("m", -9, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “D”, Format(DateAdd("m", -8, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “E”, Format(DateAdd("m", -7, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “F”, Format(DateAdd("m", -6, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “G”, Format(DateAdd("m", -5, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “H”, Format(DateAdd("m", -4, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “I”, Format(DateAdd("m", -3, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “J”, Format(DateAdd("m", -2, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “L”, Format(DateAdd("m", -1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy"),
Fields!Indexcolumn.Value = “M”, Format(DateAdd("m", 1, CDate(Parameters!AsOfDate.Value)),"MMM yyyy")
Cannot forget the error, which might pop up, which needs to be addressed: "The Value expression for the text box ‘Textbox9’ refers directly to the field ‘Indexcolumn’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.
."
edited Nov 14 '18 at 16:27
answered Nov 14 '18 at 15:42
user10581977user10581977
43
43
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53288089%2fssrs-sort-a-report-column-based-on-the-parameter-value%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown