4 Replies Latest reply on Aug 22, 2013 11:11 AM by Steve Gesuale

    changing format of one parameter selection but not others

    Steve Gesuale

      Hi,

       

      I have a parameter with 5 choices one of which is a % (Conversion Rate).

       

      I tried the following CASE formula to change the format of that choice (with help from another useful post):

       

      case [Parameters].[Choose Indicator 1]

      when 'Conversion Rate' then STR(INT(ROUND([Conversion Rate]*100,0)))+"%"

      when 'Men Booked' then sum([Men Booked])

      when 'Men Who Attended Clinic' then sum([Men Who Attended Clinic])

      when 'Cost (KR)' then sum([Cost (KR)])

      when 'Cost per Client (KR)' then [Cost per Client (KR)]

      end

       

      but I am getting an error message: "Expected type string, found float. Results from 'case' expressions must match."

       

      Do I need to apply formatting to all the choices? Apologies if this is obvious...

       

      Thanks for any help!

        • 1. Re: changing format of one parameter selection but not others
          Mark Holtz

          Hi Steve,

           

          Unfortunately, I don't think it's possible to set up conditional formatting of a single measure field being controlled by a parameter. In essence, it appears that your 5 options produce:

          1 percentage

          2 counts

          2 monetary numbers

           

          If that's the case, the only way to get each to appear correctly is to convert each THEN predicate to a string with the numeric data formatted (and rounded) the way you want.

           

          Another option would be to have your case statement return numeric float values. Then, create 3 different sheets, one with the measure always formatted as a %, one as standard number with comma, etc. and the last with the measure formatted as $.

           

          Then, create calculated fields for each view to use as a filter.

          i.e., IF Parameter = 'Conversion Rate' THEN 'Show' ELSE 'Hide' END as the "Percent View Filter"

          IF Parameter = 'Men Booked' OR Paremeter = 'Men Who Attended Clinic' THEN 'Show' ELSE 'Hide' END as the "Count View Filter"

          IF Parameter = 'Cost (KR)' OR Paremeter = 'Cost per Client (KR)'' THEN 'Show' ELSE 'Hide' END as the "Money View Filter".  Apply each filter to the appropriate view and filter to "Show." Then create a dashboard with a vertical container that contains all 3 views, and expose the parameter. Only one view will be shown at a time--the one formatted to match the parameter selection.

          If you want to have a title, I'd recommend you create a 4th empty view just for that, and apply none of these 3 filters to it.

           

          Hope that helps. Feel free to attach your workbook if you need assistance.

          1 of 1 people found this helpful
          • 2. Re: Re: changing format of one parameter selection but not others
            Steve Gesuale

            Hi Mark,

             

            Thanks for you helpful reply. I see what I need to do and tried to format each of the 5 options (your first suggestion), but am struggling with getting the calculation correct. I think I need to improve my knowledge of the formatting syntax.

             

            Here is my (failed) attempt:

             

            case [Parameters].[Choose Indicator 1]

            when 'Conversion Rate' then STR(INT(ROUND([Conversion Rate]*100,0)))+"%"

            when 'Men Booked' then STR(LOOKUP(SUM([Men Booked]),0))

            when 'Men Who Attended Clinic' then STR(LOOKUP(SUM([Men Who Attended Clinic]),0))

            when 'Cost (KR)' then STR(LOOKUP(SUM([Cost (KR)]),0))

            when 'Cost per Client (KR)' then STR(INT(ROUND([Cost per Client (KR)]),0))

            end

             

            I've attached the workbook with the functioning (but unformatted) display as well as the above.

             

            Thanks for any further guidance!

             

            Steve

            • 3. Re: Re: Re: changing format of one parameter selection but not others
              Mark Holtz

              Hi Steve,

               

              I didn't realize you were trying to graph your dynamic measure. Using a string there makes things tougher. You could possibly do it by having one calculated field as your measure and another one as the tooltip, but the axis cannot be changed.

               

              So, if you want to use graphs with the dynamic measure, you'll want to use the 2nd approach. It's not as involved as it sounds, mostly duplicating things and just changing a word in the filter formula.

               

              See attached. Let me know if you have more questions.

              • 4. Re: Re: Re: changing format of one parameter selection but not others
                Steve Gesuale

                Hi Mark,

                 

                Thanks a lot for the help! I really appreciate your taking time to lay this out so clearly. This will be useful for me in other instances as well so thanks again!

                Steve