Referencing DataSet From Other View

May 8, 2008

Hi there

Let say i have single dataset with many view (tables/list/matrix etc) and utilise "filter" to manipulate each view.

Is it possible referencing from one view (table/list/matrix) to another? Basically how to access the other field for instance SUM from different view and used for the other view instead. People mentioned about SCOPE but I don't think this is relevant to this situation ?!?!

Thank you

View 1 Replies


ADVERTISEMENT

Referencing DataSet From Other View

May 8, 2008

Hi there

Let say i have single dataset with different view (tables/list/matrix etc) with filter on it. Is it possible referencing from one view (table/list/matrix) to another? Basically how to access the other field for instance SUM from different view and used for the other view instead.

Thank you

View 1 Replies View Related

Referencing A Multi-valued Parameter In A Dataset

Oct 22, 2007



Hello,
I am attempting to create a cascading parameter.
Parameter's 1 & 2 are multi-valued.
Datasets 1 & 2 supply Parameter's 1 & 2.
The values for Parameter 1 as derived from Dataset 1 are 'A', 'B', 'C', 'D' and 'E'.
The potential values for Parameter 2 will only be created if value 'C' is amongst the multiple values selected for Parameter 1.
Thus I need to write my query for Dataset 2 so that it can check the values of Parameter 1 for the existence of 'C' otherwise it returns nothing.
What is the best practice for a dataset referencing the values of a multi-valued parameter in order to generate it's resultset?

Any help is appreciated.

View 2 Replies View Related

Referencing The Temporary Dataset In Lookup Control SQL

Feb 13, 2007

Does anyone know how to reference the ongoing dataset within the dataflow in sql?

So here is the scenario I have.

1) OLE DB SOURCE
2) Lookup a value and that column gets added to the dataset
3) Lookup another value but this time I would rather code the sql rather than select a referencing table. How do I reference the current dataset?

Thanks,
Cheston

View 3 Replies View Related

Reporting Services :: Proper Way Of Referencing A Field In SSRS Dataset?

Jul 13, 2015

I am using SSRS 2014 SP1 x64 and VS2013 Update 5.

I have the following expressions to attempt to calculate ageing on a statement report.

=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=0, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=1 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"),Now())<=30, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=31 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=60, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=61 and DateDiff("d", (Fields!OrderDate.Value, "Invoice"), Now())<=90, (Fields!LineAmount.Value, "Invoice"), 0))
=SUM(IIF(DateDiff("d", (Fields!OrderDate.Value, "Invoice"), now())>=91, (Fields!LineAmount.Value, "Invoice"), 0))

Now when I try to add First to the field selector I get an error stating ... uses a First, Last or Previous aggregate in an outer aggregate.

How exactly do I reference this field from the Invoice dataset (there are at least two datasets)?

View 5 Replies View Related

View Referencing Multiple Tables Not Updatable ?

Jun 7, 2006

Hi

This is for Sql Server 2000.

I get the following error :

View or function 'PS_EMPLOYMENT' is not updatable because the modification affects multiple base tables.

Sql is as follows:

INSERT INTO PS_EMPLOYMENT(EMPLID,EMPL_RCD,PER_ORG,BENEFIT_RCD_NBR,HOME_HOST_CLASS,OWN_5PERCENT_CO,
SECURITY_CLEARANCE,NEE_PROVIDER_ID,POSITION_PHONE,BUSINESS_TITLE,REPORTS_TO,
SUPERVISOR_ID)
VALUES('000004', 0, ' ', 0, 'M', 'N', ' ', ' ', ' ', ' ', ' ', ' ')
----------------------------------------------------------------------------------------------------------------------------------------------------------------

Searching the KB i only found links to SP1 and SP3, the server is already at SP3.

BOL says the following:

SQL Server 6.x :

Updatable views were restricted to modifications that affected only one table

SQL Server 2000:

Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.

So why can this view not be updated in SS2000 SP3 ?

Here is a copy of the view defintion:

ALTER VIEW PS_EMPLOYMENT
(EMPLID, EMPL_RCD, PER_ORG, BENEFIT_RCD_NBR, HOME_HOST_CLASS, LAST_DATE_WORKED, HIRE_DT, REHIRE_DT,
TERMINATION_DT, SENIORITY_PAY_DT, CMPNY_SENIORITY_DT, SERVICE_DT, PROF_EXPERIENCE_DT,
LAST_VERIFICATN_DT, EXPECTED_RETURN_DT, LAST_INCREASE_DT, OWN_5PERCENT_CO, PROBATION_DT,
SECURITY_CLEARANCE, NEE_PROVIDER_ID, POSITION_PHONE, BUSINESS_TITLE, REPORTS_TO, SUPERVISOR_ID)

