ParrallelPeriod Function not working in SSAS 2005

I am trying to use the ParallelPeriod Function in a Calculated Measure to have an aggregation of last years sales Quantity to display along side the Current Years sales quantity. I have done the exact same thing in the SSAS 2000 and it worked fine. I have a separate Table for the Time dimension that contains the Date as the key along with columns for year, quarter and month. There is a date field in my fact table which is referenced to the time demension table by the date. I use the following formula:

PARALLELPERIOD([Date By Day].[the_year - Quarter - the_month - the_date].[the_year],1, [Measures].[Qty] )

This creates the LQty measure which should contain last years sales. But when I process the cube, and browse the results I get a column filled with #Vaue errors. The error message states "Members belong to different hierarchies in the ParallelPeriod function". When I browse the cube for Qty, I can see the correct information aggregated by Year then Quarter then Month. The heirarchies seem to be correct. I have tried eleminating levels in the time dimension but still get the same error.

Are there any suggestions?Not sure what your exact MDX in AS 2000 was; but according to the SQL Server 2005 BOL, the syntax for ParallelPeriod() is:

http://msdn2.microsoft.com/en-us/library/ms145500(en-US,SQL.90).aspx
>>

ParallelPeriod (MDX)

Returns a member from a prior period in the same relative position as a specified member.

Arguments

Level_Expression

A valid Multidimensional Expressions (MDX) level expression.

Numeric_Expression

A valid MDX numeric expression.

Member_Expression

A valid MDX member expression.
...
>>
So, maybe you need an expression something like:

(PARALLELPERIOD([Date By Day].[the_year - Quarter - the_month - the_date].[the_year]),
[Measures].[Qty])

Parents of a SET of members

Hello,

I have a set of members, generated with a SET clause at the top of my MDX query.
Once I have this set, what function can I use on an axis to determine the parents (i.e the level above) of these members? Those parents will be a set too as the original set will have many different parents.

Or am I going about this the wrong way?

Thanks in advance
Mgale1

Try something like:

SELECT Generate({...} as Set1, ANCESTORS(Set1.CurrentMember, 1)) FROM MyCube

|||

Matt,

Thanks for the reply - unfortunately it gives me an error of "The CURRENT function cannot be invoked in this context because the 'MissedJobs' set is not in the scope."

My full query is :

WITH
MEMBER [Measures].[Incidence TY] AS SUM({[Media].[Media].CURRENTMEMBER},([Timeslice].[Timeslice].&[1], [Measures].[Incidence])),FORMAT_STRING = "#,##0.00",NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
MEMBER [Measures].[MyPublicationValue] AS SUM({[Media].[Media].&[7004]},[Measures].[Incidence TY]) ,NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
SET [MissedJobs] AS Filter(Existing([Job].[Job].CHILDREN),NOT [Measures].[MyPublicationValue])

--Produce Report
SELECT CROSSJOIN
(VISUALTOTALS({[Media].[Media].[All], [Media].[Media].&[7002],[Media].[Media].&[7004]})
,{[Measures].[Incidence TY]
}) ON COLUMNS,
NON EMPTY ANCESTORS([MissedJobs].CurrentMember,1) ON ROWS
FROM jdx_cube
WHERE [Date].[Full Date].&[2007-07-01T00:00:00]:[Date].[Full Date].&[2007-07-30T00:00:00]

The critical dimension here is the Job dimension. It has 2 attributes where the "Advertiser" atrib is the parent of the "Job" atrb. In my Set statement in the WITH section I work out which Jobs I dont have and then I want the query to return the Advertisers that 'parent' the jobs I dont have.

Can you offer anymore help please?

Thanks

Mgale1

|||

Try:

...

NON EMPTY GENERATE([MissedJobs], ANCESTORS([MissedJobs].CurrentMember, 1)) ON ROWS

...

|||

Hello Matt,

Thanks for the reply and I am sorry for not getting back to this thread earlier (been ill).

Unfortunately this is not quite what I want and I should have been clearer in my initial question.
I want to work out the missed jobs set but then display on the axis the name of the 'parent' advertiser but the value in the cellset should be the value of the missed jobs and NOT the value for the parent (which is what this GENERATE solution gives me).

What I mean is, if Advertiser1 has two jobs (Job11 and Job12) with values of 11 and 12 respectively and my Missed Jobs set calculates that only Job 11 applies, then I want to see "Advertiser1, 11 "on the axis.
The GENERATE approach displays "Advertiser1, 23" which is not what I am after.

Do you have any other ideas please?

With continuing thanks
Mgale1

Parents of a SET of members

Hello,

I have a set of members, generated with a SET clause at the top of my MDX query.
Once I have this set, what function can I use on an axis to determine the parents (i.e the level above) of these members? Those parents will be a set too as the original set will have many different parents.

Or am I going about this the wrong way?

Thanks in advance
Mgale1

Try something like:

SELECT Generate({...} as Set1, ANCESTORS(Set1.CurrentMember, 1)) FROM MyCube

|||

Matt,

Thanks for the reply - unfortunately it gives me an error of "The CURRENT function cannot be invoked in this context because the 'MissedJobs' set is not in the scope."

My full query is :

WITH
MEMBER [Measures].[Incidence TY] AS SUM({[Media].[Media].CURRENTMEMBER},([Timeslice].[Timeslice].&[1], [Measures].[Incidence])),FORMAT_STRING = "#,##0.00",NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
MEMBER [Measures].[MyPublicationValue] AS SUM({[Media].[Media].&[7004]},[Measures].[Incidence TY]) ,NON_EMPTY_BEHAVIOR=[Measures].[Incidence]
SET [MissedJobs] AS Filter(Existing([Job].[Job].CHILDREN),NOT [Measures].[MyPublicationValue])

