apply selected date in prompt to the filter

[sergiord2016]

Hello ,

i have a calendar prompt in order to make the user to select the date , so if the user sleects 18-10-2018 , the measures should be calculated from 1 January to 18 of October of 2018

Also i have to show the measures of same period of the previous year in order to compare (1 january 2017 to 18 of october 2017)

I have a date dimension with several support columns like the day number of the year but i dont know hoy to calculate the previous period and apply it to the crosstab.

I think o should get the date selected in a parameter , the calculate the parameter-365 days. Then show the calculated measures filtering from day 1 january to the day number of year from the parameter selected date. That number of the year should be the same for both periods.

Thanks in advance.

[hespora]

First off, I'm assuming relational here. If you are on a dimensional package, then I have no clue.

Secondly, I'm not entirely certain why you would want to go back exactly 365 days, rather than just taking the same date from the prior year. It would only make a difference if one of the years involved is a leap year, but if it is, you might get weird results: I.e., if you select the 18th of October from 2016, which was a leap year, going back 365 days from that yields the *19*th of October 2015.

Here's what I would do:
"?pDate?" is [selected date].
"_make_timestamp( extract( year, ?pDate? ), 1, 1 )" is [jan1st of selected year]
"_add_years( ?pDate?, -1 )" is [selected date last year]
"_add_years( [jan1st of selected year], -1 )" is [jan1st of last year]

and now, all you need is a detail filter of "[your date field] between [jan1st of selected year] and [selected date] OR [your date field] between [jan1st of last year] and [selected date last year]"

[sergiord2016]

Thanks for your answer, you are right , thats what i wanted to show.

The final step is that i have to show the data in tou rows separated (2018 and 2017)



If i drag the year dimension it works but i cant do the substract bacause i have to do it with data elements.

I think i should drag two new data elements containing the current selected year and the previous year.

If i just drag the data element , this does not filter the data into the year , it shows both years data in 2018:




The third thing i have probed is to assign the current year to the level member but it does not work  , because it says that exclusive null member is not valid

[hespora]

Ok, but sorry, thats what I meant by

First off, I'm assuming relational here. If you are on a dimensional package, then I have no clue.

What you have there is dimensional. My solution *DOES NOT APPLY* here, particularly you should not be using detail filters in dimensional style reporting.

[sergiord2016]

I could drag the year dimension from relational model,  that will not be a problem.

How can i assign the year of the parameter to the dimension(row)? I need to show the table like in the second image (with data for each year)  but need to make de diference (substraction)  between both years.

Is there any way to make relational model assigned the user parameter like in dimensional modeling?

I made a report like this in dimensional with year and month dropdown lists.  The i assigned the parameters to the dimension and it works.  Now i am working on doing the same but i feel that the assignation does not work with tje extraction of the year from the calendar.  Tje parameter selected now is a complete date so if i want to drag the year into the table i have to assign year(¿parameter?)  or with a data element that contains this,  both options gets error.

[MFGF]

I could drag the year dimension from relational model,  that will not be a problem.

How can i assign the year of the parameter to the dimension(row)? I need to show the table like in the second image (with data for each year)  but need to make de diference (substraction)  between both years.

Is there any way to make relational model assigned the user parameter like in dimensional modeling?

I made a report like this in dimensional with year and month dropdown lists.  The i assigned the parameters to the dimension and it works.  Now i am working on doing the same but i feel that the assignation does not work with tje extraction of the year from the calendar.  Tje parameter selected now is a complete date so if i want to drag the year into the table i have to assign year(¿parameter?)  or with a data element that contains this,  both options gets error.

Hi,

The crux of the issue here is that your dimensional package is based on members, but your date prompt is returning a date string. You can probably make things work, but you need your expression to resolve to a Member Unique Name (MUN) to be able to pinpoint the desired year member. We don't know what your MUNs look like, so it's very difficult to give you an off-the-cuff solution. You can find out by right-clicking a Year member in the package tree and choosing Properties from the right-click menu. Among the many things displayed, you should see the Member Unique Name. This will give you an idea of what you need to construct.

Cheers!

MF.

[sergiord2016]

When i do that this , this is the result:



If retrieve the year from a dropdownlist and fix the result like this , it works:

[BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]-> ?PANYO?

The problem is when i try to retrieve the year from the calendar (not the dropdownlist) , if i do a year(¿calendardate?) and i make [BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]-> [PANYO_CALENDAR] it does not work.

