How Do I Get ONE Table Trying To Access TWO Datasets/Dataset Fields
Oct 26, 2007
Hi
I'm all very new to SQL Reporting Services so I am hoping that someone will be able to help me.
I have two datsets. Both contain the same array of information pertaining to a particular site. For example, how much sales we had, how much revenue was made, how much commission was created at the end of each day, the usual kind of metrics. We have a stored procedure which takes a final total and puts into a table we can access by date.
I want to produce a report that can compare the data from two dates. I use two datasets to run the quries that will return the relevant data. The only thing that differs between the dataset is the date that the data is based on. So to say first dataset will have data on one date and the second dataset will have data on a another date.
I was trying to make a table where I could include fields from both datasets, mainly for making comparison easier.
so id have
columns
online hits (dateone) | online hits (datetwo)
Rows
Data date1 | Data date2
and so on.
in fact i had this as the data from the first dataset in an example field:
=Fields!OnlineSales.Value
but i couldnt get the second dataset to work even if i tried entering :
=(Fields!OfflineBookings.Value, "SecondDatePicker")
I can't get the table to include field results from the second dataset as a table can only be linked to one dataset.
How can I get round this little problem?
View 1 Replies
ADVERTISEMENT
Feb 9, 2007
Hi
I'm basically new using reporting services, I hope someone can help me
I'm using two datasets in the same table this is because I that was the only way to obtain the data that I need, my problem is that when I try to use the folowing statment =Fields!Total.Value/Fields!Total2.Value,"seconddataset" I got an error because the scope, the expression edtior give me the Sum(Fields!Total2.Value,"seconddataset") wich it works but that is not what I want, I need to divide by the single value not the total Sum
the table is using the firsdataset but I need to use some fields from the seconddataset
I hope someone can help me
thanks in advance
View 3 Replies
View Related
Oct 12, 2007
Is there any way to display this information in the report?
Thanks
View 3 Replies
View Related
Jun 18, 2008
I am working on creating a report which is retrieving data from a SQL 2005 database and being displayed in a C#.net web page. Presently, I am binding the dataset to a ASP:Gridview on the web page.
I am currently retrieving most of the needed fields using the following SQL statement:PROCEDURE [dbo].[pr_getReportTickets] @DateCreated nvarchar(15) ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure hereSELECT tbl_Queue.num_TicketNumber AS TicketID , tbl_Users.str_Name AS Technician , tbl_Queue.str_QueueLocator AS TechNTID , tbl_Queue.dat_ReceivedRequest AS dateCreated , tbl_Queue.dat_DueDate AS DueDate , tbl_Queue.str_TaskName AS TicketTitle , tbl_Queue.str_Requestor AS RequestorNTID --, I need most current dat_TimeStamp from tbl_Notes here for this record --, I need most current int_PercentComplete from tbl_Notes here for this record --, I need to sum up all of int_MinutesWorked fields from tbl_Notes here for this record , MasterEmp.dbo.fn_FormatFullName(tbl_employee.str_fname, tbl_employee.str_lname) AS RequestorName , tbl_Queue.str_TicketType AS TicketType , tbl_Status.str_TaskStatus AS TicketStatus , tbl_Severity.str_Priority , tbl_Complexity.str_Complexity , tbl_Severity.str_Priority + N' / ' + tbl_Complexity.str_Complexity AS Priority , tbl_Queue.dat_CompleteDate as DateCompleted
FROM dbo.tbl_Queue LEFT OUTER JOIN MasterEmp.dbo.tbl_employee AS tbl_employee ON dbo.tbl_Queue.str_Requestor = tbl_employee.str_ntid LEFT OUTER JOIN tbl_Users ON tbl_Queue.str_QueueLocator = tbl_Users.str_ntid LEFT OUTER JOIN tbl_Status ON tbl_Queue.num_Status_CD = tbl_Status.num_Status_CD LEFT OUTER JOIN tbl_Severity ON tbl_Queue.str_Severity = tbl_Severity.str_Severity LEFT OUTER JOIN tbl_Complexity ON tbl_Queue.int_ComplexID = tbl_Complexity.int_ComplexID
WHERE (dbo.tbl_Queue.dat_ReceivedRequest > CONVERT(DATETIME, @DateCreated, 102))
ORDER BY TicketType, tbl_Queue.str_Severity
I also have another table called tbl_Notes. This table contains an unlimited quantity of records for every “num_TicketNumber�. This table contains the following fields: num_TicketNumber, str_TechRep, str_Notes, dat_TimeStamp, int_PercentComplete and int_MinutesWorked
I need to add two more fields to the query, but I do not know how to tell the SQL statement how to retrieve the data for the specfic record and inbed the fields into the Dataset being returned from the database to the web page.1) The first field I need is the most current recorded Date field from the tbl_Notes table for the each of the records returned in the above SQL statement. Similar to: SELECT TOP (1) dat_TimeStamp FROM tbl_Notes WHERE(num_TicketNumber = xxxx) ORDER BY dat_TimeStamp DESC2) The other field I need to return with the Dataset is the sum of the int_MinutesWorked for each of the tickets being retrieved. Similar to: SELECT SUM(int_MinutesWorked) AS TotalMinutesFROM tbl_Notes WHERE (num_TicketNumber = 49)
View 4 Replies
View Related
Mar 16, 2006
Need syntax for selecting fields from 2 different datasets in one report.
Each dataset connects to a different server.
View 3 Replies
View Related
Aug 24, 2007
I have an coloumn in the report whose Expression defines a division and the numerator is from dataset1 and denominator is from another dataset2
I get an error when I try to do this. Its says that denominator is out of scope of the dataset1
View 4 Replies
View Related
Jan 9, 2007
Hello,
How can I solve this problem.
For example, I have two databases. db1 and db2.
I want to compare two float fields from tables from db1 and db2 and the present the difference between these fields in a column in reporting services.
ex. loop1
value from db1.table_db1
4
value from db2.table_db2
6
diff
2
ex. loop2
value from db1.table_db1
2
value from db2.table_db2
2
diff
0
..continues...
Should I create 2 datasets and 2 datasources? Must I write custom code for this?
Regards
View 2 Replies
View Related
Aug 22, 2006
Dear ppl,
I have got 2 datasets D1 & D2 in a Report. How can i differentiate between the fields of these two.
e.g. I got Name field in both the datasets. So when i do =Fields!Name.Value in a textbox the report gives me error
How can i tell the report from which dataset to pick up the field ??
Regards
Nabeel
View 9 Replies
View Related
Jun 25, 2006
I am trying to access the fields in a table using Visual Basic in Web Developer .NET
View 1 Replies
View Related
May 22, 2015
I am not sure if this is possible or not, we have two datasets and each one has an additional calculated field which breaks the value JobBilledExVAT into three years.
So it reports for example how much we billed in May 2015, 2014 and 2013.
I have got the table to display this when reporting on one dataset by grouping by year
The datasets both look similar to thisÂ
SELECT
 Practice.ibvSalesByJob.JobBilledExVAT
 ,Practice.ibvSalesByJob.[Year]
