How to get this output

Hi evrybody
i have a question like this
i need to display the row data like this
If i use this sql
select datediff(yyyy,'01/01/2001','01/01/2004')
i get the output = 4

problem is sometime first parameter will be null
like this
select datediff(yyyy,NULL,'01/01/2004'),'N/A')
in that case my result looks like NULL

But what i need to see is if the result is null then i need to display like this N/A

How to do this task ?
regards
suis

Hey,

You can use the isnull method, so that if a value is null, it will return another value, like 'N/A' in this case. Though I don't know how that could work in a datediff method. Rather, you may want to wrap the isnull around the entire datediff call, so that if it returns null, you can return 'N/A'.

|||Hi i already used isnull
like this way
select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')
but it giving me error !

"Conversion failed when converting the varchar value 'N/A' to data type int."
regards
suis|||

Try changing this:

select isnull(datediff(yyyy,NULL,'01/01/2004'),'N/A')

to

select isnull(convert(varchar(4), datediff(yyyy,NULL,'01/01/2004')),'N/A')

|||Hi Kent
Thanks for ur comments,
its worked out
regards
suis

sql

How to get the results when executing extended stored procedures.

Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.

|_N_T_|

See if the following works for you:

create table #FreeSpace(

Drive char(1),

MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

select * from #freespace

|||

For some reason, that didn't work for me but this did:

Declare @.FreeSpace TABLE (

Drive char(1),

MB_Free int)

insert into @.FreeSpace exec xp_fixeddrives

select * from @.freespace

Go figure...

How to get the results when executing extended stored procedures.

Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.

|_N_T_|

See if the following works for you:

create table #FreeSpace(

Drive char(1),

MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

select * from #freespace

|||

For some reason, that didn't work for me but this did:

Declare @.FreeSpace TABLE (

Drive char(1),

MB_Free int)

insert into @.FreeSpace exec xp_fixeddrives

select * from @.freespace

Go figure...

how to get the primary keys and display it horizontally?

sample results: <MRNID=10002><VesselCode=VSL1>

wherein MRNID and VesselCode are primary keys of the table.

What do you mean by "horozontally"

<MRNID=10002><VesselCode=VSL1>

This is horozontally.

<MRNID=10002>
<VesselCode=VSL1>

Would be vertical. Can you be a bit more clear?

|||

do you mean pivoting the table

well sql 2k5 has a pivot keyword

How to get the parameter type in VB.net?

I am currently using the following embedded code to get the selected values from a Parameter and display in a textbox. I however have a number of reports and I don't want to include this in all of them. I have created a dll with some other functions but when i try to include the following function it throws an error on the Parameter type. What am I doing wrong, I am not all that familiar with dot net?

Public Function ShowParameterValues(ByVal parameter as Parameter) as String
Dim s as String

If parameter.count <= 5 then
If parameter.IsMultiValue and parameter.count > 1 then
s = " "
For i as integer = 0 to parameter.Count-1
if i = parameter.count - 1
s = s + CStr(parameter.Label(i))
else
s = s + CStr(parameter.Label(i)) + ", "
end if

Next
Else
s = " " + CStr(parameter.Label(0))
End If
else
s = " Only a maximum of 5 selection values can be displayed. "
End if
Return s
End Function

What's the error message?|||I am sorry..it says the type Parameter is not defined.|||

A multi-valued parameter is a 0-based array of objects, I'm pretty sure. But you should be passing the *name* of the parameter you want to reference, as a string, not the object. No?

Then you can say Parameters(tsParamName).Label(i) and Parameters(tsParamName).Count in your code...

HTH,

>L<

|||

It simply means it cannot find the type Parameter, either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

In your code,you are passing parameter as Parameter in the signature of the function, this is where it's complaining, it cannot find the Assembly where the type Parameter is defined.

|||

>>either you need to reference the dll that contains the definition of the type Parameter or make to sure to include the CS file where that type Parameter is defined.

Jonel, I honestly don't think that's correct... rather than including the reference to Parameter, I think the actual type he would need is ReportParameter here ... but in this case he could actually be passing the name of the parameter from the textbox expression (or wherever he's using the function), as I said...

>L<

|||

Hi, Lisa,

From within a custom assembly, you need to provide a fully qualified reference for the parameters type:

Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Parameters

If you use this reference, then when you install new releases of Reporting Services, you may need to recompile and redeploy the custom assembly.

|||

My apologies, Mary, Jonel, and everybody reading -- I misread the original post (saw the "embedded code" part but not that the poster was moving the code into a DLL!). Had a braino...

>L<

|||Thanks guys for your responses. After searching the net last week I found the answer. Thanks much for all your help.

How to get the Columns list to the end user?

Hello Nilay here,

How to display the coulumn list of the reports to the end user ?

Thanks,I don't understand the question. Is it

a) How to prompt the user for the columns he/she wants to display, or
b) How to display a column containing a list of reports?|||Hi, I want to show the All coumns, which are in the report, to the end user. So user can select his usefull columns and the selected coulmn should only show on the report and other should be hide.

Regards,
Nilay|||

In SQL Server 2005, you can use a multi-value parameter to prompt the user. Then you can write an expression/custom function to parse the output of the parameter. Call that expression/custom function in the visibility expression for the column in your table/matrix.

You'll need to make sure the choices in your parameter list are are kept in sync with your fields list, that's something that we don't provide by default.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

