Hi Again parameter problem

i need pass the parameter for the following query. need to pass separately to year and quarter. from year to to year. parameters shoould b the fyear,fquarter,tyear,tquarter. coz user need to select those.

SET [CustomTimeSet] AS Descendants([Date—Fiscal].[@.Fyear].[@.Fquater]:
[Date—Fiscal].[@.TYear].[@.TQuarter],[Date—Fiscal].[month],Leaves)

Wick,

Why not you change in query as follow...

SET [CustomTimeSet] AS Descendants(

StrToSet(@.TimeRange),

[Date—Fiscal].[month],Leaves)

And Pass the complete string for @.TimeRange.

Bhudev

|||hi dev,
I need to pass year and quarter separately. those are separate multi values. how can i add year and quarter string together?
|||tried like this. but still have some error.

SET [CustomTimeSet] AS Descendants(strtomember(@.Fyear)+strtomember(@.FQuarter) : strtomember(@.TYear)+strtomember(@.TQuarter),[Date—Fiscal].[month],Leaves)

pls..... help me.
|||pls|||

Wick,

In your case

Range operator ( : ) operands have different levels; they must be the same

You need to pass Same level in Range Operator. Here your start Range Level is Year and End Range is Quarter that's why it is giving error.

Well I'm giving some example for understanding.

---

SELECT

{[Measures].[MyAmount]} ON 0,

NON EMPTY{

Descendants(

STRTOMEMBER(@.Year),

[Time].[Time Calendar].[Quarter],

AFTER

)

} ON 1

FROM [MyCube];

---

SELECT

{[Measures].[MyAmount]} ON 0,

NON EMPTY{

Descendants(

STRTOMEMBER('[Time].[Time Calendar].[CY 2006]'),

[Time].[Time Calendar].[Quarter],

AFTER

)

} ON 1

FROM [MyCube];

---

SELECT

{[Measures].[MyAmount]} ON 0,

NON EMPTY{

Descendants(

STRTOMEMBER('[Time].[Time Calendar].[CY 2006]:[Time].[Time Calendar].[CY 2007]'),

[Time].[Time Calendar].[Quarter],

AFTER

)

} ON 1

FROM [MyCube];

---

Bhudev

|||hi dev,
if so how can i pass year and the quarter ? it is better if u can edit my code and show?
|||

Wick,

You need to use following MDX query.

Pass Quarter Values in Start and End Range.

When you will use this Query in SSRS then Year and Half Year Values will come in Result Set itself.

But you need to use Time Hierarchy for this.

---

SELECT

{[Measures].[MyAmount]} ON 0,

NON EMPTY{

STRTOSET(@.Quarter)

} ON 1

FROM [MyCube];

---

SELECT

{[Measures].[MyAmount]} ON 0,

NON EMPTY{

STRTOSET('{[Time].[Time Calendar].[Q1 CY 2006]:[Time].[Time Calendar].[Q4 CY 2007]}')

} ON 1

FROM [MyCube];

---

Bhudev

|||the thing is we have separate parameter for the Year and Quarter. then we have to give to select year then quarter. but i think there is no cascading selection in reporting. in our case user have to select year as well as the Quarter in the parameter dropdown. our part of the query is like the following,

SET [CustomTimeSet] AS Descendants([Date—Fiscal].[All Date—Fiscal].[2006].[Quarter 4] : [Date—Fiscal].[All Date—Fiscal].[2007.[Quarter 4],[Date—Fiscal].[month],Leaves)

in ssrs 2005 , if we create the parameter we have the combo in the report to select the value. then we created the year and quarter and we need pass that selected particular year and date to the report. we tried like the following
SET [CustomTimeSet] AS Descendants(strtomember(@.FQuarter) : strtomember(@.TQuarter),[Date—Fiscal].[month],Leaves)

this query is ok. but we cant give user to select year. that is the problem.

|||WITH

SET [CustomTimeSet] AS Descendants(strtomember(@.Fyear)+strtomember(@.FQuarter) : [Date—Fiscal].[All Date—Fiscal].[2006].[Quarter 4],[Date—Fiscal].[month],Leaves)

}'

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
[CustomTimeSet] on rows

FROM Profitability
WHERE [Account—ETBillingCode].[MDA]|||

Dear, Dear Wick,

So your Report will be for Single Year may have multiple Quarters of that selected year.

For Example: User Selects

Year: 2006

Quarter: Quarter 3

Then Report should display data for Q1, Q2 and Q3 of Year 2006.

So in that case you can manuplate the Year String internally. Suppose User selects 2006 then pass the value for Start Range as follow

[Time].[Calander Year].[Q1 CY 2006]

And then suppose user selects Quarter 4 then pass the values for End Range as

[Time].[Calander Year].[Q4 CY 2006]

So Start Range Quarter will be every time Very First Quarter.

So arange your Procedure/Front End Code which is passing the values in that way is easy.

Bhudev

|||

Above Query will not work because of strtomember(@.Fyear)+strtomember(@.FQuarter) : this part

Bhudev