4 Replies Latest reply on Sep 18, 2013 7:49 AM by Rob King

    How can you list Parameter values in a column?

    Rob King

      I have a Parameter for metrics (Metric Report) that lists the different metrics a user can select in combination with other filters such as Date, Manager and Application.   The user also wants a separate scorecard dashboard that shows the metrics with a monthly calulation as shown below.  Since the metric values are calculated in a Parameter how do I pull in the values as listed?  I also need to figure out how to then select each metric column to do the calculations for each metric value which I would assume would be through table calculations.   My novice cababilities aren't to this level yet so any ideas, input or direction would be greatly appreciated.   Thanks.

       

      MetricParameter.png

      Dashboard.jpg

        • 1. Re: How can you list Parameter values in a column?
          Andy Piper

          Rob,

           

          You mention that "the metric values are calculated in a Parameter"; but calculations actually take place outside parameters. Calculations are typically created using the parameter selection.

           

          Do you have one field called "METRIC" or one field for each metric? Are your scorecard items in separate fields (one field called Score, another Rating, another Rating Value, another Metric Weight) or are they created by using just two fields (where one is your Scorecard name and the other is the scorecard value)?

           

          Would you be able to post a packaged workbook of what you're working with? Seeing how things are set up and the interactions you have in place will likely lead to a more timely and accurate response.

           

          Regards,


          Andy

          • 2. Re: Re: How can you list Parameter values in a column?
            Rob King

            Andy,

             

            Ok so the metric parameter isn't calculated but what I meant is this is how the metric selections are listed.  Once selected the metrics are calculated.  The user can select filters to display the needed graph.  The scorecard is a new sheet that will produce the example I included.  It will read the existing database and create all the fields on the scorecard for each metric row.  The calculations are:

            • Metric -  Metric name
            • Score - Calculation for the metric for ytd to the month selected.
            • Rating - A calculation that takes the Score and compares it to a set of ranges to determine and display a value of "Satisfactory" or "Unsatisfactory".
            • Rating Value - "1" or "2" numeric value determined by the Rating value to used to multiply by the Metric Weight to come up with the Total.

            Each row/column value will be calculated each time the selected month is selected.  First, is I don't how to create the field locations on the sheet.  I would imagine it has to be a table creation?  Second, is how to create the first selection of the metric values.  The Metric Report parameter is the only place the metrics are listed.

             

            This is something that I've never done and I'm having a hard time wrapping my head around on how it should/can be done.  I've attached an example.

             

            Thanks,

            Rob

            • 3. Re: Re: Re: How can you list Parameter values in a column?
              Andy Piper

              Rob,

               

              Based on what you have, it appears that you are trying to group a set of measure column headings and treat them as a column of dimension values. You would need to be able to reshape the data in such a way that each measure column heading is a dimension value instead of its own column.

               

              Currently you have something similar to:

              ApplicationDateAvailabilityMean Time to RecoverMean to to Failure
              App101/01/01.92102
              App201/01/01.93123

               

              and to do the table you're talking about, you would need something like:

              ApplicationDateMetricMetric Value
              App101/01/01Availability.92
              App101/01/01Mean Time to Recover10
              App101/01/01Mean Time to Failure2
              App201/01/01Availability.93
              App201/01/01Mean Time to Recover12
              App201/01/01Mean Time to Failure3

               

              You may be able to get what you're looking for by creating an Excel table that includes a METRIC column that has the list of values from your parameter. A second column would be needed to join the existing table to this new table (I chose App Owner - as that would produce a smaller number of needed records in the new Excel table than the Application field). One set of Metric values per each App Owner (7 Metrics, 5 App Owners, 35 records in new table).

               

              I then brought in the new table and blended the two tables. After creating a few calculated fields and making some assumptions on how you are doing things, I have a table similar to what you were wanting (minus the bottom right-hand corner of the Excel table posted in your initial email).

               

              I've attached the Excel file I used along with the workbook I was able to generate with it and your current workbook.

               

              Hope this helps,

               

              Andy

              1 of 1 people found this helpful
              • 4. Re: Re: Re: How can you list Parameter values in a column?
                Rob King

                Andy,


                Thanks for the reply.  You definetly got me closer the end result.  I've played with your solution and noticed my Primary Data Source is now my Secondary and I've tried to switch it but it appears this is the only way it will work.  I don't think it has any effect on my other sheets.

                 

                Now I just need to figure out how to incorporate the monty selection and how to represent the Grand Total and Overall Rating.   Again, thanks for getting me started.  I appreciate it.