Replacing Foreign Key with Reference Name from other table in SSRS report












1















I am creating an SSRS matrix report in which Loans are mapped with primary keys of Employees originated the loan, so the tables looks like:






<table><tbody><tr><td>LoanID</td><td>Employee</td></tr><tr><td>101</td><td>1</td></tr><tr><td>102</td><td>2</td></tr><tr><td>103</td><td>3</td></tr><tr><td>104</td><td>2</td></tr><tr><td>105</td><td>1</td></tr><tr><td>106</td><td>2</td></tr></tbody></table>








<table><tbody><tr><td>Employeeid</td><td>Employee Name</td></tr><tr><td>1</td><td>A</td></tr><tr><td>2</td><td>B</td></tr><tr><td>3</td><td>C</td></tr></tbody></table>





When I create the matrix using first table I get:






<table><tbody><tr><td>Employee</td><td>Count Of Loans</td></tr><tr><td>1</td><td>2</td></tr><tr><td>2</td><td>3</td></tr><tr><td>3</td><td>1</td></tr></tbody></table>





But I wanted result like: 






<table><tbody><tr><td>Employee</td><td>Count of Loans</td></tr><tr><td>A</td><td>2</td></tr><tr><td>B</td><td>3</td></tr><tr><td>C</td><td>1</td></tr></tbody></table>





How can I relate the name of values in rows groups to take out the names of Employees?






reporting-services







share|improve this question





























    1















    I am creating an SSRS matrix report in which Loans are mapped with primary keys of Employees originated the loan, so the tables looks like:






    <table><tbody><tr><td>LoanID</td><td>Employee</td></tr><tr><td>101</td><td>1</td></tr><tr><td>102</td><td>2</td></tr><tr><td>103</td><td>3</td></tr><tr><td>104</td><td>2</td></tr><tr><td>105</td><td>1</td></tr><tr><td>106</td><td>2</td></tr></tbody></table>








    <table><tbody><tr><td>Employeeid</td><td>Employee Name</td></tr><tr><td>1</td><td>A</td></tr><tr><td>2</td><td>B</td></tr><tr><td>3</td><td>C</td></tr></tbody></table>





    When I create the matrix using first table I get:






    <table><tbody><tr><td>Employee</td><td>Count Of Loans</td></tr><tr><td>1</td><td>2</td></tr><tr><td>2</td><td>3</td></tr><tr><td>3</td><td>1</td></tr></tbody></table>





    But I wanted result like: 






    <table><tbody><tr><td>Employee</td><td>Count of Loans</td></tr><tr><td>A</td><td>2</td></tr><tr><td>B</td><td>3</td></tr><tr><td>C</td><td>1</td></tr></tbody></table>





    How can I relate the name of values in rows groups to take out the names of Employees?






    reporting-services







    share|improve this question



























      1












      1








      1








      I am creating an SSRS matrix report in which Loans are mapped with primary keys of Employees originated the loan, so the tables looks like:






      <table><tbody><tr><td>LoanID</td><td>Employee</td></tr><tr><td>101</td><td>1</td></tr><tr><td>102</td><td>2</td></tr><tr><td>103</td><td>3</td></tr><tr><td>104</td><td>2</td></tr><tr><td>105</td><td>1</td></tr><tr><td>106</td><td>2</td></tr></tbody></table>








      <table><tbody><tr><td>Employeeid</td><td>Employee Name</td></tr><tr><td>1</td><td>A</td></tr><tr><td>2</td><td>B</td></tr><tr><td>3</td><td>C</td></tr></tbody></table>





      When I create the matrix using first table I get:






      <table><tbody><tr><td>Employee</td><td>Count Of Loans</td></tr><tr><td>1</td><td>2</td></tr><tr><td>2</td><td>3</td></tr><tr><td>3</td><td>1</td></tr></tbody></table>





      But I wanted result like: 






      <table><tbody><tr><td>Employee</td><td>Count of Loans</td></tr><tr><td>A</td><td>2</td></tr><tr><td>B</td><td>3</td></tr><tr><td>C</td><td>1</td></tr></tbody></table>





      How can I relate the name of values in rows groups to take out the names of Employees?






      reporting-services







      share|improve this question
















      I am creating an SSRS matrix report in which Loans are mapped with primary keys of Employees originated the loan, so the tables looks like:






      <table><tbody><tr><td>LoanID</td><td>Employee</td></tr><tr><td>101</td><td>1</td></tr><tr><td>102</td><td>2</td></tr><tr><td>103</td><td>3</td></tr><tr><td>104</td><td>2</td></tr><tr><td>105</td><td>1</td></tr><tr><td>106</td><td>2</td></tr></tbody></table>








      <table><tbody><tr><td>Employeeid</td><td>Employee Name</td></tr><tr><td>1</td><td>A</td></tr><tr><td>2</td><td>B</td></tr><tr><td>3</td><td>C</td></tr></tbody></table>





      When I create the matrix using first table I get:






      <table><tbody><tr><td>Employee</td><td>Count Of Loans</td></tr><tr><td>1</td><td>2</td></tr><tr><td>2</td><td>3</td></tr><tr><td>3</td><td>1</td></tr></tbody></table>





      But I wanted result like: 






      <table><tbody><tr><td>Employee</td><td>Count of Loans</td></tr><tr><td>A</td><td>2</td></tr><tr><td>B</td><td>3</td></tr><tr><td>C</td><td>1</td></tr></tbody></table>





      How can I relate the name of values in rows groups to take out the names of Employees? 






      <table><tbody><tr><td>LoanID</td><td>Employee</td></tr><tr><td>101</td><td>1</td></tr><tr><td>102</td><td>2</td></tr><tr><td>103</td><td>3</td></tr><tr><td>104</td><td>2</td></tr><tr><td>105</td><td>1</td></tr><tr><td>106</td><td>2</td></tr></tbody></table>





      <table><tbody><tr><td>LoanID</td><td>Employee</td></tr><tr><td>101</td><td>1</td></tr><tr><td>102</td><td>2</td></tr><tr><td>103</td><td>3</td></tr><tr><td>104</td><td>2</td></tr><tr><td>105</td><td>1</td></tr><tr><td>106</td><td>2</td></tr></tbody></table>





      <table><tbody><tr><td>Employeeid</td><td>Employee Name</td></tr><tr><td>1</td><td>A</td></tr><tr><td>2</td><td>B</td></tr><tr><td>3</td><td>C</td></tr></tbody></table>





      <table><tbody><tr><td>Employeeid</td><td>Employee Name</td></tr><tr><td>1</td><td>A</td></tr><tr><td>2</td><td>B</td></tr><tr><td>3</td><td>C</td></tr></tbody></table>





      <table><tbody><tr><td>Employee</td><td>Count Of Loans</td></tr><tr><td>1</td><td>2</td></tr><tr><td>2</td><td>3</td></tr><tr><td>3</td><td>1</td></tr></tbody></table>





      <table><tbody><tr><td>Employee</td><td>Count Of Loans</td></tr><tr><td>1</td><td>2</td></tr><tr><td>2</td><td>3</td></tr><tr><td>3</td><td>1</td></tr></tbody></table>





      <table><tbody><tr><td>Employee</td><td>Count of Loans</td></tr><tr><td>A</td><td>2</td></tr><tr><td>B</td><td>3</td></tr><tr><td>C</td><td>1</td></tr></tbody></table>





      <table><tbody><tr><td>Employee</td><td>Count of Loans</td></tr><tr><td>A</td><td>2</td></tr><tr><td>B</td><td>3</td></tr><tr><td>C</td><td>1</td></tr></tbody></table>





      reporting-services




      reporting-services






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 25 '18 at 10:23







      Mayank Chhibber

















      asked Nov 25 '18 at 9:59









      Mayank ChhibberMayank Chhibber

      62




      62
























          1 Answer
          1






          active

          oldest

          votes


















          0














          It is generally better to join tables in the source system when you can. To answer your question, you can use the Lookup function to get the Employee Name from the second dataset. In this case it would look something like this:



          =Lookup(Fields!Employee.Value, Fields!EmployeeID.Value, Fields!EmployeeName.Value, "DataSet2")


          In other words, for each row, it is looking up the employee ID in the second dataset and returning the name.






          share|improve this answer























            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
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53466411%2freplacing-foreign-key-with-reference-name-from-other-table-in-ssrs-report%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














            It is generally better to join tables in the source system when you can. To answer your question, you can use the Lookup function to get the Employee Name from the second dataset. In this case it would look something like this:



            =Lookup(Fields!Employee.Value, Fields!EmployeeID.Value, Fields!EmployeeName.Value, "DataSet2")


            In other words, for each row, it is looking up the employee ID in the second dataset and returning the name.






            share|improve this answer




























              0














              It is generally better to join tables in the source system when you can. To answer your question, you can use the Lookup function to get the Employee Name from the second dataset. In this case it would look something like this:



              =Lookup(Fields!Employee.Value, Fields!EmployeeID.Value, Fields!EmployeeName.Value, "DataSet2")


              In other words, for each row, it is looking up the employee ID in the second dataset and returning the name.






              share|improve this answer


























                0












                0








                0







                It is generally better to join tables in the source system when you can. To answer your question, you can use the Lookup function to get the Employee Name from the second dataset. In this case it would look something like this:



                =Lookup(Fields!Employee.Value, Fields!EmployeeID.Value, Fields!EmployeeName.Value, "DataSet2")


                In other words, for each row, it is looking up the employee ID in the second dataset and returning the name.






                share|improve this answer













                It is generally better to join tables in the source system when you can. To answer your question, you can use the Lookup function to get the Employee Name from the second dataset. In this case it would look something like this:



                =Lookup(Fields!Employee.Value, Fields!EmployeeID.Value, Fields!EmployeeName.Value, "DataSet2")


                In other words, for each row, it is looking up the employee ID in the second dataset and returning the name.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 26 '18 at 21:46









                StevenWhiteStevenWhite

                4,51921136




                4,51921136
































                    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














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53466411%2freplacing-foreign-key-with-reference-name-from-other-table-in-ssrs-report%23new-answer', 'question_page');
                    }
                    );

                    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







                    -

                    Popular posts from this blog

                    404 Error Contact Form 7 ajax form submitting

                    Image
                    0 The contact form does not work, and affects loading after one try. The chrome console shows the error message: POST http://example.com/wp-json/contact-form-7/v1/contact-forms/50/feedback 404 (Not Found) send @ jquery.js?ver=1.12.4:4 ajax @ jquery.js?ver=1.12.4:4 wpcf7.submit @ scripts.js?ver=5.0.3:346 (anonymous) @ scripts.js?ver=5.0.3:53 dispatch @ jquery.js?ver=1.12.4:3 r.handle @ jquery.js?ver=1.12.4:3 How to solve this issue? thanks in advance javascript wordpress contact-form-7 share | improve this question edited Jul 17 '18 at 5:39 Kiran Shahi 4,745 3 16 43 ...
                    Read more

                    How to know if a Active Directory user can login interactively

                    Image
                    1 I would like to know if and how is it possible to know if a AD user can login interactively (on a server) in a domain. I need to know if I can find it out using a LDAP search. Thanks. windows active-directory login user-permissions user-profile share | improve this question edited 1 hour ago yagmoth555 ♦ 11.6k 3 17 42 asked 1 hour ago Luigi Luigi 6 1 New contributor ...
                    Read more

                    Refactoring coordinates for Minecraft Pi buildings written in Python

                    Image
                    0 $begingroup$ I'm fairly new to coding and I wrote this script in Python for a house with a roof in Minecraft Pi. I've found different ways to write the coordinates to make the code base shorter, but I'm wondering if it is possible for me to refactor it any further so I don't have to list each coordinate line by line. import mcpi.minecraft as minecraft import mcpi.block as block mc = minecraft.Minecraft.create() p = mc.player.getTilePos() mc.setBlocks(p.x+1, p.y, p.z+1, #Main structure starting coordinates (DO NOT ALTER) p.x+19, p.y+4, p.z+6, #Structure size/coordinates (Alters cube size) block.STONE_BRICK) mc.setBlocks(p.x+2, p.y+1, p.z+2, #Hollowing out main structure interior p.x+18, p.y+3, p.z+5, block.AIR) mc.setBlocks(p.x, p.y...
                    Read more