Displaying the output

Hi,
I want to write a query such that part of the output is displayed and the
complete output is put into a temp table.
For example i have a table customer and i want the total customer details to
be put in a temp table and the top 5 to be displayed.
select * into #temp1 from customer
select top5 * from customer
I run this two queries for my result, but is there any way to get the result
in one query.
Thanks,
MaheshMahesh wrote:
> Hi,
> I want to write a query such that part of the output is displayed
> and the complete output is put into a temp table.
> For example i have a table customer and i want the total customer
> details to be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result in one query.
> Thanks,
> Mahesh
No. You are talking about two different queries. You need to use an
ORDER BY with the TOP clause, unless you want somewhat random data
returned.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi David,
My requirement is when i run a query it should go to temp table (e.g.
#temp1) and also display the output. This should happen on a single run of
query. Can you please help me in this.
Thanks,
Mahesh
"David Gugick" wrote:

> Mahesh wrote:
> No. You are talking about two different queries. You need to use an
> ORDER BY with the TOP clause, unless you want somewhat random data
> returned.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Mahesh
can you explain why you want to do that.
however it appears to be possible with functoon or procedure
--procedure
CREATE PROC DISPLAY
AS
select * into #temp1 from customer
select top5 * from customer ORDER BY CUSTOMER_ID
RETURN
GO
EXEC DISPLAY
GO
--function: you can't use temp table here but table variable
CREATE FUNCTION DISPLAYFUNCTION()
RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
AS
BEGIN
INSERT INTO @.TOPTABLE
SELECT * FROM EMPLOYEES
RETURN
END
GO
SELECT TOP 5 * FROM DISPLAYFUNCTION()
GO
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:

> Hi,
> I want to write a query such that part of the output is displayed and t
he
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the resu
lt
> in one query.
> Thanks,
> Mahesh|||space between top and 5 is missing; TOP 5
--
Regards
R.D
--Knowledge gets doubled when shared
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||RD, this is requires because i will be running multiple queries to analyze
the data, while i am running i would like to see the data and then when at
the end if it is stored in a temp table then i can run a simple select on
that temp table instead of running the whole query again. can you please hel
p
me on this.
Thanks,
Mahesh
"R.D" wrote:
> Mahesh
> can you explain why you want to do that.
> however it appears to be possible with functoon or procedure
> --procedure
> CREATE PROC DISPLAY
> AS
> select * into #temp1 from customer
> select top5 * from customer ORDER BY CUSTOMER_ID
> RETURN
> GO
> EXEC DISPLAY
> GO
>
> --function: you can't use temp table here but table variable
> CREATE FUNCTION DISPLAYFUNCTION()
> RETURNS @.TOPTABLE TABLE(EName varchar(100), mgid int)
> AS
> BEGIN
> INSERT INTO @.TOPTABLE
> SELECT * FROM EMPLOYEES
> RETURN
> END
> GO
> SELECT TOP 5 * FROM DISPLAYFUNCTION()
> GO
>
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Mahesh" wrote:
>|||David already answered. One query cannot both store the results in a temp ta
ble *and* display the
results. It is one of those, but not both. How about pushing all data into t
he temp table and then
SELECT TOP 5 from there?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mahesh" <Mahesh@.discussions.microsoft.com> wrote in message
news:E23898C7-8D15-492B-9CDE-0CFE24AC44CA@.microsoft.com...
> Hi David,
> My requirement is when i run a query it should go to temp table (e.g.
> #temp1) and also display the output. This should happen on a single run of
> query. Can you please help me in this.
> Thanks,
> Mahesh
> "David Gugick" wrote:
>|||Either you got to use sprocs or use two queries at a time with GO in betwee
n
--
Regards
R.D
--Knowledge gets doubled when shared
"Mahesh" wrote:
> RD, this is requires because i will be running multiple queries to analyze
> the data, while i am running i would like to see the data and then when at
> the end if it is stored in a temp table then i can run a simple select on
> that temp table instead of running the whole query again. can you please h
elp
> me on this.
> Thanks,
> Mahesh
> "R.D" wrote:
>|||Hi,
Try this. I did it with the northwind DB
select * into #temp1 from customers where customerid in (select top 5
customerid from customers)
Nils
"Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
> Hi,
> I want to write a query such that part of the output is displayed and
> the
> complete output is put into a temp table.
> For example i have a table customer and i want the total customer details
> to
> be put in a temp table and the top 5 to be displayed.
> select * into #temp1 from customer
> select top5 * from customer
> I run this two queries for my result, but is there any way to get the
> result
> in one query.
> Thanks,
> Mahesh|||nils
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
this inserts only five rows while Mahesh wants all rows to temp and five
rows to be displayed.
--
Regards
R.D
--Knowledge gets doubled when shared
"Nils Wolf" wrote:

> Hi,
> Try this. I did it with the northwind DB
> select * into #temp1 from customers where customerid in (select top 5
> customerid from customers)
> Nils
>
> "Mahesh" <Mahesh@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:1E806702-EB01-459C-AE86-B9E873C8DA22@.microsoft.com...
>

