Force at least one parameter (2K5)

I am trying to build a report that has about 6 different parameters. All of the parameters have been set to allow null, however, I would like the user to put in at least one "search" criteria in order to run the report.

How would I be able to do that?

In addition, when I am viewing the report, it automatically runs and does not wait until I click "View Report". How can I set it not to execute when the report is viewed, but when the user clicks the button?

ThanksHow about setting one of the parameters to not allow blank but setting the default to blank? Might solve both problems in one go, at least forcing the user to think about what they enter... Haven't tested it though |||

The program will not allow you to set a blank default value with the "Allow Blank Value" checked.

Thanks for trying ;)

|||

DotNetSavvy,

Unfortunate if all of your parameter have null values and nulls are allow, then your report will automatically run. I personally set default to all my parameters but one.

On the forcing a parameter selection, you could raise an return error from SQL if all parameters are null.

hammer

|||

Solution:

Create embedded code function that takes as parameters your report parameters values (like this MyFunction(p1value, p2value, p3value,...) ) Inside do something like this: iff(IsNothing(p1value)=true,0,1)+iff(IsNothing(p2value)=true,0,1)+... and if the result is < 1 then throw error/message box with some text (like: "Please assign at least one parameter value") and return null else return 1 or other not null value.

Add internal parameter that cannot be null to your report and in the "expression" of the default value use your code function: =Code.MyFunction(p1.value, p2.value, ...).

Here is an example of using function and parameter to validate report execution http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=829175&SiteID=1

Should work.

Maciej

FOR XML RAW & Null Characters Bug

Just curious if anyone else has seen this issue:
We had a situation where the XML being retrieved from an XML Explicit query
was malformed. After looking at the resulting XML from the query being ran
with different sets of paramaters we were able to identify that the building
of the XML was stopping on the same value. Looking further we noticed this
field had a null character (ASCII code of 0).
Here's a simple example showing the issue:
declare @.var varchar(100)
set @.var='Josh' + CHAR(0)
select @.var AS Col
for XML RAW
this results in <row Col="Josh
I couldn't find if this was a documented bug or expected behavior? Does
anyone know of any articles that describe how the null character is handled
in SQL?
Thanks
JoshOn Mon, 30 Jan 2006 13:05:31 -0800, joshb wrote:

>Just curious if anyone else has seen this issue:
>We had a situation where the XML being retrieved from an XML Explicit query
>was malformed. After looking at the resulting XML from the query being ran
>with different sets of paramaters we were able to identify that the buildin
g
>of the XML was stopping on the same value. Looking further we noticed this
>field had a null character (ASCII code of 0).
>Here's a simple example showing the issue:
>declare @.var varchar(100)
>set @.var='Josh' + CHAR(0)
>select @.var AS Col
>for XML RAW
>this results in <row Col="Josh
>I couldn't find if this was a documented bug or expected behavior? Does
>anyone know of any articles that describe how the null character is handled
>in SQL?
Hi Josh,
The problem is that ASCII 0 is used as a string delimiter in many
languages. So the low-level code that sends the results to the screen
sees the ASCII 0 and thinks it's the end of the string.
Try running your code from another client (e.g. osql).
Hugo Kornelis, SQL Server MVP|||Hi, Josh
The problem is fixed in SQL Server 2005, where your example returns
this (in Management Studio or in Query Analyzer):
<row Col="Josh�"/>
However, Management Studio warns that the character with the code 0x0
is illegal in XML documents.
Razvan

FOR XML PATH NULL Element

Hi there,
I'm using sp with FOR XML PATH('Employee'), ELEMENTS to return XML Data
from SQL Server 2005.
If row return null value return xml does not return element.
Can it be returned xml element even it contains null?
Ex
i'm getting this
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
</employee>
i want this:)
<employee>
<id>1</id>
<image>1.jpg</image>
</employee>
<employee>
<id>2</id>
<image/>
</employee>
*** Sent via Developersdex http://www.examnotes.net ***Hello Zoka,
You could do something like this:
SELECT
..
e.image AS "image/node()"
,'' AS "image/node()" -- Same as above :-), now "image/node()" is never
NULL :-)
..
FROM ... AS e
FOR XML PATH('employee')
HTH
/ Tobias|||
Hi there,
I tried this functionality but does not solve the problem.
*** Sent via Developersdex http://www.examnotes.net ***|||
Sorry Tobias,
This solves my problem, thanks:))
I haven't drink coffe when i first try the script:)
Regards,
Zoka
*** Sent via Developersdex http://www.examnotes.net ***

FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegroups.com...
to
return

FOR XML EXPLICIT and optional attributes

If attributes of an element are optional can XML Explicit be used to
only return such attrubutes if their value is not null. eg. to return
something like:
<Person Name = "Paul" BicycleBrand = "Raliegh">
<Person Name = "John" CarBrand = "Ford">
Rather than
<Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
<Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
?
It is the default behaviour of FOR XML EXPLICIT to map null value to absent
attributes.
Best regards
Michael
<paul.l@.paloma.co.uk> wrote in message
news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
> If attributes of an element are optional can XML Explicit be used to
> only return such attrubutes if their value is not null. eg. to return
> something like:
> <Person Name = "Paul" BicycleBrand = "Raliegh">
> <Person Name = "John" CarBrand = "Ford">
> Rather than
> <Person Name = "Paul" BicycleBrand = "Raliegh" CarBrand = "">
> <Person Name = "John" BicycleBrand = "" CarBrand = "Ford">
> ?
>
|||Thanks
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]
|||Thanks. Thought it might be simple.
Michael Rys [MSFT] wrote:
> It is the default behaviour of FOR XML EXPLICIT to map null value to
absent[vbcol=seagreen]
> attributes.
> Best regards
> Michael
> <paul.l@.paloma.co.uk> wrote in message
> news:1115912643.596304.92390@.o13g2000cwo.googlegro ups.com...
to[vbcol=seagreen]
return[vbcol=seagreen]

For NULL

Hello Everybody
When I do Insert query for table targeting selected columns
it has to store NULL value for rest of the columns,
without going into design and click NULL for each column ,
is there any other way to set once for all, that is to be
applicable to all tables?On Mar 22, 10:20 pm, spaulsa...@.yahoo.co.in wrote:
> Hello Everybody
> When I do Insert query for table targeting selected columns
> it has to store NULL value for rest of the columns,
> without going into design and click NULL for each column ,
> is there any other way to set once for all, that is to be
> applicable to all tables?
Select the table and columns from information_schema.columns and write
a query to ALTER TABLE ALTER COLUMN with NULL

For NULL

Hello Everybody
When I do Insert query for table targeting selected columns
it has to store NULL value for rest of the columns,
without going into design and click NULL for each column ,
is there any other way to set once for all, that is to be
applicable to all tables?
On Mar 22, 10:20 pm, spaulsa...@.yahoo.co.in wrote:
> Hello Everybody
> When I do Insert query for table targeting selected columns
> it has to store NULL value for rest of the columns,
> without going into design and click NULL for each column ,
> is there any other way to set once for all, that is to be
> applicable to all tables?
Select the table and columns from information_schema.columns and write
a query to ALTER TABLE ALTER COLUMN with NULL

For NULL

Hello Everybody
When I do Insert query for table targeting selected columns
it has to store NULL value for rest of the columns,
without going into design and click NULL for each column ,
is there any other way to set once for all, that is to be
applicable to all tables?On Mar 22, 10:20 pm, spaulsa...@.yahoo.co.in wrote:
> Hello Everybody
> When I do Insert query for table targeting selected columns
> it has to store NULL value for rest of the columns,
> without going into design and click NULL for each column ,
> is there any other way to set once for all, that is to be
> applicable to all tables?
Select the table and columns from information_schema.columns and write
a query to ALTER TABLE ALTER COLUMN with NULL

For Each Loop Failure when some values in recordset are NULL

I have a for each loop container that is performing various tasks as it loops through a record set. Some of the values from the recordset are NULL at times and this causes the FELC to fail because it is unable to map the variables that have NULL values.

Is there something I can set to have it accept the NULLS or something I can change about the variables themselves. This is valid data that still needs to be processed. There are other attributes that have data in the record.

Here are samples of my error messages:

Error: ForEach Variable Mapping number 19 to variable "User::varTransactionReference" cannot be applied.

Error: ForEach Variable Mapping number 20 to variable "User::varFlowStartDate" cannot be applied.

Error: ForEach Variable Mapping number 26 to variable "User::varCancelFee" cannot be applied.

Thanks in advance.

SK

SK1000,

The For Each control is weak. There are much better controls

that you can use to process records. Here is 2

web pages that discuss different techniques using different

controls.

This is my web site
http://www.mathgv.com/sql2005docs/SSISTransformScriptETL.htm

This Jamie

Thompson's cool blog.

http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Checking-if-a-row-exists-and-if-it-does_2C00_-has-it-changed.aspx

Thanks,

Greg Van

Mullem

|||

Thanks, Greg. I will look at both options.

SK