Page Numbers in Excel: null

Showing posts with label null. Show all posts

Wednesday, March 28, 2012

Parameter label property is null when passed to subreports

I have a main report that passes a parameter(pTime = Value: "2004" Label:
"Fiscal 2004") to a sub-report. When I print out Parameters!pTime.Label on
the main report it diplays fine. When I print it out on the subreport it
shows a null value. It seems this parameter label property is not passed
along to the subreport.
Is this bug fixed? If not, does anyone know of a work around. I do not
want to have to look up the label in all my subreports.
FYI: The Parameters!pTime.Value property is passed to the subreport fine.
Thanks,
Dan
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
dan_zaccarine@.hanfordbay.com
http://www.hanfordbay.com
___________________________Hi Dan,
Thanks for your posting!
I am trying to reproduce it on my side, I will keep you updated when I find
there is anything valueable to add.
Thank you for your patience and corporation.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Dan,
I am afraid that it is by design. An available values list contains a set
of value/label pairs. When the report is run, the user sees the label. When
the user selects a label, the corresponding value is used as the parameter
value .
However, when parameter value is transferred to subreport, it is not
neceesary to manully choose lable and the parameter value from parent
report is transferred to subreport automatically.
I am not sure why you would like to see label of a parameter in subreport?
Is it possible for you to find a workaround in your project?
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||> I am not sure why you would like to see label of a parameter in subreport?
I have to display the prompt descriptions(Labels) on the subreports detail
section. I am querying against an AS cube with MDX to populate parameter
lists.
I the use the Value to MDX query cube data in the subreport.
The values are returned in a format similar to this: "[Gender].[Male]"
with a Label of "Male"
I need to display "Male" on the subreport.
> Is it possible for you to find a workaround in your project?
Yes, I have found a method to extract the text I need from the Value using
expressions.
Thanks,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:%23duw3RdAFHA.764@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> I am afraid that it is by design. An available values list contains a set
> of value/label pairs. When the report is run, the user sees the label.
When
> the user selects a label, the corresponding value is used as the parameter
> value .
> However, when parameter value is transferred to subreport, it is not
> neceesary to manully choose lable and the parameter value from parent
> report is transferred to subreport automatically.
> I am not sure why you would like to see label of a parameter in subreport?
> Is it possible for you to find a workaround in your project?
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>

Parameter is null, return all?

Hi, heres the problem, ive got a table, and im calling a stored
procedure on the table. Now lets say a row contains the numbers 1
through 5, and i had a parameter in the sp that would be given one of
those values, and then return the corresponding row, but if they it
were passed a null value, how could i make it return all objects?Right
now i have this but i also want to return level 2, 3, 4, 5, etc:
SecurityID = ISNULL(@.SecurityLevel, '1')You can use a WHERE clause like
WHERE (
@.SecurityLevel = SecurityID
OR
@.SecurityLevel IS NULL
)
This will often perform poorly, because there is no one
query plan that can efficiently serve both cases, and another
thing to try is
WHERE (
SecurityID >= COALESCE(@.SecurityLevel,-2147483648)
AND
SecurityID <= COALESCE(@.SecurityLevel,2147483647)
)
(or with other values if the type of SecurityID is not INT).
Steve Kass
Drew University
nbs.tag@.gmail.com wrote:

>Hi, heres the problem, ive got a table, and im calling a stored
>procedure on the table. Now lets say a row contains the numbers 1
>through 5, and i had a parameter in the sp that would be given one of
>those values, and then return the corresponding row, but if they it
>were passed a null value, how could i make it return all objects?Right
>now i have this but i also want to return level 2, 3, 4, 5, etc:
>SecurityID = ISNULL(@.SecurityLevel, '1')
>
>|||SecurityID = @.SecurityLevel OR @.SecurityLevel IS NULL
Also see
http://sommarskog.se/dyn-search.html
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<nbs.tag@.gmail.com> wrote in message
news:1148911602.878618.143780@.j55g2000cwa.googlegroups.com...
> Hi, heres the problem, ive got a table, and im calling a stored
> procedure on the table. Now lets say a row contains the numbers 1
> through 5, and i had a parameter in the sp that would be given one of
> those values, and then return the corresponding row, but if they it
> were passed a null value, how could i make it return all objects?Right
> now i have this but i also want to return level 2, 3, 4, 5, etc:
> SecurityID = ISNULL(@.SecurityLevel, '1')
>

Monday, March 26, 2012

Parameter is missing a value

