Need to add expression when subreport returns NULL value

Help! @.=)
Using SRS 2000 SP2. I have a subreport which only returns a value to the
main report some of the time. At other times, it returns nothing (usually in
situations where the result would be NULL). In the main report, when it
returns NULL/Blank/Nothing, I want to that space to display "There was no
value for this query" or something similar. Unfortunately, when I
right-click the textbox containing the subreport, it doesn't give me an
expressions field.
Where could I put something like this? Or is this not something that can be
done?
Thanks in advance!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)Click the table in your report and look for a property value called NoRows.
Add text to that, and it will be displayed in your main report.
Kaisa M. Lindahl Lervik
"Catadmin" <goldpetalgraphics@.yahoo.com> wrote in message
news:CBF33AD2-95A4-4BDC-951A-D108AB2FE33E@.microsoft.com...
> Help! @.=)
> Using SRS 2000 SP2. I have a subreport which only returns a value to the
> main report some of the time. At other times, it returns nothing (usually
> in
> situations where the result would be NULL). In the main report, when it
> returns NULL/Blank/Nothing, I want to that space to display "There was no
> value for this query" or something similar. Unfortunately, when I
> right-click the textbox containing the subreport, it doesn't give me an
> expressions field.
> Where could I put something like this? Or is this not something that can
> be
> done?
> Thanks in advance!
> Catadmin
> --
> MCDBA, MCSA
> Random Thoughts: If a person is Microsoft Certified, does that mean that
> Microsoft pays the bills for the funny white jackets that tie in the
> back?
> @.=)|||AHA! That's exactly what I was looking for. Thank you ever so much!!!
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)

Need stored procedure that shows one value for null and another for not null

I have a stored sprocedure with two parameters that currently searches a table for a matching record see SQL below:

**************************************************************

@.WorkOrderNum numeric(18,0)

,@.StackNum numeric(18,0)

AS

--This procedure is used in iFIX. It looks for matching

--Work Order Number / Stack Number combination.

SELECT

work_order_no

,stack_no

FROM

dbo.prod_data

WHERE

(dbo.prod_data.work_order_no = @.WorkOrderNum)

AND (dbo.prod_data.stack_no = @.StackNum)

******************************************************************************

What I need is a stored procedure that will look for a matching criteria and if it finds some it will return a value of "2", if it does not find any criteria it needs to return a value of "1". This value does not need to be stored, for display only.

Any help would be greatly appreciated

You could do below:

Code Snippet

select case when exists(

SELECT *

FROM

dbo.prod_data as p

WHERE

p.work_order_no = @.WorkOrderNum

AND p.stack_no = @.StackNum
) then 2 else 1 end as matched

|||

Thanks it worked perfectly

Need script help w/ dates

Hi,

I have a script where I retrieve the date portion of a filename into an integer variable.

The variable value looks like this: "20070815"

My question is, how can I then take this value and compare it against the current date on the server? Is there any sample code that can show me how to use date functions in this way?

Thanks

You should be able to direclty compare in the query.Something like this for example.

IF GETDATE() > '20070815'

BEGIN

PRINT 'More'

END

<edit>

In query it would be like

select col1,col2 from tbla where dt_col > '20070815'

</edit>

Thanks

|||In your script, you can do:

Dim todaysDate As String = DateTime.Now.ToString("yyyyMMdd")

Then you can just compare to your input date.|||I noticed you said "integer" variable above. If so, then this should work:

Dim todaysDate as Integer = CInt(DateTime.Now.ToString("yyyyMMdd"))|||

Thanks Phil. This should do the trick.

need quick help with a sql query

this query works, i want to add a 4th column that is the value of the 3rd column subracted from the value of the 2nd column, how can i add this??

SELECT `tagid` AS w1, (

SELECT count( `value` )
FROM tags
WHERE `value` =1
AND `tagid` = w1
) AS w2, (

SELECT count( `value` )
FROM tags
WHERE `value` =0
AND `tagid` = w1
) AS w3
FROM tags
WHERE `value` > -1
GROUP BY `tagid`
ORDER BY w2 DESC

you cant reference those columns can you? like this

well you can do another subquery like you already did and then add all the logic to get columns 2 and 3 then there you go

SELECT `tagid` AS w1, (SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) AS w2,
(SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) AS w3,
((SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) -(SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) ) as w4
FROM tags
WHERE `value` > -1
GROUP BY `tagid`
ORDER BY w2 DESC

need help. need to check a value inside a sql statement

What i need to do is check a field inside a sql statement to see if it is null and if it is then change the value to a 0.

for example

select Field1,field2,field3(if null then =0)
from table1

does this make sense. I think in oracle PL/SQL is uses decode(field3, null... or something like that

any help would be appreciated

thanksYou can use either the coalesce function which takes the first non-null value in a list, or the isnull function. I understand coalesce is more ansi-sql friendly, and I believe is implemented in Oracle as well.|||Example:

select Field1, field2, isnull(field3, 0) from table1

or

select Field1, field2, coalesce(field3, 0) from table1

blindman|||I am trying to put this in a MS access query

My statement look like this

Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....

I get an error message saying

" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"

thanks|||I am trying to put this in a MS access query

My statement look like this

Select Table_Name.Field1, Table_name.Field2, ISNULL(Table_Name.Field3,0),Table_Name.field4
from Table, Table
where....

I get an error message saying

" wrong number of arguments used with function in query expression
ISNULL(LDSSHLRN_LDMCH1.QMCHRS,0"

thanks|||MS Access does not use the same exact same function list as SQL Server. The ISNULL function in MS Access takes a single parameter and returns a boolean value indicating whether the parameter was null.

The MSAccess function you are looking for is "NZ", short for Null to Zero.

Select Table_Name.Field1, Table_name.Field2, NZ(Table_Name.Field3,0),Table_Name.field4
from Table, Table

MS Access SQL is hybridized with VB.

blindman

Need Help! New to reporting services

I need to populate a detail cell in a table report with 0 if the value of a
group cell in the report is > 500000. I've created on iif statement and it
works great when pulling data into the cell from the query. However, I need
to know how to call the value of a specific table cell into the iif statement.
=iif(Value of group cell in table report > 500000, 0,
iif(Fields!AssetCost.Value < 5000, "0",
iif(Fields!AssetCost.value <= 100000, fields!AssetCost.Value*.01, "0")))
SamyraIIf(ReportItems!YourItem.Value>500000,0,False)
Michael
"Samyra" wrote:
> I need to populate a detail cell in a table report with 0 if the value of a
> group cell in the report is > 500000. I've created on iif statement and it
> works great when pulling data into the cell from the query. However, I need
> to know how to call the value of a specific table cell into the iif statement.
> =iif(Value of group cell in table report > 500000, 0,
> iif(Fields!AssetCost.Value < 5000, "0",
> iif(Fields!AssetCost.value <= 100000, fields!AssetCost.Value*.01, "0")))
> Samyra