parameter problem with report

I am trying to build a report based on the following query .
I want the user to enter the date value for the R.ANNLAPPT_DATE but when I try to run the query I get the following msg
ORA-00904: invalid column name

The data source for this report is an oracle db

SELECT C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME AS CLINICIAN, R.DEPT_CODE,
D.DEPT_TITLE, R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODE, MAX(A.APPT_DATE) AS EXPR1,
R.ANNLAPPT_DATE AS EXPR2
FROM ORACARE.K_REFLREG R, ORACARE.K_CPIREG C, ORACARE.K_DEPTLIST D, ORACARE.K_PROFREG P, ORACARE.K_REFPLIST RP,
ORACARE.K_APPTREG A
WHERE R.PT_CODE = C.PT_CODE AND R.DEPT_CODE = D.DEPT_CODE AND R.HOSP_CODE = D.HOSP_CODE AND R.CONS_MD_CODE = P.MPROF_CODE AND
R.REFP_CODE = RP.REFP_CODE AND R.EVENT_NO = A.EVENT_NO (+) AND

(R.ANNLAPPT_DATE < "@.ANNALAPPT_DATE")

GROUP BY C.PREFERRED_NAME, C.SURNAME, R.ANNLAPPT_DATE, R.CLNP_CODE, R.CONS_MD_CODE, P.SURNAME, R.DEPT_CODE, D.DEPT_TITLE,
R.PT_CODE, R.REFLREAS_DESC, R.HOSP_CODE, R.REFP_CODE, RP.REFP_TITLE, A.APAT_CODEI don't think you want to enclose the parameter name in quotes, and if you are using Oracle, you may need to use a ? in place of @.ANNALAPPT_DATE. I think that depends on which driver you are using, though.|||Thanks for the reply,

sql server automaically inserted the double quotes I will try ?.
|||Thanks for the help the ? did the trick.|||

Can you mark the helpful response as an answer?

Parameter problem

hi,
i m using following query in my dataset query string

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where @.Param

i m sending boolean exp as value for @.param as string like " YEAR(ORderDate) = YEAR(getDate()) "

but is showing following error

An expression of non-boolean type specified in a context where a condition is expected,near '@.param'. (Microsoft Sql Server, Error:4145)

please help me,

thanks
You cannot do that unless you use dynamic Sql in a procedure, passing the string as the parameter which should be avoided.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||thanks for reply

but can u explain further because i m new to it
|||

You dont need to send the parameter string because whatever you trying to do can be achieved in plain SQL in dataset query itself:

SELECT OrderNumber, OrderDate, Name AS Client, orderstatus
FROM Orders
Where YEAR(OrderDate) = YEAR(GETDATE())

Shyam

|||Wee that depends. if you have the same query everything with carying parameters you can use something like Where YEAR(@.SomeVar) = YEAR(Getdate()), the parameter will automatically mapped to the report if you write such a query in the pane of the query editor in the report designer. but if you want to pass in a whole condition rather than just a parameter you would probably need to pass it to a stored procedure to make it execute in a dynamic SQL statement.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Parameter passing from report to SQL

