Parameterized Expression: default

Showing posts with label default. Show all posts

Wednesday, March 28, 2012

Parms

I was wondering how to default an input parm to nulls.
I created a proc which looked liked the following:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID = null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
This compiles, but it does not run correctly. If I change the
declaration of @.ClubID and removed the '=null' - then it compiles, but
it requires the @.ClubID to be populated (will not accept nulls).
How do I define an input parm which is not required to be populated
(accepts nulls and is defaulted to null)?Hi Jim.
The parameter is defined correctly - you should check for null by using IS
NULL rather then = null in the body of the proc:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID is null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
Regards,
Greg Linwood
SQL Server MVP
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns9458D9954D842JimHeaveyhotmailcom@.207.46.248.16...
> I was wondering how to default an input parm to nulls.
> I created a proc which looked liked the following:
> ALTER proc dbo.Proc_QuerySkaters
> @.ClubID uniqueidentifier =null,
> @.msg nvarchar(220) out
> as
> declare @.cnt int
> if @.ClubID = null
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> set @.cnt = @.@.ROWCOUNT
> end
> else
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> where @.clubID = ClubID
> set @.cnt = @.@.ROWCOUNT
> end
> if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
> - Error code: ' + cast( @.@.ERROR as char(4) )
> return -1
> end
> set @.msg=''
> return 0
> This compiles, but it does not run correctly. If I change the
> declaration of @.ClubID and removed the '=null' - then it compiles, but
> it requires the @.ClubID to be populated (will not accept nulls).
> How do I define an input parm which is not required to be populated
> (accepts nulls and is defaulted to null)?

Wednesday, March 21, 2012

Parse Identifier

"Dan Guzman" wrote:

> The SQL parser is not configurable like this. Semi-colons are optional
> statement delimiters (in most cases).
> May I ask why you have this need?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
> news:BE4B5FDD-3963-4B3E-8400-6E8E9008A8CB@.microsoft.com...
>
|||SQL Server will always allow multi-statement batches regardless of how
individual statements are terminated. Aside from minimal permissions, there
isn't much that can be done on the server side to prevent SQL injection
since injection is essentially a client-side issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
news:2D4E196C-DA3B-4278-AD6F-9C293EF9ADB6@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> let's say we have aready an legacy development framework, which allow the
> user/developer build the criteria completely [ not paramterized value] no
> he
> can build complete sql criteria & the framework attach this criteria to
> the
> original sql statemnt & excute on SQL server.
> what are we trying to do is to prevent any additional sql statement that
> may
> run after the original one [ prevent sql injection] . we can't control the
> the coming values. we just recieve sql criterias. so i'm trying to depend
> on
> the delimiter to excute this statement as one statment & for sure get SQL
> error instead of escuting it.
> it's little bit messy, but again this legacy system

>
> --
> "Dan Guzman" wrote:

Parse Identifier

"Dan Guzman" wrote:

> The SQL parser is not configurable like this. Semi-colons are optional
> statement delimiters (in most cases).
> May I ask why you have this need?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
> news:BE4B5FDD-3963-4B3E-8400-6E8E9008A8CB@.microsoft.com...
>|||SQL Server will always allow multi-statement batches regardless of how
individual statements are terminated. Aside from minimal permissions, there
isn't much that can be done on the server side to prevent SQL injection
since injection is essentially a client-side issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
news:2D4E196C-DA3B-4278-AD6F-9C293EF9ADB6@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> let's say we have aready an legacy development framework, which allow the
> user/developer build the criteria completely [ not paramterized value]
no
> he
> can build complete sql criteria & the framework attach this criteria to
> the
> original sql statemnt & excute on SQL server.
> what are we trying to do is to prevent any additional sql statement that
> may
> run after the original one [ prevent sql injection] . we can't control
the
> the coming values. we just recieve sql criterias. so i'm trying to depend
> on
> the delimiter to excute this statement as one statment & for sure get SQL
> error instead of escuting it.
> it's little bit messy, but again this legacy system

>
> --
> "Dan Guzman" wrote:
>

Parse Identifier