AS
SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG ,A.BENEFIT_RCD_NBR ,A.HOME_HOST_CLASS ,D.LAST_DATE_WORKED ,
D.HIRE_DT ,D.LAST_HIRE_DT ,D.TERMINATION_DT ,A.SENIORITY_PAY_DT ,A.CMPNY_SENIORITY_DT ,A.SERVICE_DT ,
A.PROF_EXPERIENCE_DT ,A.LAST_VERIFICATN_DT ,D.EXPECTED_RETURN_DT ,A.LAST_INCREASE_DT ,A.OWN_5PERCENT_CO ,
A.PROBATION_DT ,A.SECURITY_CLEARANCE ,C.NEE_PROVIDER_ID ,A.POSITION_PHONE ,A.BUSINESS_TITLE ,D.REPORTS_TO ,
D.SUPERVISOR_ID


FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D


WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= { FN CURDATE() }) OR (JOB2.EFFDT > { FN CURDATE() } AND { FN CURDATE() } < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )

View 3 Replies View Related

Mind Boggling / How Can You Query Self Referencing Tables? (self Referencing Foreign Keys)

Jun 15, 2006

For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID  . . . . . . . .  .  .  int
Name  .  .  .  .  .  .  .  .  .  .  .  nvarchar(50)
ManagerID  . . . . . . . .  .  .  .  int
 
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?

View 6 Replies View Related

Dataset Cleared After Changing View?

Feb 12, 2008

Hello,

maybe it's a silly question...
I've created a new report by the wizard (including a data query). In the dataset frame my dataset contains the queried items.
But when I change to data view and back to layout view in the designer frame, my dataset is cleared.
Is this a bug or normal behaviour?

Regards
Rayko

View 2 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

SQL Server 2008 :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 26, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 0 Replies View Related

Integration Services :: Perform Lookup On Large Dataset Based On A Small Dataset

Oct 1, 2015

I have a small number of rows in a dataset, Table 1.  There is a CLOB on a large dataset, Table 2.  They join on a PK.  I would like to retrieve this CLOB and add it to the data flow for Table1.  In short I want to emulate the following:

Table 1:  Small table without CLOB, 10 rows. 
Table 2: Large table with CLOB, 10,000,000 rows

select CLOB
from table2
where pk = (select pk from table1)

I want this to return the CLOBs for the small number of rows in Table 1.  The PK is indexed obviously so it should be a fast look up.

Table 1 and Table 2 live on different Oracle databases.  How do I perform this operation efficiently in SSIS?  It seems the Lookup and Merge Join wont do this.

View 2 Replies View Related

Reporting Services :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 27, 2015

I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.

I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.

View 3 Replies View Related

How Can I Use SQL Reporting Services To Get A Dynamic Dataset From Another Web Service As My Reports Dataset?

May 21, 2007

I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking

View 2 Replies View Related

Listing Datasets In Report (dataset Name, Dataset's Commands)

Oct 12, 2007



Is there any way to display this information in the report?

Thanks

View 3 Replies View Related

Dataset.Tables.Count=0 Where There Are 2 Rows In The Dataset.

May 7, 2008

Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.


ALTER PROCEDURE [dbo].[MyStorProc]

(

@Status smallint,

@RowCount int = NULL,

@FacilityId numeric(10,0) = NULL,

@QueueID numeric (10,0)= NULL,

@VendorId numeric(10, 0) = NULL

)

AS

SET NOCOUNT ON

SET CONCAT_NULL_YIELDS_NULL OFF



If @Status = 0

BEGIN

SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END



View 4 Replies View Related

How To Transfer Data From One Dataset To Other Dataset

Apr 11, 2008

i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
 

View 4 Replies View Related

Filter One One Dataset With Values In Another Dataset?

Dec 19, 2006

Hi,

I have two datasets in my report, D1 and D2.

D1 is a list of classes with classid and title

D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.

I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.

Is there any way to do this in RS2005?

View 2 Replies View Related

Reporting Services :: IF Statement If Dataset Field Value Equals Value Of Dataset Field

Sep 3, 2015

Using this IIF statement:

=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date  - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015

What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.

View 4 Replies View Related

Referencing SQLDataSource In A Sub

Nov 16, 2006

I have a SqlDataSource  - The select statement
selects the DB row with an ID that equals a querystring value.... So I
know I am only selecting one row..Now I want to create a little
Sub that grabs any field(s) of my choice and then I can assign the
value of it to a textbox on my page or a variable if I need to... Any
idea how I actually reference the fields via the SQLDataSource in a
sub? I see you can reference parameters using..SqlDataSource1.SelectParameters()I was hoping I could use something similar to get the actual DB fields like the below...SqlDataSource1.Select("MyDataBaseField").Value = TextBox.TextCan anyone help please??Thanks

View 7 Replies View Related

Referencing Sql In IF THEN ELSE Statement

Mar 13, 2007

The Background:
From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2.  Page 2 then does a lookup in the sql 2000 db useing this querystring.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>"
SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @ID)">
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="id" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
 
The Problem:
What I then need to do is an IF THEN ELSE statement.
IF [Package in SQL1] = 4 Then
Package 4
IF [Package in SQL1] = 3 Then
Package 3
Only how do I reference the sql value in the if statement.
 I have tried
<%IF Eval("Package") = 4 Then %> and with Bind - No good

View 4 Replies View Related

Image Referencing

May 4, 2008

