Data between two dates

Question

Hello

Here is my code :

WITH 
MEMBER [MEASURES].[TEST]  -- CALCUL DU NOMBRE D'OPC ATTRIBUE A L'EQUIPE
AS 
[Axe_ORGANISATION].[ID_COLLABORATEUR].members.count
SELECT NON EMPTY { [MEASURES].[TEST] } ON COLUMNS, NON EMPTY { ([Axe_ORGANISATION].[PROFIL].[PROFIL].ALLMEMBERS 
*[Axe_ORGANISATION].[NOM_PLATEAU].[NOM_PLATEAU].ALLMEMBERS
* [Axe_ORGANISATION].[NOM_UNITE].[NOM_UNITE].ALLMEMBERS
*  [Axe_ORGANISATION].[ID_COLLABORATEUR].[ID_COLLABORATEUR].ALLMEMBERS  
* [Axe_ORGANISATION].[NOM_EQUIPE].[NOM_EQUIPE].ALLMEMBERS ) } 
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 
FROM ( SELECT (  [Axe_ORGANISATION].[PROFIL].&[Conseiller Client] ) ON COLUMNS 
FROM [PVC_Reporting]) 
(where format([Axe_ORGANISATION].[DATE AFFECTATION DEB].[DATE AFFECTATION DEB], "yyyyMM") <@AxeTempsMOISANNEE 
and Axe_ORGANISATION].[DATE AFFECTATION FIN].[DATE AFFECTATION FIN], "yyyyMM")  >@AxeTempsMOISANNEE)

As a parameter I receve a string of a date in format yyyMM. I need to filter data based on date begin and end of and employee.

Answer 1

Hi Zizou ,

Does the parameter contain the text itself (something like "201311") ?
If so, try to use the range in your where clause :
Where ( strtomember("[Axe_ORGANISATION].[DATE AFFECTATION FIN].&["+@AxeTempsMOISANNEE+"]")
  : strtomember("[Axe_ORGANISATION].[DATE AFFECTATION DEB].&["+@AxeTempsMOISANNEE+"]"))

---

Regards, David .

Answer 2

Thank you for your quick reply. After cheking, my code has to be like this :

AxeTempsMOISANNEE > convert(begindate; "yyyMM") and MOISANNEE < convert(enddate; "yyyMM")

In my cube begin date is DATE AFFECTATION DEB and end date is DATE AFFECTATION FIN

I started making sure that begin date would work correctly and tiped this code, so to start AxeTempsMOISANNEE  is a complete date and not only yyyyMM:

Where (

strtomember("[Axe_ORGANISATION].[DATE AFFECTATION DEB].&["+@AxeTempsMOISANNEE+"]") : NULL
  )

But this gives me this error :

impossible to convert the string '[Axe_ORGANISATION' into date type

I tried this code two :

{StrToMember("[Axe_ORGANISATION].[DATE AFFECTATION DEB].&" + Format( CDate(@AxeTempsMOISANNEE), "[yyyy-MM-ddTHH:mm:ss]" ) ):
NULL}  

but it gives me this error :

the folowing error happened while executing the managed stored procedure Format

my date format is : 2013-11-01 00:00:00

Answer 3

Hi ,

Can you please send one member from [Axe_ORGANISATION].[DATE AFFECTATION DEB] (drag and drop from the SSMS) and the text data the parameter will hold ? it will be easier for me to help ..

---

Regards, David .

Answer 4

Hi Zizou23,

Please try the query below.

{StrToMember("[Axe_ORGANISATION].[DATE AFFECTATION DEB].&[" + Format(CDate(@AxeTempsMOISANNEE),"yyyy-MM-ddTHH:mm:ss") + "]"):
NULL} 

Here is a smimilar thread for your reference.
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/15601dce-59ad-49f2-b678-7b1547543352/mdx-query-for-date-format?forum=sqlanalysisservices

Regards,

---

Charlie Liao
TechNet Community Support