Distinct Type Count

I have a weird MDX request and I'm unsure of how to accomplish this.

My relation table has two fields Department(int) and Employee Type(int)

I need to get a distinct count of the number of distinct employee types per Department.

E.g for the data below: (calculated member) DistinctTypeCount=4 (when dep=1) (four distinct types of employees in this department

Dep. Emp. Type

-

1 23

1 2

1 4

1 23

1 4

1 4

1 10

Can anyone suggest an mdx query for this calculated member? If I redesigning the relational view on which the cube is based makes things easier I can definitely go that route.

>My relation table has two fields Department(int) and Employee Type(int)

How the fields are exposed in your UDM? Are they dimension attributes? What design has the dimension?

|||yes they are dimension attributes.

DISTINCT PROBLEM

i have two fields. One is id (otomatic number) and another is email..
there are some duplicate records in email records.
I want to use distinct query for email field to eliminate duplicate
records..I also want to get id fields of resultset of distinct email.
I thought that there are more than one id value for duplicate records. Can i
get max or min id valure of duplicate records or what algorithm sql server
does use ?Maybe something like...
SELECT email, MAX([ID]) AS [ID]
FROM table
GROUP BY email
ORDER BY email
HTH,
Ben
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
>i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
> i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>|||Untested...
Select A.ID, A.EMAIL from MYTABLE A
where exists (select 1 from MYTABLE B
where B.EMAIL = A.EMAIL
and B.ID < A.ID)
This should select all rows with duplicate EMAIL except for the EMAIL with
the lowest numbered ID.
You could also use:
select A.EMAIL, count(*) from MYTABLE A
group by A.EMAIL
having COUNT(*) > 1
Which will give you a list of all EMAILs occuring more than once, along with
how many times they occur. After you ge the data cleaned up you can add a
unique constraint which will prevetn duplicate emails from getting inserted
in the future.
"Savas Ates" <in da club> wrote in message
news:OtQCQLyNGHA.916@.TK2MSFTNGP10.phx.gbl...
> i have two fields. One is id (otomatic number) and another is email..
> there are some duplicate records in email records.
> I want to use distinct query for email field to eliminate duplicate
> records..I also want to get id fields of resultset of distinct email.
> I thought that there are more than one id value for duplicate records. Can
i
> get max or min id valure of duplicate records or what algorithm sql server
> does use ?
>sql

Distinct email addresses in 2 tables with different field names

Hi everyone,
I have 2 tables with table A containing an 'email' field and table B
containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it possible
to issue a query that would return only the unqiue email addresses in these
3 fields? So a long list with no duplicate emails(distinct).
Thank you
Maz.Hi Maz,
Please don't post questions independently in multiple newsgroups. You
question has already been answered in .programming.
--
Jacco Schalkwijk
SQL Server MVP
"Maziar Aflatoun" <maz88@.rogers.com> wrote in message
news:STSEb.32573$2We1.12257@.news04.bloor.is.net.cable.rogers.com...
> Hi everyone,
> I have 2 tables with table A containing an 'email' field and table B
> containing 2 fields 'primaryemail' and 'secondaryemail'. Now is it
possible
> to issue a query that would return only the unqiue email addresses in
these
> 3 fields? So a long list with no duplicate emails(distinct).
> Thank you
> Maz.
>
>

Displaying Updated Columns

I want to use a gridview in my asp.net app to just show changes to one field in an employee table. I have a history table trigger on all fields already for other reasons, however for this purpose i want to be able to show the user what the column said before and after the change.

I read up on the columns_updated function and it seems like it could work however It seems there would be an easier way than figuring out what the bitmask is on my 14 column table for a change on the 3rd column?

Any ideas.Okay I think I already solved this using a stored procedure joining my employee table and employee history table.sql

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 related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from
a table, for eg if the table has 5 columns and two rows have same values for
all these five columns , the sql statement should be able to find all such
matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harshaselect col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/feat...cle.php/2235081
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harsha
select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/featu...le.php/2235081
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching rows in the table and display them to the user
How would i go about doing this
thank yo
harshaharsha,
You can code a self-join on the table.
Ex. SELECT A.* FROM mytable A JOIN mytable B ON
a.col1 = b.col1 and a.col2 = b.col2 and b.col3 = b.col3
and ....
Might be other ways, but this should work for you.
Doug
>--Original Message--
>Dear All,
> I want to write an SQL program that would
display all identical fields from a table, for eg if the
table has 5 columns and two rows have same values for all
these five columns , the sql statement should be able to
find all such matching rows in the table and display them
to the user.
>How would i go about doing this.
>thank you
>harsha
>.
>|||select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/features/mssql/article.php/2235081
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

Displaying records in subreport

My report is grouped by the following fields.

Region
Sector
Interval
Area
Crew Unit

I have 8 different formulas that counts the records found under each and display them under each area by crew tech.

It looks something like this

Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: ESU

crew/tech Equip Not Available Parts On Order Timely Complete etc.
AAA 5 0 2

------
Next page
Region: Central
Secotr: 11
Interval: Weekly or Greater (this is based on a formula)
Area: COMM

crew/tech Equip Not Available Parts On Order Timely Complete etc.
BBB 0 0 3
CCC 5 0 1

----

Now, I have a subreport where I want to display the records found under
each column in detail but for both areas combined. So for "Equip Not Available" it needs to display 5+5 records under Area-ESU and COMM. Then on the next page it will display 2+3+1 records for "Timely Complete" for Area-ESU and COMM. What I did was in the subreport I created another formula {@.grpname} which had above formulas combined and grouped the subreport by that formula. That works fine.
But the problem is that - Out of the 8 formulas I want to display detail records for only 5 of them even if it finds records for the other 3 formulas.
This is how my {@.grpname} formula looks like -

If {pPMPERDET.COMPLETION_STATUS} = "X" AND {pPMPERDET.END_DATE_TIME} < {pPMPERDET.LATEST_DATE} then
"Equip Not Available" else

If {pPMPERDET.COMPLETION_STATUS}) = "O" AND
{pPMPERDET.INTERVAL_LENGTH = ''} then
"Timely Complete"
//etc for all the 5 groups

