DISTINCT w/ character data

Hello,
I need to eliminate duplicates from records containing a text data type.
Here is the query I try :
select NewsGroup.NewsGroupID,
(distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
-- Line 10
NewsGroup.OnlineFlag
from...
where...
--
And here is the error I get :
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'distinct'.
--
The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what I
am trying to do is possible. But then why this error? If this is not
possible, how else could I eliminate the duplicates?
TIADISTINCT applies to the whole result not just one column. Maybe this
will do what you intended (notice the extra bracket and comma):
SELECT newsgroup.newsgroupid,
MAX(CAST(A.translatedtext AS VARCHAR(8000))) AS newsgroupname,
newsgroup.onlineflag
FROM a
WHERE ...
GROUP BY newsgroup.newsgroupid, newsgroup.onlineflag ;
David Portas
SQL Server MVP
--|||The keyword DISTINCT needs to be before any field names. Also, CAST should b
e
outside of the parentheses. Try the following
SELECT DISTINCT NewsGroup.NewsGroupID, CAST (a.TranslatedText as
varchar(8000)) as NewsGroupName ....
"alto" wrote:

> Hello,
> I need to eliminate duplicates from records containing a text data type.
> Here is the query I try :
> --
> select NewsGroup.NewsGroupID,
> (distinct (cast a.TranslatedText as varchar(8000))) as NewsGroupName
> -- Line 10
> NewsGroup.OnlineFlag
> from...
> where...
> --
> And here is the error I get :
> --
> Server: Msg 156, Level 15, State 1, Line 10
> Incorrect syntax near the keyword 'distinct'.
> --
> The Transact-SQL Reference-CAST and CONVERT section of SQL Help says what
I
> am trying to do is possible. But then why this error? If this is not
> possible, how else could I eliminate the duplicates?
> TIA
>
>sql

Distinct on Text Column

How can I select distinct values from a table which has column datatype as
Text.
data sholud n't be truncated.
--
Regards,
Kassim.http://support.microsoft.com/kb/162032/en-us
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
> How can I select distinct values from a table which has column datatype as
> Text.
> data sholud n't be truncated.
> --
> Regards,
> Kassim.|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||I do get these error, is there any other way to over come this.
Kassim.
"Jens Sü?meyer" wrote:

> http://support.microsoft.com/kb/162032/en-us
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:85267AF5-DAE1-45AE-B24B-C90502681D3F@.microsoft.com...
>
>|||Can you post some DDL and your query please.
"M Kassim" <MKassim@.discussions.microsoft.com> schrieb im Newsbeitrag
news:517D91E0-FA6D-41EB-AD5C-3CAF4ED0F393@.microsoft.com...
>I do get these error, is there any other way to over come this.
> Kassim.
> "Jens Smeyer" wrote:
>|||Does the text column need to be part of the DISTINCT
operator, or can you be sure the text columns match if all
the other columns match? If you only need DISTINCT on
the other columns, one solution is to create a primary key or
unique column for the table. If myID is a unique
integer column, you could do something like this:
select * from myTable
where myID in (
select min(myID)
from myTable
group by col1, col2, col3
-- Do *not* include the text column in this list
)
If there are two different text column values for
the same (col1, col2, col3), you will get only one
of those rows.
If you need to determine if the text columns are unequal,
you could compare the first 8000 characters, or more if
you want:
select * from myTable
where not exists (
select * from myTable as Tcopy
where Tcopy.col1 = T.col1
and Tcopy.col2 = T.col2
..
and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
and Tcopy.myID < T.myID
)
Steve Kass
Drew University
M Kassim wrote:

>How can I select distinct values from a table which has column datatype as
>Text.
>data sholud n't be truncated.
>|||Hi,
I have a table called comments, which has 2 columns commentID primarykey
and comment [Text datatype], now I would like
select distinct comment from comments.
Kassim.
---
"Steve Kass" wrote:

> Does the text column need to be part of the DISTINCT
> operator, or can you be sure the text columns match if all
> the other columns match? If you only need DISTINCT on
> the other columns, one solution is to create a primary key or
> unique column for the table. If myID is a unique
> integer column, you could do something like this:
> select * from myTable
> where myID in (
> select min(myID)
> from myTable
> group by col1, col2, col3
> -- Do *not* include the text column in this list
> )
> If there are two different text column values for
> the same (col1, col2, col3), you will get only one
> of those rows.
> If you need to determine if the text columns are unequal,
> you could compare the first 8000 characters, or more if
> you want:
> select * from myTable
> where not exists (
> select * from myTable as Tcopy
> where Tcopy.col1 = T.col1
> and Tcopy.col2 = T.col2
> ...
> and substring(Tcopy.textcol,1,8000) = substring(T.textcol,1,8000)
> and substring(Tcopy.textcol,8001,8000) = substring(T.textcol,8001,8000)
> and Tcopy.myID < T.myID
> )
> Steve Kass
> Drew University
> M Kassim wrote:
>
>sql

displaying vertical text in details section using sql reporting service

Nico,
U can change the "WritingMode" property of text box to set display
vertically.
I have another problem with the same, i want to display a text in the
details section spanning multiple rows.
below is example
S_NO NAME VERTICAL_TEXT
_______________________________
1 ABC S
2 CDE A
3 FGH M
4 IJK P
L
E
Appreciate any help!!..
VenkatCan anyone help on this?..
venkat.oar@.gmail.com wrote:
> Nico,
> U can change the "WritingMode" property of text box to set display
> vertically.
> I have another problem with the same, i want to display a text in the
> details section spanning multiple rows.
> below is example
> S_NO NAME VERTICAL_TEXT
> _______________________________
> 1 ABC S
> 2 CDE A
> 3 FGH M
> 4 IJK P
> L
> E
> Appreciate any help!!..
> Venkat

Displaying RTF fields in a report

Hi,

I would appreciate it anyone could help. I've saved RTF formatted data to a text field in the sql server db. I want to display this formatted text in a report with. Any ideas?

shot

I have the same problem, but I think I'm reallllly close to the solution.

I'm rendering the RTF to an image file and trying to load it into an image control via a referenced class. I have the class returning either an image or a byte array. Reporting services image controls apparently only work with a byte array, but as of yet I can't get it to work.

Using the same assembly reference I am able to view the resulting image with the pictureBox control, and the byte array appears to be populated as intended, it just won't load up in reporting services.

I'll attach my code thus far in hopes that you'll be able to get further than I have (if you do, be sure to post the fix).

I'm setting the image source to database, the mime type to image\bmp and value to "=Code.GetImage()". Oh, and don't forget to add the dll to the assembly cache when you add the reference to the assembly dll in reporting services, it doesn't seem to play well otherwise.

In the code section I've added the following function:

Function GetImage() As Byte()
objRTFImage.Rtf = "This is a test"
return objRTFImage.PrintToByteArray(200, 100) ' Syntax PrintToByteArray(int32 width, int32 height)
End Function

The class code in my assembly is posted below.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using System.Runtime.InteropServices;

using System.Drawing.Imaging;

using System.IO;

namespace RichTextRenderer

{

public partial class RTFImage : RichTextBox

{

public RTFImage()

{

}

protected override void OnPaint(PaintEventArgs pe)

{

// Calling the base class OnPaint

base.OnPaint(pe);

}

//Convert the unit used by the .NET framework (1/100 inch)

//and the unit used by Win32 API calls (twips 1/1440 inch)

private const double anInch = 14.4;

[StructLayout(LayoutKind.Sequential)]

private struct RECT

{

public int Left;

public int Top;

public int Right;

public int Bottom;

}

[StructLayout(LayoutKind.Sequential)]

private struct CHARRANGE

{

public int cpMin; //First character of range (0 for start of doc)

public int cpMax; //Last character of range (-1 for end of doc)

}

[StructLayout(LayoutKind.Sequential)]

private struct FORMATRANGE

{

public IntPtr hdc; //Actual DC to draw on

public IntPtr hdcTarget; //Target DC for determining text formatting

public RECT rc; //Region of the DC to draw to (in twips)

public RECT rcPage; //Region of the whole DC (page size) (in twips)

public CHARRANGE chrg; //Range of text to draw (see earlier declaration)

}

private const int WM_USER = 0x0400;

private const int EM_FORMATRANGE = WM_USER + 57;

[DllImport("USER32.dll")]

private static extern IntPtr SendMessage(IntPtr hWnd, int msg, IntPtr wp, IntPtr lp);

// Render the contents of the RichTextBox for printing

// Return the last character printed + 1 (printing start from this point for next page)

public Image PrintToImage(String plainText, Int32 width, Int32 height)

{

this.Text = plainText;

return PrintToImage( width, height);

}

public Image PrintToImage(Int32 width, Int32 height)

{

Image image = new Bitmap(width, height);

Graphics g = Graphics.FromImage(image);

Int32 retVal = this.Print(0, this.Text.Length, g, new Rectangle(this.Location, new Size(width, height)));

return image;

}

public byte[] PrintToByteArray(String plainText, Int32 width, Int32 height)

{

return ConvertImageToByteArray(PrintToImage(plainText, width, height));

}

public byte[] PrintToByteArray(Int32 width, Int32 height)

{

return ConvertImageToByteArray(PrintToImage(width, height));

}

public int Print(int charFrom, int charTo, Graphics gr, Rectangle bounds)

{

//Calculate the area to render and print

RECT rectToPrint;

rectToPrint.Top = 0;// (int)(bounds.Top * anInch);

rectToPrint.Bottom = (int)(bounds.Height * anInch);// (int)(bounds.Bottom * anInch);

rectToPrint.Left = 0;// (int)(bounds.Left * anInch);

rectToPrint.Right = (int)(bounds.Width * anInch);// (int)(bounds.Right * anInch);

//Calculate the size of the page

RECT rectPage;

rectPage.Top = 0;//(int)(bounds.Top * anInch);

rectPage.Bottom = (int)(gr.ClipBounds.Height * anInch);//(int)(bounds.Bottom * anInch);

rectPage.Left = 0;//(int)(bounds.Left * anInch);

rectPage.Right = (int)(gr.ClipBounds.Right * anInch);//(int)(bounds.Right * anInch);

IntPtr hdc = gr.GetHdc();

FORMATRANGE fmtRange;

fmtRange.chrg.cpMax = charTo; //Indicate character from to character to

fmtRange.chrg.cpMin = charFrom;

fmtRange.hdc = hdc; //Use the same DC for measuring and rendering

fmtRange.hdcTarget = hdc; //Point at printer hDC

fmtRange.rc = rectToPrint; //Indicate the area on page to print

fmtRange.rcPage = rectPage; //Indicate size of page

IntPtr res = IntPtr.Zero;

IntPtr wparam = IntPtr.Zero;

wparam = new IntPtr(1);

//Get the pointer to the FORMATRANGE structure in memory

IntPtr lparam = IntPtr.Zero;

lparam = Marshal.AllocCoTaskMem(Marshal.SizeOf(fmtRange));

Marshal.StructureToPtr(fmtRange, lparam, false);

//Send the rendered data for printing

res = SendMessage(Handle, EM_FORMATRANGE, wparam, lparam);

//Free the block of memory allocated

Marshal.FreeCoTaskMem(lparam);

//Release the device context handle obtained by a previous call

gr.ReleaseHdc(hdc);

//Return last + 1 character printer

return res.ToInt32();

}

public static byte[] ConvertImageToByteArray(System.Drawing.Image imageToConvert)

{

byte[] Ret;

//try

//{

using (System.IO.MemoryStream ms = new MemoryStream())

{

imageToConvert.Save(ms,ImageFormat.Bmp);

ms.Position = 0;

Ret = ms.ToArray();

}

//}

//catch (Exception) { throw; }

return Ret;

}

}

}

|||

Hi

Thanks so much for your response, i luckily have the option of using Crystal....which supports RTF fields apparently - and which i'll def be using!!

Good luck with the RTF prob, i might pursue it in my personal time - please let me know if/when you have a solution and how you solved it.

thanks

Displaying RTF Content in Report

Hi
I am having rtf stuff in database.
I need to display it in the Report.
Is ther any way to render rtf text in the report
or Is there any way to remove rtf stuff and just display plain text.
I Tried to use RichTextBox object of System.Windows.Forms.dll.
It worked well in design time preview tab.
But giving error at field where I used to display when I published to
Report Server and run the reportPlease tell me what solution you did for below mentioned problem
"Rama Prasad" wrote:
> Hi
> I am having rtf stuff in database.
> I need to display it in the Report.
> Is ther any way to render rtf text in the report
> or Is there any way to remove rtf stuff and just display plain text.
> I Tried to use RichTextBox object of System.Windows.Forms.dll.
> It worked well in design time preview tab.
> But giving error at field where I used to display when I published to
> Report Server and run the report

Displaying records from today on (filtering out the old)

I am trying to filter out old records from a search. I tried this but
it still seems to display the old records.
gofind is the input box text query and it passes through fine. It
searches perfectly, just doesn't filter out old records.
It should show everything from today and into the future.
dDate = Date()-1
SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes' ORDER BY date ASC
Can someone help please?You need to enclose the two sections of your where clause in
parentheses. Try something like this:
SELECT * FROM events WHERE (title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes') OR (comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes') ORDER BY date ASC
As it was written simply a match with the comments LIKE '%" & gofind &
"%' would result in an evaluation of true.
james.shearer@.gmail.com wrote:
> I am trying to filter out old records from a search. I tried this but
> it still seems to display the old records.
> gofind is the input box text query and it passes through fine. It
> searches perfectly, just doesn't filter out old records.
> It should show everything from today and into the future.
> dDate = Date()-1
> SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
> & dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
> date >= " & dDate & " AND active='yes' ORDER BY date ASC
> Can someone help please?
>|||Thanks Steve but that doesn't seem to make any difference.
The query works fine apart from the part:
date >= " & dDate & "
That doesn't seem to make any difference at all.
It should show records dated from a date greater than yesterday but it
just shows everything that meets the rest of the criteria ignoring the
date request.
Any idea?|||On 8 Nov 2005 20:05:09 -0800, james.shearer@.gmail.com wrote:

>Thanks Steve but that doesn't seem to make any difference.
>The query works fine apart from the part:
>date >= " & dDate & "
>That doesn't seem to make any difference at all.
>It should show records dated from a date greater than yesterday but it
>just shows everything that meets the rest of the criteria ignoring the
>date request.
>Any idea?
Hi James,
I don't know what front end you are using. Best would be to pass the
date as a parameter instead of passing it as text. That would prevent
any possible conversion problems, since native date/time datatypes will
be used.
If you must pass it as text, then the best way to troubleshoot this is
to display the query string instead of executing it. The part with the
date comparison should look like this:
date >= '20051109'
or
date >= '2005-11-09T22:40:34.550'
That is: the date string must be enclosed in single quotes, and it must
adhere to one of the unambigous date formats, which are:
* yyyymmdd for date only
(note: no dashes, slashes, dots, or other punctuation)
* yyyy-mm-ddThh:mm:ss for date and time
(note: dashes in the date, colons in the time and an uppercase T [not
a space!] between the two parts)
* yyyy-mm-ddThh:mm:ss.mmm for date and time with millisecond precision
(note: same as above; add a dot and then the milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Displaying HTML or other Rich Text Format content in Reporting Ser

For a report I am creating, I need to display rich text format content, that
will be saved in the database, on a reporting services report. This data
will include bolds, bullets, italics, indents, etc.
Is there a way to show this content formatted correctly in Reporting
Services? We were planning on storing the data as HTML in the database but
reporting services is displaying the actual HTML code and not the content.
Does anyone have any ideas? Any help is greatly appreciated! ThanksSSRS only prints plain text. You can't display HTML or RTF.
"giggleraz" <giggleraz@.discussions.microsoft.com> wrote in message
news:B5F25DF8-285F-4052-A159-74E4863A9829@.microsoft.com...
> For a report I am creating, I need to display rich text format content,
that
> will be saved in the database, on a reporting services report. This data
> will include bolds, bullets, italics, indents, etc.
> Is there a way to show this content formatted correctly in Reporting
> Services? We were planning on storing the data as HTML in the database
but
> reporting services is displaying the actual HTML code and not the content.
> Does anyone have any ideas? Any help is greatly appreciated! Thanks|||Does anyone know if this will be possible in SQL 2005 RS? Surely this is a
basic requirement in a reporting tool
"Brian Bischof" wrote:
> SSRS only prints plain text. You can't display HTML or RTF.
>
> "giggleraz" <giggleraz@.discussions.microsoft.com> wrote in message
> news:B5F25DF8-285F-4052-A159-74E4863A9829@.microsoft.com...
> > For a report I am creating, I need to display rich text format content,
> that
> > will be saved in the database, on a reporting services report. This data
> > will include bolds, bullets, italics, indents, etc.
> > Is there a way to show this content formatted correctly in Reporting
> > Services? We were planning on storing the data as HTML in the database
> but
> > reporting services is displaying the actual HTML code and not the content.
> > Does anyone have any ideas? Any help is greatly appreciated! Thanks
>
>|||Not sure if this helps but what I had to do to get bullets was to start the
old Windows character map...not sure if it still comes with XP but if you
have an older version of windows such as 95 or 98 or 2000 you can start
character map by "Start" RUN and typing charmap.exe
Go to the Time New Roman font, scroll down about half way and you'll find a
BULLET (code 2022), you can copy it then paste it into a Textbox object and
it will display fine.
"IWantItalics" wrote:
> Does anyone know if this will be possible in SQL 2005 RS? Surely this is a
> basic requirement in a reporting tool
> "Brian Bischof" wrote:
> > SSRS only prints plain text. You can't display HTML or RTF.
> >
> >
> > "giggleraz" <giggleraz@.discussions.microsoft.com> wrote in message
> > news:B5F25DF8-285F-4052-A159-74E4863A9829@.microsoft.com...
> > > For a report I am creating, I need to display rich text format content,
> > that
> > > will be saved in the database, on a reporting services report. This data
> > > will include bolds, bullets, italics, indents, etc.
> > > Is there a way to show this content formatted correctly in Reporting
> > > Services? We were planning on storing the data as HTML in the database
> > but
> > > reporting services is displaying the actual HTML code and not the content.
> > > Does anyone have any ideas? Any help is greatly appreciated! Thanks
> >
> >
> >

Displaying HTML in Report

Hi
I'm using a HTHL editor in a web site which enables users to format text in
HTML format which is then stored in the database e.g.
<span style="color: #000000">* Daily Email Checks <br/>* Tape Changes
<br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of Site</span>
I'm sure I read somewhere that SQL Reporting Services 2008 would enable you
to display this in HTML format but I cant seem to get it to work in the
November 2008 CTP.
Am I correct with what I've think I read or is the functionality not
available in the November CTP?
Thanks
RKPCorrect, it is not available in the November CTP.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RKP" <RKP@.discussions.microsoft.com> wrote in message
news:7D7FF1E7-9280-44C2-B8C2-9C03D3B496E4@.microsoft.com...
> Hi
> I'm using a HTHL editor in a web site which enables users to format text
> in
> HTML format which is then stored in the database e.g.
> <span style="color: #000000">* Daily Email Checks <br/>* Tape Changes
> <br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of
> Site</span>
> I'm sure I read somewhere that SQL Reporting Services 2008 would enable
> you
> to display this in HTML format but I cant seem to get it to work in the
> November 2008 CTP.
> Am I correct with what I've think I read or is the functionality not
> available in the November CTP?
> Thanks
> RKP
>|||I presume we have a CTP later than November, does that mean this
feature is now available in the latest CTP or ithis feature is still
on-hold?
On Apr 10, 5:49=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Correct, it is not available in the November CTP.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RKP" <R...@.discussions.microsoft.com> wrote in message
> news:7D7FF1E7-9280-44C2-B8C2-9C03D3B496E4@.microsoft.com...
>
> > Hi
> > I'm using a HTHL editor in a web site which enables users to format text=
> > in
> > HTML format which is then stored in the database e.g.
> > <span style=3D"color: #000000">* Daily Email Checks =A0 =A0<br/>* Tape C=hanges
> > <br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of
> > Site</span>
> > I'm sure I read somewhere that SQL Reporting Services 2008 would enable
> > you
> > to display this in HTML format but I cant seem to get it to work in the
> > November 2008 CTP.
> > Am I correct with what I've think I read or is the functionality not
> > available in the November CTP?
> > Thanks
> > RKP- Hide quoted text -
> - Show quoted text -

Displaying HTML as text

I am trying to display a field in a report that sometimes contains html
stored as text. I want to be able to strip out the html tags so I can display
the text only in the report.To strip off the html I believe there is a framework function that you could
use. Set the value of the textbox to an expression like this:
= Code.StripHTML(Fields!Fieldname.value)
You would write the function StripHTML that would return the value with the
html stripped.
This link shows how to do this using regular expressions.
http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
There is definitely a dotnet function that you could use too. Google on
stripping html and you should be able to find it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
>I am trying to display a field in a report that sometimes contains html
> stored as text. I want to be able to strip out the html tags so I can
> display
> the text only in the report.
>|||Thanks for the info Bruce. I am not a programmer so forgive for my questions,
how do you embed custom code into a sql report?
"Bruce L-C [MVP]" wrote:
> To strip off the html I believe there is a framework function that you could
> use. Set the value of the textbox to an expression like this:
> = Code.StripHTML(Fields!Fieldname.value)
> You would write the function StripHTML that would return the value with the
> html stripped.
> This link shows how to do this using regular expressions.
> http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
> There is definitely a dotnet function that you could use too. Google on
> stripping html and you should be able to find it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael" <Michael@.discussions.microsoft.com> wrote in message
> news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
> >I am trying to display a field in a report that sometimes contains html
> > stored as text. I want to be able to strip out the html tags so I can
> > display
> > the text only in the report.
> >
>
>

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 De-Serialized Images in Reports

PREFACE:
I have a field in my SQL Server database that is called "Ink" and is Text as
the datatype.
I have put into this field a Base64 String which represents serialized
digital ink collected on a Tablet PC.
I wrote a small piece of Custom Code in the Report to de-serialize the ink
and transform it into System.Drawing.Bitmap, but it does not render in the
report.
QUESTION
Is it possible to display images in a report from a function that returns a
data type of System.Drawing.Bitmap?
If not, can I ask how you are planning to implement digital INK support in
databases?
--
Shawn Nanto
Leszynski Group, Inc.
Bellevue, WAImages can be directly displayed when they are returned as Base64 encoded
byte array. The image type has to be "Database" and you must set the
MimeType to the correct image format.
Note: System.Drawing.Bitmap is not supported.
The relevant section in the RDL file would look similar to this:
<Image>
<MIMEType>image/bmp</MIMEType>
<Source>Database</Source>
<Value>=Fields!InkImage.Value</Value>
...
</Image>
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shawn Nanto" <ShawnNanto@.discussions.microsoft.com> wrote in message
news:0A54FC08-2ECB-4996-B585-5F7E4BA3A8C9@.microsoft.com...
> PREFACE:
> I have a field in my SQL Server database that is called "Ink" and is Text
as
> the datatype.
> I have put into this field a Base64 String which represents serialized
> digital ink collected on a Tablet PC.
> I wrote a small piece of Custom Code in the Report to de-serialize the ink
> and transform it into System.Drawing.Bitmap, but it does not render in the
> report.
> QUESTION
> Is it possible to display images in a report from a function that returns
a
> data type of System.Drawing.Bitmap?
> If not, can I ask how you are planning to implement digital INK support in
> databases?
>
> --
> Shawn Nanto
> Leszynski Group, Inc.
> Bellevue, WA

Displaying 'ALL' - Multivalue Parameter

Now that the Select All feature is working, I need to be able to display the
text ALL when the end user checks select all (in a textbox that is designed
to show values chosen for the parameter). Is this possible?
I am able to do this when I add an all value to the drop down selection, but
that gives you a value of ALL and a value of Select All. I am certain that
this will confuse the end user.
We need to be able to turn the Select All off.
Thanks!
--
blesrptdevOn Mar 8, 2:02 pm, blesrptdev <blesrpt...@.discussions.microsoft.com>
wrote:
> Now that the Select All feature is working, I need to be able to display the
> text ALL when the end user checks select all (in a textbox that is designed
> to show values chosen for the parameter). Is this possible?
> I am able to do this when I add an all value to the drop down selection, but
> that gives you a value of ALL and a value of Select All. I am certain that
> this will confuse the end user.
> We need to be able to turn the Select All off.
> Thanks!
> --
> blesrptdev
I don't believe that turning 'Select All' off is possible; however,
you can either take a count of the options selected by the user and
compare it to the total possible options in the query and return a
separate field to the report showing that 'Select All' was selected.
Or if you know that the total options will not exceed a certain
quantity, you could use something like the following:
=iif(Parameters!ParameterName.Count > 20, "Select All",
"SomeDefaultText")
Where 20, in this example, is the maximum number of options to select
in the drop-down list box. Sorry I could not be of more assistance.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks Enrique! This creatively worked.
--
blesrptdev
"EMartinez" wrote:
> On Mar 8, 2:02 pm, blesrptdev <blesrpt...@.discussions.microsoft.com>
> wrote:
> > Now that the Select All feature is working, I need to be able to display the
> > text ALL when the end user checks select all (in a textbox that is designed
> > to show values chosen for the parameter). Is this possible?
> >
> > I am able to do this when I add an all value to the drop down selection, but
> > that gives you a value of ALL and a value of Select All. I am certain that
> > this will confuse the end user.
> >
> > We need to be able to turn the Select All off.
> >
> > Thanks!
> > --
> > blesrptdev
> I don't believe that turning 'Select All' off is possible; however,
> you can either take a count of the options selected by the user and
> compare it to the total possible options in the query and return a
> separate field to the report showing that 'Select All' was selected.
> Or if you know that the total options will not exceed a certain
> quantity, you could use something like the following:
> =iif(Parameters!ParameterName.Count > 20, "Select All",
> "SomeDefaultText")
> Where 20, in this example, is the maximum number of options to select
> in the drop-down list box. Sorry I could not be of more assistance.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>
>

Displaying a field in SQL Server MSE

I have a long text field in a table. Is there a query that I can execute in Mgt. Studio Express that will display the whole of this field. It is too long to easily see in the table view and SELECT <fieldname> from <tablename> only displays part of the field before displaying ellipses (...).

Many thanks,

This depends on what your data is and how you're displaying it. If the data is an XML document then casting it to XML in the select will allow you to click on it and display nicely formatted XML. If it's just text then management studio has a configurable limit on how much it will display. Go to Tools->Options in the menu bar and navigate to Query Results->SQL Server->Results to Grid and set the Non XML data size for results displayed in a grid and Query Results->SQL Server->Results to Text and set the Maximum number of characters displayed in each column for text results.|||

Thanks. This is a VARCHAR(MAX) field. I will try the menu option you recommend.

- A

|||

Hi Roger,

I wanted to know how can we convert (cast) the <Long Text> field into XML.

The query below isn't working !!

SELECT CAST(RateSet AS xml) AS Readable, *
FROM Table

|||

That's the right way to do it. Perhaps if you were to elaborate a little on "isn't working"

This works for me:

create table xmlstuff(txt nvarchar(MAX))

insert into xmlstuff values ('some XML')

select CAST(txt AS xml) from xmlstuff

Displaying a field in SQL Server MSE

I have a long text field in a table. Is there a query that I can execute in Mgt. Studio Express that will display the whole of this field. It is too long to easily see in the table view and SELECT <fieldname> from <tablename> only displays part of the field before displaying ellipses (...).

Many thanks,

This depends on what your data is and how you're displaying it. If the data is an XML document then casting it to XML in the select will allow you to click on it and display nicely formatted XML. If it's just text then management studio has a configurable limit on how much it will display. Go to Tools->Options in the menu bar and navigate to Query Results->SQL Server->Results to Grid and set the Non XML data size for results displayed in a grid and Query Results->SQL Server->Results to Text and set the Maximum number of characters displayed in each column for text results.|||

Thanks. This is a VARCHAR(MAX) field. I will try the menu option you recommend.

- A

|||

Hi Roger,

I wanted to know how can we convert (cast) the <Long Text> field into XML.

The query below isn't working !!

SELECT CAST(RateSet AS xml) AS Readable, *
FROM Table

|||

That's the right way to do it. Perhaps if you were to elaborate a little on "isn't working"

This works for me:

create table xmlstuff(txt nvarchar(MAX))

insert into xmlstuff values ('some XML')

select CAST(txt AS xml) from xmlstuff

Displaying a field in SQL Server MSE

I have a long text field in a table. Is there a query that I can execute in Mgt. Studio Express that will display the whole of this field. It is too long to easily see in the table view and SELECT <fieldname> from <tablename> only displays part of the field before displaying ellipses (...).

Many thanks,

This depends on what your data is and how you're displaying it. If the data is an XML document then casting it to XML in the select will allow you to click on it and display nicely formatted XML. If it's just text then management studio has a configurable limit on how much it will display. Go to Tools->Options in the menu bar and navigate to Query Results->SQL Server->Results to Grid and set the Non XML data size for results displayed in a grid and Query Results->SQL Server->Results to Text and set the Maximum number of characters displayed in each column for text results.|||

Thanks. This is a VARCHAR(MAX) field. I will try the menu option you recommend.

- A

|||

Hi Roger,

I wanted to know how can we convert (cast) the <Long Text> field into XML.

The query below isn't working !!

SELECT CAST(RateSet AS xml) AS Readable, *
FROM Table

|||

That's the right way to do it. Perhaps if you were to elaborate a little on "isn't working"

This works for me:

create table xmlstuff(txt nvarchar(MAX))

insert into xmlstuff values ('some XML')

select CAST(txt AS xml) from xmlstuff

Displaying a custom and dynamic RTF oder HTML text

Hi,
I need to display a formatted text in the reports. Could be HTML, Word-Doc
or RTF or something else.
The text can be stored as file or in the database.
How I can do this with Reprting Services?
Thanks
EricCurrent Versions of RS does not support HTML or other formatted display
styles... Future releases will support HTML...
You might be able to create a custom control which displays rich text, but
since I have never done that - I can't offer any information as to the
difficulty...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <jug@.nospam.nospam> wrote in message
news:O1kBPY7ZFHA.3784@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to display a formatted text in the reports. Could be HTML, Word-Doc
> or RTF or something else.
> The text can be stored as file or in the database.
> How I can do this with Reprting Services?
>
> Thanks
> Eric
>|||do you have information on which of the future versions will actually
support HTML? I have seen a chat record on techNet about release of
msSQL2005 and it said that HTML will not be supported in 2005 that is coming
this summer, will be it supported in 2005 sp1?
thanks,
Alexander.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:ueNRsF8ZFHA.3224@.TK2MSFTNGP10.phx.gbl...
> Current Versions of RS does not support HTML or other formatted display
> styles... Future releases will support HTML...|||Hi Alexander,
I have consulted this with development team, unfortunately, no
out-of-the-box rendering extension for word/RTF formats at this time.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Display textbox on each page of the report

Hi,
I have created a multi-page report. I want to display a text box on each
page of the report. How to do so?
Note that I cannot make the text box as a part of data region (like list).
Its a kind of header information that need to be outputed in each page.
I cannot place the text box in header of the page since it takes value from
a database field. The header doesn't accept database fields.
regards,
Sachin.I think this is a difficult thing to do. One approach is to ensure that a
database field in a textbox is on each page of the report body. You may
then reference the textbox in the headder or footer with the ReportItems!
collection. We have reports that extend multiple pages when exported to PDF
format. I tried making a really tall and narrow textbox and this would work
for the first two pages, but when the matrix at the bottom of the report
grew onto a third page, the third page would not have a textbox and the
reference would be empty.
We took another approach, we passed in the ReportItems collection to a
custom assembly and kept a 'last known good' reference to it. We would then
call this method on each page and retrieve the last known good. I had to
pass in the whole ReportItems collection because passing in just the report
item would result in an #Error for pages that didn't contain the hidden text
box. From what I can tell all references in an expression are resolved
which is why I passed the collection. I think this would also work in code
behind without the custom assembly, but haven't tried it.
Hidden textbox in report body:
txtSchoolNameHidden
=First(Fields!OrganizationName.Value, "GetOrgInfo")
Expression in footer:
=Code.loc.SchoolName(ReportItems)
Code in Custom Assembly (because we are using a property, had to create an
instance, rather than static method)
private string _SchoolName;
public string
SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
input)
{
string ReturnVal = null;
try
{
Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
rptItemSchoolNameHidden = null;
rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
if (rptItemSchoolNameHidden != null)
{
_SchoolName = rptItemSchoolNameHidden.Value.ToString();
}
}
catch
{
//ignore report item not found error.
}
return _SchoolName;
}
Seems like a lot of work for something simple. Any other ideas would be
appreciated.
Aaarrrghhh! Runs fine in SSRS2005 web window, when I export it to PDF only
the first page has my database field in the footer.
Steve MunLeeuw
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:6866232F-21F1-42BB-A593-FCD33A2BE344@.microsoft.com...
> Hi,
> I have created a multi-page report. I want to display a text box on each
> page of the report. How to do so?
> Note that I cannot make the text box as a part of data region (like list).
> Its a kind of header information that need to be outputed in each page.
> I cannot place the text box in header of the page since it takes value
> from
> a database field. The header doesn't accept database fields.
> regards,
> Sachin.
>|||A text box can't take a database field directly in the header or footer, but
it can take a report parameter. That parameter in turn can be filled with
the value of a field in a dataset.
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:6866232F-21F1-42BB-A593-FCD33A2BE344@.microsoft.com...
> Hi,
> I have created a multi-page report. I want to display a text box on each
> page of the report. How to do so?
> Note that I cannot make the text box as a part of data region (like list).
> Its a kind of header information that need to be outputed in each page.
> I cannot place the text box in header of the page since it takes value
> from
> a database field. The header doesn't accept database fields.
> regards,
> Sachin.
>|||I put a call to a custom assembly in a textbox expression in the footer.
When I appendend a counter for each call I was suprised to find the first
page expression was getting called 6 times, then one additional time for
each page when exported to PDF. I didn't go back and do the comparison for
the browser view with interactive height, and therefore different number of
pages as pdf.
The following code works.
static string _SchoolName;
public string
SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
input)
{
string ReturnVal = null;
try
{
Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
rptItemSchoolNameHidden = null;
rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
if (rptItemSchoolNameHidden != null)
{
if (rptItemSchoolNameHidden.Value.ToString().Length > 0)
{
_SchoolName = rptItemSchoolNameHidden.Value.ToString();
}
}
}
catch
{
//ignore report item not found error.
}
return _SchoolName;
}
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:ORGfb5LNGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I think this is a difficult thing to do. One approach is to ensure that a
>database field in a textbox is on each page of the report body. You may
>then reference the textbox in the headder or footer with the ReportItems!
>collection. We have reports that extend multiple pages when exported to
>PDF format. I tried making a really tall and narrow textbox and this would
>work for the first two pages, but when the matrix at the bottom of the
>report grew onto a third page, the third page would not have a textbox and
>the reference would be empty.
> We took another approach, we passed in the ReportItems collection to a
> custom assembly and kept a 'last known good' reference to it. We would
> then call this method on each page and retrieve the last known good. I
> had to pass in the whole ReportItems collection because passing in just
> the report item would result in an #Error for pages that didn't contain
> the hidden text box. From what I can tell all references in an expression
> are resolved which is why I passed the collection. I think this would
> also work in code behind without the custom assembly, but haven't tried
> it.
> Hidden textbox in report body:
> txtSchoolNameHidden
> =First(Fields!OrganizationName.Value, "GetOrgInfo")
> Expression in footer:
> =Code.loc.SchoolName(ReportItems)
>
> Code in Custom Assembly (because we are using a property, had to create an
> instance, rather than static method)
> private string _SchoolName;
> public string
> SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
> input)
> {
> string ReturnVal = null;
> try
> {
> Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
> rptItemSchoolNameHidden = null;
> rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
> if (rptItemSchoolNameHidden != null)
> {
> _SchoolName = rptItemSchoolNameHidden.Value.ToString();
> }
> }
> catch
> {
> //ignore report item not found error.
> }
> return _SchoolName;
> }
>
> Seems like a lot of work for something simple. Any other ideas would be
> appreciated.
> Aaarrrghhh! Runs fine in SSRS2005 web window, when I export it to PDF
> only the first page has my database field in the footer.
>
>
> Steve MunLeeuw
>
>
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:6866232F-21F1-42BB-A593-FCD33A2BE344@.microsoft.com...
>> Hi,
>> I have created a multi-page report. I want to display a text box on each
>> page of the report. How to do so?
>> Note that I cannot make the text box as a part of data region (like
>> list).
>> Its a kind of header information that need to be outputed in each page.
>> I cannot place the text box in header of the page since it takes value
>> from
>> a database field. The header doesn't accept database fields.
>> regards,
>> Sachin.
>|||The parameter technique is better, I will use that.
"Steve MunLeeuw" <smunson@.clearwire.net> wrote in message
news:ORGfb5LNGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I think this is a difficult thing to do. One approach is to ensure that a
>database field in a textbox is on each page of the report body. You may
>then reference the textbox in the headder or footer with the ReportItems!
>collection. We have reports that extend multiple pages when exported to
>PDF format. I tried making a really tall and narrow textbox and this would
>work for the first two pages, but when the matrix at the bottom of the
>report grew onto a third page, the third page would not have a textbox and
>the reference would be empty.
> We took another approach, we passed in the ReportItems collection to a
> custom assembly and kept a 'last known good' reference to it. We would
> then call this method on each page and retrieve the last known good. I
> had to pass in the whole ReportItems collection because passing in just
> the report item would result in an #Error for pages that didn't contain
> the hidden text box. From what I can tell all references in an expression
> are resolved which is why I passed the collection. I think this would
> also work in code behind without the custom assembly, but haven't tried
> it.
> Hidden textbox in report body:
> txtSchoolNameHidden
> =First(Fields!OrganizationName.Value, "GetOrgInfo")
> Expression in footer:
> =Code.loc.SchoolName(ReportItems)
>
> Code in Custom Assembly (because we are using a property, had to create an
> instance, rather than static method)
> private string _SchoolName;
> public string
> SchoolName(Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItems
> input)
> {
> string ReturnVal = null;
> try
> {
> Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem
> rptItemSchoolNameHidden = null;
> rptItemSchoolNameHidden = input["txtSchoolNameHidden"];
> if (rptItemSchoolNameHidden != null)
> {
> _SchoolName = rptItemSchoolNameHidden.Value.ToString();
> }
> }
> catch
> {
> //ignore report item not found error.
> }
> return _SchoolName;
> }
>
> Seems like a lot of work for something simple. Any other ideas would be
> appreciated.
> Aaarrrghhh! Runs fine in SSRS2005 web window, when I export it to PDF
> only the first page has my database field in the footer.
>
>
> Steve MunLeeuw
>
>
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:6866232F-21F1-42BB-A593-FCD33A2BE344@.microsoft.com...
>> Hi,
>> I have created a multi-page report. I want to display a text box on each
>> page of the report. How to do so?
>> Note that I cannot make the text box as a part of data region (like
>> list).
>> Its a kind of header information that need to be outputed in each page.
>> I cannot place the text box in header of the page since it takes value
>> from
>> a database field. The header doesn't accept database fields.
>> regards,
>> Sachin.
>