FROM
 Practice.ibvSalesByJob
 INNER JOIN Practice.idvJobType
[Code] ...
The calculated field isÂ
=Switch(Fields!Year.Value = Parameters!Year.Value ,"15", Fields!Year.Value = Parameters!Pre1Year.Value ,"14", Fields!Year.Value = Parameters!Pre2Year.Value ,"13")
The field I am looking to add together is below
=Sum(Fields!JobBilledExVAT.Value)
If I do the below on the dataset associated with the matrix it displays the sum for each year correctly, I would however like to add the Sum(Fields!JobBilledExVAT.Value) from a different dataset (ABS1M) for the relevant year.
I tried this
=Sum(Fields!JobBilledExVAT.Value) + Sum(Fields!JobBilledExVAT.Value, "ABS1M")
However this adds 2015, 14 and 13 from ABS1M and doesn't add them individually if that makes sense? With the calculated field it only shows Year under the main dataset heading not 15, 14 and 13 separately.
I was think of using LOOKUP however I don't know how this would work as 15, 14 and 13 are not displayed under the dataset.
View 5 Replies
View Related
Mar 27, 2008
Hi,
I've a stored procedure which returns different number of fields depending on the input parameter.
create proc spSample (@rtnValue bit)
as
if @rtnValue = 0
begin
select field1, field2 from tablename
end
else
begin
select field1, field2, field3, field4 from tablename
end
Have a report execute this stored procedure, set the parameter value = True. Test the query, it returns all the fields as expected. However, on the fields tab of Dataset it always shows field1, and field2 no matter what; I manually add field3 and field4 under Report Datasets tree and they disappear after each execution. Anyway, I'm able to set the report fields as long as I don't refresh the query.
Is this the bug in SSRS? or am I missing something?
Thanks,
-Ash
View 1 Replies
View Related
Mar 9, 2008
Hi,
I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView.
Is it possible?
View 6 Replies
View Related
Dec 4, 2007
Hi.
I am currently working With SSRS . I have created a Store Procedure To show Data from different table in my Database,I have Used a temp table with #xxx Name,when i am creating DataSet for this Store procedure . but its not Showing the Fields of Store Procedure, but when i create other Store procedure without temp table with #xxx then its showing the fields in Data Set.
What Could be the possible Reason,
Thanks
View 8 Replies
View Related
Jan 3, 2008
Hello anyone!!!!
Anybody knows if exists some way to put a dataset field into the page footer???, or if not, tell me if there is a way to do a €œsimilar€? of footer, cuz I need put information in the bottom of the report, but just in the last page, and need to left the space of this information in the another pages.
Any suggest is welcome, tanks!!!!!
View 3 Replies
View Related
Jan 30, 2008
Hi All,
I have a Dataset. I use this dataset in my matrix for grouping based on the underlying table columns names. Users can add columns to the underlying table that the DataSet refers.
Now my problem is:
The data is grouped based on the column name selected from the parameter.
If the user adds a column to this table how will the dataset refresh so that it contains the newly added field for grouping data.
View 5 Replies
View Related
Mar 26, 2007
Hi,
I'm designing a report and have the requirement that the report title needs to be repeated on each page. However, the report title is dynamic, retrieved from the database, so i cannot place it in the page header (what an annoyance). The solution i first thought of was placing the title in a table, repeating the table header on each page, however the problem i'm encountering is that when i have a subreport in that table, that subreport is in one row
--> if the subreport spans across multiple pages, the header will only be shown on the first page the row where the subreport is actually in...
any thoughts anyone on a possible solution?
View 4 Replies
View Related
May 10, 2007
I have a question about loading data on the page lode event. The question is more conceptual then how to.
Using C#, I would like to make a call to a SQL 2000 Server, with the use of a stored procedure return one row with eleven fields. Then use the data to fill five different controls. There is no manipulation of the data it is just presented as information. With a DataReader and the GetValues method using the resultant array I can fill the controls (or can I), or with a DataSet and the Load method do the same thing. Now this code can be placed in the code behind page or it can be implemented in a class. This is the only page that uses this combination of fields and controls; however I would think a more generic call to the data could be made then pick and chose the fields as needed. What process makes the most sense? Is what I just described even possible (as yet I haven’t tried it)?
As always thanks in advance for any thoughts, comments, and suggestions.
View 1 Replies
View Related
May 7, 2008
Hi everyone
I am using Stored procedure :
ALTER PROCEDURE [dbo].[ReportChart]@Num int,@patID char(16) ASbegin if @Num=2 Begin select * from table1 where patientid=@patID End
else If @Num=1 Begin select * from table2 where patientid=@patID End end
While using the above stored procedure, when i bind it with Dataset. The fields corresponding to table1 are displayed in the Fields Tab of the Dataset whereas the Fields corresponding to table2 are not displayed.
Please help me out .
Thanks In Advance
Regards
Navdeep
View 20 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Sep 28, 2007
Hi !
I'm creating a report with SSRS (2005). In that report I have a table, and in that table I need to put fields from two different datasets. But I don't know how to link those both datasets (because if I don't I get a cartezien product)...
Could someone help me please ??
PS : Excuse my poor english please...
View 9 Replies
View Related
Jul 31, 2007
is this possible? i need to make a report that accesses two different tables, one for user activity and the other for the number of reports that user has done. the report information is in a different database however so i have to use different datasets. is there a way to use both databases in the same table? all i need is the one field from the reporting one and they can be linked by the user id...
View 1 Replies
View Related
Dec 7, 2007
Hey Everyone,
I am trying to create a table that will list data for me. The problem that I have is that my data has been separated across multiple stored procedures. The way I want to display the items are as follows:
title1 title2 title3 title4 title5 title6 title7
item1 item2 item3a item4 item5 item6a item7
item3b item6b
item3c item6c
As you can see, items3 & 6 contain significantly more rows than the others. In order to prevent duplicating items1, 2, 4, 5, and 7, I had to split the statement up into 3 stored procedures; all three procedures take parameters, item 3 and 6 taking item1 as a parameter. How will I display data like that in a report? In the design view, I am only able to use 1 dataset, let alone link them up together.
If this cannot be done, is there a query that I can perform that will omit repeated items? Basically, it will look exactly like about. I thought it was the GROUP BY, but I was way off.
View 9 Replies
View Related
Jan 18, 2008
Hi,
I need to display the datas in a table format. I have placed a table and now i need to use two different datasets for a single table. Those two different datasets are generated based on two different stored procedures. Those stored procedures retrieve data from two different sql tables. Can any one explain me how to use two different datasets for a single table. As far i have tried i was able to fetch only the first record of the other dataset. But i need to fetch all the records of both the dataset. Thanks in advance for any help.
Thanks,
Sangeethaa
View 11 Replies
View Related
Apr 24, 2007
i have 2 datasets in my report.
dataset1 brings back:
date shirts pants
4/1 3 5
4/2 4 6
4/3 2 9
dataset2 brings back:
date shoes boots
4/1 7 4
4/2 3 2
4/3 9 8
i want to to have a report that shows:
date shirts pants shoes boots
4/1 3 5 7 4
4/2 4 6 3 2
4/3 2 9 9 8
when i added a table in the layout tab, and drag in fields from the 2 datasets, for the dataset1, i would get fields like "=Fields!shirts.Value". but, when i drag in fields from dataset2, it shows up like "=Sum(Fields!shoes.Value, "dataset1")".
i don't want the sum, i just want the value. when i take out the "sum" part of the field expression, i get an error that says:
[rsFieldReference] The Value expression for the textbox €˜shoes_1€™ refers to the field €˜shoes€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
any advice?
thank you much :-)
View 7 Replies
View Related
Feb 11, 2008
How can I access a main dataset from a sub report from a main report using SSRS?
View 4 Replies
View Related
Mar 21, 2014
I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.
I have aSQL Fiddle that will allow you to visualize the schema.
The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.
select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)
[Code]...
View 2 Replies
View Related
May 7, 2007
Hi all,
I need to use a multi-table dataset as a datasource for my report items in a SRS2005 report.
But from designer, I can only see the first table.
Can any one tell me if it is possible to use multiple tables in a dataset for SRS report?
Thanks,
Samson
View 1 Replies
View Related
Feb 6, 2007
Data Set Access from a matrix
I am trying to create a schedule by room number report. I am using a matrix. The column group that I am using is room number. There is no row group. When you group by room it is only allowing me access to the first data item for that room. Even though there are other data items for that room I can not access them. I can not access all data items in that grouping expression. Is there a way to get unobstructed access to a dataset while working in a matrix and grouping?
View 1 Replies
View Related
Jun 21, 2006
Hi all...I've set the DataSourceMode = SqlDataSourceMode.DataSet, and did a .Type return and found that it is actually returning a DataView. A component I am trying to avoid rewriting....requires a dataset that loops through the table, does some cool formatting to a datagrid and then rebinds.Here's the code that I'm trying to send the dataset to....maybe there's just a couple of changes that could make it work with a DataView? int i = 0; string prevsub = ""; while (i <= ds.Tables[0].Rows.Count - 1) { DataRow dr = ds.Tables[0].Rows[i]; string sub = dr["SubHeading"].ToString(); if (sub != prevsub) { prevsub = sub; DataRow newrow = ds.Tables[0].NewRow(); newrow["Title"] = "SubHeading"; newrow[columnName] = dr[columnName]; ds.Tables[0].Rows.InsertAt(newrow, i); i++; } i++; }
View 2 Replies
View Related
Jan 2, 2008
Hi there!
For setting different languages on the column haeders i need to access a dataset from a custom code function call. I want to pass a parameter to the function that will allow me to find a specific item in the dataset - like a lookup function. I cannot find a way to get access to the entire dataset and to iterate through its components.
Is there any solution? Every hint will be helpful!
Thanks, Torsten
View 3 Replies
View Related
May 28, 2007
I have many MS Access reports that process recordsets obtained from a MySQL database, based on a user-selected date range. This uses VBA and input boxes. I'm now creating .aspx pages to get the user input via the web, and am successful in creating a DataSet. My .aspx.vb code includes using Automation to open the Access report in Snapshot Viewer (DoCmd.OutputTo). How do I pass the DataSet to MS Access to replace using recordsets?
My VBA code in Access used to be this:
Code Snippet
Dim ws As Workspace
Dim strConnection As String
Dim dbs As Database
Dim rst_chg As Recordset
Set ws = DBEngine.Workspaces(0)
strConnection = "ODBC;DSN=xxx;DATABASE=xxx;" _
& "SERVER=10.1.144.xxx;" _
& "UID=xxx;PWD=xxx;PORT=xxx;OPTION=0;" _
& "STMT=set wait_timeout=100000;;"
Set dbs = ws.OpenDatabase("", True, True, strConnection)
Set rst_chg = dbs.OpenRecordset("SELECT ...")
'process the recordset ...
I'm thinking I should be able to eliminate most of this code and Set rst_chg = DataSet. I've been successful with using WriteXml in the .aspx.vb page and Application.ImportXML in my VBA to pass the data using XML, but this writes to the hard drive, and also creates a database in Access (overhead I would rather not have to deal with). Again, is there a way to open the DataSet directly in my VBA code?
Thanks,
Guy Rivers
View 1 Replies
View Related
Jun 17, 2015
I want to use multiple datasets in a table and wants to do the full outer join on the two datasets in the same table. For example, my two datasets are:
I want to display a ssrs table like:

Both the datasets are coming from different sources. So I cannot integrate them at sql query level.
View 4 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