Displaying NULLS but not Blanks

Hi,
I am using the followng query:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
ORDER BY BRAND ASC
In all the data displayed by this query, there is also a Null value and a
Blank value. Is there any way, I can display the null value but not the blan
k
value?
--
pmudTry:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND BRAND <> ''
ORDER BY BRAND ASC
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:AC2C98C0-9DCD-430C-BA97-A50C60FEEE6C@.microsoft.com...
Hi,
I am using the followng query:
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
ORDER BY BRAND ASC
In all the data displayed by this query, there is also a Null value and a
Blank value. Is there any way, I can display the null value but not the
blank
value?
--
pmud|||SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T' AND BRAND <> ''
ORDER BY BRAND ASC
"pmud" wrote:

> Hi,
> I am using the followng query:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T'
> ORDER BY BRAND ASC
> In all the data displayed by this query, there is also a Null value and a
> Blank value. Is there any way, I can display the null value but not the bl
ank
> value?
> --
> pmud|||Hi,
I have already tried that. It doesnt even display the NULL value. I wabt to
display those values where the data is <NULL> , but not those where the fiel
d
is blank.. i mean where the cell is completely blank.
Any other ideas?
Thanks
--
pmud
"KH" wrote:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T' AND BRAND <> ''
> ORDER BY BRAND ASC
> "pmud" wrote:
>|||Please post your DDL with INSERT statements of the sample data and desired
output.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:9D434774-65A1-4C29-8415-23309F77BF08@.microsoft.com...
Hi,
I have already tried that. It doesnt even display the NULL value. I wabt to
display those values where the data is <NULL> , but not those where the
field
is blank.. i mean where the cell is completely blank.
Any other ideas?
Thanks
--
pmud
"KH" wrote:
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T' AND BRAND <> ''
> ORDER BY BRAND ASC
> "pmud" wrote:
>|||Try
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND COALESCE(BRAND,'this value is null') <> ''
ORDER BY BRAND ASC
HTH,
Stu|||Try this also
SELECT DISTINCT BRAND
FROM ITEMS
WHERE ACTIVE ='T'
AND ltrim(rtrim(BRAND)) <> ''
ORDER BY BRAND ASC|||Hi Stu,
This works. :)) can you please explain how it works...
Thanks
--
pmud
"Stu" wrote:

> Try
> SELECT DISTINCT BRAND
> FROM ITEMS
> WHERE ACTIVE ='T'
> AND COALESCE(BRAND,'this value is null') <> ''
> ORDER BY BRAND ASC
> HTH,
> Stu
>|||Sure!
COALESCE looks at a given list of values or columns, and returns the
first NON-null expression, so for every row it looks at BRAND to
determine if it's null, ad if it's not then it returns the absolute
expression 'this value is null'. You could have put any absolute
expression in there (e.g., 'whats up Doc?'), and it would have returned
that value when it encountered a NULL in the BRAND column. This
allowed SQL Server to compare a replacement value ('this value is
null') with an empty string, instead of comparing a NULL value to an
empty string, eg.
NULL <> '' = NULL; not included in the result set
'this value is null' <> '' = TRUE; included in the result set
Since the COALESCE was in the WHERE clause, and not the SELECT clause,
the SELECT returned the value where the second expression in the
COALESCE statement was used (NULL).
Boy, I read that 5 times, and I don't think I can make it any clearer.
Perhaps someone else can explain it better than I; I just know how to
make it work :)
Stu|||Hi Stu,
Thanks for taking the time to explain it. it was helpful. Combined with what
you explained, I read on Coalsce and I understand it now. :)) .. I am sure
had I read it without u explaining it, I would have never understod.
Thanks
--
pmud
"Stu" wrote:

