Parameter Information cannot be derived from SQL Statements with sub-select queries

I have the following query that finds the most Serial number of the
most current part manufactured at point 105 (near the end of the
process), then uses that serial number to find out when it started
production at point 39 (the front of the process). Then, it gives a
listing, summarized by part type (CCode), of everything in process from
the time that part entered the line, until it passes point 105. This
gives an accurate snapshot of everthing that is on the production line.
However, I need to modify this query so that the user can enter a start
date and time, and the query will find a part near that time (either
the next part, or the part just before), and produce the same results.
Here is my original query (It works with no issue):
SELECT CCode, COUNT(CCode) AS CCount
FROM [Broadcast] A
WHERE (ReportingPoint = '39') AND
(ProcessDate >= (SELECT W.ProcessDate
FROM [Broadcast] AS W JOIN
(SELECT TOP 1 ProcessDate, SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105'
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
WHERE W.ReportingPoint = '39'))
GROUP BY CCode
ORDER BY CCode
I changed the subquery to get user entry as follows:
(SELECT SerialNumber
FROM [Broadcast]
WHERE ReportingPoint = '105' AND
ProcessDate >= @.GetDateFromUser
ORDER BY ProcessDate DESC) AS X ON
W.SerialNumber = X.SerialNumber AND
W.ProcessDate < X.ProcessDate
But I get the following error:
Parameter Information cannot be derived from SQL Statements with
sub-select queries. Set parameter information before preparing
command.
How can I get this infomation from the user before the sub-select query?Bump
Timothy.Rybak@.gmail.com wrote:
> I have the following query that finds the most Serial number of the
> most current part manufactured at point 105 (near the end of the
> process), then uses that serial number to find out when it started
> production at point 39 (the front of the process). Then, it gives a
> listing, summarized by part type (CCode), of everything in process from
> the time that part entered the line, until it passes point 105. This
> gives an accurate snapshot of everthing that is on the production line.
> However, I need to modify this query so that the user can enter a start
> date and time, and the query will find a part near that time (either
> the next part, or the part just before), and produce the same results.
> Here is my original query (It works with no issue):
> SELECT CCode, COUNT(CCode) AS CCount
> FROM [Broadcast] A
> WHERE (ReportingPoint = '39') AND
> (ProcessDate >=> (SELECT W.ProcessDate
> FROM [Broadcast] AS W JOIN
> (SELECT TOP 1 ProcessDate, SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105'
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> WHERE W.ReportingPoint = '39'))
> GROUP BY CCode
> ORDER BY CCode
> I changed the subquery to get user entry as follows:
> (SELECT SerialNumber
> FROM [Broadcast]
> WHERE ReportingPoint = '105' AND
> ProcessDate >= @.GetDateFromUser
> ORDER BY ProcessDate DESC) AS X ON
> W.SerialNumber = X.SerialNumber AND
> W.ProcessDate < X.ProcessDate
> But I get the following error:
> Parameter Information cannot be derived from SQL Statements with
> sub-select queries. Set parameter information before preparing
> command.
> How can I get this infomation from the user before the sub-select query?

Parameter All Level

Hello,

I'm using SQL Server Reporting Services with an Analysis Services Cube. My current report has a query based parameter. The query returns country codes and the ALL-Level of my country dimension.

Is there a way to remove the ALL-Level of the result set of the query? How can I realize this?

Thanks in advance.

Christian

Hi,

I suggest to change the query. I think, your generated query looks like:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Country].[Country].ALLMEMBERS ON ROWS FROM [MyCube]

Change it to:

WITH MEMBER [Measures].[ParameterCaption] AS '[Country].[Country].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Country].[Country].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Country].[Country].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , Descendants([Country].[Country], [Country].[yourLevel1name]) ON ROWS FROM [MyCube]

For further help, look at the Descendants Function in MDX.

Hans

Parallelperiod problem

Hi,

I tried to get last 52 weeks from the current period for LY sales.

We have fiscal date hierarchy (year - quarter - month -week) .

In scripts,

Paralleldate ( [date].[hierarchy].[year number], 1 , [date].[hierarchy].currentmember )

this works fine.

but

Scope([Date].[FISCAL YEAR NUMBER].members, [Date].[FISCAL MONTH NAME].members ,[Date].[FISCAL WEEK HISTORICAL NUMBER].members, [Date].[DATE KEY].members);

-- PRIOR YEAR CALCULATIONS

([Time Calculations].[Prior Year]=

(ParallelPeriod([Date].[Hierarchy].[Date].[Hierarchy].[FISCAL WEEK HISTORICAL NUMBER],52,

[Date].[Hierarchy].CurrentMember)

,[Time Calculations].&[ Current Period])

);

End Scope;

) ,

