I'm new in using cubes. I setup a month prompt but the month is showing twice in the drop down list (like JANUARY JANUARY FEBRUARY FEBRUARY..).
Our Time Dimension is
Year
- Quarter
- Month
Currently, we have 2014 and 2013 years. It seem the first month is for 2014 and the second is for 2013.
How do I fix to display only 2014 months?
Code used is:
[XX Cube].[Time].[Time].[Invoice Fiscal Month] -> ? pmonth ?
Thank you in advance.
correct. cognos cube or dimensional uses the business keys and displays the caption. meaning your actual query result is internally like this:
business key caption
201301 January
201401 January
201302 February
201402 February
etc.. etc
if you only need 2014 months then why don't you add a filter for 2014 only?
How to filter only 2014 months for the prompt? I tried using functions but it's giving me errors.
you have to be more specific than that if you want us to help
How to display only the months for 2014 in my dropdown? What is the function to filter only 2014 Months for the drop down only?
Everytime I add a function to [XX Cube].[Time].[Time].[Invoice Fiscal Month] -> ? pmonth ? it's giving me error.
The report will show This Year and Last Year Data based on the month selected. This part is working.
I just need the prompt to show 2014 month members only
Thank you.
You should be able to just create a detail filter in the query being used for your prompt page to exclude all years except for 2014.
So it would be like: [XX Cube].[Time].[Time].[Invoice Fiscal Year] = 2014
Detail filters in reports against cube sources is generally not advisable. You might do well with the descendants function to show all the months beneath your latest year. You can use the lastPeriods function to find the latest member in your year level and nest that within the descendants function.
You can easily find the syntax for these in the user guides or in the dimensional function reference.
Quote from: kgcognos on 15 Sep 2014 07:20:02 PM
I'm new in using cubes. I setup a month prompt but the month is showing twice in the drop down list (like JANUARY JANUARY FEBRUARY FEBRUARY..).
Our Time Dimension is
Year
- Quarter
- Month
Currently, we have 2014 and 2013 years. It seem the first month is for 2014 and the second is for 2013.
How do I fix to display only 2014 months?
Code used is:
[XX Cube].[Time].[Time].[Invoice Fiscal Month] -> ? pmonth ?
Thank you in advance.
Assuming your months are part of a hierarchy, you can use a single parent or ancestor to limit the month members you see
Create a query calculation in the query driving your prompt values - use the expression:
descendants([your 2014 year member], [your month level])
Set this calculation as the Use Value in your prompt.
Cheers!
MF.