Distinct document map labels

Here is what my document map tree looks like know...
Plant 1
Product Type 1
Product Code 1
Plant 1
Product Type 2
Product Code 1
Plant 1
Product Type 2
Product Code 2
Plant 1
Product Type 2
Product Code 3
What I would like the tree to look like is...
Plant 1
Product Type 1
Product Code 1
Product Type 2
Product Code 1
Product Code 2
Product Code 3
My table is grouped by the plant, product type and product code with no
details group.
Thanks for your helpyou have to make the groups to display like you want the doc map...
--
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
"Heather M" <HeatherM@.discussions.microsoft.com> wrote in message
news:13BCFDAD-96C4-44FB-8CB1-2A47D2441B45@.microsoft.com...
> Here is what my document map tree looks like know...
> Plant 1
> Product Type 1
> Product Code 1
> Plant 1
> Product Type 2
> Product Code 1
> Plant 1
> Product Type 2
> Product Code 2
> Plant 1
> Product Type 2
> Product Code 3
> What I would like the tree to look like is...
> Plant 1
> Product Type 1
> Product Code 1
> Product Type 2
> Product Code 1
> Product Code 2
> Product Code 3
> My table is grouped by the plant, product type and product code with no
> details group.
> Thanks for your help|||Thanks for your reply. I thought I had my groups setup correctly. The list
I had my tables in was the culprit.

Distinct Count of Customers

Hi All,

We are following the code given below

triptype customerid
van 24
van 25
bus 24
van 24
van 25

if triptype='van' then
customerid
else
0

We r making distinct count of above formula
now we getting distinct count as 3(Include 24,25 ,0)

but we need distinct count as 2 So there is any solution plz suggest meGroup the report by triptype and Right click on the customerid column; Insert Summary;choose distinct count;

Distance Between Postal Codes

I'm looking to find out how I'd go about setting up a database where a
visitor to my site could punch in their postal code, and find out how far
they are from another postal code. For example, AutoTrader has this feature
I believe to tell you how far the vehicle is from you. Dating sites have
them so you can do proximity searches.

Anyone have any ideas where I could start? I'm thinking the post office,
but if anyone else has suggestions, I'm open to hear them.

Thanks!Are you in the UK? Royal Mail has some address database products that
may meet your requirements:

http://www.royalmail.com/portal/rm/...40085&gear=shop

Other solutions exist too:
http://www.google.co.uk/search?biw=...yUK%7CcountryGB

--
David Portas
SQL Server MVP
--|||This company does mailing list software with that data in it:

http://www.melissadata.com/Lookups/|||Actually, no, I'm in North America (Canada). Sorry for not mentioning that.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1120060282.670987.93490@.g14g2000cwa.googlegro ups.com...
> Are you in the UK? Royal Mail has some address database products that
> may meet your requirements:
> http://www.royalmail.com/portal/rm/...40085&gear=shop
> Other solutions exist too:
> http://www.google.co.uk/search?biw=...yUK%7CcountryGB
> --
> David Portas
> SQL Server MVP
> --|||Be aware of the limitations of these calculations. I have done something
similar with US zipcodes, same principle. Each zone has a latitude and
longitude associated with it. This point is known as a centroid which is a
representative point for the whole zone. If the zone is a square or a circle
the centroid would be right in the middle. For a very irregular zone the
centroid might not even be in the zone. Think of a doughnut shaped zone.

Then you calculate the distance between the 2 centroids. Imagine 2 100 mile
square zones side by side. The distance calculation would be 100 miles. The
2 interested parties could actually be across the street from each other
each in their own zone.

Displaying Total number of Rows in a Report in Page Header.

Hi,

I have requirement to display Total number of Rows in a Report in Page Header.

I have written the following code in Page header it shows RowCount for the Page only.

=Count(ReportItems!textboxInTableCell.Value)

Can anyone please help on this?

Regards

Raghav

By Total number of reports in the report do you mean the number of rows returned by the Dataset query? If so, add a textbox in your Report Body with the expression =CountRows("DataSet1") with the name of your Dataset in place of DataSet1.