hi
how can i configure the SQL server & inforce to parse & excute ONLY if he
found an specific identifier, i.e: ;(semicolon)
within default SQL server configurations one space is enough to seperate
between statement while parsing & excuting.
examples:
* current situation:
select * from t1 select * from t2 -- it will consider two statements &
excute both
* I need something like this:
select * from t1 select * from t2; -- it will give error , it will consider
it as one statement
any help '> * I need something like this:
> select * from t1 select * from t2; -- it will give error , it will
> consider
> it as one statement
The SQL parser is not configurable like this. Semi-colons are optional
statement delimiters (in most cases).
May I ask why you have this need?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
news:BE4B5FDD-3963-4B3E-8400-6E8E9008A8CB@.microsoft.com...
> hi
> how can i configure the SQL server & inforce to parse & excute ONLY if he
> found an specific identifier, i.e: ;(semicolon)
> within default SQL server configurations one space is enough to seperate
> between statement while parsing & excuting.
> examples:
> * current situation:
> select * from t1 select * from t2 -- it will consider two statements &
> excute both
> * I need something like this:
> select * from t1 select * from t2; -- it will give error , it will
> consider
> it as one statement
> any help '|||Hi Dan,
let's say we have aready an legacy development framework, which allow the
user/developer build the criteria completely [ not paramterized value] no he
can build complete sql criteria & the framework attach this criteria to the
original sql statemnt & excute on SQL server.
what are we trying to do is to prevent any additional sql statement that may
run after the original one [ prevent sql injection] . we can't control the
the coming values. we just recieve sql criterias. so i'm trying to depend on
the delimiter to excute this statement as one statment & for sure get SQL
error instead of escuting it.
it's little bit messy, but again this legacy system :)
"Dan Guzman" wrote:
> > * I need something like this:
> >
> > select * from t1 select * from t2; -- it will give error , it will
> > consider
> > it as one statement
> The SQL parser is not configurable like this. Semi-colons are optional
> statement delimiters (in most cases).
> May I ask why you have this need?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
> news:BE4B5FDD-3963-4B3E-8400-6E8E9008A8CB@.microsoft.com...
> > hi
> > how can i configure the SQL server & inforce to parse & excute ONLY if he
> > found an specific identifier, i.e: ;(semicolon)
> >
> > within default SQL server configurations one space is enough to seperate
> > between statement while parsing & excuting.
> > examples:
> >
> > * current situation:
> >
> > select * from t1 select * from t2 -- it will consider two statements &
> > excute both
> >
> > * I need something like this:
> >
> > select * from t1 select * from t2; -- it will give error , it will
> > consider
> > it as one statement
> >
> > any help '
>|||SQL Server will always allow multi-statement batches regardless of how
individual statements are terminated. Aside from minimal permissions, there
isn't much that can be done on the server side to prevent SQL injection
since injection is essentially a client-side issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
news:2D4E196C-DA3B-4278-AD6F-9C293EF9ADB6@.microsoft.com...
> Hi Dan,
> let's say we have aready an legacy development framework, which allow the
> user/developer build the criteria completely [ not paramterized value] no
> he
> can build complete sql criteria & the framework attach this criteria to
> the
> original sql statemnt & excute on SQL server.
> what are we trying to do is to prevent any additional sql statement that
> may
> run after the original one [ prevent sql injection] . we can't control the
> the coming values. we just recieve sql criterias. so i'm trying to depend
> on
> the delimiter to excute this statement as one statment & for sure get SQL
> error instead of escuting it.
> it's little bit messy, but again this legacy system :)
>
> --
> "Dan Guzman" wrote:
>> > * I need something like this:
>> >
>> > select * from t1 select * from t2; -- it will give error , it will
>> > consider
>> > it as one statement
>> The SQL parser is not configurable like this. Semi-colons are optional
>> statement delimiters (in most cases).
>> May I ask why you have this need?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Bashar Naffa" <BasharNaffa@.discussions.microsoft.com> wrote in message
>> news:BE4B5FDD-3963-4B3E-8400-6E8E9008A8CB@.microsoft.com...
>> > hi
>> > how can i configure the SQL server & inforce to parse & excute ONLY if
>> > he
>> > found an specific identifier, i.e: ;(semicolon)
>> >
>> > within default SQL server configurations one space is enough to
>> > seperate
>> > between statement while parsing & excuting.
>> > examples:
>> >
>> > * current situation:
>> >
>> > select * from t1 select * from t2 -- it will consider two statements &
>> > excute both
>> >
>> > * I need something like this:
>> >
>> > select * from t1 select * from t2; -- it will give error , it will
>> > consider
>> > it as one statement
>> >
>> > any help '
>>

Tuesday, March 20, 2012

Parent Child Dimension

I have a little problem.

I build a Parent - Child dimension an I need to change the default listing of this kind of dimensions to display some information from another column than ID and ParentID.

Is there eny way to solve that?

Tanks in advance for your help.

(AS 2005; Excel 2007)

You can change this in the name column for the the parent child primary key. Check the attribute pane in the dimension editor.

Regards

Thomas Ivarsson

|||thank you Thomas for your quick answer

Paremeter Control Greyed out

