report does not accept null values for its parameters

Hi all
Im using a stored procedure with in params as datasource to a report.
These params are initialized to null by default in the procedure.
When executing this datasource in the report designer's data tab
every thing works fine - i can live these parameters with nulls (or set
other values) and the procedures return appropriate results.
Then in the report parameters tab i allow these params to get null values,
with no default value.
But when executing the report in the 'preview' mode of visual studio i must
enter values for all params or else the report is not displayed at all (not
even with empty result set..).
No error massage is displayed - just the default blank background..
Thanks for your attention
ReaThe report will not execute unless you provide Default values in the Report
Parameters dialog. Without setting a default value on each and every
parameter, RS has no way of knowing what to use. It is unaware that your SP
has default values assigned.
hth
fmi see www.sqlreportingservices.net
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> Hi all
> Im using a stored procedure with in params as datasource to a report.
> These params are initialized to null by default in the procedure.
> When executing this datasource in the report designer's data tab
> every thing works fine - i can live these parameters with nulls (or set
> other values) and the procedures return appropriate results.
> Then in the report parameters tab i allow these params to get null values,
> with no default value.
> But when executing the report in the 'preview' mode of visual studio i
must
> enter values for all params or else the report is not displayed at all
(not
> even with empty result set..).
> No error massage is displayed - just the default blank background..
> Thanks for your attention
> Rea
>|||ok then how do i set null values as default values for report's parameters'
i tried : null , =null, =System.DBnull - all were rejected by compilation.
Thanks again
Rea
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:uAKFBp3YEHA.1764@.TK2MSFTNGP10.phx.gbl...
> The report will not execute unless you provide Default values in the
Report
> Parameters dialog. Without setting a default value on each and every
> parameter, RS has no way of knowing what to use. It is unaware that your
SP
> has default values assigned.
> hth
> fmi see www.sqlreportingservices.net
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > Hi all
> > Im using a stored procedure with in params as datasource to a report.
> > These params are initialized to null by default in the procedure.
> > When executing this datasource in the report designer's data tab
> > every thing works fine - i can live these parameters with nulls (or set
> > other values) and the procedures return appropriate results.
> > Then in the report parameters tab i allow these params to get null
values,
> > with no default value.
> > But when executing the report in the 'preview' mode of visual studio i
> must
> > enter values for all params or else the report is not displayed at all
> (not
> > even with empty result set..).
> > No error massage is displayed - just the default blank background..
> > Thanks for your attention
> > Rea
> >
> >
>|||=Nothing
(according to Peter)
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uip9OnAZEHA.1356@.TK2MSFTNGP09.phx.gbl...
> ok then how do i set null values as default values for report's
parameters'
> i tried : null , =null, =System.DBnull - all were rejected by compilation.
> Thanks again
> Rea
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:uAKFBp3YEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > The report will not execute unless you provide Default values in the
> Report
> > Parameters dialog. Without setting a default value on each and every
> > parameter, RS has no way of knowing what to use. It is unaware that your
> SP
> > has default values assigned.
> >
> > hth
> > fmi see www.sqlreportingservices.net
> >
> >
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > __________________________________
> >
> > "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> > news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > > Hi all
> > > Im using a stored procedure with in params as datasource to a report.
> > > These params are initialized to null by default in the procedure.
> > > When executing this datasource in the report designer's data tab
> > > every thing works fine - i can live these parameters with nulls (or
set
> > > other values) and the procedures return appropriate results.
> > > Then in the report parameters tab i allow these params to get null
> values,
> > > with no default value.
> > > But when executing the report in the 'preview' mode of visual studio i
> > must
> > > enter values for all params or else the report is not displayed at all
> > (not
> > > even with empty result set..).
> > > No error massage is displayed - just the default blank background..
> > > Thanks for your attention
> > > Rea
> > >
> > >
> >
> >
>|||Thanks i found a way:
Null values should be added to the parameter's result set/data source.
Then you allow null values for it and set for default value - none.
I hope =Nothing does not work because i spent some 2 hours figuring in
out...:))
Rea
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:ew$ZIAJZEHA.2016@.TK2MSFTNGP09.phx.gbl...
> =Nothing
> (according to Peter)
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:uip9OnAZEHA.1356@.TK2MSFTNGP09.phx.gbl...
> > ok then how do i set null values as default values for report's
> parameters'
> > i tried : null , =null, =System.DBnull - all were rejected by
compilation.
> > Thanks again
> > Rea
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> > news:uAKFBp3YEHA.1764@.TK2MSFTNGP10.phx.gbl...
> > > The report will not execute unless you provide Default values in the
> > Report
> > > Parameters dialog. Without setting a default value on each and every
> > > parameter, RS has no way of knowing what to use. It is unaware that
your
> > SP
> > > has default values assigned.
> > >
> > > hth
> > > fmi see www.sqlreportingservices.net
> > >
> > >
> > > --
> > > ____________________________________
> > > William (Bill) Vaughn
> > > Author, Mentor, Consultant
> > > Microsoft MVP
> > > www.betav.com
> > > Please reply only to the newsgroup so that others can benefit.
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > __________________________________
> > >
> > > "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> > > news:%23nq$JF3YEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > > > Hi all
> > > > Im using a stored procedure with in params as datasource to a
report.
> > > > These params are initialized to null by default in the procedure.
> > > > When executing this datasource in the report designer's data tab
> > > > every thing works fine - i can live these parameters with nulls (or
> set
> > > > other values) and the procedures return appropriate results.
> > > > Then in the report parameters tab i allow these params to get null
> > values,
> > > > with no default value.
> > > > But when executing the report in the 'preview' mode of visual studio
i
> > > must
> > > > enter values for all params or else the report is not displayed at
all
> > > (not
> > > > even with empty result set..).
> > > > No error massage is displayed - just the default blank background..
> > > > Thanks for your attention
> > > > Rea
> > > >
> > > >
> > >
> > >
> >
> >
>

