Default selection in a value prompt as current month

[Chandrasekar]

Hi,

I need to select current month is a default selection in the value prompt. Using dimensional model.

MUN is [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[FY]

Note: current month default selection is July, next month default selection is August and it process simultaneously.

Please help me, its very urgent requirement.

Thanks & Regards,
Chandrasekar

[navissar]

Generally speaking, there are two ways to achieve dynamic default selection in prompts - the example of current month is quite common.
The first is to create a static choice for the value prompt with use value of, for example, 999 and display value which reads "Current Month". The outcome looks like this:

Then, you set the default selection of the prompt to your use value (In our example, 999), and you set your filter to filter for current month when 999 is selected.
I'm not a big fan of this method, because it isn't "clean".
A second option is to use JavaScript. Give your prompt a name in properties. I use the name "Month". Then, you can use one of the following scripts.
Note:
1. These scripts assume there's a numeric (1-12) use value and a textual (Jan-Dec) display value. Changes may be needed for other scenarios.
2. Past experience had taught me to add this: These scripts are supplied as-is and I cannot and do not guarantee to support them here or elsewhere in the future. I'm a private person, not a helpdesk.

for 10.2 and up (using prompt API):

Code Select Expand

<script>
var RNA={};
RNA.setDefault=function(promptName){
var oCR = cognos.Report.getReport("_THIS_");
var pr = oCR.prompt.getControlByName(promptName);
//create the desired value structure for default.
//you may need to change this section for your needs
var monthNames = [ "January", "February", "March", "April", "May", "June","July", "August", "September", "October", "November", "December" ];
var today=new Date();
var currentMonthNumber=today.getMonth()+1;
var currentMonthName=monthNames[today.getMonth()];
var vals={'use': currentMonthNumber, 'display': currentMonthName };
pr.setValues([vals]);
}
RNA.setDefault("month");
</script>
for 10.1 or lower:

Code Select Expand

<script>
var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ?formWarpRequest_THIS_ : formWarpRequest_NS_ );}
var pr = fW._oLstChoicesmonth;
var today=new Date();
var currentMonthNumber=today.getMonth()+1;
for(var i=0;i<pr.length;i++){
if(pr[i].value==currentMonthNumber){
pr[i].selected=true;//change selected to checked for checkbox prompt
break;
}
}

</script>

Hope this helps.

[Chandrasekar]

Hi,

I am using this script not get solution. My version is 10.2 and my value prompt name is "p_Month".

If need to changes in prompt properties?

<script>
var RNA={};
RNA.setDefault=function(promptName){
      var oCR = cognos.Report.getReport("_THIS_");
      var pr = oCR.prompt.getControlByName(promptName);
      //create the desired value structure for default.
      //you may need to change this section for your needs
      var monthNames = [ "January", "February", "March", "April", "May", "June","July", "August", "September", "October", "November", "December" ];
      var today=new Date();
      var currentMonthNumber=today.getMonth()+1;
      var currentMonthName=monthNames[today.getMonth()];
      var vals={'use': currentMonthNumber, 'display': currentMonthName };
      pr.setValues([vals]);
}
RNA.setDefault("p_Month");
</script>

[navissar]

Sorry, I didn't understand your question.

[Chandrasekar]

If am using java script need to any changes in value prompt properties? like default selections or static choices.

[navissar]

No, you do not. Only the name property is relevant.
If your use value or display value differ than the ones suggested (use:1-12, display:January-December) then you'll need to change the script to accommodate.

[Chandrasekar]

Hi,

In this method(java script) i am not get the solution. Can you suggest any other method (like creating data item then used dimensional functions).

Please provide the solution.

Thanks & Regards,
Chandrasekar.

[navissar]

Right, if something isn't working for you, it would go a long way to help us help you if you told us what, precisely, is not working.

[Chandrasekar]

Hi,

1. Generate current month. I create a  data item name is 'current_month', Expression is extract(month,current_date). It extracts result is 7. My DB value is July.(Here any changes required)

2. And create another data item  name is 'selected_month', expression is,

2.1   [SalesReporting].[Month].[Month]->?p_Month?(old query its work fine.)