I have a report parameter with Allow null value checked (and also Allow blank value). The report works as expected when viewed in Visual Studio, i.e. if I don't supply a value for the parameter, the report still renders. However, when I try to view this report through the web ReportViewer I get the error message "The X parameter is missing a value". It works fine when a value is supplied but I want that parameter to be optional.

Any idea what's going wrong here?

Thanks.

All parameters must have a value. In your case, it sounds like you want the default value to be null (or maybe empty string). Have you specified this as the default value? Can you post a sample RDL?

|||

This is the parameter:

<DataType>String</DataType>

<Prompt>Group1</Prompt>

</ReportParameter>

In VS, Null is selected as the default value.

In the code that sets up the ReportViewer, I've tried both excluding the SetQueryParameter call for that parameter and also passing an empty string and both result in that error message.

|||This all looks ok. Will you post your code that sets the report definition and any other parameters?|||

Microsoft.Samples.ReportingServices.ReportViewer rv = new Microsoft.Samples.ReportingServices.ReportViewer();

rv.ServerUrl = "http://localhost/ReportServer$SQL2005";

rv.ReportPath = "GroupedReport";

rv.Width = Unit.Percentage(100);

rv.Height = Unit.Percentage(100);

rv.Parameters = Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

//several parameters are added via

rv.SetQueryParameter("ParamName", ParamValue);

Then for this particular parameter, I've tried just leaving out the set parameter call and also tried doing this:

rv.SetQueryParameter("Group1", "");

and

rv.SetQueryParameter("Group1", null);

Neither of which worked.

And then of course:

plhReport.Controls.Add(rv);

Thanks for your help.

|||

I didn't realize you were using the viewer sample. Since you are using SQL 2005, have you considered using the ReportViewer control that ships with Visual Studio 2005? It provides a lot more functionality than the sample and offers much better integration with your application.

You can get a lot of good information on the control from www.gotreportviewer.com.

|||

Yeah, I'm in the process of converting the code to that new control and it seems to get rid of this problem.

Thanks.

|||

Please help guys i posted in this forum yesterday and no one has replied. I cant even seem to get my report woking at all?

My form is very simple at the moment and has a drop down list that when a service is selected fires of a postback!

<title>Untitled Page</title>

</head>

<body>

<div>

<cc1:Combobox ID="Combobox1" runat="server" CSSOptionIcon="ico" AutoPostbackEnable="true"

FolderStyle="./combobox/styles/blueGray" Height="500" InnerWidth="500" Width="500">

</cc1:Combobox>

</div>

<div>

<rsweb:ReportViewer ID="SRViewer" runat="server" Visible="false" Height="600px" Width="600px" Font-Names="Verdana" Font-Size="8pt">

</rsweb:ReportViewer>

</div>

</form>

</body>

</html>

when fired the form databinds the report to a dataset / table that is returned based on drop down selection and year. for now i phisivcally put in '2007'

Protected Sub SRViewer_DataBinding(ByVal sender As Object, ByVal e As EventArgs) Handles SRViewer.DataBinding

Dim dt As DataTable = New DataTable

dt = ServiceReport(Combobox1.Text, "2007").Tables(0)

Dim DR As DataColumnCollection = dt.Columns

Dim FD As DataColumn = dt.Columns("No_of_Complaints")

Dim tD As DataColumn = dt.Columns("MonthName")

Dim bindDs_name As String = "dt"

Dim reportName As String = "Service Report"

Dim reportPath As String = "Report1.rdlc"

Dim fullReportPath As String = reportPath + reportName

Dim txtReportTitle As String = "Service Report"

If Session("fullReport") Is Nothing Then

Session.Add("fullReport", reportPath + reportName)

Else

Session.Remove("fullReport")

Session.Add("fullReport", reportPath + reportName)

End If

SRViewer.LocalReport.DisplayName = "Service Report"

SRViewer.LocalReport.ReportPath = "Report1.rdlc"

Dim No_of_Complaints As ReportParameter = New ReportParameter

Dim Month_Name As ReportParameter = New ReportParameter

No_of_Complaints.Name = "No_of_Complaints"

Month_Name.Name = "Month_Name"

For Each FD In DR

No_of_Complaints.Values.Add(FD.ToString())

Month_Name.Values.Add(tD.ToString())

Dim myReportParams As ReportParameter() = New ReportParameter() {No_of_Complaints, Month_Name}

SRViewer.LocalReport.SetParameters(myReportParams)

If Not (dt Is Nothing) Then