Report Builder Filter Prompt...

Is there a way to pass a NULL to a filter prompt in report builder much like in report designer where it allows you to check or uncheck the NULL checkbox?

Thanks.

Report Builder only enables the NULL checkbox for a report parameter if the corresponding field can have NULL values (according to the Attribute.Nullable property in the report model). Are you seeing different behavior?|||This answered my questions. Thanks.

Report Builder Conditional IF Function Problem

Hi everyone,

I have created a view from the exisiting table to use in the report builder model and in the table whenever there is a null(when users does not enter a date for this particular field in the application) for datetime field either of these 2 ("1/1/1753" and "12/31/9999") dates are stored . Since I am creating my views based on these tables these dates will be in my views as well. Checking for this at the application level and cleaning up is not a option for me at this time.

So what I am trying to do is to check for these values and replace it with Null or blank in the model designer expression property. So that when user creates a report using this field they will not see these sql standard dates.

I tried using conditional IF in Model designer .

EX:

IF(Next MCR Review Date = 1/1/1753,EMPTY,Next MCR Review Date)

IF(Next MCR Review Date = 1/1/1753,NULL,Next MCR Review Date)

Both of these gives me errors.

Can anyone tell me what I am doing wrong and also are there any other ways to get to what I want.

I have already spent a lot of time digging for documentation but no luck any help is appreciated.

Thanks a Lot

Ashwini

I struggled with this for a while, it's a good question... and the model builder stuff really is a PITA.

Here is what I suggest: include the appropriate information in your view as an extra field and use that field instead of your "real" date" in your model.

In the view you can use something like

Code Snippet

CASE WHEN [Next MCR Review Date] = '1/1/1753' THEN NULL

ELSE [Next MCR Review Date] END AS ModelReviewDate

.. you can leave the original value in as a separate column in the view for additional purposes, comparison, etc.

>L<

|||

Thank you so much Lisa this works like a charm. I had tried this before but silly me I had not put the quotes '1/1/1753' like this Instead I was just typing it this way

Code Snippet

[Next MCR Review Date] = CASE TME.next_assessment_dt
WHEN 1/1/1753 THEN ' '

WHEN 12/31/9999 THEN ' '

ELSE TME.next_assessment_dt
END

Your code Snippet rang the bell Thanks again so much!!! You have made my day.

Ashwini

Report Builder and NULL values

Hi,

I am trying to create a report with Report Builder, using a simple data model residing on SQL2005 reporting server.

The problem that literally drives me crazy is that I can't find any way to handle NULL values in my report. Say, a table has 3 numeric columns, and I need to create a formula to make a sum of those 3 columns. This would be an ordinary sum for the same entity, not an aggregate. So I create a new formula with following body:

