Optional Date Range slicer

[mrcool]

Hi All,

I am using dynamic cube as a source for my report and I want to know how to implement an optional prompt slicer on a date column which is not part of the report layout.
At present I am using it as a detail filter with role value function and all the other prompts as slicers.
Could you please help me with the right expression for the date range?

Thanks,
Mc

[mrcool]

Friends any thoughts on this?

Thanks,
Mc

[Francis aka khayman]

try this:

http://cognosm.fm3online.com/tips-and-tricks/filter-not-in-display/

[MFGF]

Hi All,

I am using dynamic cube as a source for my report and I want to know how to implement an optional prompt slicer on a date column which is not part of the report layout.
At present I am using it as a detail filter with role value function and all the other prompts as slicers.
Could you please help me with the right expression for the date range?

Thanks,
Mc

Hi,

If you're using a Dynamic Cube, you're not going to have any columns at all - you're going to have hierarchies, levels, sets, members and measures. You really (really really) shouldn't use detail filters in your report - they are a relational concept and could give you an entirely wrong result.

Try the following:

1. In the model tree on the left, expand your time dimension and the main time hierarchy, open the Members folder and right-click on the topmost member in the hierarchy (All Time?) and select "Properties". Locate the Member Unique Name property and copy the Member Unique Name of that member.
2. Go to the query of your report, and from the Toolbox tab, drag a Slicer Member Set to the Slicer area on the right.
3. Code the expression of the Slicer Member Set as: #prompt('Your desired parameter','MUN','The MUN of the member you copied in step 1')#  eg #prompt('DateParam','MUN','[great_outdoors_sales_en].[Years].[Years].[Years]->:[PC].[@MEMBER].[Years]')
4. Delete your detail filter
5. Add a value prompt to your prompt page, based on the existing parameter above, and based on the Date level of your time hierarchy. Make sure the prompt is set to Required = No.

Cheers!

MF.

[mrcool]

Hi MF,

Sorry I shouldn't have mentioned it as a column. I have a regular dimension with only date level. This level is not part of the report layout but being used a prompt.
I have tried prompt macro for all the dimensions but I am not sure on date range implementation. If date range isn't possible I am happy to change the prompt to from and to date.
Here is the slicer I am using for other dimensions.
#prompt('pProductLn','memberuniquename','rootMember([CubeName].[Brand].[Brand])')#
Once again thanks for responding.

Cheers,
Mc

[MFGF]

Hi MF,

Sorry I shouldn't have mentioned it as a column. I have a regular dimension with only date level. This level is not part of the report layout but being used a prompt.
I have tried prompt macro for all the dimensions but I am not sure on date range implementation. If date range isn't possible I am happy to change the prompt to from and to date.
Here is the slicer I am using for other dimensions.
#prompt('pProductLn','memberuniquename','rootMember([CubeName].[Brand].[Brand])')#
Once again thanks for responding.

Cheers,
Mc

Hi,

Well, a date range is another relational concept. Here you'd be looking for the user to select multiple date members? If so, you'd need to use a promptmany() macro function rather than a prompt() function. The whole thing probably needs to be within a set() function too.

Cheers!

MF.

[mrcool]

So do you basically mean that we can't use date prompts in OLAP reporting??
I have to use date prompt as part of my requirement.

Thanks,
Mc

[MFGF]

So do you basically mean that we can't use date prompts in OLAP reporting??
I have to use date prompt as part of my requirement.

Thanks,
Mc

Yes, I mean exactly that. Date prompts are designed to work with relational date / datetime columns. OLAP solutions do not have these - they have levels and members.

Cheers!

MF.

[mrcool]

Thanks MF.
I should then find an alternative to my date prompts.

[MFGF]

Thanks MF.
I should then find an alternative to my date prompts.

Your options essentially are tree prompts or value prompts.

Cheers!

MF.

[mrcool]

Thanks MF!

Even I am thinking of using Tree prompt by adding year and month to the date dimension.

Cheers,
mc