Topic
16 replies Latest Post - ‏2014-01-15T16:21:11Z by MChandrasekhara
yuvran
yuvran
101 Posts
ACCEPTED ANSWER

Pinned topic Current year and current month in prompt as default

‏2012-06-27T20:23:53Z |
Hi,

I have two prompts where you can select year and the month. I need to set to default current year and current month. This will enable our users to run the report for the current year and month, and will only have to select for another year or month upon re-running the report. How can set this in cognos 10 report studio?
Thanks,
UV.
Updated on 2012-08-02T05:47:26Z at 2012-08-02T05:47:26Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    15496 Posts
    ACCEPTED ANSWER

    Re: Current year and current month in prompt as default

    ‏2012-06-27T23:48:02Z  in response to yuvran
    You can use prompt macro functions to make the current date (or just the year using the timestampmask macro function) the default value for the parameter when you use it in a query. This will mean that when a user does not select a prompt value then the default value for the prompt macro will be used. This is the easiest method but it means that there will not be any value selected automatically in the prompt control.

    If you want a value selected in the prompt control then you can use a static choice in your prompt control with a known value, such as 9999 for the year, and a friendly caption like "Current Year". Since the value is static you can use it to set the default selection for the prompt control. You can then use the macro syntax to substitute the current year for 9999 in your query expression. The alternative is to use an IF THEN ELSE expression to convert the 9999 into another value but the macro syntax will give you better performance when executing the query.

    Another option is to use a JavaScript solution to select the current year or month value in the prompt control. If you search around for JavaScript and setting a default value for a Cognos prompt control you should find several examples for this type of thing.
    • yuvran
      yuvran
      101 Posts
      ACCEPTED ANSWER

      Re: Current year and current month in prompt as default

      ‏2012-06-29T00:43:45Z  in response to SystemAdmin
      Thank you phil
  • Cog_Dimensional
    Cog_Dimensional
    29 Posts
    ACCEPTED ANSWER

    Re: Current year and current month in prompt as default

    ‏2012-07-26T03:27:34Z  in response to yuvran
    Hi Phil -- Can you please post the Dimensional Functions and the Code that we need to use. If you dont mind can you please explain a bit in detail. I tried to use so many options that were available online, but still facing the coercion or string to MUN errors. Your help is much appreciated.
    • SystemAdmin
      SystemAdmin
      15496 Posts
      ACCEPTED ANSWER

      Re: Current year and current month in prompt as default

      ‏2012-07-26T10:03:42Z  in response to Cog_Dimensional
      Take a look at the information contained in the following document:

      http://www.ibm.com/developerworks/data/library/cognos/page232.html

      If there are questions after reading the doc then please post a report specification based on the Cognos samples demonstrating the layout and filtering that you require.
      • Cog_Dimensional
        Cog_Dimensional
        29 Posts
        ACCEPTED ANSWER

        Re: Current year and current month in prompt as default

        ‏2012-07-26T14:22:17Z  in response to SystemAdmin
        HI Phil-- Is there any other way that we can do it by not recreating a prompt using a macro and also by not changing the current Date to text in the display?. The Issue i am facing is, i am able to select the first value of a dorop down value prompt. But the thing is with out having any value Default Selections (in the properties of the prompt), it is asking me to enter the prompt values which i dont want to. In Relational report, for Default Selections i used paramdisplayvalue('parametername') and it worked fine. Provided below was the script that i am using to defaultly select the first value fo the prompt.
        <script type="text/javascript">
        function init()
        {
        var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms);
        if (fW)
        fW._oLstChoicesPM01.options[3].selected = true;
        canSubmitPrompt();
        }
        init();
        </script>
        So I basically adopted the same thing for the dimensional report as well.
        I basically used the above JS (by placing it right next to the value prompt) to automatically select the first value of the prompt. But I am not able to submit the prompt so I ended up using the default choices. I basically used the "rolevalue('_memberUniqueName',Cube.Dimension.Hierarchy)" as the use value of the prompt.
        Note: The Dimension had no levels.
        In the Default selctions of the prompt, I used "paramvalue('parametername)". I get an error "An unexpected type of parameter value was encountered. The specified parameter type is 'unknown', but the expected type is 'memberUniqueName'."
        The thing i need to know is how to auto submit even though there weren't any Default Selections?? we were on 8.4.1.
        • SystemAdmin
          SystemAdmin
          15496 Posts
          ACCEPTED ANSWER

          Re: Current year and current month in prompt as default

          ‏2012-07-26T21:29:04Z  in response to Cog_Dimensional
          Sorry, I can't really follow your complete report structure based on this text-only description. Would you be able to reproduce the problem using the Cognos samples and then post the report specification? This will make it much easier to diagnose the problem and determine a solution, if one exists.
          • Cog_Dimensional
            Cog_Dimensional
            29 Posts
            ACCEPTED ANSWER

            Re: Current year and current month in prompt as default

            ‏2012-07-27T00:03:01Z  in response to SystemAdmin
            Hi Phil -- . can you please let me know if it is possible to get the member name from the value, i think "Cube.Dimension.Hierarchy" in a data item returns all the member values (basically the values present in the members folder of under the hierarchy, we don't have any levels in a hierarchy.
            DateItem = Cube.Dimension.Hierarchy) o/p -->
            year
            year2014
            year2013
            and so on..

            I need to show year2014(This is basically the lowest value for which we slice and dice the data) as a cross tab column. for that a calculated measure was created to dynamically get the value by the prompt VALUE selected like Cube.Dimension.Hierarchy -> ?promptname?.

            To make the Current Year Default, I used the technique, mentioned in the attachment(based off of cognos sample structure). The only change is,in the month Data Item of the report query, I am using a calculated measure, using it as a column in crosstab and the data item doesn't have any filter function and looks like rolevalue('_memberUniqueName',Cube.Dimension.Hierarchy) = ?promptname? the reason is everything is based off of the memebers folder under a hierarchy (this is the key) and there were no levels and also when i use filter it is throwing an coercion error. Hope i explained it clearly for you. thanks a lot for your time.
            • Cog_Dimensional
              Cog_Dimensional
              29 Posts
              ACCEPTED ANSWER

              Re: Current year and current month in prompt as default

              ‏2012-07-27T00:09:54Z  in response to Cog_Dimensional
              Hi phil-- Just to add to it, instead of month in the propt query, I am using the Year Hierarchy and all the members come from the members folder but not the year level.
              • SystemAdmin
                SystemAdmin
                15496 Posts
                ACCEPTED ANSWER

                Re: Current year and current month in prompt as default

                ‏2012-07-28T03:09:00Z  in response to Cog_Dimensional
                It might be easier to use the macro syntax to calculate the required business key in the actual query instead of trying to put it into the prompt query results. Also, using the prompt control Static Choices option is much easier than trying to calculate a value and add/replace it in the set of values within the query.

                Note that the sample cube does not have months for 2012 so selecting the Current Month option in the prompt control will result in an error but this should work if you have the members in your data source to support the current month prompt selection.
                • Cog_Dimensional
                  Cog_Dimensional
                  29 Posts
                  ACCEPTED ANSWER

                  Re: Current year and current month in prompt as default

                  ‏2012-08-02T01:42:41Z  in response to SystemAdmin
                  Hi Phil -- Can we use the same macro in the Slicer or the Detail Filter? Slicer is the best way to retreive the data faster correct. I tried to use the macro as is in the slicer and also by removing the substitute but an getting the error. can you please let us know....
                  • SystemAdmin
                    SystemAdmin
                    15496 Posts
                    ACCEPTED ANSWER

                    Re: Current year and current month in prompt as default

                    ‏2012-08-02T05:47:26Z  in response to Cog_Dimensional
                    I don't know why you would be removing any of the macro function expression. The result of the expression should be a member unique name and you should be able to use this directly in your slicer expression. Note that a slicer is really only effective if you do not have the same dimension on the layout. If you do have this dimension on the layout then you should be using the expression in your data item calculations and not as a slicer.

                    I'm guessing that the error is "TR1901 There is a duplicate key value category index in (MDC name)". If this is your error then you could use the following document:

                    http://www-01.ibm.com/support/docview.wss?uid=swg21336102

                    If this is not the error which you are receiving then it might help if you supplied the actual error message and a report using the Cognos samples to demonstrate the error.
  • Modipalli
    Modipalli
    6 Posts
    ACCEPTED ANSWER

    Re: Current year and current month in prompt as default

    ‏2012-07-26T08:51:07Z  in response to yuvran
    Hi All,

    we can achive this current year and current month by using java scripts...
    • Cog_Dimensional
      Cog_Dimensional
      29 Posts
      ACCEPTED ANSWER

      Re: Current year and current month in prompt as default

      ‏2012-07-26T14:29:43Z  in response to Modipalli
      Hi Modiaplli/anyone -- Can you please let me know the exact JS that we need to use so that it will auto select and submit the First value of a Drop down value prompt. Do you have any script that will auto select and submit the nth member depending on the current month? If the current month is Jan it has to select first value, if feb the second value and so on..
      • Biju_Bangalore
        Biju_Bangalore
        1 Post
        ACCEPTED ANSWER

        Re: Current year and current month in prompt as default

        ‏2013-07-22T11:30:30Z  in response to Cog_Dimensional

        Did you got the solution. I am also looking for the same answer.

    • MChandrasekhara
      MChandrasekhara
      9 Posts
      ACCEPTED ANSWER

      Re: Current year and current month in prompt as default

      ‏2014-01-10T15:29:04Z  in response to Modipalli

      Hi All - Can anyone please help me how to achieve current month as default in my prompt page?.I have only month prompt (Value prompt) .

      Ex:- if I ran report this month the report default month (jan2014) has to default and if I ran next month default month would be feb 2014 etc…

      Ii hope we can achive using Java script , can you please provide javascript if any one have ! or alternative soluation.

      Appreciate your help and prompt response

      • MChandrasekhara
        MChandrasekhara
        9 Posts
        ACCEPTED ANSWER

        Re: Current year and current month in prompt as default

        ‏2014-01-15T16:21:11Z  in response to MChandrasekhara

        Hi Phil/All - Please help me the solution.. Appriciate your help