I have a balance sheet report with only one parameter, Month.
The parameter is a query and the default for this parameter is a query to
determine the current month.
We schedule this report to run every hour because managers don't like to
wait 5 minutes for a report to generate. The problem is, we can't change the
parameter because it is greyed out. Is there a setting that would allow
people to choose a new month from the drop-down control even if the report is
on a schedule?You should be able to go into Report Manager and select Prompt User
checkbox. Or you can set the PromptUser property in the RDL to true.
--
| Thread-Topic: Paremeter Control Greyed out
| thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==| X-WBNR-Posting-Host: 12.38.198.125
| From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
| Subject: Paremeter Control Greyed out
| Date: Tue, 3 May 2005 07:30:19 -0700
| Lines: 10
| Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a balance sheet report with only one parameter, Month.
|
| The parameter is a query and the default for this parameter is a query to
| determine the current month.
|
| We schedule this report to run every hour because managers don't like to
| wait 5 minutes for a report to generate. The problem is, we can't change
the
| parameter because it is greyed out. Is there a setting that would allow
| people to choose a new month from the drop-down control even if the
report is
| on a schedule?
||||You are refering to PROPERTIES>PARAMETERS for the report itself correct?
It's already checked.
""Brad Syputa - MS"" wrote:
> You should be able to go into Report Manager and select Prompt User
> checkbox. Or you can set the PromptUser property in the RDL to true.
> --
> | Thread-Topic: Paremeter Control Greyed out
> | thread-index: AcVP7KGcDvY4OJvHTNKGeE4960e8Lg==> | X-WBNR-Posting-Host: 12.38.198.125
> | From: "=?Utf-8?B?RGF2aWQ=?=" <David@.discussions.microsoft.com>
> | Subject: Paremeter Control Greyed out
> | Date: Tue, 3 May 2005 07:30:19 -0700
> | Lines: 10
> | Message-ID: <EE55F290-D74E-48BF-B7E7-F06155AB37BA@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:42589
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have a balance sheet report with only one parameter, Month.
> |
> | The parameter is a query and the default for this parameter is a query to
> | determine the current month.
> |
> | We schedule this report to run every hour because managers don't like to
> | wait 5 minutes for a report to generate. The problem is, we can't change
> the
> | parameter because it is greyed out. Is there a setting that would allow
> | people to choose a new month from the drop-down control even if the
> report is
> | on a schedule?
> |
>

Parametrized Report with Analysis Services didn't work

Y made 2 report with parametrized MDX queries and they only work with the default values. But when I select some values from the combos with legal values the report is empty.
The weird thing is when I test the report in visual studio it works really fine. But when I go from http://somemachine/reports it fails.
Can anybody try this?
The MDX querie is ok. I have a lot of experience with MDX in SQL 2000.
It says something like this.

WITH
MEMBER [Measures].[Caida] AS 'IIF([Measures].[Variacion]<-0.5, [Measures].[Variacion], NULL)'
SELECT
NON EMPTY { [Measures].[Caida]} ON COLUMNS,
NON EMPTY { STRTOMEMBER(@.Grupo).CHILDREN } ON ROWS
FROM Ventas
WHERE ( {STRTOMEMBER(@.Mes)} )

I made others easier queries with the same behavior of the empty report.
Can anybody help me or know what happend?
Thanks in advanceCan you post an RDL / query that runs against the AdventureWorks sample cube and exposes the same issue?
Additional questions:
* Which build of BI Development Studio are you running?
* Which build of RS 2005 is running on the report server?
* Which build of AS 2005 is running on the AS server?

-- Robert|||

I'm not using adventure works. I'm using all of my own. But if you see the query is really simple (no tricks). Can you help me?

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

<rd:DataSourceID>b60468c5-c60b-47e2-942d-eb4686e92533</rd:DataSourceID>

</DataSource>

</DataSources>

<DataType>String</DataType>

<Value>[Fecha].[Standard].[Anio].&[15]</Value>

</Values>

</DefaultValue>

<ValueField>ParameterValue</ValueField>

<LabelField>ParameterCaption</LabelField>

</DataSetReference>

</ValidValues>

</ReportParameter>

</ReportParameters>

<rd:DrawGrid>true</rd:DrawGrid>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<rd:DefaultName>textbox1</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Bottom>Black</Bottom>

</BorderColor>

</BorderWidth>

<Bottom>Solid</Bottom>

</BorderStyle>

<BackgroundColor>DeepSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

</Style>

<Value>Caida de las Ventas respecto a¤o anterior</Value>

</Textbox>

<GroupExpression>=Fields!Anio.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=Fields!Anio.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<rd:DefaultName>Anio</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Bottom>Solid</Bottom>

</BorderStyle>

</Style>

<Value>="A¤o: "+Fields!Anio.Value</Value>

</Textbox>

</MatrixColumn>