then it doesn't work. I don't know why. ( I can deploy the cube but when I browse the cube, it gives an error without any detailed info. )

FYI, we have fiscal week in historical number ( 52 weeks in fiscal yr) in date table.

then it doesn't work. I don't know why.

Please give me any comments.

A couple of clarifications:

- What is the purpose of this scope statement:

Scope([Date].[FISCAL YEAR NUMBER].members, [Date].[FISCAL MONTH NAME].members ,[Date].[FISCAL WEEK HISTORICAL NUMBER].members, [Date].[DATE KEY].members);

- Why is this assignment enclosed in parentheses:

([Time Calculations].[Prior Year]=

(ParallelPeriod([Date].[Hierarchy].[Date].[Hierarchy].[FISCAL WEEK HISTORICAL NUMBER],52,

[Date].[Hierarchy].CurrentMember)

,[Time Calculations].&[ Current Period])

);

|||

Sorry for the late response.

Now, we've just found some problems with perivios one, Due to the Hierarhcy attribute ( fiscal week key) we can't get last 52 weeks in time calculation called shell dimension.

But,

If I use time intelligence calculation wizard and change some for the prior year calcuation then the formula works fine only in week level .(please refer script as below)

I can't see values in year , quarter, and month levels ( these show 0 value), and in day level I can see the values but values are wrong.

To solve this problem and to view prior year value in all levels( y- q- m -w- day) , how can I approach ?

It seems this formula only works for week level values.

Thank you in advance and please give me any comments.

--Scripts for prior year--

(

[Date].[Fiscal Hierarchy Date Calculations].[Prior Year],

[Date].[FISCAL WEEK HISTORICAL NUMBER].[FISCAL WEEK HISTORICAL NUMBER].Members,

[Date].[Tbl DIM Date].Members

) =

Aggregate(

{ [Date].[Fiscal Hierarchy Date Calculations].DefaultMember } *

{

ParallelPeriod(

[Date].[Fiscal Hierarchy].[FISCAL WEEK],

52,

[Date].[Fiscal Hierarchy].CurrentMember

) : [Date].[Fiscal Hierarchy].CurrentMember

})

- Aggregate(

{ [Date].[Fiscal Hierarchy Date Calculations].DefaultMember } *

{

ParallelPeriod(

[Date].[Fiscal Hierarchy].[FISCAL WEEK],

51,

[Date].[Fiscal Hierarchy].CurrentMember

) : [Date].[Fiscal Hierarchy].CurrentMember

}

);

sql

ParallelPeriod over a set?

Classic multi-select problem. I am trying to display the delta between visits to our clinic for the current timeframe versus the same timeframe last year. Pretty straightforward application of ParallelPeriod.

The following query works just fine:
WITH
MEMBER [Measures].[Test] AS
'[Measures].[Visits] - (ParallelPeriod([Time].[Visit Date].[Year], 1), [Measures].[Visits])'

select
{[Test], [Visits]} on columns
from [Clinic]

where ([February 2006])

However, change the where clause to include a set rather than a member...
WITH
MEMBER [Measures].[Test] AS
'[Measures].[Visits] - (ParallelPeriod([Time].[Visit Date].[Year], 1), [Measures].[Visits])'

select
{[Test], [Visits]} on columns
from [Clinic]

where ({[February 2006], [March 2006]})

...and it breaks with the following error: #Error The MDX function PARALLELPERIOD failed because the coordinate for the 'Quarter' attribute contains a set.

Thanks to Moshe's very useful article on multi-select (http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx), I understand what is going on. Since I am selecting a set rather than a single member from the visit date hierarchy, parallelperiod chokes.

WHERE I NEED HELP:
What I would like to be able to do is apply parallelperiod to each member of the set and aggregate the result. As I understand it, that would fix my problem and, I believe, give me the correct result. However, I'm not yet good enough at MDX to figure out how to do this. Help, please?

Here is an example using Adventure Works and the "Existing" operator to sum the total for whatever months are contained in your WHERE clause set:

Original Query

WITH

MEMBER [Measures].[Test] AS