[CognosPaul]

Based on the mun from the image you would need to construct

Code Select Expand

[BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]->[all].[2011]

When you use a date prompt the date returned looks like yyyy-mm-dd. You need to use macro functions to extract the year from that. The macro function timestampMask will do the job, but it only takes timestamps, not dates. Try the following:

Code Select Expand

timestampMask(
   prompt('pDate','date')+'T00:00:00.000Z'
   ,'yyyy'
  )


That snippet will construct the year member based on the date you pass in pDate. It concatenates time and zone to create a timestamp (T is shorthand for a space, and Z is shorthand for the timezone) and then extracts the year. You could also use other macro functions on that constructed timestamp before creating the member.

[MFGF]

When i do that this , this is the result:



If retrieve the year from a dropdownlist and fix the result like this , it works:

[BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]-> ?PANYO?

The problem is when i try to retrieve the year from the calendar (not the dropdownlist) , if i do a year(¿calendardate?) and i make [BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]-> [PANYO_CALENDAR] it does not work.

Hi,

According to this, the format for your MUN is

[BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]->[all].[2011]

Assuming you can retrieve (only) the year into your parameter, you could use a macro to construct the MUN in your report expression:

(see attachment - for some reason the forum won't let me post when I include macro expressions in the message)

Cheers!

MF.

[sergiord2016]

Based on the mun from the image you would need to construct

Code Select Expand

[BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]->[all].[2011]

When you use a date prompt the date returned looks like yyyy-mm-dd. You need to use macro functions to extract the year from that. The macro function timestampMask will do the job, but it only takes timestamps, not dates. Try the following:

Code Select Expand

timestampMask(
   prompt('pDate','date')+'T00:00:00.000Z'
   ,'yyyy'
  )


That snippet will construct the year member based on the date you pass in pDate. It concatenates time and zone to create a timestamp (T is shorthand for a space, and Z is shorthand for the timezone) and then extracts the year. You could also use other macro functions on that constructed timestamp before creating the member.

Hello , thank you for teh answer but this does not pass the validation:



Are you sure the syntax is correct?

[sergiord2016]

Hi,

According to this, the format for your MUN is

[BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]->[all].[2011]

Assuming you can retrieve (only) the year into your parameter, you could use a macro to construct the MUN in your report expression:

(see attachment - for some reason the forum won't let me post when I include macro expressions in the message)

Cheers!

MF.

Thanks! My parameter is a calendar , so i can retrieve the complete date and i use this to filter in the query.

The problem is that what it want to project in the croosstab as a dimension is the results for one year and the previous one.

As i have flter the data in the query the only step i haev to do is to assign the selected year into the year member.

I have probed your solution , it passes the validation but it does not shows anything. One thing , i think prompt('PANYO','token','2011') shuld be different.

First , the parameter name is pdate (this is the calendar parameter name). Second , i don't know what token means. Third , i don't know why do you use 2011 as a static year , the year should be 2012 , 2013 , 2014 etc... depending on the user selection of the pDate.

Thanks in advance.

[MFGF]

Thanks! My parameter is a calendar , so i can retrieve the complete date and i use this to filter in the query.

The problem is that what it want to project in the croosstab as a dimension is the results for one year and the previous one.

As i have flter the data in the query the only step i haev to do is to assign the selected year into the year member.

I have probed your solution , it passes the validation but it does not shows anything. One thing , i think prompt('PANYO','token','2011') shuld be different.

First , the parameter name is pdate (this is the calendar parameter name). Second , i don't know what token means. Third , i don't know why do you use 2011 as a static year , the year should be 2012 , 2013 , 2014 etc... depending on the user selection of the pDate.

Thanks in advance.

Hi,

The expression I posted relies on your parameter containing just the year - not a complete date. The post Paul added before mine gives you an example of how you can use the timestampMask() function to re-format your date into a year. If you take the essence of both posts, you end up with the expression I included in the attachment below.

Cheers!

MF.

[sergiord2016]

Thanks!

I am sure we are so close yet....

One thing, i think o should change PNAYO parameter with pDate parameter (the calendar date parameter of my report). is'nt it?

[CognosPaul]

As MF said, we were both having trouble embedding the macro into the post.

#'[BBDD_T].[TIEMPO MES DIA].[TIEMPO AÑO].[AÑO]->[all].['
+ (the snippet from before)
+']'#

[sergiord2016]

it works!!!

Thanks for eveyone!!!! so happy!!