</MatrixColumns>

<GroupExpression>=Fields!Grupo.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=Fields!Grupo.Value</SortExpression>

<Direction>Ascending</Direction>

</SortBy>

</Sorting>

<rd:DefaultName>Grupo</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

</Style>

<Value>=Fields!Grupo.Value</Value>

</Textbox>

</ReportItems>

</DynamicRows>

</RowGrouping>

</RowGroupings>

<GroupExpression>=Fields!Mes.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<rd:DefaultName>Mes</rd:DefaultName>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Center</TextAlign>

</Style>

<Value>=Fields!Mes.Value</Value>

</Textbox>

</ReportItems>

</DynamicColumns>

</ColumnGrouping>

<rd:DefaultName>textbox8</rd:DefaultName>

<Style>

<Default>Solid</Default>

</BorderStyle>

<BackgroundColor>LightSkyBlue</BackgroundColor>

<TextAlign>Right</TextAlign>

</Style>

</Textbox>

</ReportItems>

</StaticColumn>

</StaticColumns>

</ColumnGrouping>

</ColumnGroupings>

<rd:DefaultName>Caida_mas_de_25_</rd:DefaultName>

<ReportName>Caida Ventas por Producto</ReportName>

<Value>=Fields!Mes.Value</Value>

</Parameter>

<Value>=Fields!Grupo.Value</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<ToolTip>Ver detalle caida de las ventas para este mes y este grupo</ToolTip>

<Style>

<TextDecoration>Underline</TextDecoration>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

</Style>

<Value>=Sum(Fields!Caida_mas_de_25_.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>

</MatrixCells>

</MatrixRow>

</MatrixRows>

<rd:DefaultName>textbox5</rd:DefaultName>

<Style>

</Style>

</Textbox>

</ReportItems>

</Corner>

</Matrix>

</ReportItems>

<Style>

<FontFamily>Times New Roman</FontFamily>

<Color>Maroon</Color>

</Style>

</List>

</ReportItems>

</Body>

<rd:ReportID>bc8342a4-de2c-4d93-ab3c-f257505a4aa9</rd:ReportID>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<CommandText>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]<-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</CommandText>

<Value>=Parameters!Anio.Value</Value>

</QueryParameter>

</QueryParameters>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Mes</LevelName><UniqueName>[Fecha].[Standard].[Mes]</UniqueName></ID><ItemCaption>Mes</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Producto</DimensionName><HierarchyName>Grupo</HierarchyName><HierarchyUniqueName>[Producto].[Grupo]</HierarchyUniqueName><LevelName>Grupo</LevelName><UniqueName>[Producto].[Grupo].[Grupo]</UniqueName></ID><ItemCaption>Grupo</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Caida mas de 25%</MeasureName><UniqueName>[Measures].[Caida mas de 25%]</UniqueName></ID><ItemCaption>Caida mas de 25%</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH

MEMBER [Measures].[Caida mas de 25%] AS 'IIF([Measures].[Variacion Igual Periodo A¤o Anterior]<-.25, [Measures].[Variacion Igual Periodo A¤o Anterior], NULL)'

SELECT

NON EMPTY {

{[Measures].[Caida mas de 25%]}

} ON COLUMNS,

NON EMPTY {

CROSSJOIN(

{STRTOMEMBER(@.Anio).CHILDREN},

{[Producto].[Grupo].CHILDREN}

)

} ON ROWS

FROM Ventas

</Statement><ParameterDefinitions><ParameterDefinition><Name>Anio</Name><DefaultValues><DefaultValue>[Fecha].[Standard].[Anio].&[15]</DefaultValue></DefaultValues><Caption>Anio</Caption><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><ParameterValuesQuery><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Fecha].[Standard].ALLMEMBERS ON ROWS FROM [Ventas]</Statement><ParameterizedStatement><ReferencedParameters /></ParameterizedStatement></ParameterValuesQuery></ParameterDefinition></ParameterDefinitions></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

<rd:TypeName>System.String</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

<rd:TypeName>System.Int32</rd:TypeName>

</Field>

</Fields>

<Query>

<CommandText>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</CommandText>

<rd:MdxQuery><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Fecha</DimensionName><HierarchyName>Standard</HierarchyName><HierarchyUniqueName>[Fecha].[Standard]</HierarchyUniqueName><LevelName>Anio</LevelName><UniqueName>[Fecha].[Standard].[Anio]</UniqueName></ID><ItemCaption>Anio</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterCaption</MeasureName><UniqueName>[Measures].[ParameterCaption]</UniqueName></ID><ItemCaption>ParameterCaption</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterValue</MeasureName><UniqueName>[Measures].[ParameterValue]</UniqueName></ID><ItemCaption>ParameterValue</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID xsi:type="Measure"><MeasureName>ParameterLevel</MeasureName><UniqueName>[Measures].[ParameterLevel]</UniqueName></ID><ItemCaption>ParameterLevel</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Ventas</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH MEMBER [Measures].[ParameterCaption] AS '[Fecha].[Standard].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Fecha].[Standard].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Fecha].[Standard].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , FILTER([Fecha].[Standard].CHILDREN, NOT ISEMPTY([Measures].[ImporteVenta])) ON ROWS FROM [Ventas]</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:MdxQuery>