How to get the Columns list to the end user?

Hello Nilay here,

How to display the coulumn list of the reports to the end user ?

Thanks,I don't understand the question. Is it

a) How to prompt the user for the columns he/she wants to display, or
b) How to display a column containing a list of reports?|||Hi, I want to show the All coumns, which are in the report, to the end user. So user can select his usefull columns and the selected coulmn should only show on the report and other should be hide.

Regards,
Nilay|||

In SQL Server 2005, you can use a multi-value parameter to prompt the user. Then you can write an expression/custom function to parse the output of the parameter. Call that expression/custom function in the visibility expression for the column in your table/matrix.

You'll need to make sure the choices in your parameter list are are kept in sync with your fields list, that's something that we don't provide by default.

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

sql

How to Get the 2nd the 2nd Record AND DISPLAY IN SINGLE ROW ?

Can you please assist me on how to get the 2nd record in case there are

3 or more records of an employee, the query below gets the MAX and MIN
BasicSalary. However, my MIN Basic Salary is wrong because I should get

the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
more records and not the last Basic Salary of the Last Record.

How to GET the 2nd Row of Record in Case that There are 3 or more
records IN A SINGLE ROW ?

-----------------------*--

This query gets the Max and Min Basic Salary on a certain Date Range.
In case there are 5 records of an employee on certain date range how
can I get the record before the Max and would reflect as my OLDBASIC,
if I use TOP2 DESC it will display 2 records. I only need one record
which should be the Basic Salary before the 1st record on a DESC order.

Please add the solution to my 2nd Select Statement which get the
OLDBASIC salary Thanks ...

SELECT TOP 100 PERCENT E.EmployeeNo, E.LastName, E.FirstName,
E.SectionCode, E.Department, E.DateHired, E.Remarks,

(SELECT TOP 1 ([BasicSalary])
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate DESC) AS NEWBASIC,

******************************* BELOW I SHOULD ALWAYS GET THE BASIC
SALARY PRIOR TO THE 1ST RECORD AND IN A SINGLE ROW ?

(SELECT TOP 1 ([BasicSalary]) (
FROM empsalaries AS T14
WHERE T14.employeeno = E.employeeno AND startdate BETWEEN @.FromDate AND

@.ToDate
ORDER BY startdate ASC) AS OLDBASIC

FROM dbo.Employees E
WHERE CONVERT(VARCHAR(10),E.DateHired, 101) BETWEEN @.FromDate AND
@.ToDate
ORDER BY E.LastNameheri (heri.carandang@.acspacific.com) writes:
> Can you please assist me on how to get the 2nd record in case there are
> 3 or more records of an employee, the query below gets the MAX and MIN
> BasicSalary. However, my MIN Basic Salary is wrong because I should get
> the Basic Salary Prior to the 1st Record (DESC)in case there are 3 or
> more records and not the last Basic Salary of the Last Record.
>
> How to GET the 2nd Row of Record in Case that There are 3 or more
> records IN A SINGLE ROW ?

SELECT TOP 1 val
FROM (SELECT TOP 2 val
FROM tbl
ORDER BY val DESC) AS x
ORDER BY val ASC

Gives you the second highest value of val.

If you want to do this for a set values, this may be more practical:

SELECT s.empid, secondest = MAX(s.salary)
FROM salaries s
JOIN (SELECT empid, maxsalary = MAX(salary)
FROM salaries
GROUP BY empid) AS m ON s.empid = m.empid
WHERE s.salary < m.maxsalary

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||or

Select min(col) from
(
select top N col from table Order by col DESC
) T

Madhivanan

how to get report by Month

hi.
i used vb 2005 and CR 11
now .i want to display Report By Month ( from input textbox )
how can i ?What do you mean by report by month?

Do you want to display report in the format Jan, Feb, March ... or you want display data for particular month?|||u can provide parameters field as date time

suppose u define startdate and enddate

and then

report menu select edit selection formula give the range of that field as

currentdate <= startdate and currentdate >= enddate

so wenevr u execute the report it will ask for the range and u can select

Hope this is beneficial for u

With Best Regards
Ruby

How to get non-html format data to open in a new window

I am totally new to reporting services and am struggling with how to
accomplish something. I have an aspx page that needs to display the
html format version of a report on the bottom portion of the page and
then provide a drop down list from which the user can select another
format and an export button. When the user clicks a new format (pdf,
csv, etc) and then the export button, I need for the report to pop open
in a new window. I have succeeded in getting the report to come out on
the bottom part of the page but have no idea how to get the other
formats to pop open in a new page. I am coding in vb - can someone
point me in the right direction.You could use window.open(URL) method to open in a new window
"lisa.bogart@.eds.com" wrote:
> I am totally new to reporting services and am struggling with how to
> accomplish something. I have an aspx page that needs to display the
> html format version of a report on the bottom portion of the page and
> then provide a drop down list from which the user can select another
> format and an export button. When the user clicks a new format (pdf,
> csv, etc) and then the export button, I need for the report to pop open
> in a new window. I have succeeded in getting the report to come out on
> the bottom part of the page but have no idea how to get the other
> formats to pop open in a new page. I am coding in vb - can someone
> point me in the right direction.
>

how to get driopdown for year

how can i display the dropdown for year values in sql reporting

i want to accept the value for year as parameter & also want user to select the value from dropdown that will display only the year values.

What product are you using?

Can you give more details about your situation.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.