SRViewer.LocalReport.DataSources.Clear()

SRViewer.LocalReport.DataSources.Add(New ReportDataSource(bindDs_name.Trim, dt))

dt.Dispose()

End If

End Sub

my report viewer wouldnt even compile without me addind a dataset and some parameters to it!! I dunno why i even need these parameters? i've downloaded numerous examples and cant actually ubderstand the logic behind this particuar control...

<?xml version="1.0" encoding="utf-8"?>

<xs:schema id="dt" targetNamespace="http://tempuri.org/dt.xsd" xmlns:mstns="http://tempuri.org/dt.xsd" xmlns="http://tempuri.org/dt.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" attributeFormDefault="qualified" elementFormDefault="qualified">

<xs:annotation>

<xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">

</Connection>

</Connections>

<CommandText>TestComplaint.SelectServiceReport</CommandText>

</Parameter>

</Parameters>

</DbCommand>

</SelectCommand>

</DbSource>

</MainSource>

</Mappings>

</Sources>

</TableAdapter>

</Tables>

</Sources>

</DataSource>

</xs:appinfo>

</xs:annotation>

<xs:element name="dt" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_UserDSName="dt" msprop:Generator_DataSetName="_dt">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:element name="dt" msprop:Generator_UserTableName="dt" msprop:Generator_RowDeletedName="dtRowDeleted" msprop:Generator_TableClassName="dtDataTable" msprop:Generator_RowChangedName="dtRowChanged" msprop:Generator_RowClassName="dtRow" msprop:Generator_RowChangingName="dtRowChanging" msprop:Generator_RowEvArgName="dtRowChangeEvent" msprop:Generator_RowEvHandlerName="dtRowChangeEventHandler" msprop:Generator_TablePropName="dt" msprop:Generator_TableVarName="tabledt" msprop:Generator_RowDeletingName="dtRowDeleting">

<xs:complexType>

<xs:sequence>

<xs:element name="Total" msprop:Generator_UserColumnName="Total" msprop:Generator_ColumnPropNameInRow="Total" msprop:Generator_ColumnVarNameInTable="columnTotal" msprop:Generator_ColumnPropNameInTable="TotalColumn" type="xs:string" minOccurs="0" />

<xs:element name="MonthName" msprop:Generator_UserColumnName="MonthName" msprop:Generator_ColumnPropNameInRow="MonthName" msprop:Generator_ColumnVarNameInTable="columnMonthName" msprop:Generator_ColumnPropNameInTable="MonthNameColumn" type="xs:string" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:element name="SelectServiceReport" msprop:Generator_UserTableName="SelectServiceReport" msprop:Generator_RowDeletedName="SelectServiceReportRowDeleted" msprop:Generator_TableClassName="SelectServiceReportDataTable" msprop:Generator_RowChangedName="SelectServiceReportRowChanged" msprop:Generator_RowClassName="SelectServiceReportRow" msprop:Generator_RowChangingName="SelectServiceReportRowChanging" msprop:Generator_RowEvArgName="SelectServiceReportRowChangeEvent" msprop:Generator_RowEvHandlerName="SelectServiceReportRowChangeEventHandler" msprop:Generator_TablePropName="SelectServiceReport" msprop:Generator_TableVarName="tableSelectServiceReport" msprop:Generator_RowDeletingName="SelectServiceReportRowDeleting">

<xs:complexType>

<xs:sequence>

<xs:element name="Total" msdata:ReadOnly="true" msprop:Generator_UserColumnName="Total" msprop:Generator_ColumnPropNameInRow="Total" msprop:Generator_ColumnVarNameInTable="columnTotal" msprop:Generator_ColumnPropNameInTable="TotalColumn" type="xs:int" minOccurs="0" />

<xs:element name="MonthName" msdata:ReadOnly="true" msprop:Generator_UserColumnName="MonthName" msprop:Generator_ColumnPropNameInRow="MonthName" msprop:Generator_ColumnVarNameInTable="columnMonthName" msprop:Generator_ColumnPropNameInTable="MonthNameColumn" minOccurs="0">

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="30" />

</xs:restriction>

</xs:simpleType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

I have no clue why i should use this dataset? The data i need comes from

Public Function ServiceReport(ByVal Service As String, ByVal Year As String) As DataSet

Dim SR As New DataSet

Dim db As New Database

Try

db.CommandText = "SELECT sum([Total])as No_of_Complaints,[MonthName]" & _