Then refer to this textbox directly in the Page Header.

This should give you the total row count for your Dataset.

-Aayush

|||

Thanks aayush,

I used =CountRows() in body header and set the RepeatWith property to "tableName" and it works as Page header.

Regards

Raghavendra

Displaying same column name in the same column

Hi, I'm trying to accomplish something with this code:

SELECT ProductionOrder.PO, ProductionOrder.Part, Single.Length,

Single.Date, Pairs.Length, Pairs.Date FROM [ProductionOrder]

LEFT OUTER JOIN Pairs ON Pairs.PO = [ProductionOrder].PO AND

Pairs.Part = [ProductionOrder].Part

LEFT OUTER JOIN Single ON Single.PO = [ProductionOrder].PO AND

Single.Part = [ProductionOrder].Part

WHERE (Single.Broke='True' AND (Single.[BrokeFixed] = 'False' OR Single.[BrokeFixed] IS NULL))

OR (Pairs.Broke='True' AND (Pairs.[BrokeFixed] = 'False' OR Pairs.[BrokeFixed] IS NULL))

With this I get the following result:

PO | Part | Length | Date | Length | Date

-

602520 | 3 | 24000 | 2007-08-24 15:33:33.727 | NULL | NULL
602521 | 3 | NULL | NULL | 14550 | 2007-08-29 17:41:01.930

But what I want is:

PO | Part | Length | Date

602520 | 3 | 24000 | 2007-08-24 15:33:33.727
602521 | 3 | 14550 | 2007-08-29 17:41:01.930

How can I accomplish this? Please, any help would be very much appreciated.

Thanks a lot in advance

You can use ISNULL or COALESCE.

eg SELECT ISNULL(Single.Date, Pairs.Date) AS Date, ISNULL(Single.Length, Pairs.Length) AS length

Remember, this method will always use Single.Date unless its NULL so if both are populated, then Single.Date will be displayed. This also won't check a dependancy on the Date and Length ie its possible that you could have a Single.Date with a Pairs.Length unless you are 100% sure this situation could never occur. If it might, you may want to consider using a CASE statement to choose which values to display.

Check CASE/ISNULL/COALESCE out in more detail in Books Online.

HTH!

|||

Thanks a lot! That's excatly what I needed. I'm not an expert T-SQL programmer so I was getting a hard time trying

to get this result. And no, they will never be both populated as there will never be the same PO number on both

Single and Pairs table. But both PO numbers will be on the ProductionOrder Table as all POs (ProductionOrders)

will be on the ProductionOrder table, but each PO has a corresponding table, defining its type with its details (Single, Pairs, Groups, and FinalProducts).

Thanks again. Both of you who tried to help me out.

Regards

Fábio

Displaying max values of each group in SQL

Hello everyone;

I have here a code that displays the most recent date for each group of records. But the problem is, I am not able to include some fields of the table.

There are 3 tables named CUST, ACCT, and TRAN:

CUST:
CNO NAME
CN101 DAN
CN102 AAA

ACCT:
ANO CNO
AN101 CN101
AN102 CN102

TRAN:
TNO ANO TDATE BAL
TN101 AN101 01/25/2006 3,000
TN102 AN101 02/15/2006 5,000
TN103 AN102 02/01/2006 4,000
TN104 AN102 02/27/2006 8,000
TN105 AN102 03/18/2006 2,000

And the resultant table should look something like this:

ANO NAME TDATE BAL
AN101 AAA 02/15/2006 5,000
AN102 BBB 03/18/2006 2,000

Now, here's my code:

SELECT DISTINCT

B.NAME,
C.ANO,
MAX(A.TDATE)

FROM TRAN A,
CUST B,
ACCT C

WHERE B.CNO = C.CNO
AND C.ANO = A.ANO

GROUP BY B.CNO,
B.NAME,
C.ANO;

And the resultant table is:

NAME ANO MAX(TDATE)
AN101 AAA 02/15/2006
AN102 BBB 03/18/2006

The problem is, I want to add the field 'BAL' to the resultant table but when I insert 'BAL' to the 'SELECT' clause, the result will look something like this:

