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

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

    Re: Current year and current month in prompt as default

    ‏2012-06-27T23:48:02Z  
    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

    Re: Current year and current month in prompt as default

    ‏2012-06-29T00:43:45Z  
    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.
    Thank you phil
  • Cog_Dimensional
    Cog_Dimensional
    29 Posts

    Re: Current year and current month in prompt as default

    ‏2012-07-26T03:27:34Z  
    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.
  • Modipalli
    Modipalli
    6 Posts

    Re: Current year and current month in prompt as default

    ‏2012-07-26T08:51:07Z  
    Hi All,

    we can achive this current year and current month by using java scripts...
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Current year and current month in prompt as default

    ‏2012-07-26T10:03:42Z  
    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.
    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

    Re: Current year and current month in prompt as default

    ‏2012-07-26T14:22:17Z  
    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.
    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.
  • Cog_Dimensional
    Cog_Dimensional
    29 Posts

    Re: Current year and current month in prompt as default

    ‏2012-07-26T14:29:43Z  
    • Modipalli
    • ‏2012-07-26T08:51:07Z
    Hi All,

    we can achive this current year and current month by using java scripts...
    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..
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Current year and current month in prompt as default

    ‏2012-07-26T21:29:04Z  
    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.
    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

    Re: Current year and current month in prompt as default

    ‏2012-07-27T00:03:01Z  
    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.
    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

    Re: Current year and current month in prompt as default

    ‏2012-07-27T00:09:54Z  
    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.
    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

    Re: Current year and current month in prompt as default

    ‏2012-07-28T03:09:00Z  
    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.
    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

    Re: Current year and current month in prompt as default

    ‏2012-08-02T01:42:41Z  
    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.
    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

    Re: Current year and current month in prompt as default

    ‏2012-08-02T05:47:26Z  
    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....
    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.
  • Biju_Bangalore
    Biju_Bangalore
    1 Post

    Re: Current year and current month in prompt as default

    ‏2013-07-22T11:30:30Z  
    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..

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

  • MChandrasekhara
    MChandrasekhara
    9 Posts

    Re: Current year and current month in prompt as default

    ‏2014-01-10T15:29:04Z  
    • Modipalli
    • ‏2012-07-26T08:51:07Z
    Hi All,

    we can achive this current year and current month by using java scripts...

    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

    Re: Current year and current month in prompt as default

    ‏2014-01-15T16:21:11Z  

    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

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