Hi, I have the following SQL code in my DataSet:
(SQL Server 2005)
DECLARE @.sel1 AS varchar(20)
DECLARE @.sel2 AS varchar(20)
DECLARE @.sel3 AS varchar(20)
DECLARE @.sel4 AS varchar(20)
DECLARE @.test AS integer
set @.test = @.TimeDiff; <-- this I want to insert via URL in report
set @.sel1=convert
(char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
set @.sel2=Replace(@.sel1,'-','');
set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
set @.sel4=Replace(@.sel3,'-','');
select @.sel2 AS StartTime, @.sel4 AS EndTime;
I have declared a report parameter (TimeDiff, without @.) in Report
Parameters. It is integer and hidden and has a default value 2 (non queried).
In dataset parameters I do not have any configurations (should I have).
I tried to opass the parameter to my sql code but it is not succeeded.
I got the followng error message:
"Must declare the scalar cariable "@.TimeDiff"
The final meaning is to insert this as parameter via URL and use it in the
dataset sql code, but I can not get it into my SQL .
When running the dataset itself it asks the parameter @.TimeDiff and works OK.
What is wrong in my parameter handling ?Push the ... button (the point button) right beside your dataset
(within the data display), a couple of tabs will popup and goto the
parameters tab.
Within this tab you're able to bind the sql declared parameter to your
reporting parameter.
You'll notice the way it works via your other parameters which are
bound to the sql parameters|||I'd suggest having a stored procedure for this code but as long as you don't
use a temp table (if you use a temp table you must put this in a stored
procedure) then this will work.
I would have expected RS to automatically create a TimeDiff report parameter
to match the @.TimeDiff since @.TimeDiff is not declared. Perhaps it is the
use of the set statement. Try
select @.test = @.TimeDiff
If you click on the ..., parameters tab and there is no @.TimeDiff in the
name column then try adding this by hand and then select your report
parameter that it is mapping to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jarmopy" <jarmopy@.discussions.microsoft.com> wrote in message
news:9123A5B9-0DA9-4639-9884-D9D7A30CAA2C@.microsoft.com...
> Hi, I have the following SQL code in my DataSet:
> (SQL Server 2005)
> DECLARE @.sel1 AS varchar(20)
> DECLARE @.sel2 AS varchar(20)
> DECLARE @.sel3 AS varchar(20)
> DECLARE @.sel4 AS varchar(20)
> DECLARE @.test AS integer
> set @.test = @.TimeDiff; <-- this I want to insert via URL in report
>
> set @.sel1=convert
> (char(10),DATEADD(dd,-convert(integer,@.test),getdate()),121);
> set @.sel2=Replace(@.sel1,'-','');
> set @.sel3=convert (char(10),DATEADD(dd,0,getdate()),121);
> set @.sel4=Replace(@.sel3,'-','');
> select @.sel2 AS StartTime, @.sel4 AS EndTime;
> I have declared a report parameter (TimeDiff, without @.) in Report
> Parameters. It is integer and hidden and has a default value 2 (non
> queried).
> In dataset parameters I do not have any configurations (should I have).
> I tried to opass the parameter to my sql code but it is not succeeded.
> I got the followng error message:
> "Must declare the scalar cariable "@.TimeDiff"
> The final meaning is to insert this as parameter via URL and use it in the
> dataset sql code, but I can not get it into my SQL .
> When running the dataset itself it asks the parameter @.TimeDiff and works
> OK.
> What is wrong in my parameter handling ?
>
>
>|||Hi, thanks for the answers.
I tried to put the following parameter setting in the dataset (... button)
@.TimeDiff = Parameter!TimeDiff.Value
but now I get the following error message:
The report parameter 'LastDate' has a DefaultValue or a ValidValue that
depends on the report parameter "TimeDiff". Forward dependencies are not
allowed"
The LastDate report parameter is coming from the dataset from field
StartTime and that is calculated from TimeDiff.
LastTime parameter is Hidden and available value is from query and the
default value is from query.
Still something wrong.|||Hi,
I solved the last problem just by changing the order of the parameters in
report side.
Thanks for nicismyname and Bruce L-C|||Hi, you should be aware of the order of your parameters. If lastdate
parameter it's dataset refers to the timediff parameter you should
first prompt the timediff parameter. The prompting order is determined
by the 'report>parameters' menu. You'll notice two arrows at the
dialog popping up after selecting 'report>parameters', use the up and
down arrow to determine the prompting order.

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 cannot set the command text for dataset

Hello

I get the following error in a very quick test system i created:

An error occurred during local report processing.

An error has occurred during report processing.

Cannot set the command text for data set 'test'.

Error during processing of the CommandText expression of dataset 'test'.

My sql is:

= "select T.testid, T.test from test T " & Iif(Parameters!test1.Value = 1, "", "Where T.testid = " & Parameters!test1.Value)

There is nothing obviously wrong in the code as far as i can see

The parameter 'test1' is of type 'string'

The database 'test' has 2 columns of type 'smallint' and 'Name:nvarchar(50)'

I am at a loss, as this query is really simple, and is similar to the example query set up my microsoft which works fine

Thanks

t.test from test?

You tried running this in sql?

|||

Yes, it works fine in sql if you strip out the bits that obviously wont work in query analyser

this works:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = 1" )

this does not:

= "select T.testid, T.test from test T " & Iif(1 = 1, "", "Where T.testid = " & Parameters!test1.Value)

why?

there is a parameter called test1

|||

Found the answer!

You have to have another table with a different name into which the parameter links (in the section 'available values' / 'from query')

I created another table called test2, et voila! it works

parameter and Oracle DB

I have a dataset that queries an Oracle DB. I am getting an error when I try
to set a parameter in the query. Basically I have the following:
WHERE x.system_num = s.system_num
and na.service_id = x.service_id
and na.location_num = sl.location_num
and sd.device_mux_id = dm2.device_mux_id
and (x.auth_date >= @.param_start)
ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
The error I get is the following:
ORA-00936: missing expression
(System.Data.OracleClient)
When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
The missing expression sounds like I have a comma at the end of the SELECT or
FROM line; but when the param line is removed the error goes away. So I
don't think that is it.
Could it be in how the parameter is set up? Just throwing out ideas here.
Thank you for any help offered.
Rob CuscadenFYI
After several HOURS of silly errors someone found the solution to this
problem. I wanted to post it here so that others can gain from my hours of
frustration. I will not say that it will work in ALL cases; but that it
works in mine and it is something for others to try if they are stuck.
Problem, using Reporting Services 2005 and accessing an Oracle database with
parameters.
In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
used in the query and then the parameter is defined in the "Report
Parameters" option on the "Reports" top menu option.
So, for example
Select *
From sometable
Where sometable.datefield >= @.param_startdate
Then you go modify the Report Parameters option for the project.
If you try this with an ORACLE DB you will get several errors (ORA-00936:
missing expression is one of them).
SOLUTION: To solve this for an ORACLE DB, use the following:
SELECT *
FROM sometable
WHERE sometable.datefield = :param_startdate
-----
Now I am sure that this little ( : ) is documented somewhere in a document
out there somewhere. BUT for crying out loud... such a simple solution that
took 6 hours to find.
Please feel my frustration and I hope I prevent this from happening elsewhere.
Rob Cuscaden
"Rob" wrote:
> I have a dataset that queries an Oracle DB. I am getting an error when I try
> to set a parameter in the query. Basically I have the following:
> WHERE x.system_num = s.system_num
> and na.service_id = x.service_id
> and na.location_num = sl.location_num
> and sd.device_mux_id = dm2.device_mux_id
> and (x.auth_date >= @.param_start)
> ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> The error I get is the following:
> ORA-00936: missing expression
> (System.Data.OracleClient)
> When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> The missing expression sounds like I have a comma at the end of the SELECT or
> FROM line; but when the param line is removed the error goes away. So I
> don't think that is it.
> Could it be in how the parameter is set up? Just throwing out ideas here.
> Thank you for any help offered.
> Rob Cuscaden
>|||Rob,
I have been having the same problem and my queries look up an Oracle DB
also. I ended up getting so frustrated at mine, that our Oracle DB
Administrator told me to use a program called Toad for Oracle. Used that
which the SQL module in that program works very similar as the Microsoft
Query tool (Data, Import External Data,
New Database Query).
Anyway, I looked at the SQL Statement after building the Query (joining
tables, adding fields, adding criteria filters, etc). And I saw the same
Where table.field LIKE :whatever
Well, I tried using that in the Microsoft Query (SQL Statement) and I end up
getting some other error which I can't duplicate right now. I tried it again
and now it just completely bails me out of excel with the ever famous Debug,
Send Error Report, etc.
Anyway, wasn't sure if you ran into anything more you could share that might
help in my situation.
I also posted this issue somewhere else on here explaining more in detail
about how originally I created the query with one line of criteria where 3 of
my criteria having 5 "or" conditions. When I refresh or return data to Excel
it works fine.
Problem starts occurring if I ever have to go back in to the query to edit
and then try and refresh or return the data again. That's where I get the
ORA-00936 Missing expression error message.
I'm so stuck on this and really need to resolve somehow, because the data
that's returned from this query is used for charts, graphs, pivot tables,
that are all part of a giant VBA I wrote to automate dashboards for a bunch
of engineers.
Anwyay, any suggestions you or anyone might have on this would be greatly
appreciated.
"Rob" wrote:
> FYI
> After several HOURS of silly errors someone found the solution to this
> problem. I wanted to post it here so that others can gain from my hours of
> frustration. I will not say that it will work in ALL cases; but that it
> works in mine and it is something for others to try if they are stuck.
> Problem, using Reporting Services 2005 and accessing an Oracle database with
> parameters.
> In Reporting Services queries of a SQL DB, I have seen the @.param_startdate
> used in the query and then the parameter is defined in the "Report
> Parameters" option on the "Reports" top menu option.
> So, for example
> Select *
> From sometable
> Where sometable.datefield >= @.param_startdate
> Then you go modify the Report Parameters option for the project.
> If you try this with an ORACLE DB you will get several errors (ORA-00936:
> missing expression is one of them).
> SOLUTION: To solve this for an ORACLE DB, use the following:
> SELECT *
> FROM sometable
> WHERE sometable.datefield = :param_startdate
> -----
> Now I am sure that this little ( : ) is documented somewhere in a document
> out there somewhere. BUT for crying out loud... such a simple solution that
> took 6 hours to find.
> Please feel my frustration and I hope I prevent this from happening elsewhere.
> Rob Cuscaden
> "Rob" wrote:
> > I have a dataset that queries an Oracle DB. I am getting an error when I try
> > to set a parameter in the query. Basically I have the following:
> >
> > WHERE x.system_num = s.system_num
> > and na.service_id = x.service_id
> > and na.location_num = sl.location_num
> > and sd.device_mux_id = dm2.device_mux_id
> > and (x.auth_date >= @.param_start)
> >
> > ORDER BY m.mso_name, sl.system_name, system_type, d.device_address
> >
> > The error I get is the following:
> >
> > ORA-00936: missing expression
> > (System.Data.OracleClient)
> >
> > When I remove the "and (x.auth_date >= @.param_start)" the error goes away.
> > The missing expression sounds like I have a comma at the end of the SELECT or
> > FROM line; but when the param line is removed the error goes away. So I
> > don't think that is it.
> >
> > Could it be in how the parameter is set up? Just throwing out ideas here.
> >
> > Thank you for any help offered.
> > Rob Cuscaden
> >sql

Paramater in Case statement

When I enter the following query in the MSSQL 2005 Server Management Studio,
I get results, but when i enter the same query (Minus the top 2 lines) in RS,
i get an a error:
Title: Microsoft Visual Database Tools
Error: The Parameter is incorrect.
Any ideas what i'm doing wrong? This query is just a simplified example
using the northwind db.
DECLARE @.Region AS NVARCHAR(15)
SELECT @.Region = '1'
SELECT *
FROM Customers
WHERE Region = CASE
WHEN @.Region = '1'
THEN 'SP'
END
--
Lucas DargisHi Lucas,
> DECLARE @.Region AS NVARCHAR(15)
> SELECT @.Region = '1'
You are using a variable (@.Region) in your query and if you leave off the
top 2 lines, you are not declaring the variable and setting its value. The
statement needs this.
HTH!
Kind regards - Fred|||thanks Fred.
I left off the top two lines because in RS you specify the parameters in the
'Report > Report Parameters' window.
i found that this is just a bug in the design view of RS. when i Previewed
the report, it worked just fine.
thanks
--
Lucas Dargis
"Fred Block" wrote:
> Hi Lucas,
> > DECLARE @.Region AS NVARCHAR(15)
> > SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>
>|||This is not true. The way it works, if you have not declared it then RS
knows that it is a query parameter and automatically creates a report
parameter for it. I used the below code against adventureworks and it works:
SELECT *
FROM sales.Customer
WHERE TerritoryID =CASE
WHEN @.Region = '2'
THEN 2
END
Use the generic query designer (the button to switch to generic mode is one
of the buttons to the right of the ...).
Execute the query, you should be prompted for a value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Lucas,
>> DECLARE @.Region AS NVARCHAR(15)
>> SELECT @.Region = '1'
> You are using a variable (@.Region) in your query and if you leave off the
> top 2 lines, you are not declaring the variable and setting its value. The
> statement needs this.
> HTH!
> Kind regards - Fred
>|||Bruce,
that is what i said... just not as well.
i just gave him the 'check' since i couldn't give it to myself.
--
Lucas Dargis
"Bruce L-C [MVP]" wrote:
> This is not true. The way it works, if you have not declared it then RS
> knows that it is a query parameter and automatically creates a report
> parameter for it. I used the below code against adventureworks and it works:
> SELECT *
> FROM sales.Customer
> WHERE TerritoryID => CASE
> WHEN @.Region = '2'
> THEN 2
> END
> Use the generic query designer (the button to switch to generic mode is one
> of the buttons to the right of the ...).
> Execute the query, you should be prompted for a value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Fred Block" <fblock@.no_spams.w-systems.com> wrote in message
> news:utQYEFuTHHA.1636@.TK2MSFTNGP02.phx.gbl...
> > Hi Lucas,
> >
> >> DECLARE @.Region AS NVARCHAR(15)
> >> SELECT @.Region = '1'
> >
> > You are using a variable (@.Region) in your query and if you leave off the
> > top 2 lines, you are not declaring the variable and setting its value. The
> > statement needs this.
> >
> > HTH!
> >
> > Kind regards - Fred
> >
>
>

param prob

hi ,
I have created 2 datasets. my first dataset quary parameter is BU. query is like following.

WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtoset(@.BU)

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

FROM Profitability

my second dataset query is like following,

SET [FilteredBUList] AS strtoset(@.BU)

SET [customtimeset] AS.....

SELECT
{[Measures].[Description],[Measures].[Amount]} ON COLUMNS,
intersect( [FilteredBUList],...) on rows

FROM Profitability

i need to map previous query BU parameter to the second dataset parameter. pls tel m the proper way.
cant access report parameter in this way from different dataset.
WITH

MEMBER [Measures].[Amount] AS 'IIF(ISEMPTY([Measures].[Amount Usd]),0,[Measures].[Amount Usd])'
MEMBER [Measures].[Description] AS '[Ledger—AccountCode].CurrentMember.Name'

SET [FilteredBUList] AS strtotuple(@.BU)

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

FROM Profitability

ParallelPeriod: Same Day Last Month in long months

I am using the following MDX query to return same day last month values:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month],1),[Measures].[Active Account Indicator])'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

