Filter data on Tablix based on value from another Dataset in Report Server
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
In my Report I have two DataSets. One of them returns Car Names, and another one returns Car infos.
So, for illustration as the real data is a bit more complicated, First Data set has
[Car Name] [Row]
Honda 1
Toyota 2
BMW 3
Second Data set is
[Car Name] [Color] [Weight]
Honda Blue 1500kg
Toyota Red 1650kg
I need to mention that both DataSets are dynamic, meaning they will have different outputs in all columns. The final result that I'm looking for is like this
[Car Name]
[Color]
[Weight]
...
This data will be contained within several rectangles that are going to be dynamically created. The [Car Name] column in the final result is dynamic and is connected to the [Row] column in the First Data Set. So, for example, the value of the first rectangle that gets data from DataSet1 has the expression
=First(IIf(Fields!Row.Value = 1, Fields!CarName.Value, 0),"DataSet1")
Since this data is Dynamic, I need the below data for [Color] and [Weight] to be shown only for that [Car Name] which is dynamic from DataSet1. How can I filter the DataSet2 to only show that dynamically set [Car Name]? I tried using Filters in Tablix, but I can't use ReportItems!TextBoxXX.Value for example.
I can't solve this on SQL level.
reporting-services ssrs-tablix
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
add a comment |
In my Report I have two DataSets. One of them returns Car Names, and another one returns Car infos.
So, for illustration as the real data is a bit more complicated, First Data set has
[Car Name] [Row]
Honda 1
Toyota 2
BMW 3
Second Data set is
[Car Name] [Color] [Weight]
Honda Blue 1500kg
Toyota Red 1650kg
I need to mention that both DataSets are dynamic, meaning they will have different outputs in all columns. The final result that I'm looking for is like this
[Car Name]
[Color]
[Weight]
...
This data will be contained within several rectangles that are going to be dynamically created. The [Car Name] column in the final result is dynamic and is connected to the [Row] column in the First Data Set. So, for example, the value of the first rectangle that gets data from DataSet1 has the expression
=First(IIf(Fields!Row.Value = 1, Fields!CarName.Value, 0),"DataSet1")
Since this data is Dynamic, I need the below data for [Color] and [Weight] to be shown only for that [Car Name] which is dynamic from DataSet1. How can I filter the DataSet2 to only show that dynamically set [Car Name]? I tried using Filters in Tablix, but I can't use ReportItems!TextBoxXX.Value for example.
I can't solve this on SQL level.
reporting-services ssrs-tablix
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
add a comment |
In my Report I have two DataSets. One of them returns Car Names, and another one returns Car infos.
So, for illustration as the real data is a bit more complicated, First Data set has
[Car Name] [Row]
Honda 1
Toyota 2
BMW 3
Second Data set is
[Car Name] [Color] [Weight]
Honda Blue 1500kg
Toyota Red 1650kg
I need to mention that both DataSets are dynamic, meaning they will have different outputs in all columns. The final result that I'm looking for is like this
[Car Name]
[Color]
[Weight]
...
This data will be contained within several rectangles that are going to be dynamically created. The [Car Name] column in the final result is dynamic and is connected to the [Row] column in the First Data Set. So, for example, the value of the first rectangle that gets data from DataSet1 has the expression
=First(IIf(Fields!Row.Value = 1, Fields!CarName.Value, 0),"DataSet1")
Since this data is Dynamic, I need the below data for [Color] and [Weight] to be shown only for that [Car Name] which is dynamic from DataSet1. How can I filter the DataSet2 to only show that dynamically set [Car Name]? I tried using Filters in Tablix, but I can't use ReportItems!TextBoxXX.Value for example.
I can't solve this on SQL level.
reporting-services ssrs-tablix
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
In my Report I have two DataSets. One of them returns Car Names, and another one returns Car infos.
So, for illustration as the real data is a bit more complicated, First Data set has
[Car Name] [Row]
Honda 1
Toyota 2
BMW 3
Second Data set is
[Car Name] [Color] [Weight]
Honda Blue 1500kg
Toyota Red 1650kg
I need to mention that both DataSets are dynamic, meaning they will have different outputs in all columns. The final result that I'm looking for is like this
[Car Name]
[Color]
[Weight]
...
This data will be contained within several rectangles that are going to be dynamically created. The [Car Name] column in the final result is dynamic and is connected to the [Row] column in the First Data Set. So, for example, the value of the first rectangle that gets data from DataSet1 has the expression
=First(IIf(Fields!Row.Value = 1, Fields!CarName.Value, 0),"DataSet1")
Since this data is Dynamic, I need the below data for [Color] and [Weight] to be shown only for that [Car Name] which is dynamic from DataSet1. How can I filter the DataSet2 to only show that dynamically set [Car Name]? I tried using Filters in Tablix, but I can't use ReportItems!TextBoxXX.Value for example.
I can't solve this on SQL level.
reporting-services ssrs-tablix
reporting-services ssrs-tablix
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
asked Nov 23 '18 at 21:02
Nikola L.Nikola L.
112311
112311
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
The following expression gives you for the same car names in both datasets the color from the second dataset. I am not sure what you want to do but this might help you:
'For the car color
=Lookup(Fields!CarNameDataset1.Value, Fields!CarNameDataset2.Value, Fields!CarColorDataset2.Value, "Dataset2")
You can put this into a filter expression as well.
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
This is something I have considered, but the thing is, that DataSet1 is filtered as well, so I need a specific value from DataSet2, not all values that match the CarName in Dataset1. So, for first "rectangle" im filtering the 1st row from the first DataSet (which can be Honda sometimes, can be Toyota), and for the Color and Weight I get that Data from second DataSet2. All I need is to match the Car type from the dataset1, only for that Car that is on Row1 from first DataSet.
– Nikola L.
Nov 24 '18 at 12:15
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%2f53452915%2ffilter-data-on-tablix-based-on-value-from-another-dataset-in-report-server%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
The following expression gives you for the same car names in both datasets the color from the second dataset. I am not sure what you want to do but this might help you:
'For the car color
=Lookup(Fields!CarNameDataset1.Value, Fields!CarNameDataset2.Value, Fields!CarColorDataset2.Value, "Dataset2")
You can put this into a filter expression as well.
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
This is something I have considered, but the thing is, that DataSet1 is filtered as well, so I need a specific value from DataSet2, not all values that match the CarName in Dataset1. So, for first "rectangle" im filtering the 1st row from the first DataSet (which can be Honda sometimes, can be Toyota), and for the Color and Weight I get that Data from second DataSet2. All I need is to match the Car type from the dataset1, only for that Car that is on Row1 from first DataSet.
– Nikola L.
Nov 24 '18 at 12:15
add a comment |
The following expression gives you for the same car names in both datasets the color from the second dataset. I am not sure what you want to do but this might help you:
'For the car color
=Lookup(Fields!CarNameDataset1.Value, Fields!CarNameDataset2.Value, Fields!CarColorDataset2.Value, "Dataset2")
You can put this into a filter expression as well.
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
This is something I have considered, but the thing is, that DataSet1 is filtered as well, so I need a specific value from DataSet2, not all values that match the CarName in Dataset1. So, for first "rectangle" im filtering the 1st row from the first DataSet (which can be Honda sometimes, can be Toyota), and for the Color and Weight I get that Data from second DataSet2. All I need is to match the Car type from the dataset1, only for that Car that is on Row1 from first DataSet.
– Nikola L.
Nov 24 '18 at 12:15
add a comment |
The following expression gives you for the same car names in both datasets the color from the second dataset. I am not sure what you want to do but this might help you:
'For the car color
=Lookup(Fields!CarNameDataset1.Value, Fields!CarNameDataset2.Value, Fields!CarColorDataset2.Value, "Dataset2")
You can put this into a filter expression as well.
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
The following expression gives you for the same car names in both datasets the color from the second dataset. I am not sure what you want to do but this might help you:
'For the car color
=Lookup(Fields!CarNameDataset1.Value, Fields!CarNameDataset2.Value, Fields!CarColorDataset2.Value, "Dataset2")
You can put this into a filter expression as well.
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
answered Nov 24 '18 at 7:04
StrawberryshrubStrawberryshrub
1,5502517
1,5502517
This is something I have considered, but the thing is, that DataSet1 is filtered as well, so I need a specific value from DataSet2, not all values that match the CarName in Dataset1. So, for first "rectangle" im filtering the 1st row from the first DataSet (which can be Honda sometimes, can be Toyota), and for the Color and Weight I get that Data from second DataSet2. All I need is to match the Car type from the dataset1, only for that Car that is on Row1 from first DataSet.
– Nikola L.
Nov 24 '18 at 12:15
add a comment |
This is something I have considered, but the thing is, that DataSet1 is filtered as well, so I need a specific value from DataSet2, not all values that match the CarName in Dataset1. So, for first "rectangle" im filtering the 1st row from the first DataSet (which can be Honda sometimes, can be Toyota), and for the Color and Weight I get that Data from second DataSet2. All I need is to match the Car type from the dataset1, only for that Car that is on Row1 from first DataSet.
– Nikola L.
Nov 24 '18 at 12:15
This is something I have considered, but the thing is, that DataSet1 is filtered as well, so I need a specific value from DataSet2, not all values that match the CarName in Dataset1. So, for first "rectangle" im filtering the 1st row from the first DataSet (which can be Honda sometimes, can be Toyota), and for the Color and Weight I get that Data from second DataSet2. All I need is to match the Car type from the dataset1, only for that Car that is on Row1 from first DataSet.
– Nikola L.
Nov 24 '18 at 12:15
This is something I have considered, but the thing is, that DataSet1 is filtered as well, so I need a specific value from DataSet2, not all values that match the CarName in Dataset1. So, for first "rectangle" im filtering the 1st row from the first DataSet (which can be Honda sometimes, can be Toyota), and for the Color and Weight I get that Data from second DataSet2. All I need is to match the Car type from the dataset1, only for that Car that is on Row1 from first DataSet.
– Nikola L.
Nov 24 '18 at 12:15
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%2f53452915%2ffilter-data-on-tablix-based-on-value-from-another-dataset-in-report-server%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
