I need the users to be able to select a particular measure from a cube and based on the selection, he/she should get data for that measure over a period of time.
Now I wanted to use an openrowset query to get data from the cube
in query analyzer. This although works, but I am not able to parameterize the query.
convert(int, convert(varchar(20),[[Period]].[Year]].[MEMBER_CAPTION]]])) as [Year],
convert(varchar(20),[[Period]].[Month]].[MEMBER_CAPTION]]]) as [Month],
[[Measures]].[%Measure]]] as Percent_Measure
FROM OpenRowset('MSOLAP','DATASOURCE=[ServerName]; USER ID=; PASSWORD=;Initial Catalog="DBName";',
FROM [Name of the Cube]'
where [[Period]].[Year]].[MEMBER_CAPTION]]] is not null
and [[Period]].[Month]].[MEMBER_CAPTION]]] is not null
This query works fine. However, now I want to parameterize the Client ID and the Measure. Can you please help me do so.