This works for most days. However, for the last day of the month this cannot work when the current month has more days than the previous month. For example, on March 29, 30 and 31, the query will return NULL for [Same Day Last Month], since February 29, 30, 31 is an invalid date.

I am sure others have run into the same problem. What is the best way to solve this?

I'm grateful for any insights.

Dan

This could perhaps be more efficiently handled in scoped MDX script; but a recursive approach like this should work (except in the first month):

WITH

MEMBER [Measures].[Same Day Last Month] AS

iif(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]) is null,

([Reporting Date].[Year - Quarter - Month - Date].PrevMember,

[Measures].[Same Day Last Month]),

(ParallelPeriod([Reporting Date].[Year - Quarter - Month - Date].[Month]),

[Measures].[Active Account Indicator]))

|||

Thank you, Deepak. I appreciate your response.

I believe I would have to use nested recursive statements for March 31. One month ago would indicate February 31, previous day would be February 30, and so forth until a valid date is reached.

However, this lead me to another solution which worked and follows our business rules: The last day of any month always contains values. Therefore, I was able to solve my problem using the following approach, employing the IsLeaf(), and the ClosingPeriod() function:

WITH

MEMBER [Measures].[Same Day Last Month] AS

'IIf(

--valtest

IsLeaf(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

)

,

--valtrue