"FROM [TestComplaints].[dbo].[servicereport]" & _

"where [Service] = '" + Service + "' and DatePart(yy, [ReceivedDate]) = '" + Year + "'" & _

"GROUP By [MonthName]"

SR = db.ReturnDataSet(conn)

Catch ex As Exception

Throw ex

End Try

Return SR

End Function

where i have classes like database in my project that return tables etc.

when i run this in sql all i retrieve is what i want to present in the 3D type charts..

5 April
2 August
3 December
4 February
7 January
3 July
6 June
5 March
10 May
1 November
6 October

my x-m axis showing the months and y axis the total value..

I wont probably hear from anyone but thought to post anyways.

sql

Parameter is missing a value

Any idea what's going wrong here?

Thanks.

All parameters must have a value. In your case, it sounds like you want the default value to be null (or maybe empty string). Have you specified this as the default value? Can you post a sample RDL?

|||

This is the parameter:

<DataType>String</DataType>

<Prompt>Group1</Prompt>

</ReportParameter>

In VS, Null is selected as the default value.

In the code that sets up the ReportViewer, I've tried both excluding the SetQueryParameter call for that parameter and also passing an empty string and both result in that error message.

|||This all looks ok. Will you post your code that sets the report definition and any other parameters?|||

Microsoft.Samples.ReportingServices.ReportViewer rv = new Microsoft.Samples.ReportingServices.ReportViewer();

rv.ServerUrl = "http://localhost/ReportServer$SQL2005";

rv.ReportPath = "GroupedReport";

rv.Width = Unit.Percentage(100);

rv.Height = Unit.Percentage(100);

rv.Parameters = Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

//several parameters are added via

rv.SetQueryParameter("ParamName", ParamValue);

Then for this particular parameter, I've tried just leaving out the set parameter call and also tried doing this:

rv.SetQueryParameter("Group1", "");

and

rv.SetQueryParameter("Group1", null);

Neither of which worked.

And then of course:

plhReport.Controls.Add(rv);

Thanks for your help.

|||

You can get a lot of good information on the control from www.gotreportviewer.com.

|||

Yeah, I'm in the process of converting the code to that new control and it seems to get rid of this problem.

Thanks.

|||

Please help guys i posted in this forum yesterday and no one has replied. I cant even seem to get my report woking at all?

My form is very simple at the moment and has a drop down list that when a service is selected fires of a postback!

<title>Untitled Page</title>

</head>

<body>

<div>

<cc1:Combobox ID="Combobox1" runat="server" CSSOptionIcon="ico" AutoPostbackEnable="true"

FolderStyle="./combobox/styles/blueGray" Height="500" InnerWidth="500" Width="500">

</cc1:Combobox>

</div>

<div>

<rsweb:ReportViewer ID="SRViewer" runat="server" Visible="false" Height="600px" Width="600px" Font-Names="Verdana" Font-Size="8pt">

</rsweb:ReportViewer>

</div>

</form>

</body>

</html>

when fired the form databinds the report to a dataset / table that is returned based on drop down selection and year. for now i phisivcally put in '2007'

Protected Sub SRViewer_DataBinding(ByVal sender As Object, ByVal e As EventArgs) Handles SRViewer.DataBinding

Dim dt As DataTable = New DataTable

dt = ServiceReport(Combobox1.Text, "2007").Tables(0)

Dim DR As DataColumnCollection = dt.Columns

Dim FD As DataColumn = dt.Columns("No_of_Complaints")

Dim tD As DataColumn = dt.Columns("MonthName")

Dim bindDs_name As String = "dt"

Dim reportName As String = "Service Report"

Dim reportPath As String = "Report1.rdlc"

Dim fullReportPath As String = reportPath + reportName

Dim txtReportTitle As String = "Service Report"

If Session("fullReport") Is Nothing Then

Session.Add("fullReport", reportPath + reportName)

Else

Session.Remove("fullReport")

Session.Add("fullReport", reportPath + reportName)

End If

SRViewer.LocalReport.DisplayName = "Service Report"

SRViewer.LocalReport.ReportPath = "Report1.rdlc"

Dim No_of_Complaints As ReportParameter = New ReportParameter

Dim Month_Name As ReportParameter = New ReportParameter

No_of_Complaints.Name = "No_of_Complaints"

Month_Name.Name = "Month_Name"

For Each FD In DR

No_of_Complaints.Values.Add(FD.ToString())

Month_Name.Values.Add(tD.ToString())