> Sure!
> COALESCE looks at a given list of values or columns, and returns the
> first NON-null expression, so for every row it looks at BRAND to
> determine if it's null, ad if it's not then it returns the absolute
> expression 'this value is null'. You could have put any absolute
> expression in there (e.g., 'whats up Doc?'), and it would have returned
> that value when it encountered a NULL in the BRAND column. This
> allowed SQL Server to compare a replacement value ('this value is
> null') with an empty string, instead of comparing a NULL value to an
> empty string, eg.
> NULL <> '' = NULL; not included in the result set
> 'this value is null' <> '' = TRUE; included in the result set
> Since the COALESCE was in the WHERE clause, and not the SELECT clause,
> the SELECT returned the value where the second expression in the
> COALESCE statement was used (NULL).
> Boy, I read that 5 times, and I don't think I can make it any clearer.
> Perhaps someone else can explain it better than I; I just know how to
> make it work :)
> Stu
>

Displaying months with no data

Hi all,
Does anyone have any thoughts on hard coding values into a result set
displayed in a grid? I have a dataset that I've built a bunch of grids
from in RS 2005. One of the reports I need to create always needs to
have all of the months of 2006 displayed regardless of whether there's
data. If there's no data for say July 2006, I get the following:
-Thanks!
Completed Programs
January 2006 1
February 2006 2
October 2006 3
November 2006 4
December 2006 233
I need
Completed Programs
January 2006 1
February 2006 2
March 2006 0
April 2006 0
May 2006 0
June 2006 0
July 2006 0
August 2006 0
September 2006 0
October 2006 3
November 2006 4
December 2006 233Hi,
What ever you want to display through report see that you bring it through
query first. if then try command from reporting server. In your case, from
query you can easily bring 0 value for the month which has no data. If not
then try in your grid
=IIF(<month> Is nothing, 0, month) some thing like this.
Regards
Amarnath
"bassunddrum@.gmail.com" wrote:
> Hi all,
> Does anyone have any thoughts on hard coding values into a result set
> displayed in a grid? I have a dataset that I've built a bunch of grids
> from in RS 2005. One of the reports I need to create always needs to
> have all of the months of 2006 displayed regardless of whether there's
> data. If there's no data for say July 2006, I get the following:
> -Thanks!
> Completed Programs
> January 2006 1
> February 2006 2
> October 2006 3
> November 2006 4
> December 2006 233
> I need
> Completed Programs
> January 2006 1
> February 2006 2
> March 2006 0
> April 2006 0
> May 2006 0
> June 2006 0
> July 2006 0
> August 2006 0
> September 2006 0
> October 2006 3
> November 2006 4
> December 2006 233
>|||You need to have the values in the dataset somehow, so if you can I would
create a Month table in the database which you can join to to provide the
complete list. If you can't do that then see if you can do a Stored
Procedure, then you can populate a memory table and return that instead.
Craig
<bassunddrum@.gmail.com> wrote in message
news:1140151459.214316.219040@.o13g2000cwo.googlegroups.com...
> Hi all,
> Does anyone have any thoughts on hard coding values into a result set
> displayed in a grid? I have a dataset that I've built a bunch of grids
> from in RS 2005. One of the reports I need to create always needs to
> have all of the months of 2006 displayed regardless of whether there's
> data. If there's no data for say July 2006, I get the following:
> -Thanks!
> Completed Programs
> January 2006 1
> February 2006 2
> October 2006 3
> November 2006 4
> December 2006 233
> I need
> Completed Programs
> January 2006 1
> February 2006 2
> March 2006 0
> April 2006 0
> May 2006 0
> June 2006 0
> July 2006 0
> August 2006 0
> September 2006 0
> October 2006 3
> November 2006 4
> December 2006 233
>|||If your completely stuck give me your SQL statement and I can add in to it
the extra months using unions.
Craig
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:%23tHX4u4MGHA.916@.TK2MSFTNGP10.phx.gbl...
> You need to have the values in the dataset somehow, so if you can I would
> create a Month table in the database which you can join to to provide the
> complete list. If you can't do that then see if you can do a Stored
> Procedure, then you can populate a memory table and return that instead.
> Craig
> <bassunddrum@.gmail.com> wrote in message
> news:1140151459.214316.219040@.o13g2000cwo.googlegroups.com...
>> Hi all,
>> Does anyone have any thoughts on hard coding values into a result set
>> displayed in a grid? I have a dataset that I've built a bunch of grids
>> from in RS 2005. One of the reports I need to create always needs to
>> have all of the months of 2006 displayed regardless of whether there's
>> data. If there's no data for say July 2006, I get the following:
>> -Thanks!
>> Completed Programs
>> January 2006 1
>> February 2006 2
>> October 2006 3
>> November 2006 4
>> December 2006 233
>> I need
>> Completed Programs
>> January 2006 1
>> February 2006 2
>> March 2006 0
>> April 2006 0
>> May 2006 0
>> June 2006 0
>> July 2006 0
>> August 2006 0
>> September 2006 0
>> October 2006 3
>> November 2006 4
>> December 2006 233
>

