Is there a way to put more then one data set in a list.
I have a report that has three data sets with three tables. Now i want to show each report by Region, per page. So you can view the same stuff for each region seperately, instead of all together. Is there a way to do this. Where i dont have to go back in my code, and find a way to link everything together, so its in one data set .
Hello and thanks in advance.
I was wondering if anyone has ever written a chart with multiple datasets.
I need to be able to show sales dollars inflow by order date on one line and on the other needs to be sales dollars delivered by delivery date. So the all sections Values, Category groups, and Series Groups in the chart will be from 2 different datasets.
I have tried but it will not allow aggreates in the series groups.
Any Ideas would be greatly appreciated.
Thanks, Leo
Hi!Another silly question:If a stored procedure returns multiple result sets, how do I choose the oneI want to insert into a table?For example sp_spaceused returns two result sets if object name is ommited.I only need the first result set. How do I do that?Tnx!Darko
View 2 Replies View RelatedHi,
I am aware that SSRS does not support stored procedures with multiple data sets, but what if the same stored procedure may only return one set based on a parameter?
For example, lets say if parameter @TableID = 0, the stored procedure returns 2 result sets. But if @TableID = 1, or @TableID = 2, the SP only brings back 1 result set. Is this also not supported?
When I try to do this it still only brings back the fields from the first table. Am I doing something wrong?
Thanks for any insight.
I know I can do a JOIN(parameter, "some seperator") and it will build me a list/string of all the values in the multiselect parameter.
However, I want to do the same thing with all the occurances of a field in my result set (each row being an occurance).
For example say I have a form that is being printed which will pull in all the medications a patient is currently listed as having perscriptions for. I want to return all those values (say 8) and display them on a single line (or wrap onto additional lines as needed).
Something like:
List of current perscriptions: Allegra, Allegra-D, Clariton, Nasalcort, Sudafed, Zantac
How can I accomplish this?
I was playing with the list box, but that only lets me repeat on a new line, I couldn't find any way to get it to repeate side by side (repeat left to right instead of top to bottom). I played with the orientation options, but that really just lets me adjust how multiple columns are displayed as best I can tell.
Could a custom function of some sort be written to take all the values and spit them out one by one into a comma seperated string?
Hi,
do u know
has sql server 2000 support MARS?
I'm having some problems dealing with a VB script which delivers multiple results back from SQL Server. The script opens a connection to SQL 2000 and executes a procedure. The stored procedure has five select statements for its result. I receive a recordset object and try to move through each resultset. I get the first two result sets but when I execute the rs.nextrecordset on the third result, the rs.EOF is set to true and I never receive the third through fifth result set. The only difference I see from the stored procedure is that the first two result sets are "Select"s from local variables (ie @headertxt) and the third through the fifth result set are "Select"s from temporary (#tmptble) tables. I can't determine the root cause of why I can't see the third though fifth result set. I have "set nocount on" in the procedure. I have executed the stored procedure in Query Analyzer under the same ID/PW that I'm setting the connection with in the VBS. No errors are generated from the execution of the stored procedure. I'm running MDAC 2.81 on the workstation.
VBS:
dim Conn, strProvider, rs, SQL, i, j
dim asoftime, errorcode, cur1, cur2, atype, asofdate, display_title
'Get the data
Set Conn = Wscript.CreateObject("ADODB.Connection")
Conn.CommandTimeout = 60
Conn.ConnectionTimeout = 60
dim datasource
datasource = "xxxxxxxxx"
strProvider = "Provider=SQLOLEDB; Data Source=" & datasource & "; Initial Catalog=s; User ID=frndis; Password=frndis1; Connect Timeout=15; Persist Security Info=True;UseProcForPrepare=0"
SQL = "exec s.dbo.prcWebGetMostActiveSongs @currency = 'USD'"
Conn.Open strProvider
Set rs = Conn.Execute(SQL)
asoftime = rs(0)
wscript.echo asoftime, rs.Fields.Count
set rs = rs.nextrecordset
cur1 = rs(0)
cur2 = rs(1)
atype = rs(2)
display_title = replace(rs(3), "&", "&")
wscript.echo cur1
wscript.echo cur2
wscript.echo atype
wscript.echo display_title, rs.Fields.Count
set rs = rs.nextrecordset
wscript.echo rs.Fields.Count
'grand total
while (not rs.eof)
for i = 0 to rs.Fields.Count - 1
wscript.echo rs(i)
next
rs.movenext
wend
wscript.echo "finished grand"
set rs = rs.nextrecordset
'mostactive total
while (not rs.eof)
rs.movenext
wend
wscript.echo "finished MA"
set rs = rs.nextrecordset
i = 0
'details
while (not rs.eof)
i = i + 1
rs.movenext
wend
wscript.echo "finished details", i
rs.close
Conn.Close
set rs = nothing
set Conn = nothing
Thanks,
Sid
Hello,
Is it possible to save the results of several SP calls in a script, to one file?
Here's what I mean:
I want to run these 4 sp calls--
exec EPC_SP1 'aph','live'
exec EAUI_SP2 'noble','newswire'
exec EAUI_SP3 'noble',1
exec EAUI_SP4 5507,'live'
And save the results of each one of those calls to the same file, in other words, an APPEND
Is this or something like it possible in TSQL?
Help appreciated!
Thank you,
--PhB
Hi,
I started working with SRS from the past one month. I am breaking my head to do this. My report has a single stored procedure. I made it to work all values of a parameter like
exec sp_employee 'All' (Returns data for all employees)
exec sp_employee '1111' (Returns data for an employee with id 1111)
exec sp_employee '1111,2222' (Returns data for an employee with id 1111 as well as 2222)
This part works perfectly. I could successfully pass the values as mentioned above using a code block.
Now my challenge is how to layout the data on the report.
I need to show the report as follows:
Employeeid Employee Name Address etc.......
Skills:
Skill# Skill Name
1 11111
2 22222
3 33333
4 44444
Contacts:
Contact Type Contact#
Main 111-1111
Emergency 222-1111
Eductation:
Name University
Degree UofU
Masters UofU
I tried using group by employeeid. But i can only show one group of data either Skills/Contacts/Education.
I do not know if I use sub-report, how to pass the parameter from the main, when I pick multiple values for Employeeid.
Any idea how to do this? I appreciate your help,
Thank you,
I have a stored procedure like the following. This returns 2 result sets, however i only want it to return 2nd (SELECT SomeField FROM SomeTable). How is this done? Note - it is not possible to change 'SomeSPThatReturnsAnIntAndAResultSet '
CREATE PROCEDURE [dbo].[SomeSP]
@SomeParam int
AS
BEGIN
SET NOCOUNT ON;
declare @SomeScalar int
exec @SomeScalar = SomeSPThatReturnsAnIntAndAResultSet @SomeParam
if @SomeScalar = 0
BEGIN
SELECT SomeField FROM SomeTable
END
END
hi
i read that stored procedures can return multiple result sets?how is that?
thanks in advance.
Seasons greetings to everyone,A simple question. Could someone show me the syntax to produce multiple (2 or 3) result sets in a stored proc and how you access those sets from a c# program (ASP.NET)..Couldn't find a reference on Google, maybe I was asking the wrong question! Thanks for any help regardsDavej
View 3 Replies View RelatedHi all,
I want to know how to handle multiple result sets return from Stored Procedure? I know one way is to insert the result sets into the table, but the limitation is the result sets must have the same data structure. If the result sets have different data structure, how can I handle it.
Thanks,
To anyone that can help me
we have a application written in straight ASP, we recently upgraded our SQL database connecting to the application from SQL 2000 to 2005 here is the issue
currently SQL 2005 is located in our test and UAT environments, SQL 2005 is not clustered in either location. Now let me make it clear THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIRONMENT, I REPEAT THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIROMENT, THIS PROBLEM IS ONLY HAPPENING IN OUR UAT ENVIROMENT
Each enviroment is outfitted with the following SQL 2005 environment, Enterprise Edition SP1 with Cumlative hotfix package 2153 just the SISS fix ONLY
now the problem is this, we have a vb program located directly on the server that has SQL 2005 on it that VB program inserts a timestamp into a table inside the database, now how this happens is the program connects to the database and opens a recordset with a forwardonly cursor and inserts the timestamp
NOW THE EXACT PROBLEM IS THAT THE VB PROGRAM CANNOT OPEN MULTIPLE RECORDSETS, THE ERROR RECEIVED IS LABLED LIKE THIS
ODBC DRIVER ERROR 8000405 SHARED MEMORY SQL DOESNT EXIST OR ACCESSED DENIED,
NOW here is the interesting part, AGAIN THIS IS NOT HAPPENING IN OUR TEST ENVIRONMENT, THE VB PROGRAM AGAIN IS ON THE TEST SQL SERVER AND HAS A FORWARD ONLY CURSOR AND INSERTS JUST FINE
NOW IF I MOVE THE VB PROGRAM OUT OF OUR UAT ENVIRONMENT ONTO ANOTHER SERVER IT INSERTS THE RECORD ONTO SQL SERVER JUST FINE, IT OPENS THE RECORDSET AND OPERATES NORMALLY, BOTH SERVERS HAVE EXACT SAME PERMISSIONS
Can anyone help me understand whats happening here
One note, when we changed the cursortype from forward-only to keyset it worked fine but that doesnt explain things
why does it work in test so clearly what we did with changing the cursortype isnt the answer
Thanks please consider this a urgent request
I have a report based on our product names that consists of two parts.Both insert data into a temporary table.1. A single grouped set of results based on all products2. Multiple tables based on individual product names.I am getting data by calling the same stored procedure multipletimes... for the single set of data I use "product like '%'"To get the data for individual products, I am using a cursor to parsethe product list.It's working great except that I have no idea how to identify theresults short of including a column with the product name. While thatis fine, I'm wondering if there is something that is like a header ortitle that I could insert prior to generating the data that would looka little tighter.Thanks in advance-DanielleJoin Bytes!
View 3 Replies View RelatedI am calling stored procedure called GetCommonItemCount within another stored procedure called CheckBoxAvailability, the first stored procedure should return a count to second stored procedure and based on that some logic will be executed.
I have 2 problems in that
1. The result is not coming from first stored so the variable called @Cnt is always 0 although it should be 18
2. At the end i need to see in the output the result from second stored procedure only while now i am seeing multiple results of record sets coming.
I have attached the scripts also, the line i described in step1 is
I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.
Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?
Could I ask how to spit the data into training and validation sets when doing data mining?
Thanks
Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.
Let's explain a little more:
I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?
Thanks for your help!
Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma
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
Hi All,
I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.
Does anyone have any suggestions how i would go about doing this?
Thanks in advance
Humate
quote:Originally posted by Michael Valentine Jones
It takes real skill to produce something good out of a giant mess.
I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.
View 12 Replies View RelatedHi
I have a matrix whos colunm group is filed by Dataset1,
now i want to add naother colunm group,but using the Dataset2
can I use two different dataset for a matrix,
for differnt colunm group
please help me in this regards
thanks
I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.
How can i write a query to do this? Examples are as below.
Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000
Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900
create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)
[Code] ....
I've seen that sometimes is better to split the table into a test dataset and a training dataset, and I'll appreciate if anyone can explain why is this...
thanks
Santiago Aceñolaza
Argentina
Hi,
I'm using a matrix report where in i want to use two datasets in the same report. How can i make the dataset dynamic for a single report.
Regards
Hi,
I'm trying to created a report.
Final report looks like this.
Total Loans/Lines (#)
13,283
Total Commitments ($ MM)
$1,703
Total Outstandings ($ MM)
$1,175
A
B
C
D
F
Bankruptcy
0
$0
$0
0.00%
0.00%
Charge Off
0
$0
$0
0.00%
0.00%
Source table looks like this;
Bankruptcy
0
Charge Off
0
CLTV
131
DSR
102
Exc Total
265
FICO
7
Foreclosure/Repossession
Grand Total
13283
Loan Amount
32
Column D = A Bankrupcy(0) / Total Loans/Lines #(13283)
But it does not let me to use report expression as its not in the same scope.
Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working.
Thanks
Hi,
I have designed a contact manager with Data Grid Control bound to a Data Set.
When the application closes, data from Data Set is written to XML file and when application opens, data from XML file is loded into Data Set and is show in Data Grid control.
Contacts in my application can exceed over 1,000
So, Is Data Set capable of handling lot of data very efficiently in memory?
Please advise
Hello,
I am using existing code, which I am trying to convert from using MS Access to SQL Server 2005...
The data set works fine with MS Access database, however when executing with SQL Server 2005 as data source, it generates the following error:
"..The data types ntext and nvarchar are incompatible in the equal to operator..."
in this line:
count = adapter.Update(dataset);
Not sure what should I look for since data sets are new to me.. Where should I check to fix this problem? I have noticed that the table has two columns with nvarchar...
I have two queries that generate two different datasets. One is a count of memebers, and the other is count of admits. I need to generate a calculated field from the two data sets called admits per 1000, which is essential the count of admits/counts of members *12000 I was able to calculte admits per 1000 easily in excel, however I need some insight on how to do is SQL.
Below are my queries from the two datasets.
MemberMonths dataset:
Select
factMembership.BusinessUnitCode,
EffectiveCCYYMM,
ISNULL(count(Distinct MemberId),0) As MemberCount
From factMembership
[Code] ....
Admits dataset:
SELECT
Factadmissions.BusinessUnitCode,
factAdmissions.AdmitCCYYMM,
ISNULL(Count(AdmitNum),0)As [Count of Admits]
FROM factAdmissions
[Code] ...
I have a situation where i have a transactional fact table which consists of date, row type, order number and value. Simple example below
Date, RowType, OrderNo, Value
01-May, New, A1, 100
01-Jun, Change, A1, -10
01-Jul, Invoiced, A1, -90
What I need to be able to do is somehow select based on a day, the total value of open orders. I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.
I created Data Collection in wrong DB, how can I change the DB or return to default(as it came with clean version of SS) ?
View 3 Replies View Related