</Query>

</DataSet>

</DataSets>

</Report>

|||After 2 days of research and kicking my desktop.
I found finnally that to build parameters you must not use UNIQUENAME. Instead use MEMBER [Measures].[ParameterValue] AS ' "[DimensionName].["+[DimensionName].CURRENTMEMBER.NAME+"]" '
It's works fine.
I think there is a bug using STRTOMEMBER(@.parameter) when @.parameter is in the UNIQUENAME form.
I hope it helps you too.|||I edit the previous post where i said that you can only put dimensions on columns. That's wrong, i'm using dimension in columns and rows and works really fine.

Monday, March 12, 2012

Parameters with all values as default

Hi all.
Have a report with 2 parameters StartDate and SessionName. Both are non-query
based. So, the user runs the report and has to enter values in both fields in
order to run the report. I would like to have by default to have the report
run all values like as in (select * from etc) and then if user decides to
hone in on any StartDate/Session then they can used the parameter fields on
the report and then click view report.
Thanks
James
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1You'll need to allow Null values for each parameter. There's an example on
www.msbicentral.com. If you login and go to Downloads, Reporting Services,
RDL files, the example is Matrix.Param.RDL. There are other useful examples
there, also.
"James Woo via SQLMonster.com" wrote:
> Hi all.
> Have a report with 2 parameters StartDate and SessionName. Both are non-query
> based. So, the user runs the report and has to enter values in both fields in
> order to run the report. I would like to have by default to have the report
> run all values like as in (select * from etc) and then if user decides to
> hone in on any StartDate/Session then they can used the parameter fields on
> the report and then click view report.
> Thanks
> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1
>|||COOL SITE.. Thanks. I will have to spend hours there.
daw wrote:
>You'll need to allow Null values for each parameter. There's an example on
>www.msbicentral.com. If you login and go to Downloads, Reporting Services,
>RDL files, the example is Matrix.Param.RDL. There are other useful examples
>there, also.
>> Hi all.
>[quoted text clipped - 7 lines]
>> Thanks
>> James
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1|||Easiest way to do this...
Create a new dataset for your Parameter:
e.g
SELECT Product,NAME
FROM Products
UNION
SELECT '***' AS Product,'All Managers' AS Name
This creates a *** as the ALL for products...
Then in your main report dataset...
Make sure you are using the generic query designer (button above the query)
Then as your statement:
WHERE ( Product = @.ProductParam or @.ProductParam='***')
Good luck
"James Woo via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:52B3D2CE3710C@.SQLMonster.com...
> COOL SITE.. Thanks. I will have to spend hours there.
> daw wrote:
> >You'll need to allow Null values for each parameter. There's an example
on
> >www.msbicentral.com. If you login and go to Downloads, Reporting
Services,
> >RDL files, the example is Matrix.Param.RDL. There are other useful
examples
> >there, also.
> >
> >> Hi all.
> >>
> >[quoted text clipped - 7 lines]
> >> Thanks
> >> James
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1

Parameters will not Default

Greetings

I am using SQL 2005 Reporting Services and I have a problem with parameters. If I leave 3 of the 4 values as non queried in the Available values section and mark them as a Default Values: of NULL, then the report works fine. However if I set the Available values to a query I have written and leave the Default values as NULL, when I view the report in either preview mode or on the reports server, it wants me to specify a value from the boxes and the tick box for NULL has disappeared. Allow NULL value is ticked inthe first section, Properties.

Anybody got any suggestions?

If you have available values, then the allowable values of the parameter are limited to those returned by your query. If you want to allow the parameter to be null, then null must be one of the available values returned by your query.

-Albert

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.sqlmonster.com
You can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.c om...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedure.
Please Try To Find it.
--
Message posted via http://www.sqlmonster.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means a
re they Default Parameter or Mandatory. Where this information is store? I c
an get ISNULLABLE Column from syscolumns table but it is only applicable to
table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.droptable.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via droptable.com" <forum@.droptable.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQ
droptable.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.droptable.com

Friday, March 9, 2012

Parameters in Report Server

There is a brown bar in Report Server that holds the report parameters. This bar seems to be CLOSED by default. Is there a way to set the default so that the parameter bar is OPEN and the users can see the parameters?