Displaying different text

I want text displayed as: some text here Somename another

text here. How to do that if Somename is a value of a field and it's

width changes? As it can't be done with a single textbox, can it be

done with a table? Can a table have dynamical width?The only dynamic sizing property in reporting services is the "CanGrow" property. However, this only applies to the height of objects. The width cannot be dynamically adjusted for larger values.|||So basically that type of formatting can't be done or is there another alternative i haven't thinked of?

Displaying Data Columnwise in Reportviewer

hai,

iam new to the ReportViewer control.when i display data from Dataset it is displayed in Rowwise.ie,

if Name and place is to be displayed then it is displaying in

Name1 Place1

Name2 Place2

i want to display in column wise

ie,Name1 Place1 Name2 Place2

can anybody tell which control can perform this functionality?

HI, jobinthomasv:

Matrix is a data region that arranges data into columns and rows that intersect at specific data points. Matrices provide functionality similar to crosstabs and pivot tables. Unlike a table, which has a static set of columns, matrix columns can be dynamic. You can define matrices that contain static and dynamic rows and columns. For more information, seeAdding Matrix Data Regions.

http://msdn2.microsoft.com/en-us/library/ms251709(VS.80).aspx

|||

HI, jobinthomasv:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

|||

Thank you for your information sir...i want to display in following manner.i tried a lot but i can't achieve it.

My Crieteria:

I Used to Develop An attendence Register for employees.Each Employees have Multiple Login and Logout.

Nw My Issue is I want to Group Each Employees with having his own IN and Out on a particular day he Present.And at the same time since each employees having several login and logout for a particular day.I want to Wrap a matrix after certain columns and display the rest in next line for that day of that employee

ie,

i Want in this format

Employee Name: E1

Present Day 1

IN1 OUT1 IN2 OUT2 IN3 OUT3........after a particular column it should be wrapped and displayed in next line

IN4 Out4 IN5 OUT5............this process is continued until all the login and Logouts of tht employees is displayed...

Then

Present Day2

INs and Outs

Present Day3

INs and Outs

After The first Employees

Similar Format is For all the other employees

How can i achieve this??

Displaying data across the page rather than down

Hi All

I am currently working on a report where the data needs to be displayed across the width of the page rather than repeating as you go down the page. Is this possible using a table or would I need to go about it another way?

Thanks

Look at the Horizontal Tables example in Chris Hays' blog http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

Displaying CustomerID in the report header on mutliple pages

Hi everybody,
I would like to have a field (CustomerID) that is displayed in the header of
the page. I can reference a field of the page with
ReportItems!CustomerID.Value. This works fine as long the customer
information is not spanned across two different pages.
If this is the case the field in the header is empty on the second page. Is
there another solution to this? Why can't I just reference a field item?
Thanks,
tomIf you have any control in the Header of a report , then
by default the controls are repeated in all pages.
There is a Report Header property where you can set print
on first page and print on last page to true or false.
If you have "Print on Last Page " set to false and if 2nd
page is your last page then you will not see the header on
the second page.
Just see if this is the case with your report.
>--Original Message--
>Hi everybody,
>I would like to have a field (CustomerID) that is
displayed in the header of
>the page. I can reference a field of the page with
>ReportItems!CustomerID.Value. This works fine as long the
customer
>information is not spanned across two different pages.
>If this is the case the field in the header is empty on
the second page. Is
>there another solution to this? Why can't I just
reference a field item?
>Thanks,
>tom
>
>.
>

