5 Replies Latest reply: Sep 5, 2013 3:18 PM by Celvin Kattookaran RSS

    Smart View VBA Question - Urgent Please

    878783

      Hi, In smart View :

       

      This Report for <Account>

      <Entity>

      for the month <Month, Year>

       

      I have POV dimensions in Smart view are Account, Entity, Period, Year .

      In my smart view Report I need the report Header like Above picture. I need the selected POV member to be displayed in the report heading place.

       

      Could you please let me know to get those header names. What are the functions I need to use.

      I would appreciate the prompt response on this.

       

      Thanks,

        • 1. Re: Smart View VBA Question - Urgent Please
          GlennS_3

          If you are on version 11.1.2.1.102 or higher, the pov members are stored on row 1 which may or may not be hidden. You could reference them directly with a formula to build your header

          If you are not on the version you might look at the VBA function HypGetActiveMember (vtDimName, vtMember) you will have to di this in a macro then plug in the value where you want it

          • 2. Re: Smart View VBA Question - Urgent Please
            878783

            Thanks for your response.

            I am not really good at VBA scripting. I am trying this function. Some how i am not able to make it work.

             

            This is the Syntax I have found, And I am declaring the below function in the VBA, The statement is not running.(It;s in red color)

             

            Public Declare Function HypGetActiveMember Lib "HsAddin" (ByVal vtDimName As Variant, ByRef vtMember As Variant) As Long

             

            Sub GetActiveMemberName()

            sts = HypGetActiveMember("Market", vtMem)

            End Sub

             

            I may need your help How to use this VBA function in Smart view where I need to use ? Could you Please help me ?

            If you give me a small Example, That would be great help fro me..

            • 3. Re: Smart View VBA Question - Urgent Please
              Celvin Kattookaran

              You can do that using VBA, how about using an EXCEL formula instead.

               

              ="This report for <"&Sheet2!A1&"> <"&Sheet2!B1&"> for the month <"&Sheet2!C1&","&Sheet2!D1&">"

               

              This assumes that Sheet2 has POV members.

               

              If you want to do it the VB way then

               

              Sub GetActiveMemberName()

              sts = HypGetActiveMember("Market", vtMarket)

              'Add rest of the members here

              Sheet1.Range("A1").Value = "This report for <" & vtMarket & "> " & " <" & vtAccount & "> for the month <" & vtMonth & "," & vtYear & ">"

              End Sub

               

              Regards

               

               

              Celvin

              http://www.orahyplabs.com

              • 4. Re: Smart View VBA Question - Urgent Please
                878783

                Thanks for your response.

                I have a question, If I keep POV members in Sheet2, if users want to change the POV, how it will change dynamically ?

                • 5. Re: Smart View VBA Question - Urgent Please
                  Celvin Kattookaran

                  (Your retrieval sheet will be Sheet 2) Since it is referring to the cells they can change the POV members in Sheet2 and the formula will pick it from there.

                   

                  Regards

                   

                   

                  Celvin

                  http://www.orahyplabs.com