Dim myReportParams As ReportParameter() = New ReportParameter() {No_of_Complaints, Month_Name}

SRViewer.LocalReport.SetParameters(myReportParams)

If Not (dt Is Nothing) Then

SRViewer.LocalReport.DataSources.Clear()

SRViewer.LocalReport.DataSources.Add(New ReportDataSource(bindDs_name.Trim, dt))

dt.Dispose()

End If

End Sub

<?xml version="1.0" encoding="utf-8"?>

<xs:annotation>

<xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">

</Connection>

</Connections>

<CommandText>TestComplaint.SelectServiceReport</CommandText>

</Parameter>

</Parameters>

</DbCommand>

</SelectCommand>

</DbSource>

</MainSource>

</Mappings>

</Sources>

</TableAdapter>

</Tables>

</Sources>

</DataSource>

</xs:appinfo>

</xs:annotation>

<xs:element name="dt" msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msprop:Generator_UserDSName="dt" msprop:Generator_DataSetName="_dt">

<xs:complexType>

<xs:choice minOccurs="0" maxOccurs="unbounded">

<xs:complexType>

<xs:sequence>

</xs:sequence>

</xs:complexType>

</xs:element>

<xs:complexType>

<xs:sequence>

<xs:simpleType>

<xs:restriction base="xs:string">

<xs:maxLength value="30" />

</xs:restriction>

</xs:simpleType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

I have no clue why i should use this dataset? The data i need comes from

Public Function ServiceReport(ByVal Service As String, ByVal Year As String) As DataSet

Dim SR As New DataSet

Dim db As New Database

Try

db.CommandText = "SELECT sum([Total])as No_of_Complaints,[MonthName]" & _

"FROM [TestComplaints].[dbo].[servicereport]" & _

"where [Service] = '" + Service + "' and DatePart(yy, [ReceivedDate]) = '" + Year + "'" & _

"GROUP By [MonthName]"

SR = db.ReturnDataSet(conn)

Catch ex As Exception

Throw ex

End Try

Return SR

End Function

where i have classes like database in my project that return tables etc.

when i run this in sql all i retrieve is what i want to present in the 3D type charts..

5 April
2 August
3 December
4 February
7 January
3 July
6 June
5 March
10 May
1 November
6 October

my x-m axis showing the months and y axis the total value..

I wont probably hear from anyone but thought to post anyways.

Friday, March 23, 2012

Parameter allow null value not reflecting in Deploy

Lads,
I'm having this problem where I've set each of my report parameters to Allow
Null Value, and Allow Blank Value to true...in the preview the null checkbox
is true. The problem is when I deploy my report, and view it through IE the
null checkbox is now false!? I haven't a notion of what the story is, any
info would be greatly appreciated.Do you deploy over existing report? Try to delete it from server before
deploying.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rowan Massey" <RowanMassey@.discussions.microsoft.com> wrote in message
news:8F8D0F2B-E07F-4EC5-A877-5E85CD0CC432@.microsoft.com...
> Lads,
> I'm having this problem where I've set each of my report parameters to
> Allow
> Null Value, and Allow Blank Value to true...in the preview the null
> checkbox
> is true. The problem is when I deploy my report, and view it through IE
> the
> null checkbox is now false!? I haven't a notion of what the story is, any
> info would be greatly appreciated.

paramater problem

