How to determine which value of a grouping in shown in Reporting Services
I have a report, in which the highest level of detail produces 2 rows of the dataset, which I need to show grouped. The grouping works fine, but a few of the columns still contain different values; how do I tell the report which value it should display?
Example:
The user wants each row of the report to be grouped by Brand name. 1 Brand can have multiple agreement that are relevant for the numbers to be shown in the report. The numbers can be aggregated, but there is also data that has changed with the new agreement, so only the most recent version needs to be shown
Agreement 1: item-A, item-B, value-A
Agreement 2: item-B, item-C, value B
Grouping by Brand should show:
Agreement: item-B, item-C, sum(value-A+B). But I do not know how to tell the report which items to show.
SQL for the dataset shouldn't be modified in order to maintain option in report to show both rows separate as well.
Any help would be much appreciated :)
Thank you
reporting-services visual-studio-2015
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
add a comment |
I have a report, in which the highest level of detail produces 2 rows of the dataset, which I need to show grouped. The grouping works fine, but a few of the columns still contain different values; how do I tell the report which value it should display?
Example:
The user wants each row of the report to be grouped by Brand name. 1 Brand can have multiple agreement that are relevant for the numbers to be shown in the report. The numbers can be aggregated, but there is also data that has changed with the new agreement, so only the most recent version needs to be shown
Agreement 1: item-A, item-B, value-A
Agreement 2: item-B, item-C, value B
Grouping by Brand should show:
Agreement: item-B, item-C, sum(value-A+B). But I do not know how to tell the report which items to show.
SQL for the dataset shouldn't be modified in order to maintain option in report to show both rows separate as well.
Any help would be much appreciated :)
Thank you
reporting-services visual-studio-2015
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
That depends on the format and value ofItem-A,Item-BandValue-A(ForStringorNumberyou can choose different aggregate functions, which might help or not). Do you have maybe a minimal example of this data? It would be best if you add a table with the raw data and a table that shows the end result.
– Strawberryshrub
Nov 22 '18 at 7:03
The aggregation of the Value-A (which are Numbers) is working as expected. Item-A and B though are Strings, for which I want to choose what to show. Example of the data: row 1: cre-h&m-045, 1000 row 2: cre-h&m-37/38, null In this case, the grouped result should show: row1: cre-h&m-045, 1000
– tkloosterman
Nov 22 '18 at 8:46
add a comment |
I have a report, in which the highest level of detail produces 2 rows of the dataset, which I need to show grouped. The grouping works fine, but a few of the columns still contain different values; how do I tell the report which value it should display?
Example:
The user wants each row of the report to be grouped by Brand name. 1 Brand can have multiple agreement that are relevant for the numbers to be shown in the report. The numbers can be aggregated, but there is also data that has changed with the new agreement, so only the most recent version needs to be shown
Agreement 1: item-A, item-B, value-A
Agreement 2: item-B, item-C, value B
Grouping by Brand should show:
Agreement: item-B, item-C, sum(value-A+B). But I do not know how to tell the report which items to show.
SQL for the dataset shouldn't be modified in order to maintain option in report to show both rows separate as well.
Any help would be much appreciated :)
Thank you
reporting-services visual-studio-2015
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
I have a report, in which the highest level of detail produces 2 rows of the dataset, which I need to show grouped. The grouping works fine, but a few of the columns still contain different values; how do I tell the report which value it should display?
Example:
The user wants each row of the report to be grouped by Brand name. 1 Brand can have multiple agreement that are relevant for the numbers to be shown in the report. The numbers can be aggregated, but there is also data that has changed with the new agreement, so only the most recent version needs to be shown
Agreement 1: item-A, item-B, value-A
Agreement 2: item-B, item-C, value B
Grouping by Brand should show:
Agreement: item-B, item-C, sum(value-A+B). But I do not know how to tell the report which items to show.
SQL for the dataset shouldn't be modified in order to maintain option in report to show both rows separate as well.
Any help would be much appreciated :)
Thank you
reporting-services visual-studio-2015
reporting-services visual-studio-2015
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
asked Nov 21 '18 at 18:11
tkloostermantkloosterman
2028
2028
That depends on the format and value ofItem-A,Item-BandValue-A(ForStringorNumberyou can choose different aggregate functions, which might help or not). Do you have maybe a minimal example of this data? It would be best if you add a table with the raw data and a table that shows the end result.
– Strawberryshrub
Nov 22 '18 at 7:03
The aggregation of the Value-A (which are Numbers) is working as expected. Item-A and B though are Strings, for which I want to choose what to show. Example of the data: row 1: cre-h&m-045, 1000 row 2: cre-h&m-37/38, null In this case, the grouped result should show: row1: cre-h&m-045, 1000
– tkloosterman
Nov 22 '18 at 8:46
add a comment |
That depends on the format and value ofItem-A,Item-BandValue-A(ForStringorNumberyou can choose different aggregate functions, which might help or not). Do you have maybe a minimal example of this data? It would be best if you add a table with the raw data and a table that shows the end result.
– Strawberryshrub
Nov 22 '18 at 7:03
The aggregation of the Value-A (which are Numbers) is working as expected. Item-A and B though are Strings, for which I want to choose what to show. Example of the data: row 1: cre-h&m-045, 1000 row 2: cre-h&m-37/38, null In this case, the grouped result should show: row1: cre-h&m-045, 1000
– tkloosterman
Nov 22 '18 at 8:46
That depends on the format and value of
Item-A, Item-B and Value-A (For Stringor Numberyou can choose different aggregate functions, which might help or not). Do you have maybe a minimal example of this data? It would be best if you add a table with the raw data and a table that shows the end result.– Strawberryshrub
Nov 22 '18 at 7:03
That depends on the format and value of
Item-A, Item-B and Value-A (For Stringor Numberyou can choose different aggregate functions, which might help or not). Do you have maybe a minimal example of this data? It would be best if you add a table with the raw data and a table that shows the end result.– Strawberryshrub
Nov 22 '18 at 7:03
The aggregation of the Value-A (which are Numbers) is working as expected. Item-A and B though are Strings, for which I want to choose what to show. Example of the data: row 1: cre-h&m-045, 1000 row 2: cre-h&m-37/38, null In this case, the grouped result should show: row1: cre-h&m-045, 1000
– tkloosterman
Nov 22 '18 at 8:46
The aggregation of the Value-A (which are Numbers) is working as expected. Item-A and B though are Strings, for which I want to choose what to show. Example of the data: row 1: cre-h&m-045, 1000 row 2: cre-h&m-37/38, null In this case, the grouped result should show: row1: cre-h&m-045, 1000
– tkloosterman
Nov 22 '18 at 8:46
add a comment |
1 Answer
1
active
oldest
votes
If it is a string you propably can just use the aggregations First() and Last().
But there is a workaround. You have to define a calculated field in your dataset and write your desired expression into it. Think of it like a rating system. The biggest value is the value you will chose in the grouping, because in the grouping you just have to use Max()then.
For example as calculated field (This is a example for strings, you can also convert in double and use > or <, that depens on your variations from the value of ItemA):
'Calculated field name: CustomField
=Switch(Fields!ItemA.Value = Nothing, 1,
Fields!ItemA.Value = "500", 2,
Fields!ItemA.Value = "1000", 3)
Now add your grouping and chose the calculated field in the groupings like this:
Max(Fields!CustomField.Value)
Then hide the Fields!CustomField.Value and show the Fields!ItemA.Value. This way it will display the row value of Fields!ItemA.Value and you custom grouped after Fields!CustomField.Value.
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
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%2f53418201%2fhow-to-determine-which-value-of-a-grouping-in-shown-in-reporting-services%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If it is a string you propably can just use the aggregations First() and Last().
But there is a workaround. You have to define a calculated field in your dataset and write your desired expression into it. Think of it like a rating system. The biggest value is the value you will chose in the grouping, because in the grouping you just have to use Max()then.
For example as calculated field (This is a example for strings, you can also convert in double and use > or <, that depens on your variations from the value of ItemA):
'Calculated field name: CustomField
=Switch(Fields!ItemA.Value = Nothing, 1,
Fields!ItemA.Value = "500", 2,
Fields!ItemA.Value = "1000", 3)
Now add your grouping and chose the calculated field in the groupings like this:
Max(Fields!CustomField.Value)
Then hide the Fields!CustomField.Value and show the Fields!ItemA.Value. This way it will display the row value of Fields!ItemA.Value and you custom grouped after Fields!CustomField.Value.
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
add a comment |
If it is a string you propably can just use the aggregations First() and Last().
But there is a workaround. You have to define a calculated field in your dataset and write your desired expression into it. Think of it like a rating system. The biggest value is the value you will chose in the grouping, because in the grouping you just have to use Max()then.
For example as calculated field (This is a example for strings, you can also convert in double and use > or <, that depens on your variations from the value of ItemA):
'Calculated field name: CustomField
=Switch(Fields!ItemA.Value = Nothing, 1,
Fields!ItemA.Value = "500", 2,
Fields!ItemA.Value = "1000", 3)
Now add your grouping and chose the calculated field in the groupings like this:
Max(Fields!CustomField.Value)
Then hide the Fields!CustomField.Value and show the Fields!ItemA.Value. This way it will display the row value of Fields!ItemA.Value and you custom grouped after Fields!CustomField.Value.
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
add a comment |
If it is a string you propably can just use the aggregations First() and Last().
But there is a workaround. You have to define a calculated field in your dataset and write your desired expression into it. Think of it like a rating system. The biggest value is the value you will chose in the grouping, because in the grouping you just have to use Max()then.
For example as calculated field (This is a example for strings, you can also convert in double and use > or <, that depens on your variations from the value of ItemA):
'Calculated field name: CustomField
=Switch(Fields!ItemA.Value = Nothing, 1,
Fields!ItemA.Value = "500", 2,
Fields!ItemA.Value = "1000", 3)
Now add your grouping and chose the calculated field in the groupings like this:
Max(Fields!CustomField.Value)
Then hide the Fields!CustomField.Value and show the Fields!ItemA.Value. This way it will display the row value of Fields!ItemA.Value and you custom grouped after Fields!CustomField.Value.
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
If it is a string you propably can just use the aggregations First() and Last().
But there is a workaround. You have to define a calculated field in your dataset and write your desired expression into it. Think of it like a rating system. The biggest value is the value you will chose in the grouping, because in the grouping you just have to use Max()then.
For example as calculated field (This is a example for strings, you can also convert in double and use > or <, that depens on your variations from the value of ItemA):
'Calculated field name: CustomField
=Switch(Fields!ItemA.Value = Nothing, 1,
Fields!ItemA.Value = "500", 2,
Fields!ItemA.Value = "1000", 3)
Now add your grouping and chose the calculated field in the groupings like this:
Max(Fields!CustomField.Value)
Then hide the Fields!CustomField.Value and show the Fields!ItemA.Value. This way it will display the row value of Fields!ItemA.Value and you custom grouped after Fields!CustomField.Value.
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
answered Nov 22 '18 at 11:34
StrawberryshrubStrawberryshrub
1,5312517
1,5312517
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%2f53418201%2fhow-to-determine-which-value-of-a-grouping-in-shown-in-reporting-services%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
That depends on the format and value of
Item-A,Item-BandValue-A(ForStringorNumberyou can choose different aggregate functions, which might help or not). Do you have maybe a minimal example of this data? It would be best if you add a table with the raw data and a table that shows the end result.– Strawberryshrub
Nov 22 '18 at 7:03
The aggregation of the Value-A (which are Numbers) is working as expected. Item-A and B though are Strings, for which I want to choose what to show. Example of the data: row 1: cre-h&m-045, 1000 row 2: cre-h&m-37/38, null In this case, the grouped result should show: row1: cre-h&m-045, 1000
– tkloosterman
Nov 22 '18 at 8:46