[Measures].[Order Count] - (ParallelPeriod([Date].[Calendar].[Calendar Year], 1), [Measures].[Order Count])

SELECT

{[Measures].[Test], [Measures].[Order Count]} on columns

FROM

[Adventure Works]

WHERE

([Date].[Calendar].[Month].&[2004]&[1])

Revised Query using Existing

WITH

MEMBER [Measures].[Test] AS

SUM(Existing [Date].[Calendar].[Month].Members,

([Measures].[Order Count] - (ParallelPeriod([Date].[Calendar].[Calendar Year], 1), [Measures].[Order Count])))

SELECT

{[Measures].[Test], [Measures].[Order Count]} on columns

FROM

[Adventure Works]

WHERE

({[Date].[Calendar].[Month].&[2004]&[1],

[Date].[Calendar].[Month].&[2004]&[2]})

HTH,

Steve

'PAGEIOLATCH_SH

In current activity i have a wait type of 'PAGEIOLATCH_SH'
BOL and other sources make no referance to this.
Can any body help?Hi
Google does though!!
Try:
http://www.sqldev.net/misc/waittypes.htm
John
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:1ee9301c4577b$3f2d8c00$a301280a@.phx
.gbl...
> In current activity i have a wait type of 'PAGEIOLATCH_SH'
> BOL and other sources make no referance to this.
> Can any body help?|||I have heard that this might also indicate contention due to a large number
of indexes on the same table...
(although I personally have not encountered it)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:1ee9301c4577b$3f2d8c00$a301280a@.phx
.gbl...
> In current activity i have a wait type of 'PAGEIOLATCH_SH'
> BOL and other sources make no referance to this.
> Can any body help?|||I've encountered this.
I may be talking out of my 4th-Point-Of-Contact, but it can also be attribut
ed to multi-processor machines and faulty optimization
(parallelism). I've worked around it by applying the optimizer option MAXDOP
.
http://www.sql-server-performance.com/case_studies.asp
http://support.microsoft.com/defaul...kb;EN-US;243589
http://www.databasejournal.com/feat...cle.php/1438891
ChrisG

'PAGEIOLATCH_SH

In current activity i have a wait type of 'PAGEIOLATCH_SH'
BOL and other sources make no referance to this.
Can any body help?Hi
Google does though!!
Try:
http://www.sqldev.net/misc/waittypes.htm
John
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:1ee9301c4577b$3f2d8c00$a301280a@.phx.gbl...
> In current activity i have a wait type of 'PAGEIOLATCH_SH'
> BOL and other sources make no referance to this.
> Can any body help?|||I have heard that this might also indicate contention due to a large number
of indexes on the same table...
(although I personally have not encountered it)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:1ee9301c4577b$3f2d8c00$a301280a@.phx.gbl...
> In current activity i have a wait type of 'PAGEIOLATCH_SH'
> BOL and other sources make no referance to this.
> Can any body help?|||I've encountered this.
I may be talking out of my 4th-Point-Of-Contact, but it can also be attributed to multi-processor machines and faulty optimization
(parallelism). I've worked around it by applying the optimizer option MAXDOP.
http://www.sql-server-performance.com/case_studies.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589
http://www.databasejournal.com/features/mssql/article.php/1438891
ChrisG

'PAGEIOLATCH_SH

In current activity i have a wait type of 'PAGEIOLATCH_SH'
BOL and other sources make no referance to this.
Can any body help?
Hi
Google does though!!
Try:
http://www.sqldev.net/misc/waittypes.htm
John
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:1ee9301c4577b$3f2d8c00$a301280a@.phx.gbl...
> In current activity i have a wait type of 'PAGEIOLATCH_SH'
> BOL and other sources make no referance to this.
> Can any body help?
|||I have heard that this might also indicate contention due to a large number
of indexes on the same table...
(although I personally have not encountered it)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:1ee9301c4577b$3f2d8c00$a301280a@.phx.gbl...
> In current activity i have a wait type of 'PAGEIOLATCH_SH'
> BOL and other sources make no referance to this.
> Can any body help?
|||I've encountered this.
I may be talking out of my 4th-Point-Of-Contact, but it can also be attributed to multi-processor machines and faulty optimization
(parallelism). I've worked around it by applying the optimizer option MAXDOP.
http://www.sql-server-performance.com/case_studies.asp
http://support.microsoft.com/default...b;EN-US;243589
http://www.databasejournal.com/featu...le.php/1438891
ChrisG