2.2
Case When
?p_Month? = 'Current Month'
Then
[current_month]
Else
[SalesReporting].[Month].[Month]->?p_Month?
End
(updated query, not works)

3. Then create static choice to 'p_Month' value prompt. (use value is NULL, display value is'Current Month' )

4. My issue is, if am selecting current month option or other month options it not works.
Possible options:
How to set static choice use value is [current_month] data item.

or else you have another option?

5. Can you help me?

Thanks & Regards,
Chandrasekar.

[Chandrasekar]

Hi Nimrod Avissar,

Finally i reach near the solution. If i select any one month its executes. when i select current month option it generates error.

Error message is :

An unexpected type of parameter value was encountered. The specified parameter type is 'String', but the expected type is 'memberUniqueName'
[/s]
My data item query is:

Case When 
?p_Month? = 'Current Month'
Then
{   extract(month,current_date)
   when 1 then 'January'
   when 2 then 'February'
   when 3 then 'March'
   when 4 then 'April'
   when 5 then 'May'
   when 6 then 'June'
   when 7 then 'July'
   when 8 then 'August'
   when 9 then 'September'
   when 10 then 'October'
   when 11 then 'November'
   when 12 then 'December'
}   
Else
[SalesReporting].[Month].[Month]->?p_Month?
End


I guess the error comes in static choice use value "Current Month" is string item. How to pass value from static choice to this  query?

Can you help?

Thanks & Regards,
Chandrasekar.

[navissar]

Well, you're getting the error because your prompt passes an MUN, and you try to pass a number.
If your prompt is for month in year (the users are prompted to select members like "2014/Jan" or "PY/February") and you're keen to keep it this way, then your only feasible option is to use Javascript (Which you have still failed to explain why it doesn't work for you).
Otherwise, there may be a way for you - using macro expressions, but you'll need to change your month prompt and year prompt entirely, and also to post here what your month MUN looks like.

[Chandrasekar]

FY MUN is : [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[FY]

In my db does not have month member. I get a month from [Full Year]. That query is: descendants([Full Year],2).

Month prompt values: Current Month from (static choice), January to December from (prompt query).

[Chandrasekar]

Sample coding

[navissar]

FY MUN is : [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[FY]

In my db does not have month member. I get a month from [Full Year]. That query is: descendants([Full Year],2).

if FY has descendants, then there are month members. Without knowing how your month member is built I cannot offer you a specific expression to match your needs.

[Chandrasekar]

Month is a data item from prompt query.

Month MUN is: [pq_Month].[Month] is it correct.

[MFGF]

Month is a data item from prompt query.

Month MUN is: [pq_Month].[Month] is it correct.

Hi,

What you posted isn't a MUN. The one you posted up for FY was [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[FY] and your Month member MUNs will have the same kind of format.

That (I think) is what Nimrod is looking for.

MF.

[Chandrasekar]

I am already told, month is a data item not a member  (How i get a MUN for this?)

[MFGF]

I am already told, month is a data item not a member  (How i get a MUN for this?)

Hi,

In that case you seem to be contradicting yourself? You say you have no month members in your cube, but you are delivering those same month members with an expression:

descendants([Full Year],2).

This is returning the members two levels below Full Year in your cube - ie the month members. If you have month members in your cube (which you must do for this to work) then they each have a MUN. All you need to do is to tell us what a month member MUN looks like.

Expand your member tree in the package view and expand the members below FY for two levels until you see the month members. Right-click a month member and choose Properties. Copy the Member Unique Name property and post it into the thread.

Cheers!

MF.

[Chandrasekar]

Hi MFGF,

It looks like :

Jan  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q1^1]
Feb  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q1^2]
Mar  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q1^3]
Apr  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q2^4]
May MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q2^5]
Jun  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q2^6]
Jul  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q3^7]
Aug  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q3^8]
Sep  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q3^9]
Oct  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q4^10]
Nov  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q4^11]
Dec  MUN: [SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].[Q4^12]

[navissar]