Hi all, How do I return pictures along with my search results?  For example, I have a shoe database and for the results I would like one column to hold a picture of the shoe (the other columns hold Brand, Name, Price, etc)  The way I am looking at implementing this is by referencing the image and retrieving it from the server.  I'm a bit lost on how to do this though.  Any help? Thanks in advance,Nick 

View 4 Replies View Related

Self-referencing Table

Sep 1, 2007

Hi,

I'm using MS SQL 2005 Express.

CREATE TABLE Folder (
iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
folderName varchar(50) NOT NULL,
parentFolderID int NULL
FOREIGN KEY REFERENCES Folder (iD)
)
GO

if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.

The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'

Anyone got any advice?

View 1 Replies View Related

Referencing To A Function..

Jul 20, 2005

I was created a function named aa.It is possible to reference to it without using the dbo. prnounce ?eg: select aa(), not select dbo.aa()thanks,Viktor

View 1 Replies View Related

Referencing Subreport Value

Nov 16, 2007

Hello,

I need to sum up a sub-report value, but I don't know the syntax to reference the subreport.

How do I reference a subreports value to sum it up?

Thanks,

View 7 Replies View Related

Trigger Referencing

Nov 28, 2007



I'm setting up a trigger that will fire off an email explaining that this entry has been updated, however I need a reference to the updated row so I only send that bit of information off.

Heres my code thus far:


ALTER TRIGGER [dbo].[completeCreation2]

ON [Database_Test].[dbo].[Projects]

AFTER UPDATE

AS

-- SEND EMAIL

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Administration',

@recipients = 'email@email.com',

@body = 'A new project has been created view details of the project below:',

@subject = 'Project Created',

@body_format = 'TEXT',

@importance = 'High',

@query = 'Select * FROM updated';

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

END


I thought the select From updated would work but it doesn't I need a reference for that line, select * FROM _____.

Thanks

View 3 Replies View Related

Referencing Issues

Apr 17, 2008



I am trying to run a mobile device with SQL's mobile 3.5.
When I run my application I am given an error that sqlceme35.dll plus several other references cannot be found

When I try referencing them It does not allow me too.
Is there any steps I should take to make referencing allowed?

View 1 Replies View Related

Referencing Result Set

Nov 20, 2007

Hi



This is probably a noob question. In reference to my previous post about OLE DB and variables, I need to copy the whole result set to a table. i tried


Code Block

exec('insert into '+@tableName+' ('+@ColumnNames+') '+@curTableResult)
where @tableName is the table name, @ColumnNames are the column names and @curTableResult is the Result set produced by the Execute SQL statement before. Obviously didnt work.

How do I copy the Result set to a table?

Thanks

CoyoteM

View 7 Replies View Related

Referencing Variables By Name

Nov 8, 2007

Hi there everyone,

A quick question. How do I reference variables (system and user) in SSIS by name instead of by just using a "?". The reason why I'm asking is because I'm trying to log what happens in a package to SQL, and if I cannot reference a variable by name it tries to insert variable values into incorrect fields?

An example of the code I'm trying to use can be found below:



Code Block
UPDATE dbo.History_Control_Table
SET
End_Date = GetDate()
, Result = 'Success'
, Status = NULL
, No_Of_Records_Processed = ? --This is where I want to tell it which variable to use
, Package_Name = ?
, No_Of_Records_Rejected = ?

--etc etc etc

WHERE
Package_Name = ?
AND Task_Name = 'Deals_Fact_Stg'
AND Status = 'Running'




Thanks in advance.

Chow.

View 11 Replies View Related

Foreign Key NOT Referencing PK

Sep 18, 2006

This book 'MS SQL Server 2000 Bible' says the following:

'The foreign key can reference primary keys, unique constraints, or unique indexes of any table'

I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this?

thx,

Kat

View 5 Replies View Related

Referencing Textboxes

Feb 7, 2008

Is there a way to reference textboxes in an SSRS table like one would for cells in Excel? For example, something like =textbox1/textbox2? I am trying to replace a spreadsheet by turning it into an automated report and need to do some horizontal formulas and when I put in the grouped total, it always defaults to averaging percetages from the rows above, which isnt a true summary in my case.

View 11 Replies View Related

Select Self Referencing Table

May 25, 2005

I have a table that holds a ParentID and the RecordID.  There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not.  0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if   '   1- 2   ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J

View 4 Replies View Related

Referencing Certain Excel Fields

Jul 24, 2002

Anyone know a way to pull specific fields from an excel spreadsheet.
I'm using SQL7 DTS to pull data into a SQL table. I can pull the entire
sheet but I just want certain fields.

thanks, archie

View 2 Replies View Related

Referencing A Table By Variable

Jul 18, 2000

I would like a stored proc to be fed the table name as a paramater.
I tried below

create procedure testit as

set nocount on
declare @tblnm varchar(50)
select * from @tblnm

but it get Server: Msg 170, Level 15, State 1, Procedure testit, Line 5
Line 5: Incorrect syntax near '@tblnm'.

Any idea

View 1 Replies View Related







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