ANO NAME TDATE BAL
AN101 AAA 01/25/2006 3,000
AN101 AAA 02/15/2006 5,000
AN102 BBB 02/01/2006 4,000
AN102 BBB 02/27/2006 8,000
AN102 BBB 03/18/2006 2,000

I will really appreciate any help.

Thnks,
dan15phselect B.NAME
, C.ANO
, A.TDATE
, A.BAL
from TRAN A
inner
join ACCT C
on C.ANO = A.ANO
inner
join CUST B
on C.CNO = B.CNO
where A.TDATE
= ( select max(TDATE)
from TRAN
where ANO = A.ANO )|||Sorry for taking so looong to reply. But anyway, thanks for the help r937 (http://www.dbforums.com/member.php?find=lastposter&t=1606412). I finally made it. just made a couple of changes to the code. Actually, I'm still a newbie in SQL and havent used 'inner join' (just recently) and seldom in using inner queries. thanks a lot for the help.:D

Displaying last record in SQL database table

Got a question here and as I am no expert programmer, this should be easy for you gurus. I have this fairly generic code I've created where I return data from an SQL table in a DataList control. I want to take it to the next level and return only the last record in the table, but I am unsure of how to do that. Perhaps I shouldn't even be using a DataList control, I'm not sure.

Basically, I have a form I developed in Visual Studio using ASP.NET VB. I submit the form and now I want to recall the last entry into the database I would have just made and display it on the following page (thank you page).
Here is the code I have:

PublicClass WebForm1

Inherits System.Web.UI.Page

ProtectedWithEvents SqlSelectCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlInsertCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlUpdateCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlDeleteCommand1As System.Data.SqlClient.SqlCommand

ProtectedWithEvents SqlConnection1As System.Data.SqlClient.SqlConnection

ProtectedWithEvents SqlDataAdapter1As System.Data.SqlClient.SqlDataAdapter

ProtectedWithEvents DataSet1As System.Data.DataSet

ProtectedWithEvents DataList1As System.Web.UI.WebControls.DataList

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.

<System.Diagnostics.DebuggerStepThrough()>PrivateSub InitializeComponent()

Me.SqlSelectCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlInsertCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlUpdateCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlDeleteCommand1 =New System.Data.SqlClient.SqlCommand()

Me.SqlConnection1 =New System.Data.SqlClient.SqlConnection()

Me.SqlDataAdapter1 =New System.Data.SqlClient.SqlDataAdapter()

Me.DataSet1 =New System.Data.DataSet()

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).BeginInit()

'

'SqlSelectCommand1

'

Me.SqlSelectCommand1.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM" & _

" authors"

Me.SqlSelectCommand1.Connection =Me.SqlConnection1

'

'SqlInsertCommand1

'

Me.SqlInsertCommand1.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, phone, address, city, state, zip, " & _

"contract) VALUES (@.au_id, @.au_lname, @.au_fname, @.phone, @.address, @.city, @.state," & _

" @.zip, @.contract); SELECT au_id, au_lname, au_fname, phone, address, city, state" & _

", zip, contract FROM authors WHERE (au_id = @.au_id)"

Me.SqlInsertCommand1.Connection =Me.SqlConnection1

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

'

'SqlUpdateCommand1

'

Me.SqlUpdateCommand1.CommandText = "UPDATE authors SET au_id = @.au_id, au_lname = @.au_lname, au_fname = @.au_fname, ph" & _

"one = @.phone, address = @.address, city = @.city, state = @.state, zip = @.zip, cont" & _

"ract = @.contract WHERE (au_id = @.Original_au_id) AND (address = @.Original_addres" & _

"s OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_au" & _

"_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Origi" & _

"nal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (phon" & _

"e = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL AND" & _

" state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS NUL" & _

"L); SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract" & _

" FROM authors WHERE (au_id = @.au_id)"