Display text in SQL reports

Good evening. This may be an easy question but I am using SMS Reporting
(which is SQL Reporting Services) to generate reports for inventory
collections, etc. I am not the greatest with SQL but is / how can I display
plain text in my reports? This text is descriptive / comment text and not
part of the SQL data itself.
Thanks in advance for any assistance,
UCGOn Jun 26, 4:53 pm, UnderCoverGuy
<UnderCover...@.discussions.microsoft.com> wrote:
> Good evening. This may be an easy question but I am using SMS Reporting
> (which is SQL Reporting Services) to generate reports for inventory
> collections, etc. I am not the greatest with SQL but is / how can I display
> plain text in my reports? This text is descriptive / comment text and not
> part of the SQL data itself.
> Thanks in advance for any assistance,
> UCG
On the Layout view, in the toolbox, select a textbox control and add
it to the report and then click inside the textbox and enter the text.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks - but unfortunately it won't work for this situation. What I have to
do is directly edit SQL code / statements - no controls or GUI.
Any other thoughts anyway?
Thanks Enrique,
UCG|||On Jun 26, 8:06 pm, UnderCoverGuy
<UnderCover...@.discussions.microsoft.com> wrote:
> Thanks - but unfortunately it won't work for this situation. What I have to
> do is directly edit SQL code / statements - no controls or GUI.
> Any other thoughts anyway?
> Thanks Enrique,
> UCG
Could you explain the situation in more detail? I'm not quite
following you.
Enrique Martinez
Sr. Software Consultant|||See below:
---
select distinct
v_GS_Computer_system.Name0 AS "PC Name"
, v_GS_Computer_system.UserName0 AS "User last logged on"
, v_gs_Operating_system.csdversion0 AS "SP Level"
, v_GS_Computer_system.manufacturer0 AS "Make"
, v_GS_Computer_system.model0 AS "Model"
, v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"
, v_gs_processor.maxclockspeed0 AS "CPU Speed"
, v_gs_disk.Size0 AS "Disk Size"
, v_R_System.Active0 AS "Active"
from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_GS_video_controller,
v_GS_Operating_system,
v_GS_Network_Adapter_Configur,
v_R_System,
v_gs_disk
WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_disk.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 like '%Microsoft Windows 2000 Pro%'
and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
and v_GS_x86_PC_memory.totalphysicalmemory0 > '512384'
and v_gs_processor.maxclockspeed0 > '1400'
and v_gs_disk.Size0 > '10000'
and v_R_System.Active0 = 1
Order by
v_gs_computer_system.name0
---
This isn't the entire report but it should help you get the gist of what I
am trying to do. SMS Reporting (which is a SQL Reporting Services back-end)
is how this is being done (no GUI, no wizards, no controls, etc. but only a
SQL code editor). This report will gather info from the SQL db (such as HD
size, CPU speed, etc.) and display the systems where the criteria is met.
There is a header with the "entire" report that will explain what the data is
for. Now, I need to show more reports with different criteria (but from
within the same report). So, what I have done is copy this report (above)
and paste it at the end of what I have and change the criteria (where RAM <
512000, etc.) so that we see which systems meet different criteria - so that
we can install XP on it. Next, copy / paste the same report to the end of
what is already there and again, change the criteria (maybe HD size needs).
Basically, combining several reports into one. I could break these into
individual reports and link to each of them from the main report but I don't
know how to do that. I need to display a header (or comments) at the
beginning of each so that the reader of the report knows which section is
which. So, unless I can figure out how to "chain" reports, i.e.,
sub-reports, then what I need to do is try and display comments (like a
header - text for each report) along the way at the start of each section
(i.e., comment - "This section shows HD's needing to be upgraded / replaced",
comments like that).
Hope this helps explain it.
Thanks in advance,
UCG|||I think this may be helpful. You can simply select text and give it a
column header and include it in your query. Each row will then have the
text (I'm calling it group_text) and you can group on it and produce your
header row so that it's not repeated. See the following example:
select distinct
'This section shows HDs needing to be upgraded / replaced' AS group_text,
v_GS_Computer_system.Name0 AS "PC Name"
, v_GS_Computer_system.UserName0 AS "User last logged on"
, v_gs_Operating_system.csdversion0 AS "SP Level"
, v_GS_Computer_system.manufacturer0 AS "Make"
, v_GS_Computer_system.model0 AS "Model"
, v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"
, v_gs_processor.maxclockspeed0 AS "CPU Speed"
, v_gs_disk.Size0 AS "Disk Size"
, v_R_System.Active0 AS "Active"
from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_GS_video_controller,
v_GS_Operating_system,
v_GS_Network_Adapter_Configur,
v_R_System,
v_gs_disk
WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_disk.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 like '%Microsoft Windows 2000 Pro%'
and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
and v_GS_x86_PC_memory.totalphysicalmemory0 > '512384'
and v_gs_processor.maxclockspeed0 > '1400'
and v_gs_disk.Size0 > '10000'
and v_R_System.Active0 = 1
Order by
v_gs_computer_system.name0
"UnderCoverGuy" <UnderCoverGuy@.discussions.microsoft.com> wrote in message
news:5E037208-3BA5-47CF-8B6A-C84F6A1EDC6F@.microsoft.com...
> See below:
> ---
> select distinct
> v_GS_Computer_system.Name0 AS "PC Name"
> , v_GS_Computer_system.UserName0 AS "User last logged on"
> , v_gs_Operating_system.csdversion0 AS "SP Level"
> , v_GS_Computer_system.manufacturer0 AS "Make"
> , v_GS_Computer_system.model0 AS "Model"
> , v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"
> , v_gs_processor.maxclockspeed0 AS "CPU Speed"
> , v_gs_disk.Size0 AS "Disk Size"
> , v_R_System.Active0 AS "Active"
> from
> v_gs_computer_system,
> v_GS_x86_PC_memory,
> v_GS_processor,
> v_GS_video_controller,
> v_GS_Operating_system,
> v_GS_Network_Adapter_Configur,
> v_R_System,
> v_gs_disk
> WHERE
> v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
> and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
> and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
> and v_GS_disk.resourceid = v_gs_computer_system.resourceid
> and v_GS_processor.resourceid = v_gs_computer_system.resourceid
> and v_GS_Operating_system.caption0 like '%Microsoft Windows 2000 Pro%'
> and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
> and v_GS_x86_PC_memory.totalphysicalmemory0 > '512384'
> and v_gs_processor.maxclockspeed0 > '1400'
> and v_gs_disk.Size0 > '10000'
> and v_R_System.Active0 = 1
> Order by
> v_gs_computer_system.name0
> ---
> This isn't the entire report but it should help you get the gist of what I
> am trying to do. SMS Reporting (which is a SQL Reporting Services
> back-end)
> is how this is being done (no GUI, no wizards, no controls, etc. but only
> a
> SQL code editor). This report will gather info from the SQL db (such as
> HD
> size, CPU speed, etc.) and display the systems where the criteria is met.
> There is a header with the "entire" report that will explain what the data
> is
> for. Now, I need to show more reports with different criteria (but from
> within the same report). So, what I have done is copy this report (above)
> and paste it at the end of what I have and change the criteria (where RAM
> <
> 512000, etc.) so that we see which systems meet different criteria - so
> that
> we can install XP on it. Next, copy / paste the same report to the end of
> what is already there and again, change the criteria (maybe HD size
> needs).
> Basically, combining several reports into one. I could break these into
> individual reports and link to each of them from the main report but I
> don't
> know how to do that. I need to display a header (or comments) at the
> beginning of each so that the reader of the report knows which section is
> which. So, unless I can figure out how to "chain" reports, i.e.,
> sub-reports, then what I need to do is try and display comments (like a
> header - text for each report) along the way at the start of each section
> (i.e., comment - "This section shows HD's needing to be upgraded /
> replaced",
> comments like that).
> Hope this helps explain it.
>
> Thanks in advance,
> UCG
>|||That was exactly what I needed. You response is MUCH appreciated.
Thanks again,
UCG

display text in html format

hi,
i have a column with text "<b>test</b>."
how can i display the "test" as bold instead of <b>test</b>
cynthiaselect the text box >> select B in the formatting toolbar or select the format option and the click the bold option.|||but then i'll be forever bold, m i right?

the data i'm going to display are in html format. for example, <b>test</b><u>under</u>. base on the data, the crystal report display the data in html format.

any idea?

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.