7 Replies Latest reply on Mar 3, 2016 7:18 PM by Nirupam Haas Tirukkovalluri

    Create Dynamic Header Colum

    Shanaka Gunaratna

      Hi

       

      I have a cross tab report and I want to change the column headers based on a parameter. Parameter would be the period (FEB 2016)

       

       

      This report has a hierarchy so I cant have a separate worksheet with dynamic columns. Each column coming from single measure

       

      Thanks

        • 1. Re: Create Dynamic Header Colum
          Nirupam Haas Tirukkovalluri

          After dragging the sheet to dashboard edit title by adding parameter

           

          Instead of replacing each column with date, parameter can be added to title so it changes dynamically

           

          go to edit title --> insert --> parameters (period)

           

          This is a workaround.

          • 2. Re: Create Dynamic Header Colum
            Amanjot Klair

            Hi Shanaka,

             

            As you mentioned that each column is coming from a single measure. I assume in that case you have a dimenssion column which is separating the measure values by ACT, P.FCST, Var to P.FCST , BGT, Var to BGT.

             

            Also i have observed that in column 4 and 6 there is no Feb 2016 involved, which means you don't want Feb 2016 to be shown there. If my assumptions and observations are correct you can do it by creating another cacl field and the the calculation will be as follows.

             

            [Measure Name With Par] = IF [Your Measure Name Field] = 'ACT' THEN [Your Parameter Name]+' '+[Your Measure Name Field]

                                                         ELSEIF [Your Measure Name Field] = 'P.FCST' THEN [Your Parameter Name]+' '+[Your Measure Name Field]

                                                         ELSEIF [Your Measure Name Field] = 'BGT' THEN [Your Parameter Name]+' '+[Your Measure Name Field]

                                                         ELSE [Your Measure Name Field]

                                                         END

             

            Replace the [Your Measure Name FIeld] with your dimension separating the measure values and [Your Parameter Name] with the name of your parameter and place this new field on the Columns shelf. This should solve the issue. Let me know if this works out for your.

             

            -Amanjot

            1 of 1 people found this helpful
            • 3. Re: Create Dynamic Header Colum
              Amanjot Klair

              Nirupam Haas Tirukkovalluri i think you got confused with the question the requirement is to add the parameter value to the column header of measure values.

               

              Shanaka Gunaratna alternatively you can use another calculation which is small, it didn't strike my mind earlier

               

              IF [A] = 'ACT' OR [A] = 'P.FCST' OR [A] = 'BGT' THEN [B]+' '+[A] ELSE [A] END

               

              Replace [A] with the field giving the measure name and [B] with your parameter.

               

              -Amanjot

              1 of 1 people found this helpful
              • 4. Re: Create Dynamic Header Colum
                Shanaka Gunaratna

                Hi Amanjot

                I have to rephrase my question again. Data coming from multiple measures, ACT would be one measure PFCT would be another measure. So there is no dimension to apply above calculation.

                • 5. Re: Create Dynamic Header Colum
                  Amanjot Klair

                  Shanaka, i am afraid in that case there is no way to include the parameter value to column headers, as far as i know. Possibly what you can do is use data scafolding technique.

                   

                  What is the source of your data? What is the size of your dataset? And are you ok in using data blending? Is there a key column in your data right now? Will this be an adhoc analysis or you are building this dashboard for the consumption of huge population?

                   

                  Answers to above question can help me identify how we can go ahead and help you with the data scafolding and will it be useful to you or not.

                   

                  -Amanjot

                  • 6. Re: Create Dynamic Header Colum
                    Shanaka Gunaratna

                    Data getting from summary table on sql server database.  Data set would be few hundred rows. Yes I m ok with data blending. This will use by huge population

                    • 7. Re: Create Dynamic Header Colum
                      Amanjot Klair

                      Can you share a sample data? I think we can fix this. But i'll need the data as the solution will depend upon your table structure and data.