Displaying a subreport in main report at runtime

Hi

I have a report with a subreport within it. When I look at in the preview facility, the data from the subreport is displayed within the main report. At runtime, the sub report is not visible and has to be opened in a separate window. How can I display it in the main report at runtime?

Thanks.I've found the answer to this one. If anyone is interested, please see:

http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2007600&sliceId=&dialogID=6924658&stateId=1%200%206926126

Displaying a relational database as it is displayed in MS Access

Can this be done in ASP.Net, as it stands my database views in my ASP.Net application are just standard

Unlike the view in MS Access which shows the collapsable linked data below the data (from a different table)

Many thanks

Rich

Yes, it's possible to create a GUI similar to the one that comes with Access. It's not a trivial undertaking though. You can use CSS, javascript and Ajax to overcome the inherent limitations of html.|||If your tables/views are relatively narrow and have few rows (the table can be convenantly viewed on a screen without scrolling), then use a DataGrid or GridView, and set it to automatic columns - this will give you a read-only view of the database. Code behind can be written to allow updates, but you do need to enforce any business rules.|||Have a look at http://www.codeproject.com/cs/database/DBViewer.asp A simple database viewer to manipulate SQL Server data types (in particular: image, binary, varbinary and text).|||Also try http://www.codeproject.com/asp/ute.asp "Viewer and Editor for any table in any Database you can reach from your IIS/PWS."

Display text based on the value of a field

In my reports i have a field called mydataset.fruittype

In the details section when I display the data, currently the value is being displayed as 1, 2 etc...

I need to display for eg:

if the value of mydataset.fruittype = 1, then display apple,
if 2 then display mango etc...

How can i do that ? Do I need a formula editor for that.

I am using Crystal Reports 8.0

Can anyone please suggest me a solution.

Thanks.Create a formula @.fruits_name:

if {mydataset.fruittype} = 1 then 'Apple' else
if {mydataset.fruittype} = 2 then 'Mango' else
.
.
.
if {mydataset.fruittype} =n then 'xxxxxx'|||In the details section when I display the data, currently the value is being displayed as 1, 2 etc...

I need to display for eg:

if the value of mydataset.fruittype = 1, then display apple,
if 2 then display mango etc...

I think that you can create a view in your database set values you want to show it diretly in your report.


create view myview as
select
case when mydataset.fruittype=1 then
'apple'
case when mydataset.fruittype=2 then
'mango'
case when mydataset.fruittype=3 then
'orange'
end as fruit
from my table

using this, your client has less works to process the data and
server would make the work.

display sql datas in xml file with stylesheet

hi,
can any one help me in displaying my sql database datas in an xml file and apply stylesheet to that displayed content.

thanks in advance...You have to learn how to write queries with
FOR XML EXPLICIT close.

Read this article for starters.

http://www.topxml.com/sql/for_xml_explicit.asp

display problem

Hi

I m using CR 11.0.

In my report, I want to display the page header details only on the pages where the group header is displayed.

I cannot put the page header details also on the group header details, because it has to be displayed on the top of the page where the group details are displayed. And also I cannot give page break option for hew group.

Please help !!!

Thanking in advancePut the group header on every page :)
or suppress the page header section if it's not a new group.
e.g. not (onfirstrecord or previous(group field) <> group field)

Display or export empty report

The report is running well. However, nothing will be displayed if there is no
data return from database. I would like to display an emty report (the report
frame) when there is no data. Could someone help?
Thanks,Why would you like to display an empty report when there is no data ?
Are you running it from some application, if so just display a message
that there are no records found for the criteria entered.
Billy wrote:
> The report is running well. However, nothing will be displayed if there is no
> data return from database. I would like to display an emty report (the report
> frame) when there is no data. Could someone help?
> Thanks,|||Some scheduled reports (subscription) runnning every day and sending to
customers by emai in EXCEL format. When there is no qualified data, customers
do not want to see an empty file. Instead, they want to see an empty report
(display the report frame)
thanks,
"Sara" wrote:
> Why would you like to display an empty report when there is no data ?
> Are you running it from some application, if so just display a message
> that there are no records found for the criteria entered.
> Billy wrote:
> > The report is running well. However, nothing will be displayed if there is no
> > data return from database. I would like to display an emty report (the report
> > frame) when there is no data. Could someone help?
> >
> > Thanks,
>|||If you have a table, list or subreport, you can set the the NoRows property ="No data found".
"Billy" wrote:
> The report is running well. However, nothing will be displayed if there is no
> data return from database. I would like to display an emty report (the report
> frame) when there is no data. Could someone help?
> Thanks,|||Thanks William. Even the report frame is not displayed by setting this
property, the email attachement is not an empty file now.
"William" wrote:
> If you have a table, list or subreport, you can set the the NoRows property => "No data found".
> "Billy" wrote:
> > The report is running well. However, nothing will be displayed if there is no
> > data return from database. I would like to display an emty report (the report
> > frame) when there is no data. Could someone help?
> >
> > Thanks,

