Replacing Nulls

I have a query that has serveral Left Joins. Obviously when there are no
results in the right tables a value of NULL is returned. When creating an
MS Reporting Services report, the tables seem to ignore the NULL values
(because they aren't recognized as real values I assume). How do I alter my
query to replace a NULL value with an actually value, such as 999 so I can
count the "NULL" results?
I appreciate any help.
Thankstry ISNULL function
ISNULL(<fieldname>, 999) AS SomeThing
"Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>I have a query that has serveral Left Joins. Obviously when there are no
>results in the right tables a value of NULL is returned. When creating an
>MS Reporting Services report, the tables seem to ignore the NULL values
>(because they aren't recognized as real values I assume). How do I alter
>my query to replace a NULL value with an actually value, such as 999 so I
>can count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Thanks for the reply SharkSpeed. That makes sense, but where would I put
it?
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:eqqHn8e9FHA.1020@.TK2MSFTNGP15.phx.gbl...
> try ISNULL function
> ISNULL(<fieldname>, 999) AS SomeThing
>
> "Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
> yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>|||Hi Amon
Have you tried the ISNULL([ColumnName], [NewValue]) function?
Lucas
"Amon Borland" wrote:

> I have a query that has serveral Left Joins. Obviously when there are no
> results in the right tables a value of NULL is returned. When creating an
> MS Reporting Services report, the tables seem to ignore the NULL values
> (because they aren't recognized as real values I assume). How do I alter
my
> query to replace a NULL value with an actually value, such as 999 so I can
> count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Lucas, where would I use this at. In the Select or after the table in the
join?
Thanks
"Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote in
message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
> Hi Amon
> Have you tried the ISNULL([ColumnName], [NewValue]) function?
> Lucas
> "Amon Borland" wrote:
>|||in the Select I assume..
SELECT a, b, n FROM table LEFT JOIN SELECT x, y, z FROM table2 ON ...
becomes
SELECT a, b, ISNULL(n, 999) AS n FROM table LEFT JOIN SELECT x, y, ISNULL(z,
999) FROM table2 ...
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:eZMs3Mf9FHA.220@.TK2MSFTNGP14.phx.gbl...
> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
> in message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>|||You can use it on the Select part of your SQL Statement. For example:
SELECT [ColumnName1], ISNULL([ColumnName2], [NewValue])
FROM [TableName]
Lucas
"Amon Borland" wrote:

> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
in
> message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>
>

Replace report query

I design a report template which can serve for serveral reports. The
template and parameter are same, but the difference is 'select' statment. I
have another web application to retrieve different reports. Can I
dynamically change the report query by calling report web service ? I
can't find any example in RS books online. Is there any other example ?Hi May,
can you use a stored procedure and handle the logic internally (making the
results parameter driven)? That way your report is always pointing to the
same database object.
regards
Matt
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> I design a report template which can serve for serveral reports. The
> template and parameter are same, but the difference is 'select' statment.
I
> have another web application to retrieve different reports. Can I
> dynamically change the report query by calling report web service ? I
> can't find any example in RS books online. Is there any other example ?|||This is one way of doing it. Alternatively, you could:
1. Call SetReportDefinition SOAP API to update the report query.
2. Use an expression-based query. For example, you can check User!UserId and
if it gives you the web app identity change the report query respectively.
Or, you could use parameters to control your query statement.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> Hi May,
> can you use a stored procedure and handle the logic internally (making the
> results parameter driven)? That way your report is always pointing to the
> same database object.
> regards
> Matt
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > I design a report template which can serve for serveral reports. The
> > template and parameter are same, but the difference is 'select'
statment.
> I
> > have another web application to retrieve different reports. Can I
> > dynamically change the report query by calling report web service ? I
> > can't find any example in RS books online. Is there any other example
?
>|||Is there any example ?
Thanks !!!
"Teo Lachev" wrote:
> This is one way of doing it. Alternatively, you could:
> 1. Call SetReportDefinition SOAP API to update the report query.
> 2. Use an expression-based query. For example, you can check User!UserId and
> if it gives you the web app identity change the report query respectively.
> Or, you could use parameters to control your query statement.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > Hi May,
> >
> > can you use a stored procedure and handle the logic internally (making the
> > results parameter driven)? That way your report is always pointing to the
> > same database object.
> >
> > regards
> >
> > Matt
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > I design a report template which can serve for serveral reports. The
> > > template and parameter are same, but the difference is 'select'
> statment.
> > I
> > > have another web application to retrieve different reports. Can I
> > > dynamically change the report query by calling report web service ? I
> > > can't find any example in RS books online. Is there any other example
> ?
> >
> >
>
>|||Anyone can help me ? I search this topic in the internet but there is no
related result. This problem annoy me so much and I have so many reports
that will be implemented using this approach.
Thanks a lot !!!
"May Liu" wrote:
> Is there any example ?
> Thanks !!!
> "Teo Lachev" wrote:
> > This is one way of doing it. Alternatively, you could:
> >
> > 1. Call SetReportDefinition SOAP API to update the report query.
> > 2. Use an expression-based query. For example, you can check User!UserId and
> > if it gives you the web app identity change the report query respectively.
> > Or, you could use parameters to control your query statement.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > > Hi May,
> > >
> > > can you use a stored procedure and handle the logic internally (making the
> > > results parameter driven)? That way your report is always pointing to the
> > > same database object.
> > >
> > > regards
> > >
> > > Matt
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > > I design a report template which can serve for serveral reports. The
> > > > template and parameter are same, but the difference is 'select'
> > statment.
> > > I
> > > > have another web application to retrieve different reports. Can I
> > > > dynamically change the report query by calling report web service ? I
> > > > can't find any example in RS books online. Is there any other example
> > ?
> > >
> > >
> >
> >
> >