Field1 + Field2 + Field3

The problem is, each one of the 3 columns can have NULL values, and if any column actually is NULL, I end up with empty sum value, instead of having sum of those columns that do have values.

What I need is one of the following:

- a way to check for NULL values before summarizing, like ISNOTHING() in Report Designer

- a way to replace NULL values with empty values, like ISNULL() or COALESCE() in TSQL

- a way to tell the report to just convert NULLs to empty values, like in Crystal Reports.

I haven't been able to find any of the above in Report Builder. Please help me!

Denis

This is a nice usability feature request.

Right now you can do it by building the following expression:
if(Field1 = Empty, 0, Field1) + if(Field2 = Empty, 0, Field2) + ...

|||

Thank you Alexander, this worked like a charm.

I think that this Empty keyword should be mentioned in Report Builder help system, or in a KB article.

Best regards,

Denis

P.S. Please also read my another usability request that I will submit very shortly.

Report automatically runs with null params allowed. SSRS 2000

I see that other people have had a problem with this, but I never saw
a solution or a work around. Has anyone found a work around to this
issue? I want to allow nulls in all the parameters, but I don't want
the report to run by itself in Report Manager.
And does anyone know if this is fixed in SSRS 2005? We'll be
migrating to that later this year.
TIAOn Sep 17, 10:20 am, beener <rmp_c...@.hotmail.com> wrote:
> I see that other people have had a problem with this, but I never saw
> a solution or a work around. Has anyone found a work around to this
> issue? I want to allow nulls in all the parameters, but I don't want
> the report to run by itself in Report Manager.
> And does anyone know if this is fixed in SSRS 2005? We'll be
> migrating to that later this year.
> TIA
As far as I know, this is still the case in SSRS 2005. I've seen
several posts requesting this functionality. The only thing I can
think of is to have one of the parameters not have a default value.
Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Replication-Replication Distribution Subsystem: agent (null) failed.

