date range in slicer (DMR Model)

[inu]

Hi Team,

I have year range prompt, which will filter the data, i used below statement in the slicer as follows:
filter([Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year],
roleValue('_businessKey', currentMember([Hiring Process].[Requisition Raise Date].[Requisition Raise Date])) >= #sq(prompt('From', 'token',''))# and
roleValue('_businessKey', currentMember([Hiring Process].[Requisition Raise Date].[Requisition Raise Date])) <= #sq(prompt('To', 'token',''))#
)

But when i run the report , i dont get the data.
Please suggest ..




Regards,
Inam

[CognosPaul]

In your date dimension, make sure the Year level has a numeric year attribute. Then in your filter you should be able to do:

Code Select Expand


filter([Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year],
[Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year].[Year] >= #prompt('From', 'token','')# and
[Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year].[Year] <= #prompt('To', 'token','')#
)


[inu]

In your date dimension, make sure the Year level has a numeric year attribute. Then in your filter you should be able to do:

Code Select Expand


filter([Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year],
[Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year].[Year] >= #prompt('From', 'token','')# and
[Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year].[Year] <= #prompt('To', 'token','')#
)


When i use the below single statement it works well.

[Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year].[Year] ->[all].[2017]

Works well and displays the data.
Please suggest how can i check, what my previous statement is returning.

6

Regards,
Inam

[inu]

Year is not in interger data type, can i cast it to  the integer .

[CognosPaul]

Why isn't year integer? Do you not have a numeric year field in your time dimension? Go back to the DMR model and create the numeric attribute as I suggested. Cast it there if you need to.

[inu]

Why isn't year integer? Do you not have a numeric year field in your time dimension? Go back to the DMR model and create the numeric attribute as I suggested. Cast it there if you need to.

Yes, i checked in fm , year was in integer format, but the medeler has casted that to character format. And set the same as both business key and member caption. What i did, i took the same year (integer format  )and set as business key.
After publishing . I saw the member folder of the year level . It's coming only one year four times. I dont understand why.
I completely frustrated from dmr reporting.
Please help me out in this.
Your resppnse increases the confidence level and bring relaxed face and mood.

Regard,
Inam

[inu]

Yes, i checked in fm , year was in integer format, but the medeler has casted that to character format. And set the same as both business key and member caption. What i did, i took the same year (integer format  )and set as business key.
After publishing . I saw the member folder of the year level . It's coming only one year four times. I dont understand why.
I completely frustrated from dmr reporting.
Please help me out in this.
Your resppnse increases the confidence level and bring relaxed face and mood.

Regard,
Inam




Yes, now package is published and am getting all values of year under member folder.
But still slicer is not filtering the the year.
What I did for testing purpose that slicer is working or not, i created one query, where only year level as well as slicer which contains
filter([Hiring Process].[Requisition Raise Date].[Requisition Raise Date].[Year], roleValue('_businessKey',currentMember([Hiring Process].[Requisition Raise Date].[Requisition Raise Date]))<=2017)

But unfortunately , all year are displaying , here also slicer is not working fine. Please suggest CognosPaul...

[New_Guy]

Hi,
Another solution you can try is from cognos paul with periods to date and except and union.

http://www.cognoise.com/index.php?topic=12731.0
Good luck
New guy

[CognosPaul]

Late reply - April was a rough month.


The reason the modeler cast the field to char is DMR returns an error when you make a key with an integer. YOU CAN STILL USE AN INT AS A KEY. Just ignore the error.

Alternatively, the modeler can make a char specifically for the level key, but leave the integer as an attribute. Again, the Year number needs to be an attribute of the level.