Thanks.

I don't think you can set this as a default, and I don't suspect your users will be browsing the reportserver virtual directory. So assuming you are providing a link of some sorts (throu email or an application) you can control the parameters area using the rc: Prameters URL query string parameter.

The following is an excerpt from http://msdn2.microsoft.com/en-us/library/ms152835.aspx

Use this parameter To

Toolbar

Show or hide the toolbar. If the value of this parameter is false, all remaining options are ignored. If you omit this parameter, the toolbar is automatically displayed for rendering formats that support it. The default of this parameter is true.

Parameters

Show or hide the parameters area of the toolbar. If you set this parameter to true, the parameters area of the toolbar is displayed. If you set this parameter to false, the parameters area is not displayed and cannot be displayed by the user. If you set this parameter to a value of Collapsed, the parameters area will not be displayed, but can be toggled by the end user. The default value of this parameter is true.

Zoom

Set the report zoom value as an integer percentage or a string constant. Standard string values include Page Width and Whole Page. This parameter is ignored by versions of Microsoft Internet Explorer earlier than Internet Explorer 5.0 and all non-Microsoft browsers. The default value of this parameter is 100.

Section

Set which page in the report to display. Any value that is greater than the number of pages in the report displays the last page. Any value that is less than 0 displays page 1 of the report. The default value of this parameter is 1.

StartFind

Specify the first section to search. The default value of this parameter is the first page of the report.

EndFind

Set the number of the last page to use in the search. For example, a value of 5 indicates that the last page to be searched is page 5 of the report. The default value is the number of the current page. Use this parameter in conjunction with the StartFind parameter.

FallbackPage

Set the number of the page to display if a search or a document map selection fails. The default value is the number of the current page.

GetImage

Get a particular icon for the HTML Viewer user interface.

Icon

Get the icon of a particular rendering extension.

Stylesheet

Specify a style sheet to be applied to the HTML Viewer.

|||Right now I'm just pointing my browser to "reports". What troubles me is that this says Parameters is defaulted to true and in my browser it is not. I have never changed any defaults that I know of.

Wednesday, March 7, 2012

parameters default value remains "Query based" in spite of setting