(

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

),

[Measures].[Active Account Indicator]

)

,

--valfalse

(

ClosingPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Date],

ParallelPeriod(

[Reporting Date].[Year - Quarter - Month - Date].[Month],

1

[Reporting Date].[Year - Quarter - Month - Date].CurrentMember.Parent

)

),

[Measures].[Active Account Indicator]

)

) --End IIf

'

SELECT

{[Measures].[Active Account Indicator],[Measures].[Same Day Last Month]} ON COLUMNS,

NON EMPTY {[Reporting Date].[Date].Members} ON ROWS

FROM [Financial]

ParallelPeriod MDX function not working

I created a calculated measure called [Previous Year Percent Rejected]. I used the following expression:

(ParallelPeriod([Year],1,[Time].[Time Hierarchy].CurrentMember),[Measures].[Percent Rejected])

The syntax checks out fine. However, when I try to use the calculation, I get an error message -- with a #VALUE! in the column.

The error message reads " The parallel period expression expects a level expression for the argument. A hierarchy expression was used."

I tried many different variations but I'm always receiving the same message. Any suggestions?

David

If you're using AS 2005, the problem could be that there is also an attribute hierarchy associated with [Year]. So, you could try fully specifying the [Year] level, like:

(ParallelPeriod([Time].[Time Hierarchy].[Year],1,[Time].[Time Hierarchy].CurrentMember),[Measures].[Percent Rejected])