Me.SqlUpdateCommand1.Connection =Me.SqlConnection1

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_id", System.Data.SqlDbType.VarChar, 11, "au_id"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_lname", System.Data.SqlDbType.VarChar, 40, "au_lname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.au_fname", System.Data.SqlDbType.VarChar, 20, "au_fname"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.phone", System.Data.SqlDbType.VarChar, 12, "phone"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.address", System.Data.SqlDbType.VarChar, 40, "address"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.city", System.Data.SqlDbType.VarChar, 20, "city"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.state", System.Data.SqlDbType.VarChar, 2, "state"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.zip", System.Data.SqlDbType.VarChar, 5, "zip"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.contract", System.Data.SqlDbType.Bit, 1, "contract"))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlUpdateCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlDeleteCommand1

'

Me.SqlDeleteCommand1.CommandText = "DELETE FROM authors WHERE (au_id = @.Original_au_id) AND (address = @.Original_addr" & _

"ess OR @.Original_address IS NULL AND address IS NULL) AND (au_fname = @.Original_" & _

"au_fname) AND (au_lname = @.Original_au_lname) AND (city = @.Original_city OR @.Ori" & _

"ginal_city IS NULL AND city IS NULL) AND (contract = @.Original_contract) AND (ph" & _

"one = @.Original_phone) AND (state = @.Original_state OR @.Original_state IS NULL A" & _

"ND state IS NULL) AND (zip = @.Original_zip OR @.Original_zip IS NULL AND zip IS N" & _

"ULL)"

Me.SqlDeleteCommand1.Connection =Me.SqlConnection1

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_id", System.Data.SqlDbType.VarChar, 11, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_id", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_address", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "address", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_fname", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_fname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_au_lname", System.Data.SqlDbType.VarChar, 40, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "au_lname", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_city", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "city", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_contract", System.Data.SqlDbType.Bit, 1, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "contract", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_phone", System.Data.SqlDbType.VarChar, 12, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "phone", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_state", System.Data.SqlDbType.VarChar, 2, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "state", System.Data.DataRowVersion.Original,Nothing))

Me.SqlDeleteCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Original_zip", System.Data.SqlDbType.VarChar, 5, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "zip", System.Data.DataRowVersion.Original,Nothing))

'

'SqlConnection1

'

Me.SqlConnection1.ConnectionString = "data source=NDAVENPORT2;initial catalog=pubs;persist security info=False;user id=" & _

"sa;workstation id=NDAVENPORT2;packet size=4096"

'

'SqlDataAdapter1

'

Me.SqlDataAdapter1.DeleteCommand =Me.SqlDeleteCommand1

Me.SqlDataAdapter1.InsertCommand =Me.SqlInsertCommand1

Me.SqlDataAdapter1.SelectCommand =Me.SqlSelectCommand1

Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "authors",New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("au_id", "au_id"),New System.Data.Common.DataColumnMapping("au_lname", "au_lname"),New System.Data.Common.DataColumnMapping("au_fname", "au_fname"),New System.Data.Common.DataColumnMapping("phone", "phone"),New System.Data.Common.DataColumnMapping("address", "address"),New System.Data.Common.DataColumnMapping("city", "city"),New System.Data.Common.DataColumnMapping("state", "state"),New System.Data.Common.DataColumnMapping("zip", "zip"),New System.Data.Common.DataColumnMapping("contract", "contract")})})

Me.SqlDataAdapter1.UpdateCommand =Me.SqlUpdateCommand1

'

'DataSet1

'

Me.DataSet1.DataSetName = "NewDataSet"

Me.DataSet1.Locale =New System.Globalization.CultureInfo("en-US")

CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).EndInit()

EndSub