--Produce Report
SELECT CROSSJOIN
(VISUALTOTALS({[Media].[Media].[All], [Media].[Media].&[7002],[Media].[Media].&[7004]})
,{[Measures].[Incidence TY]
}) ON COLUMNS,
NON EMPTY ANCESTORS([MissedJobs].CurrentMember,1) ON ROWS
FROM jdx_cube
WHERE [Date].[Full Date].&[2007-07-01T00:00:00]:[Date].[Full Date].&[2007-07-30T00:00:00]

The critical dimension here is the Job dimension. It has 2 attributes where the "Advertiser" atrib is the parent of the "Job" atrb. In my Set statement in the WITH section I work out which Jobs I dont have and then I want the query to return the Advertisers that 'parent' the jobs I dont have.

Can you offer anymore help please?

Thanks

Mgale1

|||

Try:

...

NON EMPTY GENERATE([MissedJobs], ANCESTORS([MissedJobs].CurrentMember, 1)) ON ROWS

...

|||

Hello Matt,

Thanks for the reply and I am sorry for not getting back to this thread earlier (been ill).

Unfortunately this is not quite what I want and I should have been clearer in my initial question.
I want to work out the missed jobs set but then display on the axis the name of the 'parent' advertiser but the value in the cellset should be the value of the missed jobs and NOT the value for the parent (which is what this GENERATE solution gives me).

What I mean is, if Advertiser1 has two jobs (Job11 and Job12) with values of 11 and 12 respectively and my Missed Jobs set calculates that only Job 11 applies, then I want to see "Advertiser1, 11 "on the axis.
The GENERATE approach displays "Advertiser1, 23" which is not what I am after.

Do you have any other ideas please?

With continuing thanks
Mgale1

Parse IP address

I am fairly new to SQL and am trying to write a function to parse the ip address into 4 sections. I have been searching through the forums to see if anyone has a posted example of parsing an ip address but could not find one.

I am wondering what would be the best method of doing this, or if anyone has an example.

Thank youlook at SUBSTRING and CHARINDEX in books online and think about how you could nest them.|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:

,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Thanks|||Holy non-standard usage Batman! :shocked:

Just goes to show what a motivated user will do (and drive the developers crazy in the process).

Kudos to you for the unorthodox approach. Hope it works.

Regards,

hmscott|||I found a better answer, builtin function PARSENAME

takes the 4 sections between the . and seperates them. only 1 line of code for each section:


,PARSENAME(ip, 4) AS 'Sec1'
,PARSENAME(ip, 3) AS 'Sec2'
,PARSENAME(ip, 2) AS 'Sec3'
,PARSENAME(ip, 1) AS 'Sec4'

Lol - that is brilliant.
There have been a few discussions on how to store IP addresses (char, 4 tinyints, integer etc). This is a new way on me to split the human-friendly form though!|||yup it works great! Def the easiest way to do it :)|||if you have a string with more parts, try this one:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Parameters!FiledName in Custom Code

Hi All,
How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
within in Custom Code.
Public Shared Function GetParameterText() As String
Return Parameters!vcReportVersion.Value
End Function
This function gives the following error message :
"There is an error on line 37 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."
What is the Object name that contain Parameter collection?
Thanks,
SamYou'll need to pass it as a parameter to the custom code.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:uCAAkJBZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
> within in Custom Code.
> Public Shared Function GetParameterText() As String
> Return Parameters!vcReportVersion.Value
> End Function
> This function gives the following error message :
> "There is an error on line 37 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
> What is the Object name that contain Parameter collection?
> Thanks,
> Sam
>

Parameters in Function in OLE DB Source

Hi there!

I need to use some parameters in an OLE DB Source like that:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= dbo.fkt_get_guelt_von (?)
and dbo.fkt_get_guelt_bis (posh_valid_to) >= dbo.fkt_get_guelt_bis (?)

The function returns a valid datetime. These parameters normaly works fine, but not with use as funtion-paramerters. I get feeble error messages (by trying to map the parameters) like that:

"Falsche Syntax in der N?he von ')'. (Microsoft SQL Native Client)" - what means "Wrong syntax near ')'" and says nothing. If i replace the parameters with the mapped value (i.E. '200601') it works fine.

What is the reason for that? It there any solution or a workaround?

Thanks, Torsten

The OLE DB source may be having problems detecting the parameter in your query. I have the impression that it just understands simple queries. The work around is to place the query in a variable, Set the 'EvaluateAsExpression' property to TRUE, and then use an expre ssion to make the query dynamic.

Then in the OLE DB source, choose the option that says your query is in a variable and select the variable from the dropdown list. This is a very common practice to pass the query into source components

Here you can find more posts on how to do that:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=query+variable+expression&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||

Thanks!

I've found another way: I execute these functions in an "Execute SQL-Task" an put the results into a variable, what i use in the OLE DB Source. Works fine:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= ?
and dbo.fkt_get_guelt_bis (posh_valid_to) >= ?

Parameters collection

I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.

I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.

How do I access the Parameters collection within my custom code?

Ideas anyone?|||Try...
report.Parameters!parameter_name.Value
Thanks Tomson McCabe :)

Parameters collection

I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.

I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.

How do I access the Parameters collection within my custom code?

Ideas anyone?|||Try...
report.Parameters!parameter_name.Value
Thanks Tomson McCabe :)

Parameters collection

I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?In your custom code, use:
Report.Parameters!parameter1.Value
"Paul H" wrote:
> I have a function defined in Report->Report Properties-> Code Tab which
> I want to access the parameters collection to look for a Debug flag.
> I get the following error:
> c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
> [BC30469] Reference to a non-shared member requires an object
> reference.
> How do I access the Parameters collection within my custom code?
>