Display NULL as BLANK ?

I am new to SQL Server 2005. When I choose "Results to File" for a query,
I find that fields with NULL value is displayed as "NULL" string. In SQL
Server 2000, NULL value is displayed as BLANK.
Is there any option in Management Studio to be set up so that NULL value can
be exported as BLANK ?
If not, what is the best way to handle it (End users expect that NULL string
should not be displayed) ?
ThanksPeter
Are you sure? I have a table with column defined as smallint and allows
NULLS. I got NULL on both SQL 2000 (SP3) and SQL Server 2005 (SP2)
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>I am new to SQL Server 2005. When I choose "Results to File" for a query,
> I find that fields with NULL value is displayed as "NULL" string. In SQL
> Server 2000, NULL value is displayed as BLANK.
> Is there any option in Management Studio to be set up so that NULL value
> can
> be exported as BLANK ?
> If not, what is the best way to handle it (End users expect that NULL
> string
> should not be displayed) ?
> Thanks
>|||Not sure of a setting that controls this of the top of my head, however, you
could try something like this...
declare @.var varchar(10)
select @.var = NULL
select case when @.var IS NULL then '' else @.var end
Immy
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>I am new to SQL Server 2005. When I choose "Results to File" for a query,
> I find that fields with NULL value is displayed as "NULL" string. In SQL
> Server 2000, NULL value is displayed as BLANK.
> Is there any option in Management Studio to be set up so that NULL value
> can
> be exported as BLANK ?
> If not, what is the best way to handle it (End users expect that NULL
> string
> should not be displayed) ?
> Thanks
>|||Dear Uri,
With SQL Server 2000 Query Analyser (SP4), I run the same query and choose
"Result to file", NULL value is shown as BLANK.
On the other hand, when I use Management Studio (SP2) today, I really get
NULL as string.
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eLLbzLZsHHA.1168@.TK2MSFTNGP02.phx.gbl...
> Peter
> Are you sure? I have a table with column defined as smallint and allows
> NULLS. I got NULL on both SQL 2000 (SP3) and SQL Server 2005 (SP2)
>
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>|||Or, shorten it down to ISNULL or COLAESCE:
SELECT ISNULL(colname, ''), ...
SELECT COALESCE(colname, ''), ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23fhkAPZsHHA.3640@.TK2MSFTNGP05.phx.gbl...
> Not sure of a setting that controls this of the top of my head, however, y
ou could try something
> like this...
> declare @.var varchar(10)
> select @.var = NULL
> select case when @.var IS NULL then '' else @.var end
> Immy
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>|||and that version too ;)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:2DF5290B-A646-4C6D-A85B-23EB5C6B6851@.microsoft.com...
> Or, shorten it down to ISNULL or COLAESCE:
> SELECT ISNULL(colname, ''), ...
> SELECT COALESCE(colname, ''), ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23fhkAPZsHHA.3640@.TK2MSFTNGP05.phx.gbl...
>|||Dear Tibor / Immy,
Many thanks for your advice.
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:2DF5290B-A646-4C6D-A85B-23EB5C6B6851@.microsoft.com...
> Or, shorten it down to ISNULL or COLAESCE:
> SELECT ISNULL(colname, ''), ...
> SELECT COALESCE(colname, ''), ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23fhkAPZsHHA.3640@.TK2MSFTNGP05.phx.gbl...
>

Display NULL as BLANK ?