PrivateSub Page_Init(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Init

'CODEGEN: This method call is required by the Web Form Designer

'Do not modify it using the code editor.

InitializeComponent()

EndSub

#EndRegion

PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load

SqlDataAdapter1.Fill(DataSet1)

DataList1.DataSource = DataSet1

DataList1.DataBind()

EndSub

EndClass

Any corrections, thoughts, comments, ideas, or criticism is welcome. Thanks.

Try sorting it usingdesc andtop 1 inselect statement.|||

Simple...great! Thanks for redirection.

Found that I could not order descending o_id field in my query. The syntax was fine, but it would not return the rows in descending order. I simply changed it wihin SQL Server itself (design) and returned top 1 and it's very nice now. Thanks!

Displaying data in Columns based on Criteria

I have data in rows that I want to display in columns based on a value in
each row.
Here's an example of the data:
Employee ID Type Code Amount
123 PAY BONUS 1,000
123 PAY SALARY 5,000
123 DED INS 500
123 DED DENTAL 100
123 DED FLEX 50
Here's how I'd like to display the data:
EMPLOYEE CODE AMOUNT CODE AMOUNT
123 BONUS 1,000 INS 500
123 SALARY 5,000 DENTAL 100
123 FLEX 50
My problem is understanding how to display the items in each column starting
at the top.
Thanks for any help.
--
Charles Allen, MVPOn Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I have data in rows that I want to display in columns based on a value in
> each row.
> Here's an example of the data:
> Employee ID Type Code Amount
> 123 PAY BONUS 1,000
> 123 PAY SALARY 5,000
> 123 DED INS 500
> 123 DED DENTAL 100
> 123 DED FLEX 50
> Here's how I'd like to display the data:
> EMPLOYEE CODE AMOUNT CODE AMOUNT
> 123 BONUS 1,000 INS 500
> 123 SALARY 5,000 DENTAL 100
> 123 FLEX 50
> My problem is understanding how to display the items in each column starting
> at the top.
> Thanks for any help.
> --
> Charles Allen, MVP
The best way to produce this type of layout is to create a matrix
report where your pivot column (the column that gets split into
multiple columns based on distinct values) is Type. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'll give it a shot. Thanks
--
Charles Allen, MVP
"EMartinez" wrote:
> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > I have data in rows that I want to display in columns based on a value in
> > each row.
> >
> > Here's an example of the data:
> > Employee ID Type Code Amount
> > 123 PAY BONUS 1,000
> > 123 PAY SALARY 5,000
> > 123 DED INS 500
> > 123 DED DENTAL 100
> > 123 DED FLEX 50
> >
> > Here's how I'd like to display the data:
> >
> > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > 123 BONUS 1,000 INS 500
> > 123 SALARY 5,000 DENTAL 100
> > 123 FLEX 50
> >
> > My problem is understanding how to display the items in each column starting
> > at the top.
> >
> > Thanks for any help.
> > --
> > Charles Allen, MVP
>
> The best way to produce this type of layout is to create a matrix
> report where your pivot column (the column that gets split into
> multiple columns based on distinct values) is Type. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Please do suggest me on the below requirement in addition to what Charles
Allen has asked for:
How can I add another column that shows the difference between first CODE
column value and second CODE column value?
Thanks in advance!
"Charles Allen" <callen@.nospam-bkd.com> wrote in message
news:AD136641-F3A8-42B3-A900-92E78174D443@.microsoft.com...
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
>
> "EMartinez" wrote:
>> On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
>> > I have data in rows that I want to display in columns based on a value
>> > in
>> > each row.
>> >
>> > Here's an example of the data:
>> > Employee ID Type Code Amount
>> > 123 PAY BONUS 1,000
>> > 123 PAY SALARY 5,000
>> > 123 DED INS 500
>> > 123 DED DENTAL 100
>> > 123 DED FLEX 50
>> >
>> > Here's how I'd like to display the data:
>> >
>> > EMPLOYEE CODE AMOUNT CODE AMOUNT
>> > 123 BONUS 1,000 INS 500
>> > 123 SALARY 5,000 DENTAL 100
>> > 123 FLEX
>> > 50
>> >
>> > My problem is understanding how to display the items in each column
>> > starting
>> > at the top.
>> >
>> > Thanks for any help.
>> > --
>> > Charles Allen, MVP
>>
>> The best way to produce this type of layout is to create a matrix
>> report where your pivot column (the column that gets split into
>> multiple columns based on distinct values) is Type. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>|||On Sep 30, 11:20 pm, Charles Allen <cal...@.nospam-bkd.com> wrote:
> I'll give it a shot. Thanks
> --
> Charles Allen, MVP
> "EMartinez" wrote:
> > On Sep 30, 9:20 am, Charles Allen <cal...@.nospam-bkd.com> wrote:
> > > I have data in rows that I want to display in columns based on a value in
> > > each row.
> > > Here's an example of the data:
> > > Employee ID Type Code Amount
> > > 123 PAY BONUS 1,000
> > > 123 PAY SALARY 5,000
> > > 123 DED INS 500
> > > 123 DED DENTAL 100
> > > 123 DED FLEX 50
> > > Here's how I'd like to display the data:
> > > EMPLOYEE CODE AMOUNT CODE AMOUNT
> > > 123 BONUS 1,000 INS 500
> > > 123 SALARY 5,000 DENTAL 100
> > > 123 FLEX 50
> > > My problem is understanding how to display the items in each column starting
> > > at the top.
> > > Thanks for any help.
> > > --
> > > Charles Allen, MVP
> > The best way to produce this type of layout is to create a matrix
> > report where your pivot column (the column that gets split into
> > multiple columns based on distinct values) is Type. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Displaying data from multiple databases

I am using a subreport to lookup up a name from a code value since the lookup
table is in a separate database from the main report. How do I limit the
white space on the name field? I have defined a single textbox in the
subreport, but when I preview the subreport it displays a complete line.
Similarly, when the main report previews the subreport displays the complete
line. I have tried placing the textbox in a rectangle, but it doesn't appear
to make a difference. Any suggestions?If you have a table for the data of the main report, add another column to
the table and put the subreport in that column. If it is a single value they
it will not go to another row.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:7C1E0D63-E86D-4A54-A7BE-7EBB2BD7F4F1@.microsoft.com...
>I am using a subreport to lookup up a name from a code value since the
>lookup
> table is in a separate database from the main report. How do I limit the
> white space on the name field? I have defined a single textbox in the
> subreport, but when I preview the subreport it displays a complete line.
> Similarly, when the main report previews the subreport displays the
> complete
> line. I have tried placing the textbox in a rectangle, but it doesn't
> appear
> to make a difference. Any suggestions?|||You could also try putting the subreport in a rectangle. (Can you guess,
I'm stuck on rectangles for solving white space problems?)
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OZux6OM3EHA.3596@.TK2MSFTNGP12.phx.gbl...
> If you have a table for the data of the main report, add another column to
> the table and put the subreport in that column. If it is a single value
> they it will not go to another row.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:7C1E0D63-E86D-4A54-A7BE-7EBB2BD7F4F1@.microsoft.com...
>>I am using a subreport to lookup up a name from a code value since the
>>lookup
>> table is in a separate database from the main report. How do I limit the
>> white space on the name field? I have defined a single textbox in the
>> subreport, but when I preview the subreport it displays a complete line.
>> Similarly, when the main report previews the subreport displays the
>> complete
>> line. I have tried placing the textbox in a rectangle, but it doesn't
>> appear
>> to make a difference. Any suggestions?
>

Displaying data - question

Dont laugh;

How do I create a simple sqlcommand in C# that shows data. I have the code for VB but I a missing something in the converstion. I know SQL but I dont get the simple steps of displaying data. I have got all of the Visual Basic stuff down I just need help with doing it by hand in C#.

or point my to a URL so that I can get the code.

Thanks

VB.Net and C# connect to databases the same way so if you can hook VB.Net up to a database then you should be able to with C#. It must be a syntax error. Here's a link to some code for setting up a simple connection with C# below.

http://www.csharpfriends.com/Articles/getArticle.aspx?articleID=1

Display Vlaue

Hi I am reporting off a dataset and I want to have a Display Value witch is the code rather then having the value witch is a Id is it possible?

All fields returned in your query should be available in design mode. If not then you may need to click the refresh button on the data tab for Visual Studio to re-create your dataset filed definitions. To access fileds from your dataset just use the following expression template:

= Fields!<<column_name>>.Value

When grouping of when you want to reference a filed outside of the context of a containing control e.g. matrix , table, list, then you have to use an aggregation function e.g. Sum, Avg, Max, Min, First, Last, and pass the dataset name as a scope e.g.

= First(Fields!<<column_name>>.Value, "<<dataset_name>>")

|||

Hi Adam

Thank you for replying but what I mean is having the actual column should be the ID but the display should be the Description or the code from a other dataset and I can’t use a join because I have the data in memory of my dataset

|||You need to do the join in your query and bring back an extra column (the description) in the same dataset as the ID. Use SQL for joins and use RS for presentation. RS has no capability for joining disperate datasets - not that I know of anyway.

Display SQL 2005 KPI status graphic using visual studio 2005

Dear All,

Could anyone help send me a sample mdx code on how i could get the KPI status graphics from MS SQL 2005. I create a cube and add a few KPI's into the cube, on the management studio 2005 I am able to view the graphics e.g. smiley but when i use the mdx command I could only display the KPI status -1, 0 or 1 not the graphics.

The MDX functions does not include the KPI_status_graphics.

Thank you in advance.

Mike Siow siowm@.metierview.com

You can retrieve the KPI_STATUS_GRAPHIC and KPI_TREND_GRAPHIC for a KPI using the MDSCHEMA_KPIS Rowset:

http://msdn2.microsoft.com/en-us/library/ms126258.aspx

>>

MDSCHEMA_KPIS Rowset

Describes the key performance indicators (KPIs) within a database.

...

>>

For example, for the Adventure Works Internet Revenue KPI:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>MDSCHEMA_KPIS</RequestType>

<Restrictions>

<RestrictionList>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

</PropertyList>

</Properties>

</Discover>

--

<return xmlns="urn:schemas-microsoft-com:xml-analysis">

<root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">

<xsd:element name="root">

<xsd:complexType>

<xsd:sequence minOccurs="0" maxOccurs="unbounded">

<xsd:element name="row" type="row" />

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:simpleType name="uuid">

<xsd:restriction base="xsd:string">

<xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />

</xsd:restriction>

</xsd:simpleType>

<xsd:complexType name="xmlDocument">

<xsd:sequence>

<xsd:any />

</xsd:sequence>

</xsd:complexType>

<xsd:complexType name="row">

<xsd:sequence>

<xsd:element sql:field="CATALOG_NAME" name="CATALOG_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="SCHEMA_NAME" name="SCHEMA_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="CUBE_NAME" name="CUBE_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="MEASUREGROUP_NAME" name="MEASUREGROUP_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_NAME" name="KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CAPTION" name="KPI_CAPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DESCRIPTION" name="KPI_DESCRIPTION" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_DISPLAY_FOLDER" name="KPI_DISPLAY_FOLDER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_VALUE" name="KPI_VALUE" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_GOAL" name="KPI_GOAL" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS" name="KPI_STATUS" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND" name="KPI_TREND" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_STATUS_GRAPHIC" name="KPI_STATUS_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_TREND_GRAPHIC" name="KPI_TREND_GRAPHIC" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_WEIGHT" name="KPI_WEIGHT" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_CURRENT_TIME_MEMBER" name="KPI_CURRENT_TIME_MEMBER" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="KPI_PARENT_KPI_NAME" name="KPI_PARENT_KPI_NAME" type="xsd:string" minOccurs="0" />

<xsd:element sql:field="ANNOTATIONS" name="ANNOTATIONS" type="xsd:string" minOccurs="0" />

</xsd:sequence>

</xsd:complexType>

</xsd:schema>

<row>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASUREGROUP_NAME>Internet Sales</MEASUREGROUP_NAME>

<KPI_NAME>Internet Revenue</KPI_NAME>

<KPI_CAPTION>Internet Revenue</KPI_CAPTION>

<KPI_DESCRIPTION>Revenue realized through direct sales via the internet.</KPI_DESCRIPTION>

<KPI_DISPLAY_FOLDER>Financial Perspective\Grow Revenue</KPI_DISPLAY_FOLDER>

<KPI_VALUE>[Measures].[Internet Sales Amount]</KPI_VALUE>

<KPI_GOAL>[Measures].[Internet Revenue Goal]</KPI_GOAL>

<KPI_STATUS>[Measures].[Internet Revenue Status]</KPI_STATUS>

<KPI_TREND>[Measures].[Internet Revenue Trend]</KPI_TREND>

<KPI_STATUS_GRAPHIC>Cylinder</KPI_STATUS_GRAPHIC>

<KPI_TREND_GRAPHIC>Standard Arrow</KPI_TREND_GRAPHIC>

<KPI_WEIGHT />

<KPI_PARENT_KPI_NAME />

<ANNOTATIONS />

</row>

</root>

</return>

|||

Hi Deepak,

Thank you.

Mike Siow

Display report as PDF in current window

How could I display Reporting Services report in the opened window ?

This is my code in the Page_Load()

Dim RptParameters(0)As Microsoft.Reporting.WebForms.ReportParameter RptParameters(0) =New Microsoft.Reporting.WebForms.ReportParameter("ProjectId",CStr(Session("CurrentProject"))) ReportViewer1.ServerReport.SetParameters(RptParameters)Dim warningsAs Microsoft.Reporting.WebForms.Warning() =Nothing Dim streamidsAs String() =Nothing Dim mimeTypeAs String =Nothing Dim encodingAs String =Nothing Dim extensionAs String =Nothing Dim DeviceInfoAs String ="<DeviceInfo>" &" <OutputFormat>PDF</OutputFormat>" &" <PageWidth>29.7cm</PageWidth>" &" <PageHeight>21cm</PageHeight>" &" <MarginTop>0.5cm</MarginTop>" &" <MarginLeft>0.5cm</MarginLeft>" &" <MarginRight>0.5cm</MarginRight>" &" <MarginBottom>0.5cm</MarginBottom>" &"</DeviceInfo>"Dim bytesAs Byte() bytes = ReportViewer1.ServerReport.Render("PDF", DeviceInfo, mimeType, encoding, extension, streamids, warnings) Response.Clear() Response.ContentType = mimeType Response.AddHeader("content-disposition","attachment; filename=Project." & extension) Response.BinaryWrite(bytes) Response.End()

With this code I am able to get the report as PDF. However I have 2 issues:

First if I try to open directly the PDF (I press the Open button when prompted) Acrobat Reader opens but complains he cannot find the file. Where is the file coming from and why it cannot be found ?

In order to open the PDF I have to save it first on the local client. Then I am prompted again and I can open the PDF file in Acrobat.

Secondly, I would like to open the PDF directly in the current window and not in he Acrobat window. How to achieve it ?

hi,

i had similar problems with Acrobat Reader. The new version of it (7.0) cannot handle opening pdfs from the web.

So i think the problem is not in the code but in the reader itself.

You could try with another pdf reader like Foxit - http://www.foxitsoftware.com/pdf/rd_intro.php

I have a similar report exporting it my self, and works for me to open it without downloading (however not using Acrobat Reader).

Unfortunately, I don't know either how to display the pdf report directly into the browser. Tried playing a lot with the content-type, but didn't help.

In case you find it feel free to share :)

Cheers,

Yani

display msgbox in browser

hi..

i have the following embedded code in my rdl file..

Function validateDate (ByVal startDt as datetime, ByVal endDt as datetime)

if (startDt > endDt)
System.Windows.Forms.MessageBox.Show("Date Range From must be earlier or the same as Date Range To")
End if

End function

this code works in VS IDE but the msgbox would not appear when view using a browser...

how can i get the msgbox to appear when view using a browser?

thanksss

Code execution will happen at the server, not at the client. Client only receives the output of the renderer. The MessageBox might be showing up at the server, but that doesn't help the client.

There is no good way to cause such a dialog to display at the client.

|||

thanks mike for your reply. is there any workabout to this?

is there any error handling i can do to alert the users?

thanks!

Display DB Table in ASP

hello forum friends,

i need to display the database table in ASP

page.how it is possible,can anyone explain me

with code.

Regards

Prathap

What about:

http://www.stardeveloper.com/articles/display.html?article=2002061201&page=1

Jens K. Suessmeyer

http://www.sqlserver2005.de