|||

Thank you Deepak. Your solution fixed the problem.

David

Parallel Index Operations

Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
Johnny
We have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>
|||There is no errors in the system & application event log around the time of running index rebuild.

Parallel Index Operations

Got the following error messages when rebuilding indexes on SQL Server 2000
+ SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has alread
y service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of
running index rebuild.

Parallel Index Operations

Got the following error messages when rebuilding indexes on SQL Server 2000 + SP3 server.
WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF latch.
Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
Refer to KB-810195, but it should be corrected in SP3. My server has already service pack 3 installed.
JohnnyWe have seen this in relation to IO problems, have you checked the windows
system event log?
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:087B2B6A-32A4-4C14-841D-4FE2993B8E75@.microsoft.com...
> Got the following error messages when rebuilding indexes on SQL Server
2000 + SP3 server.
> WARNING: EC 5c166098, 2 waited 300 sec. on latch 5c1694bc. Not a BUF
latch.
> Waiting for type 0x2, current count 0xa, current owning EC 0x5C16A098.
> Refer to KB-810195, but it should be corrected in SP3. My server has
already service pack 3 installed.
> Johnny
>|||There is no errors in the system & application event log around the time of running index rebuild.

Pagination issue in SQL SERVER reporting services 2005

Issue: Page breaks in the following kind of reports are ignored :
Reports having matrix controls
Sub-reports having conditional visibility
Scenario:
What happens is that if a letter has 3 pages then in report viewer it is
showing all the 3 pages in 1 page itself. But it would actually show 3 pages
in print preview. So this problem is only when the report is viewed on the
screen and not with the print preview. So while printing it takes 3 pages
which is fine. But the display on report viewer control is the issue.
Investigation:
The following are a couple of approaches tried :
1. Use the expression =Int((RowNumber(Nothing)-1)/25)
2. Place your matrix inside a â'listâ' control and add a grouping with
the following expression =Ceiling(RowNumber(Nothing)/20)
3. Installed SQL Server 2005 service pack 2 and checked if this can
solve the problem. But it did not help.
Can someone tell me how to fix this pagination issue ?I see nobody has responded to this, so I'm going to throw in my six-penny
worth - but I'm relatively new to Reporting Services, so please don't take
this as gospel. I have observed similar behaviour myself, and this is using a
list control rather than a matrix. I came to the conclusion that it was just
part of the behaviour of the report viewer. In fact if you are trying to lay
out a report to a specific design the report viewer can be a very frustrating
tool. In my case it didn't matter since we had already taken the decision
that all reports were going to be in PDF format, and this displays everything
just fine. I would recommend that you do the same.
John
"selva" wrote:
> Issue: Page breaks in the following kind of reports are ignored :
> Reports having matrix controls
> Sub-reports having conditional visibility
> Scenario:
> What happens is that if a letter has 3 pages then in report viewer it is
> showing all the 3 pages in 1 page itself. But it would actually show 3 pages
> in print preview. So this problem is only when the report is viewed on the
> screen and not with the print preview. So while printing it takes 3 pages
> which is fine. But the display on report viewer control is the issue.
> Investigation:
> The following are a couple of approaches tried :
> 1. Use the expression =Int((RowNumber(Nothing)-1)/25)
> 2. Place your matrix inside a â'listâ' control and add a grouping with
> the following expression =Ceiling(RowNumber(Nothing)/20)
> 3. Installed SQL Server 2005 service pack 2 and checked if this can
> solve the problem. But it did not help.
>
> Can someone tell me how to fix this pagination issue ?

