Mixing
ssrs lookupset between 2 datasets using date range as part of destination expression
I have 2 SQL databases I am pulling data from. I am unable to query across servers or merge this data in SQL, so I need to have 2 separate datasets & perform a Join / Lookupset between the 2.
The first dataset has order / agent data.
The second dataset has call log information.
(The call data does not have any linkage to the order data so I am "joining" on the phone number of the call to one we have on the order.)
What I want to do is provide all of the calls received by the phone number on the date the agent says a specific task happened.
I need to join on 3 fields, the phone, the date & the agent. This I am able to do no problem by concatenating the 3 fields on each side of the join to then return the value I want from the 2nd dataset.
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_Date.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_Date.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
This code works just file & returns the ident value I am looking for when the call & the task are completed on the same date.
I am now being asked to enhance this by looking for any calls that happened on or up to 3 days prior to the task being completed.
This is where I am stuck, how to use a date range as part of the expression.
What I am currently doing in the SQL for both datasets is calculating the calendar week & can use that in place of a date range, but for a task completed on Friday I can be including calls that happened on Monday instead of Wednesday through Friday.
Here is my code to match by week:
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_WK.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_WK.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
the vbCRLF creates a line break between each returned value as there can be several calls on the same day.
CPCCC_Date is my task completion date in the 1st data set.
Rec_Date is the date of the call.
It would be easy enough in the SQL for the 2nd data set to create a Rec Date - 3 days value (I also have an business Days function I can use in SQL to get the date that way instead of in SSRS).
So if the task was completed on Friday the 13th, I want to return calls on the 10th through the 13th (instead of the 8th - 13th).
So adding a field that pulls the calendar week for these 2 datasets & matching on does work, but I am looking to see if anyone has been able to use a range as part of the expressions, that way I can dial this in better to what is being requested.
visual-studio-2012 reporting-services
asked Nov 19 '18 at 21:18
JohnJohn
13
add a comment |
I have 2 SQL databases I am pulling data from. I am unable to query across servers or merge this data in SQL, so I need to have 2 separate datasets & perform a Join / Lookupset between the 2.
The first dataset has order / agent data.
The second dataset has call log information.
(The call data does not have any linkage to the order data so I am "joining" on the phone number of the call to one we have on the order.)
What I want to do is provide all of the calls received by the phone number on the date the agent says a specific task happened.
I need to join on 3 fields, the phone, the date & the agent. This I am able to do no problem by concatenating the 3 fields on each side of the join to then return the value I want from the 2nd dataset.
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_Date.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_Date.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
This code works just file & returns the ident value I am looking for when the call & the task are completed on the same date.
I am now being asked to enhance this by looking for any calls that happened on or up to 3 days prior to the task being completed.
This is where I am stuck, how to use a date range as part of the expression.
What I am currently doing in the SQL for both datasets is calculating the calendar week & can use that in place of a date range, but for a task completed on Friday I can be including calls that happened on Monday instead of Wednesday through Friday.
Here is my code to match by week:
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_WK.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_WK.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
the vbCRLF creates a line break between each returned value as there can be several calls on the same day.
CPCCC_Date is my task completion date in the 1st data set.
Rec_Date is the date of the call.
It would be easy enough in the SQL for the 2nd data set to create a Rec Date - 3 days value (I also have an business Days function I can use in SQL to get the date that way instead of in SSRS).
So if the task was completed on Friday the 13th, I want to return calls on the 10th through the 13th (instead of the 8th - 13th).
So adding a field that pulls the calendar week for these 2 datasets & matching on does work, but I am looking to see if anyone has been able to use a range as part of the expressions, that way I can dial this in better to what is being requested.
visual-studio-2012 reporting-services
asked Nov 19 '18 at 21:18
JohnJohn
13
And now I am being asked to create a similar report where I would display all calls after the order has closed. Easy if I am only looking at 1 order. Not so easy when you want to run a date range of a month and show all closed orders with calls after. I can pass the same beginning date to the call query, but in the Lookupset there is not a way to say the call date has to be after a date on the order, so I am matching up all calls for that date range & as I am needing to stuff multiple results into 1 cell as I could have 20 calls, looks like I will need to do this in Excel or Access.
– John
Dec 3 '18 at 17:27
add a comment |
I have 2 SQL databases I am pulling data from. I am unable to query across servers or merge this data in SQL, so I need to have 2 separate datasets & perform a Join / Lookupset between the 2.
The first dataset has order / agent data.
The second dataset has call log information.
(The call data does not have any linkage to the order data so I am "joining" on the phone number of the call to one we have on the order.)
What I want to do is provide all of the calls received by the phone number on the date the agent says a specific task happened.
I need to join on 3 fields, the phone, the date & the agent. This I am able to do no problem by concatenating the 3 fields on each side of the join to then return the value I want from the 2nd dataset.
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_Date.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_Date.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
This code works just file & returns the ident value I am looking for when the call & the task are completed on the same date.
I am now being asked to enhance this by looking for any calls that happened on or up to 3 days prior to the task being completed.
This is where I am stuck, how to use a date range as part of the expression.
What I am currently doing in the SQL for both datasets is calculating the calendar week & can use that in place of a date range, but for a task completed on Friday I can be including calls that happened on Monday instead of Wednesday through Friday.
Here is my code to match by week:
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_WK.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_WK.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
the vbCRLF creates a line break between each returned value as there can be several calls on the same day.
CPCCC_Date is my task completion date in the 1st data set.
Rec_Date is the date of the call.
It would be easy enough in the SQL for the 2nd data set to create a Rec Date - 3 days value (I also have an business Days function I can use in SQL to get the date that way instead of in SSRS).
So if the task was completed on Friday the 13th, I want to return calls on the 10th through the 13th (instead of the 8th - 13th).
So adding a field that pulls the calendar week for these 2 datasets & matching on does work, but I am looking to see if anyone has been able to use a range as part of the expressions, that way I can dial this in better to what is being requested.
visual-studio-2012 reporting-services
asked Nov 19 '18 at 21:18
JohnJohn
13
I have 2 SQL databases I am pulling data from. I am unable to query across servers or merge this data in SQL, so I need to have 2 separate datasets & perform a Join / Lookupset between the 2.
The first dataset has order / agent data.
The second dataset has call log information.
(The call data does not have any linkage to the order data so I am "joining" on the phone number of the call to one we have on the order.)
What I want to do is provide all of the calls received by the phone number on the date the agent says a specific task happened.
I need to join on 3 fields, the phone, the date & the agent. This I am able to do no problem by concatenating the 3 fields on each side of the join to then return the value I want from the 2nd dataset.
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_Date.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_Date.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
This code works just file & returns the ident value I am looking for when the call & the task are completed on the same date.
I am now being asked to enhance this by looking for any calls that happened on or up to 3 days prior to the task being completed.
This is where I am stuck, how to use a date range as part of the expression.
What I am currently doing in the SQL for both datasets is calculating the calendar week & can use that in place of a date range, but for a task completed on Friday I can be including calls that happened on Monday instead of Wednesday through Friday.
Here is my code to match by week:
=join(LookupSet(Fields!in_phone.Value + Fields!CPCCC_WK.Value + Fields!CPCCC_CompBy.Value, Fields!phone_join.Value + Fields!Rec_WK.Value + Fields!ad_username.Value , Fields!ident.Value, "CallRecorder"), vbCRLF)
the vbCRLF creates a line break between each returned value as there can be several calls on the same day.
CPCCC_Date is my task completion date in the 1st data set.
Rec_Date is the date of the call.
It would be easy enough in the SQL for the 2nd data set to create a Rec Date - 3 days value (I also have an business Days function I can use in SQL to get the date that way instead of in SSRS).
So if the task was completed on Friday the 13th, I want to return calls on the 10th through the 13th (instead of the 8th - 13th).
So adding a field that pulls the calendar week for these 2 datasets & matching on does work, but I am looking to see if anyone has been able to use a range as part of the expressions, that way I can dial this in better to what is being requested.
visual-studio-2012 reporting-services
visual-studio-2012 reporting-services
asked Nov 19 '18 at 21:18
JohnJohn
13
asked Nov 19 '18 at 21:18
JohnJohn
13
edited Nov 27 '18 at 16:50
John
asked Nov 19 '18 at 21:18
JohnJohn
13
asked Nov 19 '18 at 21:18
JohnJohn
13
asked Nov 19 '18 at 21:18
JohnJohn
13
13
And now I am being asked to create a similar report where I would display all calls after the order has closed. Easy if I am only looking at 1 order. Not so easy when you want to run a date range of a month and show all closed orders with calls after. I can pass the same beginning date to the call query, but in the Lookupset there is not a way to say the call date has to be after a date on the order, so I am matching up all calls for that date range & as I am needing to stuff multiple results into 1 cell as I could have 20 calls, looks like I will need to do this in Excel or Access.
– John
Dec 3 '18 at 17:27
add a comment |
And now I am being asked to create a similar report where I would display all calls after the order has closed. Easy if I am only looking at 1 order. Not so easy when you want to run a date range of a month and show all closed orders with calls after. I can pass the same beginning date to the call query, but in the Lookupset there is not a way to say the call date has to be after a date on the order, so I am matching up all calls for that date range & as I am needing to stuff multiple results into 1 cell as I could have 20 calls, looks like I will need to do this in Excel or Access.
– John
Dec 3 '18 at 17:27
And now I am being asked to create a similar report where I would display all calls after the order has closed. Easy if I am only looking at 1 order. Not so easy when you want to run a date range of a month and show all closed orders with calls after. I can pass the same beginning date to the call query, but in the Lookupset there is not a way to say the call date has to be after a date on the order, so I am matching up all calls for that date range & as I am needing to stuff multiple results into 1 cell as I could have 20 calls, looks like I will need to do this in Excel or Access.
– John
Dec 3 '18 at 17:27
And now I am being asked to create a similar report where I would display all calls after the order has closed. Easy if I am only looking at 1 order. Not so easy when you want to run a date range of a month and show all closed orders with calls after. I can pass the same beginning date to the call query, but in the Lookupset there is not a way to say the call date has to be after a date on the order, so I am matching up all calls for that date range & as I am needing to stuff multiple results into 1 cell as I could have 20 calls, looks like I will need to do this in Excel or Access.
– John
Dec 3 '18 at 17:27
add a comment |
0
active
oldest
votes
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%2f53382774%2fssrs-lookupset-between-2-datasets-using-date-range-as-part-of-destination-expres%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f53382774%2fssrs-lookupset-between-2-datasets-using-date-range-as-part-of-destination-expres%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
And now I am being asked to create a similar report where I would display all calls after the order has closed. Easy if I am only looking at 1 order. Not so easy when you want to run a date range of a month and show all closed orders with calls after. I can pass the same beginning date to the call query, but in the Lookupset there is not a way to say the call date has to be after a date on the order, so I am matching up all calls for that date range & as I am needing to stuff multiple results into 1 cell as I could have 20 calls, looks like I will need to do this in Excel or Access.
– John
Dec 3 '18 at 17:27