I am new to SQL Server 2005. When I choose "Results to File" for a query,
I find that fields with NULL value is displayed as "NULL" string. In SQL
Server 2000, NULL value is displayed as BLANK.
Is there any option in Management Studio to be set up so that NULL value can
be exported as BLANK ?
If not, what is the best way to handle it (End users expect that NULL string
should not be displayed) ?
ThanksPeter
Are you sure? I have a table with column defined as smallint and allows
NULLS. I got NULL on both SQL 2000 (SP3) and SQL Server 2005 (SP2)
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>I am new to SQL Server 2005. When I choose "Results to File" for a query,
> I find that fields with NULL value is displayed as "NULL" string. In SQL
> Server 2000, NULL value is displayed as BLANK.
> Is there any option in Management Studio to be set up so that NULL value
> can
> be exported as BLANK ?
> If not, what is the best way to handle it (End users expect that NULL
> string
> should not be displayed) ?
> Thanks
>|||Not sure of a setting that controls this of the top of my head, however, you
could try something like this...
declare @.var varchar(10)
select @.var = NULL
select case when @.var IS NULL then '' else @.var end
Immy
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>I am new to SQL Server 2005. When I choose "Results to File" for a query,
> I find that fields with NULL value is displayed as "NULL" string. In SQL
> Server 2000, NULL value is displayed as BLANK.
> Is there any option in Management Studio to be set up so that NULL value
> can
> be exported as BLANK ?
> If not, what is the best way to handle it (End users expect that NULL
> string
> should not be displayed) ?
> Thanks
>|||Dear Uri,
With SQL Server 2000 Query Analyser (SP4), I run the same query and choose
"Result to file", NULL value is shown as BLANK.
On the other hand, when I use Management Studio (SP2) today, I really get
NULL as string.
Peter
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eLLbzLZsHHA.1168@.TK2MSFTNGP02.phx.gbl...
> Peter
> Are you sure? I have a table with column defined as smallint and allows
> NULLS. I got NULL on both SQL 2000 (SP3) and SQL Server 2005 (SP2)
>
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>>I am new to SQL Server 2005. When I choose "Results to File" for a
>>query,
>> I find that fields with NULL value is displayed as "NULL" string. In SQL
>> Server 2000, NULL value is displayed as BLANK.
>> Is there any option in Management Studio to be set up so that NULL value
>> can
>> be exported as BLANK ?
>> If not, what is the best way to handle it (End users expect that NULL
>> string
>> should not be displayed) ?
>> Thanks
>>
>|||Or, shorten it down to ISNULL or COLAESCE:
SELECT ISNULL(colname, ''), ...
SELECT COALESCE(colname, ''), ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23fhkAPZsHHA.3640@.TK2MSFTNGP05.phx.gbl...
> Not sure of a setting that controls this of the top of my head, however, you could try something
> like this...
> declare @.var varchar(10)
> select @.var = NULL
> select case when @.var IS NULL then '' else @.var end
> Immy
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>>I am new to SQL Server 2005. When I choose "Results to File" for a query,
>> I find that fields with NULL value is displayed as "NULL" string. In SQL
>> Server 2000, NULL value is displayed as BLANK.
>> Is there any option in Management Studio to be set up so that NULL value can
>> be exported as BLANK ?
>> If not, what is the best way to handle it (End users expect that NULL string
>> should not be displayed) ?
>> Thanks
>>
>|||and that version too ;)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:2DF5290B-A646-4C6D-A85B-23EB5C6B6851@.microsoft.com...
> Or, shorten it down to ISNULL or COLAESCE:
> SELECT ISNULL(colname, ''), ...
> SELECT COALESCE(colname, ''), ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23fhkAPZsHHA.3640@.TK2MSFTNGP05.phx.gbl...
>> Not sure of a setting that controls this of the top of my head, however,
>> you could try something like this...
>> declare @.var varchar(10)
>> select @.var = NULL
>> select case when @.var IS NULL then '' else @.var end
>> Immy
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>>I am new to SQL Server 2005. When I choose "Results to File" for a
>>query,
>> I find that fields with NULL value is displayed as "NULL" string. In
>> SQL
>> Server 2000, NULL value is displayed as BLANK.
>> Is there any option in Management Studio to be set up so that NULL value
>> can
>> be exported as BLANK ?
>> If not, what is the best way to handle it (End users expect that NULL
>> string
>> should not be displayed) ?
>> Thanks
>>
>>
>|||Dear Tibor / Immy,
Many thanks for your advice.
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:2DF5290B-A646-4C6D-A85B-23EB5C6B6851@.microsoft.com...
> Or, shorten it down to ISNULL or COLAESCE:
> SELECT ISNULL(colname, ''), ...
> SELECT COALESCE(colname, ''), ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23fhkAPZsHHA.3640@.TK2MSFTNGP05.phx.gbl...
>> Not sure of a setting that controls this of the top of my head, however,
>> you could try something like this...
>> declare @.var varchar(10)
>> select @.var = NULL
>> select case when @.var IS NULL then '' else @.var end
>> Immy
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:O3CWN6YsHHA.1416@.TK2MSFTNGP06.phx.gbl...
>>I am new to SQL Server 2005. When I choose "Results to File" for a
>>query,
>> I find that fields with NULL value is displayed as "NULL" string. In
>> SQL
>> Server 2000, NULL value is displayed as BLANK.
>> Is there any option in Management Studio to be set up so that NULL value
>> can
>> be exported as BLANK ?
>> If not, what is the best way to handle it (End users expect that NULL
>> string
>> should not be displayed) ?
>> Thanks
>>
>>
>