Oh, that's better.
I'm sorry for the delay in answering, there's a bit of a war going on where I am and being in a bomb shelter a couple of hours a day sure does reduce the amount of free time one has.
Anyway, what we want to do is to create an expression that will mimic the behavior of the current month's member dynamically. We're going to use some trickery.
First of all, in your month data item (The one where you're referencing ?p_Month?), make the following changes:
replace ?p_Month? with this:
#prompt('p_Month','token')#
Then, change your month prompt - edit the static value of Current Month, change use value to the following expression:

Code Select Expand

#'[SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].['+substitute('''','',substitute('''','',csv(substitute(timestampMask($current_timestamp,'mm'),'',grep(timestampMask($current_timestamp,'mm'),array('01Q1^1','02Q1^2','03Q1^3','04Q2^4','05Q2^5','06Q2^6','07Q3^7','08Q3^8','09Q3^9','10Q4^10','11Q4^11','12Q4^12'))))))+']'#
It's a very long expression and I'm tired, basically this is a macro for finding out the current month member, it follows the same rules I used in previous posts.

Now, when you select a month, the month's MUN gets passed along and should work fine (I couldn't test this scenario). When you select Current Month, you'll pass the macro that will create the member.

If you're getting an error (everything's possible) then please make a new query, create a data item and paste in the following:

Code Select Expand

#sq('[SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].['+substitute('''','',substitute('''','',csv(substitute(timestampMask($current_timestamp,'mm'),'',grep(timestampMask($current_timestamp,'mm'),array('01Q1^1','02Q1^2','03Q1^3','04Q2^4','05Q2^5','06Q2^6','07Q3^7','08Q3^8','09Q3^9','10Q4^10','11Q4^11','12Q4^12'))))))+']')#
and post back the results, it'll make it easier for me to debug.

[Chandrasekar]

Hi,

Your effort is good. In this macro execute(Check). When enhance in my report it generates error looks like,
The "crossJoinSet" operator is not applicable. The limit on the number of tuples per edge has been exceeded (-2).

[navissar]

Hi,

Your effort is good. In this macro execute(Check). When enhance in my report it generates error looks like,
The "crossJoinSet" operator is not applicable. The limit on the number of tuples per edge has been exceeded (-2).

Right, listen: I'm sure you're doing your best, but I feel like an interrogator and I don't like it. If you need help, from me or anyone else, you're going to have to supply more details, and constantly asking for it is getting tiresome.

Your error message is for the entire query, which we know nothing about. So, if you want help, you'll have to tell us more about this query and your report, specifically: which other dimensions/measures and parameters are there, how is your query structured and so on.
Also, I have specifically asked that if you're getting an error you'll try something and post back the result.

If you're getting an error (everything's possible) then please make a new query, create a data item and paste in the following:

Code Select Expand

#sq('[SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].['+substitute('''','',substitute('''','',csv(substitute(timestampMask($current_timestamp,'mm'),'',grep(timestampMask($current_timestamp,'mm'),array('01Q1^1','02Q1^2','03Q1^3','04Q2^4','05Q2^5','06Q2^6','07Q3^7','08Q3^8','09Q3^9','10Q4^10','11Q4^11','12Q4^12'))))))+']')#
and post back the results, it'll make it easier for me to debug.

Also, does this happen when you select any month?


Please try to post back with details.

[Chandrasekar]

Finally i got the solution Mr.Nimrod Avissar.

Using concept: Your static choice use value code.

#'[SalesReporting].[Month].[Month]->:[TM].[Month].[Month].[@MEMBER].['+substitute('''','',substitute('''','',csv(substitute(timestampMask($current_timestamp,'mm'),'',grep(timestampMask($current_timestamp,'mm'),array('01Q1^1','02Q1^2','03Q1^3','04Q2^4','05Q2^5','06Q2^6','07Q3^7','08Q3^8','09Q3^9','10Q4^10','11Q4^11','12Q4^12'))))))+']'#

Its automatically extract the month from member. So need not case condition.

Old data item:

case when ?p_Month?='Current Month'
Then
[current_month]
else
[SalesReporting].[Month].[Month]->?p_Month?
end

In that condition not needed.

New data item:
[SalesReporting].[Month].[Month]->?p_Month?

Its work, that static choice directly select a month.

Really you are good. Thanks for your effort once again.

How to you get that format, If you have any syntax?