PAGE_VERIFY setting CHECKSUM - how to detect?

Does anyone know how to detect the CHECKSUM setting of the PAGE_VERIFY database option (2005 only)?

BOL (ALTER DATABASE) includes the following statement:

---

PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }

The current setting of this option can be determined by examining the page_verify_option column in the sys.databases catalog view or the IsTornPageDetectionEnabled property of the DATABASEPROPERTYEX function.

---

However, there is no column named page_verify_option in the view sys.databases, and DATABASEPROPERTYEX('IsTornPageDetectionEnabled') does not discriminate between the settings CHECKSUM and NONE (it returns 0 for both)!

Actually, I was looking in sys.sysdatabases! sys.databases is just fine....

Page Setup disabled in Report Designer

I'm trying to change the snap-to grid functionality in a SRS 2005 Report Design (within VS 2005). I tried the same steps in the following post, without success. Based on the SRS help, it appears you change Grid Settings by accessing the File > Page Setup dialog, but the Page Setup (along with Print) is disabled.

Maybe these are instructions for Report Builder only? If so, how do you adjust the Grid Settings (to turn off the "snap" functionality) for a SRS Report project in Visual Studio?

Thanks,

Michael

Hello Michael,

From the Report menu, select Report Properties, then in the General tab, uncheck the 'Snap to grid' checkbox.

Hope this helps.

Jarret

|||

Jarret,

That was so easy!

I thought I was seeing all the available properties by right-clicking on the report and selecting 'Properties'.

Great help!

Thanks,
Michael