Report Builder charts - grouping data by a specific age group
0
I have a pretty large dataset where learners are divided into age groups (16-18, 19+). I am trying to calculate the retention of learners (completed field/ Leavers field) and display the chart by just one of those age groups (16-18).
How can I do this as an expression?
reporting-services
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
add a comment |
0
I have a pretty large dataset where learners are divided into age groups (16-18, 19+). I am trying to calculate the retention of learners (completed field/ Leavers field) and display the chart by just one of those age groups (16-18).
How can I do this as an expression?
reporting-services
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
This is the script I am using but it brings back zero whereas I am expecting around 1300 starts: =Sum(Fields!Starts.Value) AND (Fields!Age_Group.Value) = 16-18
– Paul Eatwell
Nov 26 '18 at 11:46
add a comment |
0
0
0
I have a pretty large dataset where learners are divided into age groups (16-18, 19+). I am trying to calculate the retention of learners (completed field/ Leavers field) and display the chart by just one of those age groups (16-18).
How can I do this as an expression?
reporting-services
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
I have a pretty large dataset where learners are divided into age groups (16-18, 19+). I am trying to calculate the retention of learners (completed field/ Leavers field) and display the chart by just one of those age groups (16-18).
How can I do this as an expression?
reporting-services
reporting-services
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
asked Nov 26 '18 at 11:08
Paul EatwellPaul Eatwell
32
32
This is the script I am using but it brings back zero whereas I am expecting around 1300 starts: =Sum(Fields!Starts.Value) AND (Fields!Age_Group.Value) = 16-18
– Paul Eatwell
Nov 26 '18 at 11:46
add a comment |
This is the script I am using but it brings back zero whereas I am expecting around 1300 starts: =Sum(Fields!Starts.Value) AND (Fields!Age_Group.Value) = 16-18
– Paul Eatwell
Nov 26 '18 at 11:46
This is the script I am using but it brings back zero whereas I am expecting around 1300 starts: =Sum(Fields!Starts.Value) AND (Fields!Age_Group.Value) = 16-18
– Paul Eatwell
Nov 26 '18 at 11:46
This is the script I am using but it brings back zero whereas I am expecting around 1300 starts: =Sum(Fields!Starts.Value) AND (Fields!Age_Group.Value) = 16-18
– Paul Eatwell
Nov 26 '18 at 11:46
add a comment |
1 Answer
1
active
oldest
votes
0
I would create a calculated field where you can store the different age groups:
'Name: AgeGroups
=IIF(CInt(Fields!Age.Value) > 15 And CInt(Fields!Age.Value) < 19, "16-18", "19+")
Now add everything you need to your chart. The Fields!AgeGroups.Value is now the cathegory. In the chart properties under filter you can add the following filter, to display just one of the age group:
'Expression
=Fields!AgeGroups.Value
'Format
String
'Operator
=
'Value
16-18
This way if your custom grouping changes sometimes, you just need to apply the changes in the calculated field once.
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
Thanks for your reply. This I have already done but how do I get the calculation into the expression (completed field/ Leavers field)? I need to know the retention of all learners who are 16-18? The expression needs to have both criteria... must be 16-18 and then the calculation to return their retention
– Paul Eatwell
Nov 26 '18 at 13:16
Just use the expression as chart data values:=Sum(CDbl(Fields!Completed.Value)) / Sum(CDbl(Fields!Leavers.Value)). This expression is your chart value. It gets filtered now and then summed up by your category.
– Strawberryshrub
Nov 26 '18 at 13:21
Apologies, I totally forgot to mention that I am using Academic year (3 years) as my category. So it is, in actual fact, 3 different criteria. Academic year in the category, then the calculation in the expression for values
– Paul Eatwell
Nov 26 '18 at 13:26
Thats no problem you can add as much categorys as you want, just wrap it in theIIF()statements. For example:IIF(CInt(Fields!Age.Value) < 15, "Smaller 15", IIF(CInt(Fields!Age.Value) = 16, "Equals 16", "Bigger 16"))
– Strawberryshrub
Nov 27 '18 at 5:21
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f53479829%2freport-builder-charts-grouping-data-by-a-specific-age-group%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
0
I would create a calculated field where you can store the different age groups:
'Name: AgeGroups
=IIF(CInt(Fields!Age.Value) > 15 And CInt(Fields!Age.Value) < 19, "16-18", "19+")
Now add everything you need to your chart. The Fields!AgeGroups.Value is now the cathegory. In the chart properties under filter you can add the following filter, to display just one of the age group:
'Expression
=Fields!AgeGroups.Value
'Format
String
'Operator
=
'Value
16-18
This way if your custom grouping changes sometimes, you just need to apply the changes in the calculated field once.
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
Thanks for your reply. This I have already done but how do I get the calculation into the expression (completed field/ Leavers field)? I need to know the retention of all learners who are 16-18? The expression needs to have both criteria... must be 16-18 and then the calculation to return their retention
– Paul Eatwell
Nov 26 '18 at 13:16
Just use the expression as chart data values:=Sum(CDbl(Fields!Completed.Value)) / Sum(CDbl(Fields!Leavers.Value)). This expression is your chart value. It gets filtered now and then summed up by your category.
– Strawberryshrub
Nov 26 '18 at 13:21
Apologies, I totally forgot to mention that I am using Academic year (3 years) as my category. So it is, in actual fact, 3 different criteria. Academic year in the category, then the calculation in the expression for values
– Paul Eatwell
Nov 26 '18 at 13:26
Thats no problem you can add as much categorys as you want, just wrap it in theIIF()statements. For example:IIF(CInt(Fields!Age.Value) < 15, "Smaller 15", IIF(CInt(Fields!Age.Value) = 16, "Equals 16", "Bigger 16"))
– Strawberryshrub
Nov 27 '18 at 5:21
add a comment |
0
I would create a calculated field where you can store the different age groups:
'Name: AgeGroups
=IIF(CInt(Fields!Age.Value) > 15 And CInt(Fields!Age.Value) < 19, "16-18", "19+")
Now add everything you need to your chart. The Fields!AgeGroups.Value is now the cathegory. In the chart properties under filter you can add the following filter, to display just one of the age group:
'Expression
=Fields!AgeGroups.Value
'Format
String
'Operator
=
'Value
16-18
This way if your custom grouping changes sometimes, you just need to apply the changes in the calculated field once.
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
Thanks for your reply. This I have already done but how do I get the calculation into the expression (completed field/ Leavers field)? I need to know the retention of all learners who are 16-18? The expression needs to have both criteria... must be 16-18 and then the calculation to return their retention
– Paul Eatwell
Nov 26 '18 at 13:16
Just use the expression as chart data values:=Sum(CDbl(Fields!Completed.Value)) / Sum(CDbl(Fields!Leavers.Value)). This expression is your chart value. It gets filtered now and then summed up by your category.
– Strawberryshrub
Nov 26 '18 at 13:21
Apologies, I totally forgot to mention that I am using Academic year (3 years) as my category. So it is, in actual fact, 3 different criteria. Academic year in the category, then the calculation in the expression for values
– Paul Eatwell
Nov 26 '18 at 13:26
Thats no problem you can add as much categorys as you want, just wrap it in theIIF()statements. For example:IIF(CInt(Fields!Age.Value) < 15, "Smaller 15", IIF(CInt(Fields!Age.Value) = 16, "Equals 16", "Bigger 16"))
– Strawberryshrub
Nov 27 '18 at 5:21
add a comment |
0
0
0
I would create a calculated field where you can store the different age groups:
'Name: AgeGroups
=IIF(CInt(Fields!Age.Value) > 15 And CInt(Fields!Age.Value) < 19, "16-18", "19+")
Now add everything you need to your chart. The Fields!AgeGroups.Value is now the cathegory. In the chart properties under filter you can add the following filter, to display just one of the age group:
'Expression
=Fields!AgeGroups.Value
'Format
String
'Operator
=
'Value
16-18
This way if your custom grouping changes sometimes, you just need to apply the changes in the calculated field once.
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
I would create a calculated field where you can store the different age groups:
'Name: AgeGroups
=IIF(CInt(Fields!Age.Value) > 15 And CInt(Fields!Age.Value) < 19, "16-18", "19+")
Now add everything you need to your chart. The Fields!AgeGroups.Value is now the cathegory. In the chart properties under filter you can add the following filter, to display just one of the age group:
'Expression
=Fields!AgeGroups.Value
'Format
String
'Operator
=
'Value
16-18
This way if your custom grouping changes sometimes, you just need to apply the changes in the calculated field once.
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
answered Nov 26 '18 at 11:46
StrawberryshrubStrawberryshrub
1,5482517
1,5482517
Thanks for your reply. This I have already done but how do I get the calculation into the expression (completed field/ Leavers field)? I need to know the retention of all learners who are 16-18? The expression needs to have both criteria... must be 16-18 and then the calculation to return their retention
– Paul Eatwell
Nov 26 '18 at 13:16
Just use the expression as chart data values:=Sum(CDbl(Fields!Completed.Value)) / Sum(CDbl(Fields!Leavers.Value)). This expression is your chart value. It gets filtered now and then summed up by your category.
– Strawberryshrub
Nov 26 '18 at 13:21
Apologies, I totally forgot to mention that I am using Academic year (3 years) as my category. So it is, in actual fact, 3 different criteria. Academic year in the category, then the calculation in the expression for values
– Paul Eatwell
Nov 26 '18 at 13:26
Thats no problem you can add as much categorys as you want, just wrap it in theIIF()statements. For example:IIF(CInt(Fields!Age.Value) < 15, "Smaller 15", IIF(CInt(Fields!Age.Value) = 16, "Equals 16", "Bigger 16"))
– Strawberryshrub
Nov 27 '18 at 5:21
add a comment |
Thanks for your reply. This I have already done but how do I get the calculation into the expression (completed field/ Leavers field)? I need to know the retention of all learners who are 16-18? The expression needs to have both criteria... must be 16-18 and then the calculation to return their retention
– Paul Eatwell
Nov 26 '18 at 13:16
Just use the expression as chart data values:=Sum(CDbl(Fields!Completed.Value)) / Sum(CDbl(Fields!Leavers.Value)). This expression is your chart value. It gets filtered now and then summed up by your category.
– Strawberryshrub
Nov 26 '18 at 13:21
Apologies, I totally forgot to mention that I am using Academic year (3 years) as my category. So it is, in actual fact, 3 different criteria. Academic year in the category, then the calculation in the expression for values
– Paul Eatwell
Nov 26 '18 at 13:26
Thats no problem you can add as much categorys as you want, just wrap it in theIIF()statements. For example:IIF(CInt(Fields!Age.Value) < 15, "Smaller 15", IIF(CInt(Fields!Age.Value) = 16, "Equals 16", "Bigger 16"))
– Strawberryshrub
Nov 27 '18 at 5:21
Thanks for your reply. This I have already done but how do I get the calculation into the expression (completed field/ Leavers field)? I need to know the retention of all learners who are 16-18? The expression needs to have both criteria... must be 16-18 and then the calculation to return their retention
– Paul Eatwell
Nov 26 '18 at 13:16
Thanks for your reply. This I have already done but how do I get the calculation into the expression (completed field/ Leavers field)? I need to know the retention of all learners who are 16-18? The expression needs to have both criteria... must be 16-18 and then the calculation to return their retention
– Paul Eatwell
Nov 26 '18 at 13:16
Just use the expression as chart data values:
=Sum(CDbl(Fields!Completed.Value)) / Sum(CDbl(Fields!Leavers.Value)). This expression is your chart value. It gets filtered now and then summed up by your category.– Strawberryshrub
Nov 26 '18 at 13:21
Just use the expression as chart data values:
=Sum(CDbl(Fields!Completed.Value)) / Sum(CDbl(Fields!Leavers.Value)). This expression is your chart value. It gets filtered now and then summed up by your category.– Strawberryshrub
Nov 26 '18 at 13:21
Apologies, I totally forgot to mention that I am using Academic year (3 years) as my category. So it is, in actual fact, 3 different criteria. Academic year in the category, then the calculation in the expression for values
– Paul Eatwell
Nov 26 '18 at 13:26
Apologies, I totally forgot to mention that I am using Academic year (3 years) as my category. So it is, in actual fact, 3 different criteria. Academic year in the category, then the calculation in the expression for values
– Paul Eatwell
Nov 26 '18 at 13:26
Thats no problem you can add as much categorys as you want, just wrap it in the
IIF() statements. For example: IIF(CInt(Fields!Age.Value) < 15, "Smaller 15", IIF(CInt(Fields!Age.Value) = 16, "Equals 16", "Bigger 16"))– Strawberryshrub
Nov 27 '18 at 5:21
Thats no problem you can add as much categorys as you want, just wrap it in the
IIF() statements. For example: IIF(CInt(Fields!Age.Value) < 15, "Smaller 15", IIF(CInt(Fields!Age.Value) = 16, "Equals 16", "Bigger 16"))– Strawberryshrub
Nov 27 '18 at 5:21
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53479829%2freport-builder-charts-grouping-data-by-a-specific-age-group%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
This is the script I am using but it brings back zero whereas I am expecting around 1300 starts: =Sum(Fields!Starts.Value) AND (Fields!Age_Group.Value) = 16-18
– Paul Eatwell
Nov 26 '18 at 11:46