Retrieving Unique Fields From Table Into Existing Dataset

Jun 18, 2008

I am working on creating a report which is retrieving data from a SQL 2005 database and being displayed in a C#.net web page.  Presently, I am binding the dataset to a ASP:Gridview on the web page.

I am currently retrieving most of the needed fields using the following SQL statement:
PROCEDURE [dbo].[pr_getReportTickets]
 
 @DateCreated nvarchar(15)
 
AS
BEGIN


 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT    
tbl_Queue.num_TicketNumber AS TicketID
 , tbl_Users.str_Name AS Technician
 , tbl_Queue.str_QueueLocator AS TechNTID
 , tbl_Queue.dat_ReceivedRequest AS dateCreated
 , tbl_Queue.dat_DueDate AS DueDate
 , tbl_Queue.str_TaskName AS TicketTitle
 , tbl_Queue.str_Requestor AS RequestorNTID
 --, I need most current dat_TimeStamp from tbl_Notes here for this record
 --, I need most current int_PercentComplete from tbl_Notes here for this record
 --, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record
 , MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName
 , tbl_Queue.str_TicketType AS TicketType
 , tbl_Status.str_TaskStatus AS TicketStatus
 , tbl_Severity.str_Priority
 , tbl_Complexity.str_Complexity
 , tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority
 , tbl_Queue.dat_CompleteDate as DateCompleted
 

FROM        
 dbo.tbl_Queue
LEFT OUTER JOIN
 MasterEmp.dbo.tbl_employee AS tbl_employee
ON
dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid
LEFT OUTER JOIN
 tbl_Users
ON
 tbl_Queue.str_QueueLocator = tbl_Users.str_ntid
LEFT OUTER JOIN
 tbl_Status
ON
 tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD
LEFT OUTER JOIN
 tbl_Severity
 ON
tbl_Queue.str_Severity = tbl_Severity.str_Severity
LEFT OUTER JOIN
 tbl_Complexity
 ON
 tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID

WHERE    
 (dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102))
   


 ORDER BY
    TicketType,
    tbl_Queue.str_Severity


I also have another table called tbl_Notes.  This table contains an unlimited quantity of records for every “num_TicketNumberâ€?.  This table contains the following fields:  num_TicketNumber, str_TechRep, str_Notes, dat_TimeStamp, int_PercentComplete and int_MinutesWorked

I need to add two more fields to the query, but I do not know how to tell the SQL statement how to retrieve the data for the specfic record and inbed the fields into the Dataset being returned from the database to the web page.
1) The first field I need is the most current recorded Date field from the tbl_Notes table for the each of the records returned in the above SQL statement.  Similar to:  SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC
2) The other field I need to return with the Dataset is the sum of the int_MinutesWorked for each of the tickets being retrieved. Similar to:  SELECT SUM(int_MinutesWorked) AS TotalMinutes
FROM tbl_Notes WHERE (num_TicketNumber = 49)

View 4 Replies


ADVERTISEMENT

Transact SQL :: Create Unique ID Based On Existing Fields

Sep 1, 2015

I have the following table (Table does not have unique key id )

Last Name     First Name         DATE        Total-Chrg

Jaime               KRiSH           5/1/2015         -4150.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015          1043.66
Jaime               KRiSH           5/1/2015           4150.66
Jaime               KRiSH           5/3/2015           4150.66
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/1/2015           321.02
Peter                Jason            5/23/2015         123.02

I want the results to be in following way

Uniq ID    Last Name    First Name          DATE         Total-Chrg

1                Jaime            KRiSH                 5/1/2015     -4150.66
2                Jaime            KRiSH                 5/1/2015     1043.66
2                Jaime            KRiSH                 5/1/2015     1043.66
3                Jaime            KRiSH                 5/1/2015      4150.66
4               Jaime             KRiSH                 5/3/2015      4150.66
5               Peter              Jason                5/1/2015       321.02
6               Peter              Jason                5/1/2015       321.02
7               Peter              Jason               5/23/2015     123.02

May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values.  There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates). 

View 4 Replies View Related

How Can You View The Connection String Of An Existing Datasource? How Do You Delete Existing Dataset?

Nov 14, 2007

I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.

View 1 Replies View Related

How Do I Get ONE Table Trying To Access TWO Datasets/Dataset Fields

Oct 26, 2007

Hi

I'm all very new to SQL Reporting Services so I am hoping that someone will be able to help me.