Hello,
In my report manager I am not able to set a default value for a report
parameter, because the default value is indicated to be "query based".
I do not seem to be able to change that whatever I do. Surely I tried
setting the report parameters to "non-queried" and "none".
The default values I set in report designer also do not show in the
deployed report. (Even though other changes do.)
Anybody some ideas? Thanks.In Solution Explorer, right-click on the Project and choose Properties. Change OverwriteDataSources
to True and redeploy.
Optionally, you can use Report Manager (http://yourserver/reports) to do this by navigating to the
report you want to modify, clicking the Properties tab and selecting Data Sources on the left. You
can them make your changes on the server.
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Paul vO" <info@.vanoordt.nl> wrote in message
news:200e5c1b.0408230349.39c8c5d0@.posting.google.com...
> Hello,
> In my report manager I am not able to set a default value for a report
> parameter, because the default value is indicated to be "query based".
> I do not seem to be able to change that whatever I do. Surely I tried
> setting the report parameters to "non-queried" and "none".
> The default values I set in report designer also do not show in the
> deployed report. (Even though other changes do.)
> Anybody some ideas? Thanks.

Parameters Caching?

Hello,
I've created a report (with default parameters) and uploaded it to Report
Manager.
When I later change the default parameters in the RDL and "Update" the
report definition, the default parameters do not update in Report Manager.
When I remove the report definition and re-upload it, the report parameters
are once again updated.
My theory is that when I upload a report, the parameters are stored in a
table in the ReportServer database, and pulled from there no matter what is
contained in the report definition. If this is the case, is there a way to
"Refresh" the default parameters?
Any assistance would be greatly appreciated.
Best Regards,
BenAfter you deploy the report to the server with the change. Close IE and
re-open it. You should see the new report then.
Bruce L-C
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:OWB78$vfEHA.644@.tk2msftngp13.phx.gbl...
> Hello,
> I've created a report (with default parameters) and uploaded it to Report
> Manager.
> When I later change the default parameters in the RDL and "Update" the
> report definition, the default parameters do not update in Report Manager.
> When I remove the report definition and re-upload it, the report
parameters
> are once again updated.
> My theory is that when I upload a report, the parameters are stored in a
> table in the ReportServer database, and pulled from there no matter what
is
> contained in the report definition. If this is the case, is there a way
to
> "Refresh" the default parameters?
> Any assistance would be greatly appreciated.
>
> Best Regards,
> Ben
>|||Hello,
I've attempted to refresh the report (and have cleared my cache), but to no
avail.
If I issue this query, I can see that the old parameters are still sitting
in the catalog table.
SELECT Parameter FROM Catalog
It looks like updating a report definition on your report manager doesn't
actually change the default parameters in the Catalog table, even if they
have changed in the updated RDL.
Does this seem like a bug to anybody? I'm more than happy to provide an
easy to setup example if requested.
Thanks again,
Benjamin Pierce
Open Text Corporation
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O%23BKBJwfEHA.1428@.TK2MSFTNGP10.phx.gbl...
> After you deploy the report to the server with the change. Close IE and
> re-open it. You should see the new report then.
> Bruce L-C
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:OWB78$vfEHA.644@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I've created a report (with default parameters) and uploaded it to
Report
> > Manager.
> >
> > When I later change the default parameters in the RDL and "Update" the
> > report definition, the default parameters do not update in Report
Manager.
> > When I remove the report definition and re-upload it, the report
> parameters
> > are once again updated.
> >
> > My theory is that when I upload a report, the parameters are stored in a
> > table in the ReportServer database, and pulled from there no matter what
> is
> > contained in the report definition. If this is the case, is there a way
> to
> > "Refresh" the default parameters?
> >
> > Any assistance would be greatly appreciated.
> >
> >
> > Best Regards,
> >
> > Ben
> >
> >
>|||It is actually our design, although an arguably strange one. Report
parameters can be modified after the report has been published and we
decided to favor the admin over the developer and not overwrite them by
default when the report definition is changed. If you want to do so, you
would need to call SetReportParameters after SetReportDefinition.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:uzXTMVwfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I've attempted to refresh the report (and have cleared my cache), but to
no
> avail.
> If I issue this query, I can see that the old parameters are still sitting
> in the catalog table.
> SELECT Parameter FROM Catalog
> It looks like updating a report definition on your report manager doesn't
> actually change the default parameters in the Catalog table, even if they
> have changed in the updated RDL.
> Does this seem like a bug to anybody? I'm more than happy to provide an
> easy to setup example if requested.
>
> Thanks again,
> Benjamin Pierce
> Open Text Corporation
>
> "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:O%23BKBJwfEHA.1428@.TK2MSFTNGP10.phx.gbl...
> > After you deploy the report to the server with the change. Close IE and
> > re-open it. You should see the new report then.
> >
> > Bruce L-C
> >
> > "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> > news:OWB78$vfEHA.644@.tk2msftngp13.phx.gbl...
> > > Hello,
> > >
> > > I've created a report (with default parameters) and uploaded it to
> Report
> > > Manager.
> > >
> > > When I later change the default parameters in the RDL and "Update" the
> > > report definition, the default parameters do not update in Report
> Manager.
> > > When I remove the report definition and re-upload it, the report
> > parameters
> > > are once again updated.
> > >
> > > My theory is that when I upload a report, the parameters are stored in
a
> > > table in the ReportServer database, and pulled from there no matter
what
> > is
> > > contained in the report definition. If this is the case, is there a
way
> > to
> > > "Refresh" the default parameters?
> > >
> > > Any assistance would be greatly appreciated.
> > >
> > >
> > > Best Regards,
> > >
> > > Ben
> > >
> > >
> >
> >
>|||Thank you sir!
Your workaround is quite acceptable.
Regards,
Benjamin Pierce
"Tudor Trufinescu (MSFT)" <tudortr@.ms.com> wrote in message
news:ulCejkwfEHA.3024@.TK2MSFTNGP10.phx.gbl...
> It is actually our design, although an arguably strange one. Report
> parameters can be modified after the report has been published and we
> decided to favor the admin over the developer and not overwrite them by
> default when the report definition is changed. If you want to do so, you
> would need to call SetReportParameters after SetReportDefinition.
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:uzXTMVwfEHA.3928@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > I've attempted to refresh the report (and have cleared my cache), but to
> no
> > avail.
> >
> > If I issue this query, I can see that the old parameters are still
sitting
> > in the catalog table.
> >
> > SELECT Parameter FROM Catalog
> >
> > It looks like updating a report definition on your report manager
doesn't
> > actually change the default parameters in the Catalog table, even if
they
> > have changed in the updated RDL.
> >
> > Does this seem like a bug to anybody? I'm more than happy to provide an
> > easy to setup example if requested.
> >
> >
> > Thanks again,
> >
> > Benjamin Pierce
> > Open Text Corporation
> >
> >
> > "Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> > news:O%23BKBJwfEHA.1428@.TK2MSFTNGP10.phx.gbl...
> > > After you deploy the report to the server with the change. Close IE
and
> > > re-open it. You should see the new report then.
> > >
> > > Bruce L-C
> > >
> > > "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> > > news:OWB78$vfEHA.644@.tk2msftngp13.phx.gbl...
> > > > Hello,
> > > >
> > > > I've created a report (with default parameters) and uploaded it to
> > Report
> > > > Manager.
> > > >
> > > > When I later change the default parameters in the RDL and "Update"
the
> > > > report definition, the default parameters do not update in Report
> > Manager.
> > > > When I remove the report definition and re-upload it, the report
> > > parameters
> > > > are once again updated.
> > > >
> > > > My theory is that when I upload a report, the parameters are stored
in
> a
> > > > table in the ReportServer database, and pulled from there no matter
> what
> > > is
> > > > contained in the report definition. If this is the case, is there a
> way
> > > to
> > > > "Refresh" the default parameters?
> > > >
> > > > Any assistance would be greatly appreciated.
> > > >
> > > >
> > > > Best Regards,
> > > >
> > > > Ben
> > > >
> > > >
> > >
> > >
> >
> >
>

Saturday, February 25, 2012

Parameters - get last minus one

I have a list of parameters, and they are months. This list is dynamic, and
is based on a query to an OLAP cube.
I want my default parameter to be the last month minus one, so that when the
last month is December, I want to have November as default.
How can I do this?
All help appreciated!
Kaisa M. Lindahljust use other query to get max(month),then use the result to parameter's
default value
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> Ð´ÈëÏûÏ¢
news:#sYiAa61EHA.4004@.tk2msftngp13.phx.gbl...
> I have a list of parameters, and they are months. This list is dynamic,
and
> is based on a query to an OLAP cube.
> I want my default parameter to be the last month minus one, so that when
the
> last month is December, I want to have November as default.
> How can I do this?
> All help appreciated!
> Kaisa M. Lindahl
>

Parameters - Coalesce!

Hi all,
Just a quick question.
I am calling a stored procedure which has two optional parameters.
Optional meaning they are declared with a default value NULL.
If for some reason and empty string send sent to the procedure will the
internal value of that parameter be NULL or "" ?
Area these the same thing in TSQL ?
Following on from that:
Will the Coalesce Function treat "" & NULL the same?
IE: I need to know if the following will result in 10 if an empty string is
sent to a stored procedure for the 'myParam' parameter..
CREATE PROCEDURE [dbo].[myProcedure]
@.myParam INT = NULL
As
BEGIN
SELECT * FROM myTable
WHERE myTableID = Coalesce(@.myParam,10)
END
Many thanks to those who respond!!!
Adam"Adam Knight" <adam@.pertrain.com.au> wrote in message
news:eNcdq2gyFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> Just a quick question.
> I am calling a stored procedure which has two optional parameters.
> Optional meaning they are declared with a default value NULL.
> If for some reason and empty string send sent to the procedure will the
> internal value of that parameter be NULL or "" ?
An "Empty String" passed in as a parameter is treated as an empty string in
SQL ('')

> Area these the same thing in TSQL ?
A empty string, or zero-length string (ZLS) is not the same thing as NULL.

> Following on from that:
> Will the Coalesce Function treat "" & NULL the same?
COALESCE(value1, value2, ...) returns the first non-NULL value from the list
of values. A common usage of coalesce is something like this:
COALESCE(column, '')
To return a zero-length string in place of a NULL.

> IE: I need to know if the following will result in 10 if an empty string
> is sent to a stored procedure for the 'myParam' parameter..
> CREATE PROCEDURE [dbo].[myProcedure]
> @.myParam INT = NULL
> As
> BEGIN
> SELECT * FROM myTable
> WHERE myTableID = Coalesce(@.myParam,10)
> END
>
An empty string is not a NULL. You shouldn't be passing a 'string' value in
to this procedure anyway - it's an INT parameter. For your example would it
make sense to set the default for @.myParam INT = 10 and get rid of the
COALESCE() function call?

> Many thanks to those who respond!!!
> Adam
>

Wednesday, March 28, 2012

Parms

Wednesday, March 21, 2012

Parse Identifier

Parse Identifier

Parse Identifier

Tuesday, March 20, 2012

Parent Child Dimension

Paremeter Control Greyed out

Parametrized Report with Analysis Services didn't work

Monday, March 12, 2012

Parameters with all values as default

Parameters will not Default

Parameters Status in Store Procedure

Parameters Status in Store Procedure

Parameters Status in Store Procedure

Friday, March 9, 2012

Parameters in Report Server

Wednesday, March 7, 2012

parameters default value remains "Query based" in spite of setting

Parameters Caching?

Saturday, February 25, 2012

Parameters - get last minus one

Parameters - Coalesce!

Parameterized Expression

Blog Archive

About Me