Using Sets In My Dataset Query
Oct 11, 2007
I popped into the Transact-SQL forum to get some help and created the dbo.selectaudit1 table, which can be queried succesfully.
Code Blockcreate table dbo.selectaudit1 (startkey varchar(10) not null,
endvalue varchar(10) null,
endkey as coalesce(endvalue, startkey))
insert into dbo.selectaudit1 (startkey)
select '042'
union all select '140'
union all select '2089'
union all select '2031'
union all select '2051'
union all select '2100'
union all select '2299'
union all select '2300'
union all select '2349'
union all select '2350'
union all select '2389'
union all select '2390'
union all select '2399'
union all select '2732'
union all select '2733'
union all select '2849'
union all select '2850'
union all select '2883'
union all select '2898'
union all select 'V073'
union all select 'V078'
union all select 'V100'
union all select 'V109'
union all select 'V580'
union all select 'V581'
union all select 'V661'
union all select 'V662'
union all select 'V671'
union all select 'V672'
union all select 'V711'
union all select 'V760'
union all select 'V769'
insert into dbo.selectaudit1 (startkey, endvalue)
select '1400' ,'2089'
union all
select '2100', '2299'
union all
select '2300' ,'2349'
union all
select '2350' ,'2389'
union all
select '2390' ,'2399'
union all
select 'V100' ,'V109'
union all
select 'V760' ,'V769'
Then in reporting services, I created a report with the following query in my dataset. This query works fine in a BIDS query windows, however, when I refresh my dataset and attempt to execute the query I am getting the following error:
Cannot find the object "selectaudit1" because it does not exist or you do not have permissions.
(Microsoft SQL Server, Error: 1088)
Code Block
Select
p.MRN
, p.PatientName
, CONVERT(CHAR(20),p.AdmitDate,101) AS AdmitDate
, CONVERT(CHAR(20),p.DischDate,101) AS DischDate
, p.VisitTypeCode
, d.VisitTypeName
, p.AnyDx
, p.PrinDxCode
, p.PrinDxDesc
, p.SecDx1Code
, p.SecDx2Code
, p.SecDx3Code
, p.SecDx4Code
, p.SecDx5Code
, p.SecDx6Code
, p.SecDx7Code
, p.SecDx8Code
, p.SecDx9Code
, p.SecDx10Code
, p.SecDx11Code
, p.SecDx12Code
, p.SecDx13Code
, p.SecDx14Code
, p.SecDx15Code
From dbo.PtMstr p inner join
ampfm.dct_VisitType d on d.VisitTypeCode=p.VisitTypeCode
INNER JOIN dbo.selectaudit1 sel
ON (PrinDxCode between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
OR (SecDx1Code between sel.startkey and sel.endkey
AND DischDate between '2006-11-01' and '2007-09-30')
What am I missing?
View 4 Replies
ADVERTISEMENT
Nov 8, 2007
hi all.....
i select some records from my database using mysqldatareader.......but i want to load it's result sets into my datatable or dataset....
is it possible and how can i do it this way ?
thanks
View 1 Replies
View Related
Apr 21, 2003
PLEASE SEE my update (post #3) for a better simpler explanation!!!!!
Hi,
I need a query which is basically
(query 1)
Except
(query 2)
SQL server supports
(query 1)Union (query 2)
but I can't get the Except to work.
Alternatively,
I also tried to implement it by using the "NOT IN" but didnt work.
Background: 3 tables, accnts, Opty, Opty_postn
I want
the accounts who have 1 or more opty's at 100% (sold) before 1/1/2002 but zero opty's at 100% after 12/31/2001.
accnts has fields ID & name, ROW_ID
opty has fields acct_id, prob, ROW_ID
opty_postn has fields opty_id, close_dt
My query:
SELECT accnt.name
FROM accnt
WHERE
accnt.ROW_ID IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt<1/1/2002)
)
AND accnt.ROW_ID NOT IN
(
SELECT acct_id
FROM opty
where prob = 100
AND opty.ROW_ID IN
(SELECT opty_id
FROM opty_postn
WHERE close_dt >12/31/2001)
)
AND accnt.ROW_ID = opty.accnt_ID
my intent was
select account.names
where id
is in set(prob = 100 & sale before 1/1/02)
but not in ( prob = 100 & sale after 21/31/01)
SO ,the accounts which have sales both before and after get counted. But I want only those accoutns which have sales only before 1/1/02.
I hope i explained this right.
Thanks in advance for your help.
Ash.
View 14 Replies
View Related
May 7, 2008
I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME
View 13 Replies
View Related
May 22, 2000
We are trying to limit are query that returns items from our database. The
query currently returns 32,000 records. We are trying to figure out an effecient way so we can request the 1st 50, or the 3rd 50, or the 5th 50 to display to the screen. We dont want to return the entire 32,000 then limit whats displayed to the screen in ADO. We want the select statment to only return 50 at a time. Any suggestions?
View 1 Replies
View Related
Aug 16, 2014
I need to get 3 rows per set based on a date given (must be this date) but I want the rows to be based on this date as...
1 ( row where the date from the date column is the next date after given date )
0 ( row where the date is the closest date prior to the date given )
-1 ( prior to the date at 0 )
And add a column with the relative number.
** The dates for the same name and item will never repeat.
For example, a set of rows...
Row ID, Name, Item, Number, Date
1 Andy, Item1, 12030, 2014-06-30
2 Andy, Item1, 62030, 2014-03-31
3 Andy, Item1, 30300, 2013-12-31
4 Andy, Item1, 40030, 2013-10-31
5 Andy, Item1, 50030, 2013-08-30
6 John, Item2, 50240, 2014-04-30
7 John, Item2, 41400, 2014-03-31
8 John, Item2, 40509, 2014-01-31
9 Andy, Item2, 24004, 2014-03-31
10 Andy, Item2, 20144, 2013-12-31
11 Andy, Item2, 20450, 2013-09-30
12 Andy, Item2, 25515, 2013-06-30
If I have 2014-03-15 as the date and search for 'Andy', I expect...
Row ID, Item, Date, Relative Date
2, Item1, 2014-03-31, 1
3, Item1, 2013-12-31, 0
4, Item1, 2013-10-31, -1
9, Item2, 2014-03-31, 1
10, Item2, 2013-12-31, 0
11, Item2, 2013-09-30, -1
This is what I'm using which I have no issues switching if necessary...
DATEDIFF( quarter, 2014-03-31, date )
date BETWEEN DATEADD( quarter, -1, '20140315' ) AND
DATEADD( day, 1 ( DATEADD ( quarter, 2, '20140315' ) )
which returns...
Row ID, Item, Date, Relative Date
2, Item1, 2014-06-30, 1
3, Item1, 2014-03-31, 0
4, Item1, 2013-12-31, -1
9, Item2, 2014-03-31, 0
10, Item2, 2013-12-31, -1
Not sure if date math is the best option here. Perhaps using row_number() in some way?
I'm trying to avoid having to process the entire table programmatically given the size of the data set.
View 8 Replies
View Related
Jun 10, 2015
I have a general question I need a few pointers or explanations. I have a top level Data Flow query in a SSIS package that has been running for years for which I have made some minor changes to pull additional fields and rectify a filter condition.
I have noticed that when I run this same query, exactly as entered in the Data Flow task, in the SQL Server 2012 Management Studio, the query returns some 105,000+ records but when run in development by executing the package the top level task only indicates some 21,000 records returned. Since there was not other transformations or filtering performed, I had expected the same record count and am not getting it.
View 5 Replies
View Related
Oct 12, 2007
Hi All,
I have two SQL queries that we would like to automate. Ideally we want them to both be scheduled to run and dump their results to a single Excel spreadsheet with two workbooks, one for each query
Is it possible to do this? If not, sending each query to a seperate XLS or CSV file would be OK
Here are the queries:
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, p21_view_unvouched_po_currency_report.po_line_number, po_line.created_by, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report, P21.dbo.po_line po_line
WHERE po_line.po_no = p21_view_unvouched_po_currency_report.po_no AND po_line.line_no = p21_view_unvouched_po_currency_report.po_line_number
and
SELECT p21_view_unvouched_po_currency_report.unvouched_document_type, p21_view_unvouched_po_currency_report.date_created, p21_view_unvouched_po_currency_report.unvouched_document_no, p21_view_unvouched_po_currency_report.line_number, p21_view_unvouched_po_currency_report.po_no, contacts.last_name, p21_view_inventory_return_hdr.buyer_id, p21_view_unvouched_po_currency_report.po_line_number, p21_view_unvouched_po_currency_report.item_id, p21_view_unvouched_po_currency_report.item_desc, p21_view_unvouched_po_currency_report.qty_received, p21_view_unvouched_po_currency_report.qty_vouched, p21_view_unvouched_po_currency_report.order_date, p21_view_unvouched_po_currency_report.location_id, p21_view_unvouched_po_currency_report.supplier_id, p21_view_unvouched_po_currency_report.supplier_name, p21_view_unvouched_po_currency_report.extended_cost_home
FROM P21.dbo.contacts contacts, P21.dbo.p21_view_inventory_return_hdr p21_view_inventory_return_hdr, P21.dbo.p21_view_unvouched_po_currency_report p21_view_unvouched_po_currency_report
WHERE p21_view_inventory_return_hdr.return_number = p21_view_unvouched_po_currency_report.unvouched_document_no AND contacts.id = p21_view_inventory_return_hdr.buyer_id
View 4 Replies
View Related
Apr 14, 2007
I create a Query in Dataset that get values from multiple tables when i execute query in query builder its execute successfuly but whan i am save this query found following message.
"The new command text returns data with schema different from the schema of the main query. Check your query's command text if this is not desired"
after OK i am check my query result in Preveiwdata its show deffrent result.
Help me
regards
Fakhruddin
View 1 Replies
View Related
Nov 7, 2006
I have a dataset filled with data.
I want to get a particular (row,column) value from the dataset.
How do I query on the dataset if the query is
firstname, lastname where employeeid='1234'?
View 9 Replies
View Related
Oct 2, 2007
Please Help I'm having a brain freeze here,
Table
column 1 column 2
A 10
A 20
B 10
C 10
C 20
Im looking for a query that returns record 3. Ruturning just the records that have a record in which column 2 is a 10 but not a 20. If I enter WHERE = 10 I get 1,3,4. Please help. Thanks!!
View 6 Replies
View Related
Jul 9, 2002
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
View 1 Replies
View Related
Sep 27, 2007
I've faced the following problem while forming DataSet. I'd like to ask the following SQL query:
___
select Users.UserID,Users.FirstName,Users.LastName, Users.Email, data.Suffix, data.Unit,data.Telephone, data.IM,
data.Websitefrom(select UserID, max(case PropertyName when 'Suffix' then PropertyValue end)Suffix, max(case PropertyName when 'Unit' then PropertyValue end)Unit, max(case PropertyName when 'Telephone' then PropertyValue end)Telephone, max(case PropertyName when 'IM' then PropertyValue end)IM, max(case PropertyName when 'Website' then PropertyValue end)Website from (SELECT UserProfile.UserID, UserProfile.PropertyValue, UserProfile.PropertyDefinitionID,
ProfilePropertyDefinition.PropertyName FROM UserProfile,ProfilePropertyDefinition WHERE UserProfile.PropertyDefinitionID=ProfilePropertyDefinition.PropertyDefinitionID )table2 group by UserID)datainner join Users on Users.UserID = data.UserIDWHERE data.Suffix = 'IT';
__
Nothing happens while fulfilling as well (i.e. if to set up Breakpoint and watch the DataSet condition - it will be empty there). If to change the request to let's say some standart - then DataSet downloading begins - it seems there's something wrong with the request
I'm checking....If to look with the holp of request master and press "perform", data is reflected in a normal way...But as soon as i press "Bild", emptiness is shown as usual
Please, prompt me what to do
Initial code in the file .aspx.cs:
------------------------------
UserInfo ds = new UserInfo();UserInfoTableAdapters.UserInformationTableAdapter da = new UserInfoTableAdapters.UserInformationTableAdapter();
da.Fill(ds.UserInformation);
Info.DataSource = ds.UserInformation;
Info.DataBind();
View 15 Replies
View Related
May 5, 2008
Hello, I have two datasets. One dataset has some delivery informations the other dataset has billing informations. The first dataset builds the resultset out of the selected parameters (those parameters do not correspond to the values in table two). What I wanna do is pass some informations (like custno, artno) to the other dataset so that I can only display the corresponding information. Any ideas?
Thanks in advance!
View 2 Replies
View Related
Oct 27, 2006
I am trying to build a report with a prompt to select building. It all works fine except I in the prompt building is list as may times as there is data for it. I want it to only display once, but not effect the other data is there a way I can query this?
I have my 2 datasets and my parameter set up to display building to select from. My issue is I get multiple values in the Select Value Prompt. It will pull the correct data I just want to clean up the Select Value Prompt.
Example: of prompt list
Building 1
Building 1
Building 1
Building 2
Building 2
Dataset 1:
Select Building, First, Last
From Table
Dataset 2:
SELECT Building, First, Last
From Table
WHERE (Table.Building = @Building)
View 4 Replies
View Related
Dec 19, 2007
I need to write a query in which some of the columns will be derived from a dataset (join) and the other columns will be derived from another dataset. Is there a way to do this?
Thanks!
View 1 Replies
View Related
Feb 27, 2007
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value.
This is the code for the Method to return a datareader
public SqlDataReader GetOrgID()
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
SqlConnection con = new SqlConnection(conString);
string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlCommand cmd = new SqlCommand(selectString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
con.Open();
SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return dtr;
This is the code trying to accomplish the same thing with a Dataset instead.
public DataSet organID(DataSet dataset)
{
Singleton s1 = Singleton.Instance();
Guid uuid;
uuid = new Guid(s1.User_id);
string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID";
SqlConnection con = new SqlConnection(conString);
SqlCommand cmd = new SqlCommand(queryString, con);
cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(dataset);
return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well.
So assume that the Guid is a valid entry..I should return a valid dataset but its null.
View 2 Replies
View Related
Sep 17, 2005
I would like to know about Reporting Services. Can we change the Dataset queries used in a report at run time.
View 1 Replies
View Related
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
May 23, 2007
Let me simplify. The value of my dataset field Dimension_1 is
[SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION]
I want to use parameters for SOB Year and Yr Id like I do on the query.
But if I use
="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]"
it returns null for the values. I don't know how to make these parameters
I am passing in parameters for the table and column to build an MDX query here is my query.
="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"
Anyone know what value I need to use on the dataset field to access the column?
Thanks,
Roger
View 10 Replies
View Related
Oct 16, 2007
Is there a way to create an expression that references the DataSets / Queries? I'd like to be able to create an expandable text box that shows the raw sql query used to generate the report.
View 4 Replies
View Related
Apr 2, 2008
Hello All,
I have 2 MultiValued Parameters in my reports, our of which first is dependent on the other.
Eg. I have a SubProductGroup Parameter and depending upon the value selected in SubProductGroup, the other parameter which is Product gets filled in combo box.
Both these parameters are MultiValued and I am having "Select ALL" option in both the combo box.
Now, whenever you select any SubProductGroup values, the Dataset for Product gets executed to fill the Product combo box.
The MultiValued Parameters works using the "IN" Clause. All those products are retrieved which are "IN" the selected SubProductGroup.
Whenever user selects "Select All" option for SubProductGroup, there is no need for this "IN" clause, because we actually need all the records for Product.
Similarly, I have the Main Dataset which retrieves the Sales data for the Products selected from the Combo Box. So, the same question arises there also.
Right now I am using STRTOSET( ) and Join( ) function and this is working fine for me.
But when User Selects "Select All" in SubProductGroup, it takes a long time to fill the combo box for Product as well as when user selects "Select All" for Product, it takes a long time for the actual Dataset to retrieve records. But its working fine.
In order to improve the Performance, I am trying to eliminate this IN clause when "Select All" is selected.
How can I do this ?
Any Inputs ?
Thanks & Regards,
Kapadia Shalin P.
View 4 Replies
View Related
Jun 15, 2007
Hi every1,
I really need your help.
I have a web form which is having Drop Down box in which I have 2 selection 'P' and 'C'. Depending upon the selection I have to call the Dataset( If I can make it in code behind) or I can do it in one Complex query if you guys will help me.
Query should be like this.
select a.* , case when selection='P' then select p.column1 from ptable p
case when selection='C' then select c.column1 from ctable c
else a.column1
from atable a
where
acolumn2 = "USERINPUT"
but now the problem is in FROM and Where Clause.
when selection is 'P' then I have to include ptable in FROM Clause and in where clause I have to include two condition like (p.column1 = a.column1 and p.column2="USERINPUT1" or
if selection is 'C' then I have to include ctable in FROM clause and in where clause I have to include two different condition like (c.column1 = a.column1 and c.column2="USERINPUT")
So at once the Query will be any of one like below:
(1) some times this:
select a.* , case when selection='P' then select p.column1 from ptable p
case when selection='C' then select c.column1 from ctable c
else a.column1
from atable a, ptable p
where
a.column2 = "USERINPUT" and
p.column1 = a.column1 and p.column2="USERINPUT1"
----------------------------
(2) some times this:
select a.* , case when selection='P' then select p.column1 from ptable p
case when selection='C' then select c.column1 from ctable c
else a.column1
from atable a, ctable c
where
a.column2 = "USERINPUT" and
c.column1 = a.column1 and c.column2="USERINPUT"
Please let me know if you have any kind of confusion so I can clear this but if you got everything from my description please do help me to solve this. I am really struggling for this.
One way I was thinking to generate 3 dataset one(query or dataset) for 'P' , one for 'C' and one for else so I can get the input (selection) from the form and then decide in codebehind which dataset I have to make call so no need to write complex query. But this is my thought and I dont know how to make a call and everything either so please help me in this as well, if you like this solution .
Or try to solve the above query please.
Thanks
Roy
View 1 Replies
View Related
Jul 19, 2007
Hello Guys,
I have a question that seems easy but I can not figure out...
Premise:
Have Custom code that fixes Divide by Zero Errors in SSRS. I have added the code to the Custom Code area in Report Properties correctly.
I have a Dataset that has a calculation for a column within a select statement
Query Pseudocode:
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from
(subquery"blah" )
Union
(Subquery"blah")
Custom Code:
Public Function SafeDiv(ByVal numerator as Double, ByVal denominator as Double) as Double
if denominator = 0 then
return 0
else
return numerator/denominator
end if
End Function
How To use:
If you have a field that does division and you need to eliminate the divide by zero error that occurs with SSRS then type =code.SafeDiv(first,second) in the field.
Problem:
How do I add this code reference in the following dataset select statement
select ...[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income))...
,year
from
(subquery"blah" )
Union
(Subquery"blah") table1
I tried to do this:
from this:
[FRC%]=convert(decimal(13,2),sum(cost))/convert(decimal(13,2),sum(income)) ...
to this
[FRC%]=code.Safediv(convert(decimal(13,2),sum(cost)),convert(decimal(13,2),sum(income))) ...
But it did not work...gave me this error:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous.
------------------------------
ADDITIONAL INFORMATION:
Cannot find either column "code" or the user-defined function or aggregate "code.safediv", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=4121&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Help!
P.S.
this is a Matrix report and this select statement is within one of the datasets that fill a matrix.
View 8 Replies
View Related
Jan 16, 2007
Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,ratjetoes.
View 2 Replies
View Related
Mar 6, 2007
I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?
View 5 Replies
View Related
Jul 13, 2005
I have a form that loads a dataset. This dataset is composed from SQL statements using alias and unions. Basically it takes uses data from 3 tables. This dataset also has a alias column called ClientName that consists of either people's name or business name.In addition, the form also consist of a search field that allows user to enter the 'ClientName' to be searched (i.e. to search the alias column). So, my question is how can the alias column be searched (user can also enter % in the search field)Function QueryByService(ByVal searchClientNameText As String) As System.Data.DataSet
If InStr(Trim(searchClientNameText), "%")>0 Then searchStatement = "WHERE ClientName LIKE '" & searchClientNameText & "'"Else searchStatement = "WHERE ClientName = @searchClientNameText"End If
Dim queryString As String = "SELECT RTrim([People].[Given_Name])"& _"+ ' ' + RTrim([People].[Family_Name]) AS ClientName, [Event].[NumEvents],"& _"[Event].[Event_Ref]"& _"FROM [Event] INNER JOIN [People] ON [Event].[APP_Person_ID] = [People].[APP_Person_ID]"& _searchStatement + " "& _"UNION SELECT [Bus].[Organisation_Name],"& _"[Event].[NumEvents], [Event].[Event_Ref]"& _"FROM [Bus] INNER JOIN [Event] ON [Bus].[APP_Organisation_ID] = [Event].[APP_Organisation_ID] "& _searchStatement
..........End Function
View 2 Replies
View Related
Feb 5, 2007
I'm bothered by an issue of updating a column in input dataset from a update query. It looks like SSIS has a very poor function on this.
Example, I have an input dataset of name, salary, dept_no, and I have another table called departments which has fields of dept_no, Dept_name, basic_salary
now I want to update salary column in input dataset with basic_salary if it's salary is smaller than the basic_salary.
update #tmp set salary = basic_salary where #tmp.salary <departments.basic_salary and #tmp.dept_no = departments.dept_no
how could I impletement this in SSIS package?
I tried with lookup, modify scripts by enabling memory restriction. It doesn't say any error when I save the package, but I never get pass debug.
HELP!!
View 5 Replies
View Related
Jun 13, 2007
Hi,
I've a dataset which is filled using a stored procedure in either MSSQL2005 or 2000.
The procedure may return sql-errors (f.ex 208 if a table is not found), but the procedure will anyway return data which shall populate the dataset, independent on 208 errors.
The procedure works fine if running it from f.ex. SqlMgtStudio, and the dataset is filled if the procedure triggers no errors.
However, if the procedure triggers an error like 208, then I get an exception in the application and the dataset is not populated with the data returned by the procedure.
Is there a way of telling the DataSet/SqlDataAdapter to fill the dataset even if the sql-code genererates som errors?
Regards, Guttorm
View 2 Replies
View Related
Jul 27, 2007
It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:
reportMonth - Just a non-queried list of months for the user to select
reportYear - Generated using the following query from dataset "Years":
declare @curYear int
set @curYear = 2000
declare @yearTable table (repYear int not null)
while @curYear <= year(getdate())
begin
insert into @yearTable(repYear) values (@curYear)
set @curYear = @curYear + 1
end
select * from @yearTable
This generated a list like:
2000
2001
2002
etc.
I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.
Thanks
View 10 Replies
View Related
Sep 12, 2007
hello
I have been assigned the task to port some reports from Business Objects 5.0 to Reporting Services 2005.
The BO reports used data regions based on multiple datasets, which is not feasible in SSRS.
So I have to get everything in one big query. The problem is that the query runs for 2 hours instead of a couple of minutes with our old BO solution which got it's data from an access database !
is there a better way to do this ?
here is the query
Code Snippet
ALTER PROCEDURE [dbo].[MERCKGEN_HDMCKQ02_DoctorBrickDoctorGroups_values]
-- Add the parameters for the stored procedure here
@TimePeriodDesc varchar(255),
@TotalMarket varchar(255),
@datasetname varchar(255),
@VisibleProducts varchar(500),
@DoctorSubTerritory varchar(255)
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 xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal ,
SUM
(
CASE
WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN xpomeasures.rxvalues
ELSE 0
END
) AS totalrxvalues,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc <> 'Non -')
AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS rxrcount,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [high],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Medium')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [medium],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very high],
(SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography
ON xpomeasures.geographyid = xpogeography.geographyid
WHERE (xpomarket.productdesc = @TotalMarket)
AND (xpomeasures.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (xpomarket.datasetname = @DatasetName)
AND (xpogeography.datasetname = @DatasetName)
AND (xpogeography.doctorgroup = a.doctorgroup)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
) AS rxvaluesdoctorgroup
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.XpoMarketSize
ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN dbo.xpoproductsize
ON xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography a
ON xpomeasures.geographyid = a.geographyid
INNER JOIN dbo.xpospecialty
ON xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' )
ON xpomarket.productdesc = fn_mvparamsorted.parame
LEFT JOIN (
SELECT xpogeography.doctorgroup,
SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal',
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal'
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid
WHERE xpomeasures.datasetname = @DatasetName
and (xpomarket.datasetname = @DatasetName)
and (xpogeography.datasetname = @DatasetName)
AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
GROUP BY xpogeography.doctorgroup
) AS s ON s.doctorgroup = a.doctorgroup
WHERE (xpomeasures.datasetname = @DatasetName)
AND (xpomarketsize.datasetname = @DatasetName)
AND (xpoproductsize.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (a.datasetname = @DatasetName)
AND (XpoMarket.datasetname = @DatasetName)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
AND (a.doctorsubterritory = @DoctorSubTerritory)
GROUP BY xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
fn_mvparamsorted.parame ,
fn_mvparamsorted.sortid ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal
ORDER BY s.totalmarketrank,
a.doctorgroup ,
fn_mvparamsorted.sortid
END
View 4 Replies
View Related
Jun 26, 2007
I got this Error, Query Execution for Dataset 'Source'.
When i try to run a report that is actually a drillthrough from another report. It runs fine in Report Designer, and when i deploy to my Local server. But when i deploy it to my virtual Report Server, I get this error messege. Why is it doing this? and where can i see errors for this type of stuff, so i can figure this out. This uses the same Datasource as the report linked from it. That report works fine, why wont this one? any ideas?
View 8 Replies
View Related
Mar 13, 2008
Hi all,
hope anyone can help me on this one.
I have a parameterized report based on a MDX query (so no drag and drop of filters and fields).
Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter.
when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset.
Has anyone experienced this problem before, or am i forgetting something here?
thanks in advance
Steven J
View 1 Replies
View Related