Multiple Dataset
Nov 12, 2007
Hi
I am new to Reporting service, Trying to create a report RS2005.
I defined two dataset (query getting data from same database)
In report designer, I inserted a group and drap columns from first dataset. and inserted second group, when i dragcolumns from second dataset it shows like this
=First(Fields!GroupStatus.Value, "ParentGroup")
in my first group columns shows like
=Fields!Member.Value
when i run the report i am getting one row in second group.
this looks like very basic mistake I am doing, since I don't have any experience in RS2005, i am posting this question,
thanks in advance
kal
View 4 Replies
ADVERTISEMENT
Jan 30, 2007
Hi,
I have to design a tabular report where the data in all the columns of the table comes from the cube, but for one column where the data comes from the ODS table.
Could anyone please let me know if it is possible to combine the results of the query from a cube with query from an ODS table and display together in a single report?
Any thoughts on this would be greatly appreciated
Thanks.
View 1 Replies
View Related
May 8, 2008
I usually use Access for my database work, but a recent request needs data from a table with about 8 million records - a mite outside of Access' league.
So, I am using SQL Server Report Services to create this report.
Essentially, I need to be able to use a table from database A and a table from databse B in a single dataset for the report I'm making.
I'm self-learning this package and have not been able to locate if this is even possible, let alone how to do it. I can't see it not being possible, so I figure the procedure is simply eluding me.
I've seen a thread on a similar - if not the same - problem, and the answers given all seemed to be based upon the user being able to write to the database server or in some other manner manipulate the server. I have no such capabilities. All I can do is look at the data and create a report based upon what I see. No write access at all.
This wasn't a problem in Access, because, though the main data was on the server, I linked to any needed tables and everything else was local to my box.
Thanx in advance for any help!
View 5 Replies
View Related
Aug 23, 2006
Hi,
Is it possible to populate a dataset with tables returned by a stored proc?Consider this:
BEGINSELECT * FROM Table1SELECT * FROM Table2SELECT * FROM Table3END
If that is my stored proc, could I call it from a page and automatically populate a dataset with all 3 tables (if yes, then how?), or would I have to make 3 seperate calls to the db for each table?
Thanks
View 2 Replies
View Related
Dec 25, 2006
Hi,
I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.
I have seen the problem earlier and could not resolved it. Please let me know if any one can help.
Thanks in advance
Rohit
View 3 Replies
View Related
Sep 26, 2007
I'm looking for some advice on how to manage reports that use the same query in their datasets. I have multiple reports that use several datasets that are the same. If I need to make a change to one dataset, I need to remember to update the other datasets. Of course I don't always remember to do that!
Is there a way to create a dataset in a single location and then share it? I was thinking of using a View but I don't think it'll accept the parameters.
I've been cutting & pasting the entire query as I make change but I'm afraid it'll mess that up or forget to update a dataset.
How do you do it?
Any suggestions you have would be helpful.
Rob
View 7 Replies
View Related
Sep 1, 2007
I would like to know how to dynamically change the And/Or value on the Filter tab within a dataset.
Here are my two expressions within the filter:
Code Snippet
=Switch(Parameters!TotalCostFilterOp.Value = "=",Fields!tcost.Value = Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">",Fields!tcost.Value > Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = ">=",Fields!tcost.Value >= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "between",Fields!tcost.Value >= Parameters!TotalCost.Value AND Fields!tcost.Value <= Parameters!TotalCost2.Value,Parameters!TotalCostFilterOp.Value = "ie", ISNothing(Fields!tcost.Value), Parameters!TotalCostFilterOp.Value= "<",Fields!tcost.Value < Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<=",Fields!tcost.Value <= Parameters!TotalCost.Value,Parameters!TotalCostFilterOp.Value = "<>",Fields!tcost.Value <> Parameters!TotalCost.Value)
Code Snippet
=Switch(Parameters!CompanyFilterOp.Value = "%",LCASE(Fields!company.Value) like "*" & LCASE(Parameters!Company.Value) & "*", Parameters!CompanyFilterOp.Value = "=",Fields!company.Value = Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "ie", ISNothing(Fields!company.Value), Parameters!CompanyFilterOp.Value = "?", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value), Parameters!CompanyFilterOp.Value = "<>", Fields!company.Value <> Parameters!Company.Value, Parameters!CompanyFilterOp.Value = "?%", LCASE(Fields!company.Value) LIKE LCASE(Parameters!Company.Value) & "*")
The operator for both these is set to "=", and the value is set to "=true".
Based on user selection in a report wizard, the 4th column (And/Or) may need to be changed from 'and' to 'or', or vice-versa.
Is there anyway to accomplish this programatically?
Thanks in advance for you help!
View 2 Replies
View Related
Aug 23, 2007
Hey everyone,
I am trying to combine like data from two different data sources into a single data set. Is there anyway I can do this? It seems like I can only add one data set, but is there some sort of workaround I could use?
thanks,
Keith
View 4 Replies
View Related
Aug 16, 2006
Hi,
is there anyway to insert all the rows from a dataset to SQL Server table in a single stretch..
Thanks
Anz
View 1 Replies
View Related
Apr 29, 2008
Hi,
OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
View 5 Replies
View Related
Aug 11, 2004
Hi!
I have a question about SQL Reporting Services. I have a stored procedure which returns multiple resultsets (multiple select). In my reporting services project I have a dataset that connects to this procedure. But, when I execute it in the Data tab, I only get the first resultset. So can't I have more resultsets?
I can't use more datasets (that have the selects in the procedure) because the procedure is complicated and has many calculation. I've managed to get all the selects in only one and the reporting services in working in this way. How about many resultsets in a dataset?
Thank you!
Irina Stanca
View 5 Replies
View Related
Jun 15, 2007
Hello,
I have the following tables :
Area
areano
areaname
Locality
locno
areano
RequestType
reqid
reqdtls
WebSummit
SummitId
RequestorName
DateOfRequest
reqid
Areano
I want to display the total no. of Addition, Removals and Relocations during the past 3 months in a paticular Area.
I have written the query for this.
select q.[areaname] as Area,
classCount as TotalCount, ReqType
from ( select a.areano,
a.classCount, ReqType
from ( select areano,
count(*) as classCount, reqdtls as ReqType
from WebSummit
join RequestType
on WebSummit.reqid = RequestType.reqid
where date_created >= dateadd(mm, -3, getdate())
group by areano, reqdtls
) a
join WebSummit b
on a.areano = b.areano
group by a.areano,
a.classCount, ReqType
) p
join Area q
on p.areano = q.areano
The query works fine and has been tested. Now I want to display this in chart format.
The display should be in a chart format, one for each area showing a bar for each RequestType.
I have placed "TotalCount" in the Data Fields section and ReqType in Category fields. How do I use the Filter to set an expression so that it displays the data in a seperate chart for each Area.
I tried using =Fields!Area.Value and set it to Area1 to display only Area1 values, however the preview shows nothing.
Plz help me out.
Here's some sample data
Area
AreaNo AreaName
1 Area1
2 Area2
3 Area 3
4 Area4
5 Area5
6 Area6
7 Area7
Locality
LocNo AreaNo
1 1
2 1
3 1
1 2
2 2
1 3
2 3
3 3
4 3
1 4
2 4
1 5
2 5
3 5
RequestType
reqid reqdtls
1 Addition
2 Removal
3 Relocate
WebSummit
SummitId RequestorName DateOfRequest reqid AreaNo
1 John 12/6/2007 1 1
2 Jack 13/6/2007 1 1
3 Bill 12/6/2007 2 1
4 Ben 12/6/2007 3 1
5 Dale 14/6/2007 1 2
6 Evjen 15/6/2007 1 2
7 Fuller 16/6/2007 2 2
8 Jimmy 16/6/2007 3 2
9 Kart 16/6/2007 3 2
10 Fuller 16/6/2007 3 2
Regards,
Vidya
View 1 Replies
View Related
Jan 17, 2008
Hi, has anyone been able to implement something like this?
I have two data sets
1) List of all stores
2) Table containing the following fields: store, sales as of end of quarter, quarter end date
I am creating a graph that would track the trend of sales quarter per quarter for each store and would need to have an individual graph for each.
Is there a way to dynamically do this rather than creating an individual recordset for each store?
Thanks
View 1 Replies
View Related
Jan 14, 2008
Wondering how this is handled by Multicast component. If multiple copies are created in the memory and the size of the dataset is large, this could cause some performance problems. Any thoughts? TIA
View 8 Replies
View Related
Sep 13, 2007
Hi,
I want to design a report in which it will contain fields derived from 2 different stored procedures. I understand a 'table' can display data from a single dataset. How can i bind these two stored procedures into a single dataset so as when i click on the table and use its property 'DataSetName', to be able to select the dataset which holds all columns from stored proc 1 and stored proc 2. How can i link multible tables ( multible stored procedures with different column names in each one) into a single dataset to feed the report?
Thank you
George
View 1 Replies
View Related
May 20, 2015
I'm looking to build a report that is basically a series of line charts that is plotting a value over time. I can accomplish this one chart/dataset at a time, but I believe with 'List's this can be achieved in a more automated fashion.If I have some data similar to this:
Area_ID, Location_Name, DataValue, ReadingDate
1,Site1,100,1990-10-10
1,Site1,110,1990-11-11
1,Site2,105,1994-01-07
1,Site2,105,1994-02-07
2,Site3,113,1994-06-02
2,Site3,120,1994-07-30
2,Site4,120,1994-06-30
2,Site4,120,1994-07-30
I want the charts grouped by the Area_ID, and then each Location_Name is a Series in that chart. So in this example, I would end up with 2 charts (Areas 1 & 2), each have 2 series (Area 1: Site1, Site2 and Area 2: Site3, Site4)Is it possible to build this with a list? or do I need to structure the data differently?
View 6 Replies
View Related
Apr 10, 2007
Hi there,
I have a MS Access database (mdb) containing the following tables:
Crime
Criminal
CrimeCommitted
Hideout
CriminalType
The Criminal table contains information about each criminal and the CrimeCommitted table contains information about the specific crimes. I've written the following query to return only the latest crime committed by each criminal:
Code Snippet
SELECT Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType, Max(CrimeComitted.Date) AS Last_Crime_Comitted
FROM Criminal INNER JOIN CrimeComitted ON Criminal.CriminalID=CrimeComitted.CriminalID
GROUP BY Criminal.CriminalID, Criminal.Firstname, Criminal.Lastname, Criminal.Nickname, Criminal.Gender, Criminal.DOB, Criminal.Eyes, Criminal.Complexion, Criminal.Weight, Criminal.Height, Criminal.Build, Criminal.Scars, Criminal.Occupation, Criminal.CrimeOrgID, Criminal.IQ, Criminal.Hideout, Criminal.CriminalType;
This query works fine for obtaining the Criminal table data, but once i've include CrimeCommitted.Country in the SELECT statement, the data returned contained all the crimes committed by each criminal (i just need the latest crime).
The query doesn't work when another table, other than Criminal, is selected. How can i obtain the columns in the CrimeCommitted table in this query?
View 2 Replies
View Related
Oct 25, 2015
I have used my parameter value in where clause of my dataset control SQL query. When I choose a single value in my multiple-value parameter then I get output as expected. Hovewer when I choose multuple values I get an arror:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
----------------------------
Query execution failed for dataset 'DATABASE'. (rsErrorExecutingCommand)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)
I am convinced that it's a syntax problem. Is there a way I can change syntax of data output from query:
select @parameter
I need output to be like: value, value, value ... etc.
The dataset control query looks something like the following:
select
ID,
a,
b,
c,
d
from DATASET.dbo.Table
where ID in (select @parameter)
I have been searching through google for weeks to solve this and got this far and got stuck. Is it possible at all to achieve what I am looking for?
View 4 Replies
View Related
May 5, 2008
Hi All,
I created one report having more than two datasets. How can I display the "No result found Message" If some of the dataset having no data.
Thanks,
Aneesh
View 4 Replies
View Related
Sep 12, 2007
Hi All,
We are post-deployment with a serious reporting issue that's causing us to rethink our reporting solution. We're considering moving from what we have to SQL Server Reporting Services (client side).
I have spent the past couple of days getting up to speed on this feature and seeing if we can easily migrate it into our existing application. However, I've hit a couple of stumbling blocks and was hoping perhaps someone here could either tell me the solution or point me in the right direction.
I'm not having any problems creating basic reports (e.g. flat data).
However, I am having problems creating reports where there are related tables in a dataset.
The way it works with our existing solution is that I get a dataset (which contains several data tables) and point it to the report's datasource. That report expects those tables and I have defined table-relationships in the report which process and display the information correctly.
I'm not having as much luck with RDLC.
I can go into futher detail about how I'm creating the report, but let me just ask these general questions first:
1. Can I set a dataset containing multiple datatables equal to a property on an RDLC report and that RDLC report know how to treat and display the data?
2. Is there a better/smarter than this to get a field selection from my datasource (remember, this information is coming from a stored procedure so connecting directly to the database is not an option):
a. In code, populate dataset
b. In code, write dataset schema to xml (e.g. an xsd file)
c. In Visual Studio, add the XSD file to project
d. Use fields from XSD file to drag and drop fields on report
A fancy example would be nice too. I've googled like crazy the past couple of days and downloaded as many samples as I can find (including the ones on ftponline.com, gotreportviewer.com, "Tudor's WebLog", and several others). However, I have yet to find one that uses grouping and related datatables.
Thank you so much (if, for nothing else, reading this post )
View 4 Replies
View Related
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
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
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
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
Jun 14, 2006
Dear Friends,
In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Please advice. Thanks.
warm regards
Rakin
Singapore.
View 6 Replies
View Related
Oct 12, 2007
Is there any way to display this information in the report?
Thanks
View 3 Replies
View Related
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
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
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
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
Apr 5, 2007
Hi every body...
I have a probleme
I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime))
I need to call this method. But my problem is how pass parameter ?
I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example...
now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression :
=First(Fields!idEq.Value, "EquipmentDataSet")
but when i execute the query, the promter display <NULL>...
So I don't know how to do and if it is possible !
I hope someone can help me !
Thank you !
View 3 Replies
View Related
Dec 3, 2007
Hi experts,
I'm not sure my design is normal or not. Please give me some advice.
I've a dataset and query by a field name 'companyid'.
select * from companyid where companyid = @icompany which @icompany is a input field.
if user select all, i'll send 0 to @icompany then I need to select all records.
question 1. How can I get all records? (i think about this query select * from companyid <> 0)
if user select for example companyid = 1, i'll send 1 to @icompany and the query work fine.
question 2. How can I change the query to adopt this 2 condition?
Thanks a lot,
Jeff
View 8 Replies
View Related
Aug 10, 2015
Here is my requirement, How to handle using SSIS.
My flatfile will have multiple columns like :
ID key1 key2 key3 key 4
I have SP which accept 3 parameters ID, Key, Date
NOTE: Key is the coulm name from the Excel. So my sp call look like
sp_insert ID, Key1, date
sp_insert ID, Key2,date
sp_insert ID, Key3,date
View 7 Replies
View Related
