Hi guys,
I need your help!
- My report contains 12 columns = 12 months.
- 1 prompt "Month"
- And under each month I would like to add conditional data item that will select one of the 2 possible members from dimension "Scenario" (Actuals or Budget) based on the following condition:
if selected prompt month, let's say user selects "April", which is the latest available month of Actuals. I would like that all columns that are < April, take the Scenario "Actuals" and the rest of the columns are filled in with the data of Budget Scenario.
Any help will be appreciated,
cheers,
Aspire
Broad brush terms, but this is the sort of approach I'd take. In your prompt I'd have static choices, with it displaying the month name, but using the month number. So in your example, the user would choose April, but the prompt would pass the number 4.
Then I'd create 12 boolean variables, 1 for each month. January: ?month_prompt? < 2, February: ?month_prompt?<3 etc.
Then for each of your actual / budget columns you could apply the relevant variable as a render variable.
Hi Chris,
Thanks for your reply! I wanted to test your solution but I'm blocken when setting up the "Static" setting of the month prompt. In order to use as you said numerical values of the month I used the following expression: extract(month,[Jan]) but it doesn't seem to work --> I get the error stating that the second member of the extract function should be a string..
Maybe you were talking about different thing, correct me if I'm wrong
thanks
Hi,
It might depend on what platform you're using, but you can probably just use month([DateField])
Is your source relational or dimensional? In a dimensional model there aren't any dates...there are only MUNs with captions. In a date dimension the captions may resemble dates but they are not date data types.
If it is dimensional is it a cube or is it a DMR?