is there any solution to use null values to the parameter
my sql commandtext=
"select * from info where name=@.name and surname=@.surname"
in visual basic .net
info table data=
name surname
_______ _______________
1 null
2 null
i execute my program and use this commandtext
but i cant find any solution because of the null values
what can i do at visual basic .net or at sql server to solve this
bafidi wrote:
> is there any solution to use null values to the parameter
> my sql commandtext=
> "select * from info where name=@.name and surname=@.surname"
> in visual basic .net
> info table data=
> name surname
> _______ _______________
> 1 null
> 2 null
> i execute my program and use this commandtext
> but i cant find any solution because of the null values
> what can i do at visual basic .net or at sql server to solve this
Use can use a LIKE clause instead. Always add a '%' to the end of the
parameter value and never use a NULL. You should also explicitly define
all columns you require rather than using SELECT *.
Select
Col1,
Col2
From
dbo.Info
Where
name LIKE @.Name
and
surname LIKE @.surname
I might consider using a clustered index on the surname and a
non-clustered index on name. That way, you are covered in all cases.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Use a WHERE clause, no?
SELECT * FROM tblInfo
where vchName = @.name AND vchSurName = @.surname
AND @.name IS NOT NULL
AND @.surname IS NOT NULL
Is that okay for U?
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:ekLjiO2wFHA.2880@.TK2MSFTNGP12.phx.gbl...
> bafidi wrote:
> Use can use a LIKE clause instead. Always add a '%' to the end of the
> parameter value and never use a NULL. You should also explicitly define
> all columns you require rather than using SELECT *.
> Select
> Col1,
> Col2
> From
> dbo.Info
> Where
> name LIKE @.Name
> and
> surname LIKE @.surname
> I might consider using a clustered index on the surname and a
> non-clustered index on name. That way, you are covered in all cases.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||John Smith wrote:
> Use a WHERE clause, no?
> SELECT * FROM tblInfo
> where vchName = @.name AND vchSurName = @.surname
> AND @.name IS NOT NULL
> AND @.surname IS NOT NULL
> Is that okay for U?
Why do you need the IS NOT NULL? Nothing is equal to NULL, so you're not
going to ever get NULL values back. And what happened to the query I
posted with the LIKE clauses?
Maybe you could explain your requirements in more detail. Do you need to
be able to search for NULL values? If so, you need to use WHERE COLNAME
IS NULL syntax. If you don't need NULL values and need to search on
wildcards, then look at the example I posted.
David Gugick
Quest Software
www.imceda.com
www.quest.com

ParallelPeriod that never returns null

I'm sure this is easy, but I haven't been able to cook up the secret sauce: I need a function like ParallelPeriod that will always return a member (unless I'm asking for a member that predates the oldest member in the dimension, when null is perfectly fine).

For example, ParallelPeriod([Month],1,[2006].[December].[31]) returns null. Instead, I want to get back [2006].[November].[30] (the last day of the month).

What's the secret?

There are many ways to do it. One way is to have an attribute which keeps number of days needed to go back to get to the day in the previous month. Then you will use

Date.CurrentMember.Lag(Date.DaysToGoBack.MemberValue)

for December 30 it will be 30, and for December 31 it will be 31 - so both of them will land on November 30.

|||How 'bout a way that doesn't involve changing the definition of the cube?|||

Possible too, but it won't have as good performance as the solution I proposed first. Again, there are many ways, but they revolve around the following theme (note that the formula below is only valid for the Month level. I am not sure what you want to do on the other levels).

IIF(Date.CurrentMember IS Date.CurrentMember.Parent.LastChild, Date.CurrentMember.Parent.PrevMember.LastChild, ParallelPeriod(...))

|||

OK, I see the pattern. The solution you propose above won't quite work (think of March), but it's close enough to something that will work to see where to take it.

If I decide that I really need this function, I'll probably go with the first suggestion you made, since I can still change the cube design at this point, I was just looking for a wider variety of options.

Thanks for the suggestions!

Monday, March 12, 2012

Paging in sql 2005 using ROW_NUM()

It seems that Row_num() function does not works with Dynamic sql having if
blocks to check input parameters for null and sp_executesql t excute the
query...Please help me to find a necesasry solution..Hari
Can yopu post sample data + an expected result to be tested?
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:8CE0B2A3-4347-4983-BD35-A2B502BFFD8D@.microsoft.com...
> It seems that Row_num() function does not works with Dynamic sql having if
> blocks to check input parameters for null and sp_executesql t excute the
> query...Please help me to find a necesasry solution..|||Have you considered using key values rather than page numbers for paging?
Using key values is significantly faster than row numbering techniques can
ever be b/c each page query is simply an index range scan rather than having
to materialise row numbers in a result-set simply to work out which rows
make up page n. It also more accurately accounts for movements in the
underlying database.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:8CE0B2A3-4347-4983-BD35-A2B502BFFD8D@.microsoft.com...
> It seems that Row_num() function does not works with Dynamic sql having if
> blocks to check input parameters for null and sp_executesql t excute the
> query...Please help me to find a necesasry solution..

Wednesday, March 28, 2012

Parameter label property is null when passed to subreports

Parameter is null, return all?

Monday, March 26, 2012

Parameter is missing a value

Parameter is missing a value

Friday, March 23, 2012

Parameter allow null value not reflecting in Deploy

paramater problem

ParallelPeriod that never returns null

Monday, March 12, 2012

Paging in sql 2005 using ROW_NUM()

Paging in sql 2005 using ROW_NUM()

Paging in sql 2005 using ROW_NUM()

Page Numbers in Excel

Blog Archive

About Me