After migrate SQL 2000 replication to SQL 2005, by script and have maked some
changes, like drop some publications and have added many articles in one,
this message has appered so often:
Replication-Replication Distribution Subsystem: agent (null) failed. The
publication '<name of publication>'does not exist.
The application log of distributor machine is always with this message like
this.
What is the real problem, because some articles don't exist any more and
there aren't any errors or mistakes of any agents, I mean , the replication
are ok.
Unfortely, I didn't see any articles or foruns about the solution and these
problems impact with Replication alerts.
I have one machine with three sql instances of distribution functions. All of
them are administering many publications with many articles.
Sds!
Krisnamourt
Message posted via http://www.droptable.com
This could be a pull subscriber. Run profiler to see if you can see it logon
to the distributor and then capture it's hostname and visit the host and
drop the subscription there.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7b995dc2c337c@.uwe...
> After migrate SQL 2000 replication to SQL 2005, by script and have maked
> some
> changes, like drop some publications and have added many articles in one,
> this message has appered so often:
> Replication-Replication Distribution Subsystem: agent (null) failed. The
> publication '<name of publication>'does not exist.
> The application log of distributor machine is always with this message
> like
> this.
> What is the real problem, because some articles don't exist any more and
> there aren't any errors or mistakes of any agents, I mean , the
> replication
> are ok.
> Unfortely, I didn't see any articles or foruns about the solution and
> these
> problems impact with Replication alerts.
> I have one machine with three sql instances of distribution functions. All
> of
> them are administering many publications with many articles.
> Sds!
> Krisnamourt
> --
> Message posted via http://www.droptable.com
>
|||Thanks Hilary,
I just dropped these agents and the pull jobs at subscriber and distributor,
but these message still appear at Distributor.
I thought these message would be some register from oldest row of
distribution table, but I couldn't identify any of them.
At moment, it's so difficult to "redo" all publication , because all of them
are at production replication, without any "windows time" to do quickly and
we just have done three weeks ago.
Any suggest to help me to find out where were all of these row register at
distribution system tables?
Sds!
Krisnamourt
Hilary Cotter wrote:[vbcol=seagreen]
>This could be a pull subscriber. Run profiler to see if you can see it logon
>to the distributor and then capture it's hostname and visit the host and
>drop the subscription there.
>[quoted text clipped - 24 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
|||I'd look to see if I can figure out when these agents are running or
logging. For example suppose you find they log these messages every 10
minutes. Then I would run profiler when you next expect it to run and see if
you can track down the culprit.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bcba7853b95b@.uwe...
> Thanks Hilary,
> I just dropped these agents and the pull jobs at subscriber and
> distributor,
> but these message still appear at Distributor.
> I thought these message would be some register from oldest row of
> distribution table, but I couldn't identify any of them.
> At moment, it's so difficult to "redo" all publication , because all of
> them
> are at production replication, without any "windows time" to do quickly
> and
> we just have done three weeks ago.
> Any suggest to help me to find out where were all of these row register at
> distribution system tables?
> Sds!
> Krisnamourt
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>
|||Hilary,
I just followed your advice and see that application "Replication
Distribution History" send these message.
How can I delete history informatiom without damage any replication?
See the sequence:
if @.publication is not null and
exists (select * from dbo.MSpublications
where publication = @.publication and
publisher_id = @.publisher_id and
publisher_db = @.publisher_db and
independent_agent = 0)
The subscription to publication 'Pub_T400RECL' has expired or does not exist.
RAISERROR (21056, 16, -1, @.publication)
return (1)
Hilary Cotter wrote:[vbcol=seagreen]
>I'd look to see if I can figure out when these agents are running or
>logging. For example suppose you find they log these messages every 10
>minutes. Then I would run profiler when you next expect it to run and see if
>you can track down the culprit.
>[quoted text clipped - 28 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
|||I am sorry look for the entries in mspublications, then drop it in that
database.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bcd3a3b13b7c@.uwe...
> Hilary,
> I just followed your advice and see that application "Replication
> Distribution History" send these message.
> How can I delete history informatiom without damage any replication?
> See the sequence:
> if @.publication is not null and
> exists (select * from dbo.MSpublications
> where publication = @.publication and
> publisher_id = @.publisher_id and
> publisher_db = @.publisher_db and
> independent_agent = 0)
> --
> The subscription to publication 'Pub_T400RECL' has expired or does not
> exist.
> --
> RAISERROR (21056, 16, -1, @.publication)
> --
> return (1)
> --
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>
|||look for an entry in the distribution database where the syspublications
table has entries for this publication.
Note the publisher and the publication database. Then go to it and drop the
publication
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bcd3a3b13b7c@.uwe...
> Hilary,
> I just followed your advice and see that application "Replication
> Distribution History" send these message.
> How can I delete history informatiom without damage any replication?
> See the sequence:
> if @.publication is not null and
> exists (select * from dbo.MSpublications
> where publication = @.publication and
> publisher_id = @.publisher_id and
> publisher_db = @.publisher_db and
> independent_agent = 0)
> --
> The subscription to publication 'Pub_T400RECL' has expired or does not
> exist.
> --
> RAISERROR (21056, 16, -1, @.publication)
> --
> return (1)
> --
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>
|||Hilary,
I did what you say to do, but I wasn't finding any register at distributor
tables that refer to any of these publication that no exists any more.
are there any kind of multiple register on Monitor replication? I mean, now I
have many Publication to see in only one SQL 2005 Monitor. When I add some
new publication, the Monitor connect to your distribution instance. Speaking
in numbers, It's about 6 distribution connect together in one Monitor.
What do you think? I can't imagine any other suggest to these problem!
Sds!
Hilary Cotter wrote:[vbcol=seagreen]
>I am sorry look for the entries in mspublications, then drop it in that
>database.
>[quoted text clipped - 29 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
|||Can you check each database on the publisher for these entries?
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Krisnamourt via droptable.com" <u21487@.uwe> wrote in message
news:7bda14a7e07c4@.uwe...
> Hilary,
> I did what you say to do, but I wasn't finding any register at distributor
> tables that refer to any of these publication that no exists any more.
> are there any kind of multiple register on Monitor replication? I mean,
> now I
> have many Publication to see in only one SQL 2005 Monitor. When I add some
> new publication, the Monitor connect to your distribution instance.
> Speaking
> in numbers, It's about 6 distribution connect together in one Monitor.
> What do you think? I can't imagine any other suggest to these problem!
> Sds!
> Hilary Cotter wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server-replication/200711/1
>