Display group data on every page

Hi!
I have two groupings placed in on list object and I'm trying to
grouping data to be displayed on every page but without luck. The
grouping data is displayed correctly at the beginning of the group but
since it might be a long list of items in one group I would like to
display the group header on every page. Is this possible? I've
searched the net and found answers that it should be possible to do
this but I can't figure out how. HELP Please.
Cheers ErikOn Feb 14, 10:16 pm, e...@.liffner.se wrote:
> Hi!
> I have two groupings placed in on list object and I'm trying to
> grouping data to be displayed on every page but without luck. The
> grouping data is displayed correctly at the beginning of the group but
> since it might be a long list of items in one group I would like to
> display the group header on every page. Is this possible? I've
> searched the net and found answers that it should be possible to do
> this but I can't figure out how. HELP Please.
> Cheers Erik
When editing group in designer, there should be checkboxes called
'Repeat Group Header', Repeat Group Footer'. These will cause the
group header/footer to be repeated on every page.|||On 15 Feb, 01:56, "Rowen" <rowen...@.gmail.com> wrote:
> On Feb 14, 10:16 pm, e...@.liffner.se wrote:
> > Hi!
> > I have two groupings placed in on list object and I'm trying to
> > grouping data to be displayed on every page but without luck. The
> > grouping data is displayed correctly at the beginning of the group but
> > since it might be a long list of items in one group I would like to
> > display the group header on every page. Is this possible? I've
> > searched the net and found answers that it should be possible to do
> > this but I can't figure out how. HELP Please.
> > Cheers Erik
> When editing group in designer, there should be checkboxes called
> 'Repeat Group Header', Repeat Group Footer'. These will cause the
> group header/footer to be repeated on every page.
Hi!
Is this true even if I use a list as the grouping object. I'm unable
to find the checkboxes in my rdl file.|||On Feb 16, 2:04 am, e...@.liffner.se wrote:
> On 15 Feb, 01:56, "Rowen" <rowen...@.gmail.com> wrote:
> > On Feb 14, 10:16 pm, e...@.liffner.se wrote:
> > > Hi!
> > > I have two groupings placed in on list object and I'm trying to
> > > grouping data to be displayed on every page but without luck. The
> > > grouping data is displayed correctly at the beginning of the group but
> > > since it might be a long list of items in one group I would like to
> > > display the group header on every page. Is this possible? I've
> > > searched the net and found answers that it should be possible to do
> > > this but I can't figure out how. HELP Please.
> > > Cheers Erik
> > When editing group in designer, there should be checkboxes called
> > 'Repeat Group Header', Repeat Group Footer'. These will cause the
> > group header/footer to be repeated on every page.
> Hi!
> Is this true even if I use a list as the grouping object. I'm unable
> to find the checkboxes in my rdl file.
The checkbox is visible when editing a report in design mode (i.e. in
Visual Studio). There will not be any checkbox in the rdl file.
If you want to add the property to the rdl file via a text editor, it
should be like this:
<TableGroup>
<Header>
<TableRows>...
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="...">
<GroupExpressions>
...
</GroupExpressions>
</Grouping>
<Sorting>
...
</Sorting>
</TableGroup>