I have two datsets. Both contain the same array of information pertaining to a particular site. For example, how much sales we had, how much revenue was made, how much commission was created at the end of each day, the usual kind of metrics. We have a stored procedure which takes a final total and puts into a table we can access by date.

I want to produce a report that can compare the data from two dates. I use two datasets to run the quries that will return the relevant data. The only thing that differs between the dataset is the date that the data is based on. So to say first dataset will have data on one date and the second dataset will have data on a another date.

I was trying to make a table where I could include fields from both datasets, mainly for making comparison easier.

so id have

columns
online hits (dateone) | online hits (datetwo)
Rows
Data date1 | Data date2

and so on.

in fact i had this as the data from the first dataset in an example field:
=Fields!OnlineSales.Value

but i couldnt get the second dataset to work even if i tried entering :
=(Fields!OfflineBookings.Value, "SecondDatePicker")

I can't get the table to include field results from the second dataset as a table can only be linked to one dataset.

How can I get round this little problem?

View 1 Replies View Related

Add Other Table Fields To An Existing ROW_NUMBER Partition By Select Query

Feb 5, 2008


I have the following insert query which works great. The purpose of this query was to flatten out the Diagnosis codes (ex: SecDx1, SecDx2, etc.) [DX_Code field] in a table.




Code Snippet
INSERT INTO reports.Cardiology_Age55_Gender_ACUTEMI_ICD9
SELECT
Episode_Key,
SecDX1 = [1],
SecDX2 = [2],
SecDX3 = [3],
SecDX4 = [4],
SecDX5 = [5],
SecDX6 = [6],
SecDX7 = [7],
SecDX8 = [8],
SecDX9 = [9],
SecDX10 = [10],
SecDX11 = [11],
SecDX12 = [12],
SecDX13 = [13],
SecDX14 = [14],
SecDX15 = [15]
FROM (SELECT
Episode_Key, DX_Key,
ROW_NUMBER() OVER ( PARTITION BY Episode_Key ORDER BY DX_Key ) AS 'RowNumber', DX_Code
FROM srm.cdmab_dx_other
WHERE Episode_key is not null
) data
PIVOT
( max( DX_Code ) FOR RowNumber IN ( [1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12], [13], [14], [15] )) pvt
ORDER BY Episode_Key







The query below also works fine by itself. You may notice that the Episode_Key field appears in both the query above and below therefore providing a primary key / foreign key relationship. The srm.cdmab_dx_other table also appears in both queries. I would like to add the fields in the select statement below to the select statement above. Using the relationships in my FROM statements, can anyone help me figure this one out?




