SSRS - Extract value from a column from a dataset based on condition from 2 other columns












0














I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:



Category   Month   Amount
A Jan 20
A Feb 25
A Mar 10
R Jan 15
R Feb 50
R Mar 55


On the report I need:



    Jan   Feb   Mar
A 20 25 10
R 15 50 55


I have tried placing this expression in each group row column, for example, in the Jan column it would be:



=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)


But I get the result 0, like false result.



Could you please let me know how to solve this problem.










share|improve this question





























    0














    I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:



    Category   Month   Amount
    A Jan 20
    A Feb 25
    A Mar 10
    R Jan 15
    R Feb 50
    R Mar 55


    On the report I need:



        Jan   Feb   Mar
    A 20 25 10
    R 15 50 55


    I have tried placing this expression in each group row column, for example, in the Jan column it would be:



    =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)


    But I get the result 0, like false result.



    Could you please let me know how to solve this problem.










    share|improve this question



























      0












      0








      0







      I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:



      Category   Month   Amount
      A Jan 20
      A Feb 25
      A Mar 10
      R Jan 15
      R Feb 50
      R Mar 55


      On the report I need:



          Jan   Feb   Mar
      A 20 25 10
      R 15 50 55


      I have tried placing this expression in each group row column, for example, in the Jan column it would be:



      =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)


      But I get the result 0, like false result.



      Could you please let me know how to solve this problem.










      share|improve this question















      I have 3 columns (category, Month, Amount) in my dataset, with values similar to these below:



      Category   Month   Amount
      A Jan 20
      A Feb 25
      A Mar 10
      R Jan 15
      R Feb 50
      R Mar 55


      On the report I need:



          Jan   Feb   Mar
      A 20 25 10
      R 15 50 55


      I have tried placing this expression in each group row column, for example, in the Jan column it would be:



      =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 0)


      But I get the result 0, like false result.



      Could you please let me know how to solve this problem.







      reporting-services






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 10 at 23:21









      Strawberryshrub

      1,1081216




      1,1081216










      asked Nov 10 at 15:39









      user10581977

      43




      43
























          2 Answers
          2






          active

          oldest

          votes


















          0














          If you need it as expression, the correct expression would be:



          =Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))


          But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value and into your column group Fields!Month.Value. And where the row and column group meets you can also drag and drop the Fields!Amount.Value because the default aggregate function in the matrix is Sum(). Which is exactly what you need Sum(Fields!Amount.Value)).






          share|improve this answer





















          • thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
            – user10581977
            Nov 11 at 18:56












          • @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
            – user10581977
            Nov 11 at 19:32












          • Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9). The only possibilty is that your Fields!Month.Value doesnt work. Is this field a Date/Time? Try CDate(Fields!Month.Value) and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
            – Strawberryshrub
            Nov 12 at 5:21












          • Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
            – user10581977
            Nov 12 at 14:42












          • @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
            – user10581977
            Nov 12 at 16:59





















          0














          SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with



          =IIF(Fields!Category.Value = "A" ,1, 0)


          Another with



          =IIF(Fields!Month.Value = "Jan", 1, 0)


          Another with



          =IIF(1=1,Fields!Amount.Value, 0)


          When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.



          =sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))





          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%2f53240524%2fssrs-extract-value-from-a-column-from-a-dataset-based-on-condition-from-2-othe%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            0














            If you need it as expression, the correct expression would be:



            =Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))


            But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value and into your column group Fields!Month.Value. And where the row and column group meets you can also drag and drop the Fields!Amount.Value because the default aggregate function in the matrix is Sum(). Which is exactly what you need Sum(Fields!Amount.Value)).






            share|improve this answer





















            • thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
              – user10581977
              Nov 11 at 18:56












            • @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
              – user10581977
              Nov 11 at 19:32












            • Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9). The only possibilty is that your Fields!Month.Value doesnt work. Is this field a Date/Time? Try CDate(Fields!Month.Value) and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
              – Strawberryshrub
              Nov 12 at 5:21












            • Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
              – user10581977
              Nov 12 at 14:42












            • @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
              – user10581977
              Nov 12 at 16:59


















            0














            If you need it as expression, the correct expression would be:



            =Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))


            But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value and into your column group Fields!Month.Value. And where the row and column group meets you can also drag and drop the Fields!Amount.Value because the default aggregate function in the matrix is Sum(). Which is exactly what you need Sum(Fields!Amount.Value)).






            share|improve this answer





















            • thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
              – user10581977
              Nov 11 at 18:56












            • @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
              – user10581977
              Nov 11 at 19:32












            • Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9). The only possibilty is that your Fields!Month.Value doesnt work. Is this field a Date/Time? Try CDate(Fields!Month.Value) and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
              – Strawberryshrub
              Nov 12 at 5:21












            • Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
              – user10581977
              Nov 12 at 14:42












            • @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
              – user10581977
              Nov 12 at 16:59
















            0












            0








            0






            If you need it as expression, the correct expression would be:



            =Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))


            But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value and into your column group Fields!Month.Value. And where the row and column group meets you can also drag and drop the Fields!Amount.Value because the default aggregate function in the matrix is Sum(). Which is exactly what you need Sum(Fields!Amount.Value)).






            share|improve this answer












            If you need it as expression, the correct expression would be:



            =Sum(IIF(Fields!Category.Value = "A" And Fields!Month.Value = "Jan", Fields!Amount.Value, 0))


            But I would recommend you just use a matrix, drag and drop into your row gorup Fields!Category.Value and into your column group Fields!Month.Value. And where the row and column group meets you can also drag and drop the Fields!Amount.Value because the default aggregate function in the matrix is Sum(). Which is exactly what you need Sum(Fields!Amount.Value)).







            share|improve this answer












            share|improve this answer



            share|improve this answer










            answered Nov 10 at 15:59









            Strawberryshrub

            1,1081216




            1,1081216












            • thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
              – user10581977
              Nov 11 at 18:56












            • @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
              – user10581977
              Nov 11 at 19:32












            • Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9). The only possibilty is that your Fields!Month.Value doesnt work. Is this field a Date/Time? Try CDate(Fields!Month.Value) and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
              – Strawberryshrub
              Nov 12 at 5:21












            • Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
              – user10581977
              Nov 12 at 14:42












            • @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
              – user10581977
              Nov 12 at 16:59




















            • thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
              – user10581977
              Nov 11 at 18:56












            • @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
              – user10581977
              Nov 11 at 19:32












            • Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9). The only possibilty is that your Fields!Month.Value doesnt work. Is this field a Date/Time? Try CDate(Fields!Month.Value) and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
              – Strawberryshrub
              Nov 12 at 5:21












            • Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
              – user10581977
              Nov 12 at 14:42












            • @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
              – user10581977
              Nov 12 at 16:59


















            thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
            – user10581977
            Nov 11 at 18:56






            thank you for your response Strawberryhrub !! By adding sum in front of the expression, will not do anything to my expression, it actually throws error; I don't have anything to sum there, the value is being processed in the SQL query, which brings one amount for each month, for that category.
            – user10581977
            Nov 11 at 18:56














            @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
            – user10581977
            Nov 11 at 19:32






            @Strawberryhrub ...And by changing the value to return, if the first part is not true, to 9 (instead of 0): =IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9) ==> it returns 9. I think the key is to understand why the expression does not find the 2 conditions to be TRUE. Am I missing something?
            – user10581977
            Nov 11 at 19:32














            Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9). The only possibilty is that your Fields!Month.Value doesnt work. Is this field a Date/Time? Try CDate(Fields!Month.Value) and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
            – Strawberryshrub
            Nov 12 at 5:21






            Your expression is correct=IIF(Fields!Category.Value = "A" and Fields!Month.Value = "Jan", Fields!Amount.Value, 9). The only possibilty is that your Fields!Month.Value doesnt work. Is this field a Date/Time? Try CDate(Fields!Month.Value) and check the result. Otherwise if `Fields!Month.Value is a string the expression is correct.
            – Strawberryshrub
            Nov 12 at 5:21














            Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
            – user10581977
            Nov 12 at 14:42






            Hi @Strawberryhrub , yes, Fields!Month.Value is a string. Also I have tried changing it to an integer, no difference. Now, for some unknown reason, using the same expression, It is returning the first value "Amount" it finds for the "A" category, for example, Maybe if could make the expression to return the specified row for that category, expected outcome can be achieved... Any other suggestion? Otherwise after trying so many things, last option is to change my design to be like you have mention earlier with the matrix, even if trying to avoid that for now. thank you.
            – user10581977
            Nov 12 at 14:42














            @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
            – user10581977
            Nov 12 at 16:59






            @Strawberryhrub, IIF does not short-circuit, the key is to have the expression work with IIF. I found this post, which still need to make it work for my case [link] stackoverflow.com/questions/1204179/…
            – user10581977
            Nov 12 at 16:59















            0














            SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with



            =IIF(Fields!Category.Value = "A" ,1, 0)


            Another with



            =IIF(Fields!Month.Value = "Jan", 1, 0)


            Another with



            =IIF(1=1,Fields!Amount.Value, 0)


            When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.



            =sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))





            share|improve this answer


























              0














              SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with



              =IIF(Fields!Category.Value = "A" ,1, 0)


              Another with



              =IIF(Fields!Month.Value = "Jan", 1, 0)


              Another with



              =IIF(1=1,Fields!Amount.Value, 0)


              When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.



              =sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))





              share|improve this answer
























                0












                0








                0






                SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with



                =IIF(Fields!Category.Value = "A" ,1, 0)


                Another with



                =IIF(Fields!Month.Value = "Jan", 1, 0)


                Another with



                =IIF(1=1,Fields!Amount.Value, 0)


                When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.



                =sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))





                share|improve this answer












                SSRS error messages are not very meaningful, so to solve this problem test each part of your expression in turn. Create one column with



                =IIF(Fields!Category.Value = "A" ,1, 0)


                Another with



                =IIF(Fields!Month.Value = "Jan", 1, 0)


                Another with



                =IIF(1=1,Fields!Amount.Value, 0)


                When you know which part of your expression is failing then we can suggest possible solutions. One thing to try is converting the datatypes e.g.



                =sum(IIF(cstr(Fields!Category.Value) = "A" And cstr(Fields!Month.Value) = "Jan", Fields!Amount.Value, cint(0)))






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Nov 14 at 11:33









                RET

                351111




                351111






























                    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.





                    Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                    Please pay close attention to the following guidance:


                    • 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%2f53240524%2fssrs-extract-value-from-a-column-from-a-dataset-based-on-condition-from-2-othe%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







                    這個網誌中的熱門文章

                    Tangent Lines Diagram Along Smooth Curve

                    Yusuf al-Mu'taman ibn Hud

                    Zucchini