Right now it displays the records for the 5 groups but in addition to that it is also displaying the remaining records found under no heading. How should I stop it from displaying these records?

ThanksI'm not entirely sure what it is you're describing below, but it sounds like you want to look at suppressing either fields or entire subreports/sections.

To suppress a subreport : put it in it's own section, right click the section and go to Section Expert -> next to the Suppress option there is an x+2 button.

To suppress a field : right click it, go to Format Field (Common tab) and next to Suppress option is the x+2 box.

Suppression formula must return true or false. Use formula editor to get the result you're after, for example...

If IsNull( [your group name] ) then
true --yes, suppress the subreport/field
else
false --no, don't suppress.|||Got it. Works gr8! Thanks

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

displaying null fields

i have a query that gets orders made, visits to the page, and revenue produced for each day in a month and country we ship to.

when there are no visits to the page, then there are no orders and no revenue is produces, so those fields are null. However, instead of displaying a row with the date and country and null values for the rest, the query just skips those rows entirely.

is there anyway to get what I want either using crystal or the sql (i.e, a list of every day in the month with the specific info for that day, regardless if it's null (cause i can always replace the null values with something else))?

P.S. it's an Oracle DBCan you post your SQL and the selection formula?
Do you really mean that you have data whose values are NULL or are you, for example, joining to an orders table and the lack of orders means there is no join so there is no data?|||I think istead of Inner join, you need to use Left outer Join

displaying non-duplicate keys of all duplicate entries

Ok, so I'm checking for duplicate data in a table. Let's say it has 3 data fields and a key field (i.e. "ID", "FIRST", "MIDDLE", "LAST"). No keys are duplicated. If I find entries that have the same data in each of the non-key fields, I want to know the keys for all those entries. I have been able to find duplicate rows using this...

SELECT
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
FROM
TABLE
GROUP BY
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
HAVING
COUNT(*) > 1

Unfortunately I've found no way to incorporate the return of the TABLE."ID" for every duplicated entry. Is there some way I can join the result with the db.table to find this, or some other way to make this happen?

Thanks,
DeanSure, use:SELECT
A."ID", A."FIRST", A."MIDDLE", A."LAST"
FROM TABLE AS A
WHERE 1 < (SELECT Count(*)
FROM TABLE AS B
WHERE B."FIRST" = A."FIRST"
AND B."LAST" = A."LAST"
AND B."MIDDLE" = A."MIDDLE")-PatP|||Thanks, that worked well, although it takes a good while for the server to process the query.|||Indicies would help this query a lot, particularly an index on last, first, middle.

-PatP

Displaying Fields from Multiple Datasets (RS2000)

Hi guys, I have this problem of displaying fields from multiple datasets.

When I drag a field from the first dataset into a table, it works and displayed correctly. However when I dragged a field from a second dataset, it will be shown as

=First(Fields!FirstName.Value, "DataSet2") for Strings and

=Sum(Fields!StatusFlag.Value, "DataSet2") for Integers

I just want the integer from that single row but it sums up all the rows. When I removed the SUM keyword, the IDE will show build errors when I try to build the report. Is there any way to get around this? thanks!

Use the First keyword for integers as well.|||According to the data types, the IDE will always choose SUM as the aggregate for numeric values and FIRST as the aggregate for non-Integers. As Brad mentioned you can changed that, this is just a suggestion of the Designer.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Display SQL server Image fields in reporting services headers or footers

I know you cannot directly place data fields in the headers or footers
with SQL server reporting services. You can trick it by putting a
hidden text field in the body of the report and referencing it in the
header or footer by using ReportItems.
How do you display SQL server Image data fields in headers or footers?
I tried using Report parameters, but that caused unpredictable results
with my text fields. Report parameters don't have an option for Image
fields for the data type which makes sense.
Appreciate your help.You need encode image in the parameter, and decode it later
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jtabar" <john.tabar@.usa.net> wrote in message
news:b62a5355.0409231320.3b340324@.posting.google.com...
>I know you cannot directly place data fields in the headers or footers
> with SQL server reporting services. You can trick it by putting a
> hidden text field in the body of the report and referencing it in the
> header or footer by using ReportItems.
> How do you display SQL server Image data fields in headers or footers?
> I tried using Report parameters, but that caused unpredictable results
> with my text fields. Report parameters don't have an option for Image
> fields for the data type which makes sense.
> Appreciate your help.|||Lev, tnx for your help. With some help from Microsoft support, I got
it to work by using a textbox in the body of the report and setting
the value to:
=System.Convert.ToBase64String(First(Fields!MyImage.Value, "MyDB"))
Then setup an image field in the footer and set the following:
- MIMEType: image/bmp
- Source: Database
- Value: =System.Convert.FromBase64String(ReportItems!Mytextbox.value)
I couldn't get the parameter to work. Wasn't sure how to do what you
suggested by "You need encode image in the parameter" in your note.
"Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message news:<usSu5yfoEHA.2948@.TK2MSFTNGP11.phx.gbl>...
> You need encode image in the parameter, and decode it later
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "jtabar" <john.tabar@.usa.net> wrote in message
> news:b62a5355.0409231320.3b340324@.posting.google.com...
> >I know you cannot directly place data fields in the headers or footers
> > with SQL server reporting services. You can trick it by putting a
> > hidden text field in the body of the report and referencing it in the
> > header or footer by using ReportItems.
> >
> > How do you display SQL server Image data fields in headers or footers?
> >
> > I tried using Report parameters, but that caused unpredictable results
> > with my text fields. Report parameters don't have an option for Image
> > fields for the data type which makes sense.
> >
> > Appreciate your help.|||It is similar technique, where parameter is used instead of hidden textbox
to pass encoded image
Create calculated field "MyEncodedImage" and set expression to
=System.Convert.ToBase64String(Fields!MyImage.Value)
Create report parameter "MyEncodedImageParam", set its type to String, erase
Prompt, clear Allow Null and Allow Blank checkboxes, set default value "from
query" and select MyDB as dataset and MyEncodedImage as value field.
Set value for your image in the footer to
=System.Convert.FromBase64String(Parameters!MyEncodedImageParam.value)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"jtabar" <john.tabar@.usa.net> wrote in message
news:b62a5355.0409241237.53141003@.posting.google.com...
> Lev, tnx for your help. With some help from Microsoft support, I got
> it to work by using a textbox in the body of the report and setting
> the value to:
> =System.Convert.ToBase64String(First(Fields!MyImage.Value, "MyDB"))
> Then setup an image field in the footer and set the following:
> - MIMEType: image/bmp
> - Source: Database
> - Value: =System.Convert.FromBase64String(ReportItems!Mytextbox.value)
> I couldn't get the parameter to work. Wasn't sure how to do what you
> suggested by "You need encode image in the parameter" in your note.
>
> "Lev Semenets [MSFT]" <levs@.microsoft.com> wrote in message
> news:<usSu5yfoEHA.2948@.TK2MSFTNGP11.phx.gbl>...
>> You need encode image in the parameter, and decode it later
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "jtabar" <john.tabar@.usa.net> wrote in message
>> news:b62a5355.0409231320.3b340324@.posting.google.com...
>> >I know you cannot directly place data fields in the headers or footers
>> > with SQL server reporting services. You can trick it by putting a
>> > hidden text field in the body of the report and referencing it in the
>> > header or footer by using ReportItems.
>> >
>> > How do you display SQL server Image data fields in headers or footers?
>> >
>> > I tried using Report parameters, but that caused unpredictable results
>> > with my text fields. Report parameters don't have an option for Image
>> > fields for the data type which makes sense.
>> >
>> > Appreciate your help.

Display Report AUTHOR and DESCRIPTION

What is the syntax to display the report property
fields within a textbox on the report header ?These properties are not exposed in the Report Object Model (which is
accessible from expressions within a report).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <davidldoyle@.yahoo.com> wrote in message
news:4e7ae5a2.0406210905.67ff246e@.posting.google.com...
> What is the syntax to display the report property
> fields within a textbox on the report header ?|||"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message news:<uqo1jCAWEHA.2340@.TK2MSFTNGP09.phx.gbl>...
> These properties are not exposed in the Report Object Model (which is
> accessible from expressions within a report).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Dave" <davidldoyle@.yahoo.com> wrote in message
> news:4e7ae5a2.0406210905.67ff246e@.posting.google.com...
> > What is the syntax to display the report property
> > fields within a textbox on the report header ?
I did not understand your reply in parenthesis....Do you mean
properties are not available, or that they are within expressions
within a report.
Thank you.|||The author and description properties are not available to textboxes. Your
choices are to either hard code the values into a textbox(es) or to add the
information to a table and pull it into the report using a dataset.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <davidldoyle@.yahoo.com> wrote in message
news:4e7ae5a2.0406221148.7468775@.posting.google.com...
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<uqo1jCAWEHA.2340@.TK2MSFTNGP09.phx.gbl>...
> > These properties are not exposed in the Report Object Model (which is
> > accessible from expressions within a report).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> >
> > "Dave" <davidldoyle@.yahoo.com> wrote in message
> > news:4e7ae5a2.0406210905.67ff246e@.posting.google.com...
> > > What is the syntax to display the report property
> > > fields within a textbox on the report header ?
> I did not understand your reply in parenthesis....Do you mean
> properties are not available, or that they are within expressions
> within a report.
> Thank you.|||I meant that expressions in reports can only access information and metadata
that is exposed in the Report Object Model. This object model contains the
following collections: Fields, Reportitems, Parameters, Globals, User.
The properties for Author and Description are not exposed there. So you
cannot access them.
BTW: these properties are exposed in the Rendering Object Model, but you
would need to write your own custom rendering extension to take advantage of
that and it would still be very hard to solve your initial problem (show
these properties in header / footer).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <davidldoyle@.yahoo.com> wrote in message
news:4e7ae5a2.0406221148.7468775@.posting.google.com...
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<uqo1jCAWEHA.2340@.TK2MSFTNGP09.phx.gbl>...
> > These properties are not exposed in the Report Object Model (which is
> > accessible from expressions within a report).
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> >
> > "Dave" <davidldoyle@.yahoo.com> wrote in message
> > news:4e7ae5a2.0406210905.67ff246e@.posting.google.com...
> > > What is the syntax to display the report property
> > > fields within a textbox on the report header ?
> I did not understand your reply in parenthesis....Do you mean
> properties are not available, or that they are within expressions
> within a report.
> Thank you.

Display over 256 characters for VARCHAR(3500) field

Hopefully, someone can help me.
I am working with a database that contains multiple fields within the tables that are being used for Clinical notes. The fields are defined as VARCHAR(3500). But when I try to extract data (either through Query Analyzer or Crystal Reports), only the first 256 characters are displayed. I ran a query to give me the length of the maximum entry size which returned 2722 characters, yet only 256 are displayed.

How do I go about extracting ALL of the data from this field? Any help is much appreciated.

Thanks in advance.In Query Analyzer, select Options from the menu and bump up the maximum output characters, which defaults to 256.

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 fields vertically and horizontally

hello, i just started using crystal reports and have been trying to get the report to display the same fields as shown below.

2006/02/11 2005/11/09 2006/01/01
James john sandy

2003/08/16 2007/01/22 2006/01/01
Jane fred martha

2004/12/10 2005/05/03 2006/07/03
phil diane roger

The data is all come from the same fields (Start_Date) and (first_name) .

is it possible to display the data as such using crystal reports?

regards,

peter.Try this.
Place first field in the details section. In section expert check the box "Format with multiple columns". Set your options on layout tab.

Next, right click on details section and select "Insert section below". Place your second field in this section. Repeat the steps from above ("format with multiple columns", etc).

Hope this helps.