Code Snippet
SELECT
e.episode_key,
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(p.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(p.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(p.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(p.patient_sname)), '') AS PatientName,
CONVERT(CHAR(50), e.admission_date, 112) as Admit_Date,
CONVERT(CHAR(50), e.episode_date, 112) as Disch_Date,
e.episode_type as VisitTypeCode,
d.VisitTypeName,
convert(int, pm.PatientAge) as PatientAge,
pm.PatientAgeGroup,
pm.patientsex,
p.race
FROM srm.episodes e inner join
srm.cdmab_dx_other dxo on dxo.episode_key=e.episode_key inner join
srm.cdmab_base_info cbi on cbi.episode_key=e.episode_key inner join
srm.item_header ih on ih.item_key = e.episode_key inner join
srm.patients p on p.patient_key = ih.logical_parent_key inner join
ampfm.dct_VisitType d on d.VisitTypeCode=e.episode_type inner join
dbo.PtMstr pm on pm.AccountNumber = e.Account_Number






View 3 Replies View Related

Help Please Retrieving Unique Records.

Apr 15, 2008

I'm trying to read from a table and return only the unique records from a table.
The table has 3 columns,
ID - Autoinc
Amount - decimal 6,2
Name - varchar(10)

If there are records like

ID Amount Name
1 0.03 Name1
2 0.07 Name9
3 0.05 Name3
4 0.03 Name8
5 0.07 Name4
6 0.06 Name7


I am wanting to retreive only records 3 & 6 (values 0.05
0.06) as they are the only ones where amount is unique i.e. only 1 entry in the table.

I put the following sql statement together which achieves this but it it only returns the amount value
select Count(amount),amount from bids group by amount having count(amount)<2 order by amount

So I then expanded it to read
select Count(amount),amount,name from bids group by amount,name having count(amount)<2 order by amount
not sure where i'm going wrong but the results returned were
0.03
0.03
0.05
0.06
0.07
0.07

Appreciate any help here.

View 2 Replies View Related

Retrieving Unique Rows

Jun 3, 2008

I have the following sql:

SELECT DISTINCT patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID, patientFirstName, patientLastName, patientDOB, patientGender, completed_date

This brings up duplicate records, my aim is to bring distinct records, now if I take out the other returned fields after patientID
and using the following sql:

SELECT DISTINCT patient.patientID
FROM patient
LEFT JOIN patient_record ON patient_record.patientID = patient.patientID
WHERE (sub_categoryID = 4 OR patient_record.allocated = 4)
AND (patient_status = 1 OR patient_status = 2 OR patient_status = 5)
GROUP BY patient.patientID

This bring up distinct results, but I need to retrieve the other fields from the database i.e. patientFirstName and patientLastName

Please can you help.

View 2 Replies View Related

SQL Server CE 3.5, Typed Dataset, Retrieving @@IDENTITY Of Inserts?

Mar 19, 2008

I'm at loss how I'm supposed to work with typed datasets and Sql Server Compact 3.5, when inserting records and I need to update my datatables with the primary key of newly inserted rows.

I've tried adding a RowUpdated handler to all tableadapters that look like this:





Code Snippet
void Adapter_RowUpdated(object sender, System.Data.SqlServerCe.SqlCeRowUpdatedEventArgs e) {

if(e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert) {

if(e.Row.Table.PrimaryKey.Length > 0) {
SqlCeCommand selectIdentityCommand = new SqlCeCommand("SELECT @@IDENTITY",e.Command.Connection);
if(e.Command.Connection.State == ConnectionState.Open) {

e.Row[e.Row.Table.PrimaryKey[0].Ordinal] = (int)selectIdentityCommand.ExecuteScalar();

}
}
}






I've previously used this type of approach when working with an OleDbDatabase, which works just fine. But it doesn't work with Sql Server CE 3.5, and since it doesn't support stored procedures I can't fix it that way either. And it doesn't support commands in a batch (i.e appending the Insert command of the adapter with ";SELECT @@IDENTITY") so that doesn't work either...

So how are we supposed to use Sql Server CE 3.5? It's impossible together with datasets? Or am I missing something obvious?

Any hints would be greatly appreciated! Thanks!

Cheers!

View 3 Replies View Related

Set Datasource To Pre-existing Dataset

Dec 6, 2007

Hi guys

Firstly, i have a solution that is made up of multiple projects. One of these projects contains the majority of the business based datasets that are used throughout the applications.



One of the front ends that I have in the solution is a web site. I am wanting to use the report viewer control to display a report in local mode. I created a new report and i am trying to set its data source to one of the pre-existing datasets that I have and can not figure out how to do this.



Also these datasets do not have adaptors I populate the datasets manually. So the second half of what i need to know is once I have told a report to use one of my existing reports when do I do the population of the dataset.



Lastly if the answer for how to do this is different in local vs. remote modes i would be interested in what the difference is in this scenario.

Thanks

Anthony

View 3 Replies View Related

SQL Server 2012 :: Retrieving Dataset That Only Includes Latest Versions Of Data

Mar 28, 2014

In our Microsoft Dynamics Nav instance we have a Sales Header Archive table - into which copies of the Sales Header are placed, with 3 items forming the compound key:

Document Number
Version
Occurrence number

so if doc 1 is archived, then the records would be

Doc# | Version | Occurrence #
1 | 1 | 1

When a second copy is archived a new record is added:

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2

and then when maybe a 3rd version is archived a 3rd entry added

Doc# | Version | Occurrence #
1 | 1 | 1
1 | 1 | 2
1 | 2 | 2

This is for EACH document and I now need to retrieve the dataset which is the latest version of each document... but I'm drawing a blank!

If I

select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#]

then I get the distinct list of docs, but I now need to use this list to select the records which match this criteria, from this table.

How do I select just these?

I thought (wrongly) that I could simply say:

Select * from Invoice Table where
Invoice.[Doc#], Invoice.[V], Invoice.[Occurrence #] in
(select [Doc#], max([Version]) as [V], max([Occurrence #]) from (table) group by [Doc#])

View 2 Replies View Related

Retrieving Varchar(max) And Varbinary(max) Fields With VFP9

Mar 20, 2007

I have a test database that contains a varbinary(max) field and a varchar(max) field.

when I do a

Select * from test where id = xx

I get the expected results if my connection string uses

'driver=SQL Server;Server'

but these two fields return no data if I use

'driver=SQL Native Client'

the other fields in the record come back with no problems.

Is there anything special I need to do to retrieve these types of fields?

View 1 Replies View Related

Dataset With Existing Stored Procedure

Mar 11, 2008

I am trying to use a dataset for the first time and I've run into a roadblock early.  I added the dataset to the AppCode folder, set the connection string, and selected 'use existing stored procedures' in the configuration wizard.  The problem is that there are three input parameters on this procedure and they're not showing up in the 'Set select procedure parameters' box.  I went through several of the stored procedures and this is the case for all of them.  The weird thing is that if I select the same procedure as an insert procedure then the parameters do show up.  Very frustrating, any thoughts?
Thanks in advance,
N

View 6 Replies View Related

Query To Return Unique Value From Dataset

Feb 13, 2013

I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.

Please see attached .pdf for full explanation....

View 2 Replies View Related

How To Create New Column Indexed And Unique In Existing Tbl - 2005

Mar 7, 2008

Need help,

I'm a newby to SQL and looking for how-to-help.
I have an existing DB and within a certain table, I have created a new Column via Studio manager, but need help with the following:

Need to make the new Col "Unique, and Indexed" but cannot see anywhere in studio manager interface to do it.

Thanks.

View 12 Replies View Related

Unique Fields

May 16, 2006

have a table with 2 fields i want them to be unique. idont want duplicate.

how will i do it

View 9 Replies View Related

Unique Constraint Violation On Single Cell Dataset

May 9, 2007

Hi,



I'm retreiving data from a SAP BW data source. In the dataset designer I can build a query that returns a single value, the data returns successfully in the designer. When I switch to the layout mode I can place the field of the datset in table / matrix / textbox. However when i switch to preview mode or deploy the report and try to see them in Report Manager the report fails to render with the following message:



Query Execution Failed for dataset 'XXX'

These columns don't currently have unique values.



Its a single cell datset?!?!



I can successfully retreive data from other SAP Infocubes and queries. I also cannot replicate the error using a SQL Server datasource. The stack trace error looks like this:


w3wp!processing!6!5/9/2007-16:57:41:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'DataSet1'. ---> System.ArgumentException: These columns don't currently have unique values.
at System.Data.ConstraintCollection.AddUniqueConstraint(UniqueConstraint constraint)
at System.Data.ConstraintCollection.Add(Constraint constraint, Boolean addUniqueWhenAddingForeign)
at System.Data.DataTable.set_PrimaryKey(DataColumn[] value)
at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.CreatePrimaryKeys(DataTable table, InternalObjectType objectType)
at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Populate(InternalObjectType objectType)
at Microsoft.ReportingServices.XmlaClient.CubeMetadataCache.Microsoft.ReportingServices.XmlaClient.IMetadataCache.Populate(InternalObjectType objectType)
at Microsoft.ReportingServices.XmlaClient.ObjectMetadataCache.Microsoft.ReportingServices.XmlaClient.IObjectCache.Populate()
at Microsoft.ReportingServices.XmlaClient.CacheBasedFilteredCollection.PopulateCollection()
at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollectionInternal.Find(String index)
at Microsoft.ReportingServices.XmlaClient.DimensionPropertyCollection.Find(String index)
at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.FindOptionalPropertyName(String internalColumnName, CubeDef cube, Boolean fIsYukonSP1)
at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader.GenerateColumnInformation(CubeDef cube, Boolean fIsYukonSP1)
at Microsoft.ReportingServices.DataExtensions.SapBw.DataReader..ctor(AdomdDataReader dataReader, CubeDef cube)
at Microsoft.ReportingServices.DataExtensions.SapBw.SapBwCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSetNode.RunDataSetQuery()





Any ideas why this happening and how I can get around it?



Thanks





Sanjay



ps Why would you want to put a unique constraint on a read only single table dataset?

View 1 Replies View Related

Add Unique Constraint On 3 Fields?

Jul 21, 2015

I want to add a unique constraint on 3 fields, to only allow the value in the field ONE time. The value will NEVER be the same for anything else. This is the table structure

Code:
Create Table Employees
(
P_Id int NOT NULL,
InstructorName varchar(255) NOT NULL,
CourseName varchar(100) NOT NULL,
DataTableName varchar(100) NOT NULL
)

I want to create a unique constraint across the fields Instructorname, CourseName, DataTableName as their is ONLY 1 instructor per course per table so those 3 fields will ALWAYS hold unique values. I think the constraint syntax would go like so, but want to check before I go butchering some sql syntax.

constraint syntax:

Code:
Alter Table Employees
Contstraint uniqueEmployee Unique (InstructorName ,CourseName ,DataTableName )

View 1 Replies View Related

Non-existing Field In Fields Collection

Apr 11, 2007

I'm developing a report in RS 2000 that uses two datasets. The first dataset provides data to a drill-down report on the top part of the screen and the second small dataset provides some data for additional summary information on the bottom of the screen. I'm getting a runtime error "An unexpected error occurred in Report Processing. The expression referenced a non-existing field in the fields collection." Of course, I've edited every expression I can think of: text boxes, data grid, parameters, filters, drill-down lists... I think I've gone through every object on the report but I can't isolate the bad expression. Can anyone suggest a better way to debug this type of error? It compiles fine, but fails in runtime. thanks.

View 1 Replies View Related

Fields Not Showing In Dataset

Mar 27, 2008

Hi,

I've a stored procedure which returns different number of fields depending on the input parameter.


create proc spSample (@rtnValue bit)
as
if @rtnValue = 0
begin

select field1, field2 from tablename
end
else
begin

select field1, field2, field3, field4 from tablename
end

Have a report execute this stored procedure, set the parameter value = True. Test the query, it returns all the fields as expected. However, on the fields tab of Dataset it always shows field1, and field2 no matter what; I manually add field3 and field4 under Report Datasets tree and they disappear after each execution. Anyway, I'm able to set the report fields as long as I don't refresh the query.

Is this the bug in SSRS? or am I missing something?

Thanks,

-Ash

View 1 Replies View Related

Unique Fields In SQL Server Tables

Oct 12, 2006

 I feel sure that this is a very naive question, but it is freeky me out that I can solve it. With SQL server what is the easiest way to make a field other than the Primary key unique?Would appreciate help with this. ThanksPaul

View 3 Replies View Related

Using Multiple Fields As The Unique Identifier

Jun 10, 2008

Please see below post

View 2 Replies View Related

Duplicate Data For Unique Fields With Different Name?

Oct 25, 2013

I'm designing an app for stock keeping. In my DB, I have a field called "ItemSerialNo" and I made it unique(but not the table's primary key). On my front end, I have a text box for Item Serial No and a combo box loaded with the item brands and also a save button. I know that if I try to save a serial no already existing in my DB, the app won't allow me because of the unique property of the field named "ItemSerialNo". But I want to be able to save a serial no already existing in my DB but with a different brand name.

For example, I want to be able to save information like:

1. ITEM SERIAL NO = 12345
BRAND NAME = AA
2. ITEM SERIAL NO = 12345
BRAND NAME = BB.

View 2 Replies View Related

Update Fields Using Data In Existing Field?

Jan 15, 2015

I have a field where all of the data is 5 characters in length. The last character denotes a status and will always be an F, H, or T. I want to add a new field (which I will do manually) and populate the new field with the last character from the "old" field. Once that is complete, I want to eliminate that 5th character from the old field.

OLD FIELDNEW FIELD
B123F
B123H
B123T

OLD FIELDNEW FIELD
B123F
B123H
B123T

View 2 Replies View Related

Check If DataSet Contains Specific Fields?

Mar 9, 2008

Hi,
I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView.
Is it possible?

View 6 Replies View Related

DataSet Of SSRS Is Not Showing Fields

Dec 4, 2007

Hi.

I am currently working With SSRS . I have created a Store Procedure To show Data from different table in my Database,I have Used a temp table with #xxx Name,when i am creating DataSet for this Store procedure . but its not Showing the Fields of Store Procedure, but when i create other Store procedure without temp table with #xxx then its showing the fields in Data Set.

What Could be the possible Reason,

Thanks

View 8 Replies View Related

How To Put Dataset Fields Into The Page Footer ... :(

Jan 3, 2008

Hello anyone!!!!


Anybody knows if exists some way to put a dataset field into the page footer???, or if not, tell me if there is a way to do a €œsimilar€? of footer, cuz I need put information in the bottom of the report, but just in the last page, and need to left the space of this information in the another pages.

Any suggest is welcome, tanks!!!!!

View 3 Replies View Related

How To Refresh DataSet Fields Dynamically

Jan 30, 2008

Hi All,

I have a Dataset. I use this dataset in my matrix for grouping based on the underlying table columns names. Users can add columns to the underlying table that the DataSet refers.

Now my problem is:
The data is grouped based on the column name selected from the parameter.
If the user adds a column to this table how will the dataset refresh so that it contains the newly added field for grouping data.

View 5 Replies View Related

Repeating A Header With Fields From The Dataset

Mar 26, 2007

Hi,

I'm designing a report and have the requirement that the report title needs to be repeated on each page. However, the report title is dynamic, retrieved from the database, so i cannot place it in the page header (what an annoyance). The solution i first thought of was placing the title in a table, repeating the table header on each page, however the problem i'm encountering is that when i have a subreport in that table, that subreport is in one row

--> if the subreport spans across multiple pages, the header will only be shown on the first page the row where the subreport is actually in...



any thoughts anyone on a possible solution?

View 4 Replies View Related

Re-populate A Unique Number Into Multiple Fields

Oct 14, 2004

What I need to do is re-populate a unique number into multiple fields,

Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.


Please help!!!!! Below are how the tables are set up and an example of the result.

TABLE 1

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST_TBL]
GO

CREATE TABLE [dbo].[TEST_TBL] (
[Empl_Num] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mid_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hours_Day] [numeric](18, 2) NULL ,
[Hours_Wk] [numeric](18, 2) NULL ,
[KR_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[Date_Term] [datetime] NULL ,
[Empl_Type] [int] NULL ,
[Cost_Centre] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Empl_Status] [int] NULL
) ON [PRIMARY]
GO

TABLE 2

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST2_TBL]
GO

CREATE TABLE [dbo].[TEST2_TBL] (
[EmpNumber] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[KR_ID] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[EmpStatus] [int] NULL ,
[EmpType] [int] NULL ,
[CommonName] [char] (32) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Query goes as follows for table 1:

SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber


Query goes as follows for table 2:

SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber


From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.

Example:Table 1

000001,Jackson,James,Sam,7.6,38,12345,475431212


Example:Table 2

000001,Jackson,James,Sam,7.6,38,12345,475431212
001000,Jackson,James,Sam,7.6,38,,475431212

I hope this helps

Thanks in advance

View 10 Replies View Related

Joining / Concatenating Fields - Unique Reference?

Jun 13, 2014

I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?

My query and my desired results are below:

select item, test_date
from inventory
where cat = 'TELE' and itemised_status > 15

item test_datecert_no
-------------------- ----------------------------------------
05MC0002 2014-06-10 00:00:0005MC0002-06-2014
06MT0001 2014-05-13 09:02:0006MT0001-05-2014
06MT0002 2014-05-13 09:03:0006MT0002-05-2014
06MT0003 2014-05-13 09:03:0006MT0003-05-2014
06MT0004 2014-05-09 14:12:0006MT0004-05-2014

View 2 Replies View Related

Importing Data From Excel To Update Existing Fields

May 12, 2004

I have an excel file that contains column A with names of components and products followed by column B which has each respective quantity on hand. I want to import that data to our website's SQL database that has a products table with a column, Pf_ID, that has only product names not component names and In_Stock which contains out-dated information that I want updated from column B of the excel file.

I think I've figured out how to use DTS and update the two fields, but I'm afraid that when everything runs new entries will be created with component information. Is it possible to specify that only rows where Pf_ID matches some row in column A that same row's column B will be used to update the data in In_Stock. I may have just made things too confusing than they need to be, but I don't have much experience with EM or Excel.

I'm also considering trying to write a macro that will match Pf_IDs in an exported excel file of the products table and take rows out of the excel file with current quantity information putting them in a new excel file to import into the website's database.

Please help, this is getting really confusing.

View 4 Replies View Related

Insert Diffrent Columns,Determine Non Unique Fields

Sep 19, 2006

andre writes "I have two questions:

1.)How do i determine if fields are not unique??
2) How to update one table with values from another table when the row names are diffrent??

Thank you!!"

View 1 Replies View Related

Fields Not Displayed Binding Dataset To Stored Procedure

May 7, 2008

Hi everyone

I am using Stored procedure :

ALTER PROCEDURE [dbo].[ReportChart]@Num int,@patID char(16) ASbegin if @Num=2 Begin select * from table1 where patientid=@patID End
else If @Num=1 Begin select * from table2 where patientid=@patID End end

While using the above stored procedure, when i bind it with Dataset. The fields corresponding to table1 are displayed in the Fields Tab of the Dataset whereas the Fields corresponding to table2 are not displayed.
Please help me out .
Thanks In Advance
Regards
Navdeep

View 20 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved