Date prompt based on DMR model

[DrHouse]

Hi,
I need to develop one Report which has 2 parameters Date From and Date To, based on that I need to have "Selected Period", "Selected Period Last LY", "YTD" (based on Date To) and "YTD LY".

Customer strictly want Date prompt not Tree Prompt. Date prompt is fine filtering Data if you put ?pDateFrom? in Filter, but because of my problem "Selected Period LY" filter is not so good solution. But problem is when I put filter in Data Item (ROLAP Model.D Date.Date-Y-M-D-> ?pDateFrom?) he is expecting MUN.

I try to build MUN like this:
MUN='[all].[' + LEFT(#prompt('pDateFrom', 'string')#,4) + '].['+LEFT(#prompt('pDateFrom', 'string')#,4) + SUBSTRING(#prompt('pDateFrom', 'string')#,6,2) + '].[' + LEFT(#prompt('pDateFrom', 'string')#,4) + SUBSTRING(#prompt('pDateFrom', 'string')#,6,2) + SUBSTRING(#prompt('pDateFrom', 'string')#,9,2)+']'

and then create Data Item
[ROLAP Model].[D Date].[Date-Y-M-D].[Date]->[MUN]
But this is not working.

Does anybody have some good Idea for this issue ?

Regards

[pricter]

What error do you get?

Could you provide the mun of the month dimention?

[DrHouse]

No Error just no data.

Now I succeed to get MUN with this

#prompt('pDateFrom', 'memberuniquename', '[ROLAP Model].[D Date].[Date-Y-M-D]')#

But When I put this in List I get 4 rows:
MUN
Date-Y-M-D (All)
2012
2012/6
30.06.2012

Do you know how to combine this syntax with this [ROLAP Model].[D Date].[Date-Y-M-D].[Date]-> ?

[DrHouse]

I resolve this issue with

Code Select Expand

total([Amount local YTD] within set (filter([ROLAP Model].[D Date].[Date-Y-M-D].[Date], [ROLAP Model].[D Date].[Date-Y-M-D].[Date].[Date] between ?pDateFrom? and ?pDateTo?)))


Do you know how to get same total for same period Last Year ?

Thanks

[pricter]

Since you have a dimentional model you can find the YTD in a different way
using the periodsToDate  function.

In order to find the PYTD use  a function to get the same month in the previous year ( you can choose among lag, parrallel period and cousin)

Then you can fine the PYTD months by

Code Select Expand

periodsToDate ([Year Level],[Same Month Previous Year])
and

Code Select Expand

total([Sales] within set [PYTD])

[DrHouse]

Hi Pricter,
problem is that I can't put ?pDateFrom? in function periodsToDate it's not working.
Parameter is from date picker not Tree Prompt.

Thanks

[pricter]

O no I see
You have problem to construct the mun but I can not help if you do not provide the full path of the MUN.

PS: If you would like to continue with the filter function you can use addyears in order to get the same period in the previous year.

[DrHouse]

I made workaround solution and resolve a problem.

I put two more Text Prompt on Prompt page and one html object with this code:

Code Select Expand


<button class="bp" onClick="setDatePrevYear();">Finish</button>

<script type="text/javascript">

function setDatePrevYear() {


var fW = (typeof getFormWarpRequest == "function" ?getFormWarpRequest() : document.forms["formWarpRequest"]);

var PPfromDate = new Date(fW.txtDateFromDate.value);
var PPtoDate = new Date(fW.txtDateToDate.value);

PPfromDate .setFullYear(PPfromDate .getFullYear() - 1);
PPtoDate .setFullYear(PPtoDate .getFullYear() - 1);

// CCYY-MM-DDThh:mm:ss
var Dan= PPfromDate.getDate();
var Mjesec = PPfromDate.getMonth() + 1;
var Godina=PPfromDate.getFullYear();
if (Dan < 10) Dan="0"+Dan;
if (Mjesec < 10) Mjesec ="0"+Mjesec;
// CCYY-MM-DDThh:mm:ss
var DanT= PPtoDate .getDate();
var MjesecT = PPtoDate .getMonth() + 1;
var GodinaT=PPtoDate .getFullYear();
if (DanT < 10) DanT="0"+DanT;
if (MjesecT < 10) MjesecT ="0"+MjesecT;

fW._textEditBoxYearLess.value=Godina + "-" + Mjesec  + "-" + Dan + "T00:00:00";
fW._textEditBoxYearLessTo.value=GodinaT + "-" + MjesecT  + "-" + DanT + "T00:00:00";

promptAction("finish");
}
</script>


This read Date from two date prompts, create year -1, format in Cognos date format and put that value in hidden Text prompts. This 2 text prompt has parameter below and everything else is history :). Html object is replacing standard finish button.