SQL Server 2005 Reporting Svcs - How To Use Dataset 1 Info To Drive Nested Table Select
May 20, 2008
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know.
What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in.
This is the relationship of the data.
I have at the highest group, an industry code, then a customer, then the part and then the fiscal year.
What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks!
industry Customer Year OCT NOV DEC
001 - Signposts
M12345
Part 1
2006 5 6 2
2007 0 3 1
public DataSet EeoReport(DateTime start, DateTime end)
{}
i want to know how to send the date time parameters to this from RS using XML data source.
basically i need the <Query></Query> XML used to make this happen. So far i'v eonly been able to statically create parameters, but that does me no good, i want the users to be able to pick a date range from RS dynamically to get the report.
I have a remote report ... I need to call one of two stored procedures depending on which parameters were passed to the report. (Both stored procedures return the same fields.) Does SQL Reporting Services allow you to switch the dataset or stored procedure name dynamically? If so, where do I put the logic? Right now I have it working with one stored procedure. I'm thinking that I should just make one stored procedure that takes all parameters and calls one of the other two stored procedures. Do I have any options besides this?
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM ...this is where you come in ORDER BY TABLE_NAME
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM
We have 2005 Reporting Services fully functional - Dev, QA and prod (farm). I have at least 5 distinct business group trees in my structure, who have inherited or modified security based upon NT Users and AD groups. Now, we want to replace a major group (<domain_name>Domain Users) with a controlled group.
I cannot find anyway to report or map the folders that a specific NT User and AD group has rights. Cannot it be done?
I need ssrs report  which  get job info from job table in list control  and other  one is jobid and workdate which also need to be group by and storeproc I am using for this  report is as follow:
ALTER proc [dbo].[VRAJ_JobScheduleReport]              @JobID int = null,  @StartDate varchar(25) = null,  @EndDate varchar(25) = nullASSET NOCOUNT ON SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON DECLARE @sql varchar(max);DECLARE @param varchar(max);DECLARE @where1 varchar(max);DECLARE @where2 varchar(max);SET @param='';SET @where1='';SET @where2='';IF @JobID IS NOT NULL  BEGIN   SET @param = @param + 'DECLARE @JobID int; SET @JobID=' + CAST(@JobID AS varchar(50)) + ';';   IF @where1 = ''    [Code] ...
I need ssrs report which get job info from job table in list control and other one is jobid and workdate which also need to be group by and storeproc I am using for this report is as follow:
ALTER proc [dbo].[VRAJ_JobScheduleReport] @JobID int = null, @StartDate varchar(25) = null, @EndDate varchar(25) = null AS SET NOCOUNT ON
Am new to sql, and I wold appreciate help with optimising the folloing example. The result of the example should be to list a result with details of the Column names:
OPBal| Receipt| IssTrns| Transfer| ClBal
SELECT dbo.inventory.location, dbo.inventory.itemnum, (select sum(dbo.matrectrans.linecost) where dbo.matrectrans.issuetype LIKE 'RECEIPT' ) As Receipt, ( select sum(dbo.matrectrans.linecost)where dbo.matrectrans.issuetype LIKE 'TRANSFER' ) As Transfer, ( select(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost)where dbo.IST_ITEMDETAIL.logdate='2006-07-20' ) As OpBal, ( select (IST_ITEMDETAIL.curbal*IST_ITEMDETAIL.avgcost)where IST_ITEMDETAIL.logdate='2006-08-21' ) As ClBal, ( sum(matusetrans.linecost) ) As IssTrnf FROM dbo.matrectrans, dbo.matusetrans, dbo.IST_ITEMDETAIL , ( dbo.inventory inner JOIN dbo.item ON dbo.inventory.itemnum = dbo.item.itemnum AND dbo.inventory.orgid = dbo.item.orgid )
WHERE dbo.inventory.location = dbo.matusetrans.storeloc AND dbo.inventory.itemnum = dbo.matrectrans.itemnum AND dbo.inventory.siteid = dbo.matrectrans.siteid
OR dbo.inventory.location = dbo.matrectrans.tostoreloc AND dbo.inventory.itemnum = dbo.matusetrans.itemnum AND dbo.inventory.siteid = dbo.matusetrans.siteid OR dbo.inventory.location = dbo.matrectrans.fromstoreloc
OR dbo.inventory.location = dbo.ist_itemdetail.location AND dbo.inventory.itemnum = dbo.ist_itemdetail.itemnum GROUP BY dbo.inventory.location, dbo.inventory.itemnum,dbo.matrectrans.issuetype,(dbo.IST_ITEMDETAIL.curbal*dbo.IST_ITEMDETAIL.avgcost), dbo.IST_ITEMDETAIL.logdate,dbo.IST_ITEMDETAIL.curbal,dbo.IST_ITEMDETAIL.avgcost
hi all, i have speed issue on displaying 4k line of records using temp table.. before this it works fine and fast.. but maybe when i starts joining group by it loads slower.
SELECT DISTINCT customlotno, itemid, ItemName, Ownership, TotalCTNInPlt, TotalCarton, sum(CartonPcs) AS CartonPcs, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate INTO #ByItemID FROM ( SELECT * FROM tblItemdetail )AS L1 GROUP BY customlotno, itemid, ItemName, ownership, TotalCTNInPlt, TotalCarton, StorageID, StorageStatus ,OriginUOM, PickQtyUOM, WhsID, WhsName, LocID, Zone, Expirydate, recvDate
SELECT * FROM #ByItemID ORDER BY CustomLotNo
DROP TABLE #ByItemID
---------------------------- or maybe just use something like nested SELECT like this, but cannot work:-
select customlotno, itemid, locid(
select * from tblitemdetail where customlotno='IN28606000'
I have a database running in an instance of SQL Server.
I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.
PROBLEM:
I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:
Microsoft Visual Studio is Busy.
Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.
Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.
Hopefully someone out there can help.
Additional Services running:
IIS (Whatever version comes with Windows XP Pro. I think 5.1)
SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch
UPDATE: I am editing this post with an update.
I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
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...
This is day 1 of me ever using RS so forgive ignorance of the topic.
Here's the basic scenario:
Input parameter from the user is an Account name.
For that account name I want to list Addresses of locations under the account, and then for each of those, I want to list received shipments from the address.
So Ideally the result is:
Account Name
Address 1
Shipment 1 Shipment 2 Shipment 3
Address 2
Shipment 1 Shipment 2
Address 3
Shipment 1
So, I am able to list the addresses just fine, but the next step is getting the stuff from the shipments table based on the currently running address? I'm probably saying that poorly. What I need to know is how do I structure the dataset/report to accomplish this. I suppose I could combine the query for the shipments with the query for the addresses, but that seems suboptimal to me.
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.
I need to have a report with summary and details. The summray list is 'EmpolyeeID' and 'Name'. The details are 'emails' and 'phone numbers'. One Employee could have one or more emails, and one or more phone numbers.
The dataset are 1. select EmployeeID, Name from Empolyee 2. select EmployeeID, email from Emails where EmployeeID = @ID 3. select EmployeeID, PhoneNumber from PhoneNumbers where EmployeeID = @ID
I'd like to have one summary list with dataset 1. The list contains two nested lists, one for email, the other for phone numbers.
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
Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.
and what is the table/views name that store this info.
for SQL 2000 , we were using following query:
SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v," "master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 " "and v.number = r.config and v.name ='affinity mask'");
I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
Why wont the installer for SQL Server let me install onto a drive other than the C drive? I am trying to install the Client tools only, but it insists on making me install to C. I need to install to a different drive for space reasons. I had SQL Server 2000 client installed, but I have since uninstalled it and tried the SQL 2005 install again and every time it wants to use the C drive.
If anyone knows a why to make this work I would greatly appreciate it.
I created two partitions for my hard drive. The C drive which holds all of the Windows 2003 server operating system files and E drive which will be just for data and applications. The C drive is only 30GB while the E drive is 119GB. I need to be able to install in the application on the E drive. I already tried creating a folder on the E partition named program files with the sql2005 folder in it when I go through the installer package. SQL server still wants to be installed on the C drive. Is there anyway I can change this so it gets installed on the E partition. Thank You
I am trying to install the SQL Sever 2005 Workgroup Edition software that came with our Small Business Server 2003 R2 system. I would like to install to the d: drive because space on the c: drive is limited. Can I do that? If so, how?
I created two partitions for my hard drive. The C drive which holds all of the Windows 2003 server operating system files and E drive which will be just for data and applications. The C drive is only 30GB while the E drive is 119GB. I need to be able to install in the application on the E drive. I already tried creating a folder on the E partition named program files with the sql2005 folder in it when I go through the installer package. SQL server still wants to be installed on the C drive. Is there anyway I can change this so it gets installed on the E partition. Thank You
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
Trying to install SQL Server 2005 beta version on partition disk drive sayF: (Other than local disk drive c:),Does anyone know how can I do this? During the installation, it never asksme on which drive it install to nor browse button is there to browse thedisk drive for installation...Any help???ThanksJ.
I am trying to install SQL Server 2005 Express Edition onto my PC on the D: drive. I do not have write privelleges to the C Drive. All I want to do is point the executable to install to another drive like D: or E: etc. Is this possible with SQL Server 2005 Express Edition setup executable file.
Just to let you know I have changed the template.ini file in the source folder to reflect the following (I tried both with and without quotes)
What is the equivalent for Oracle's nested table concept in SQL Server ? Is there anything like TABLE( ) function to select from nested table as in Oracle ?
Eg in Oracle :
SELECT t.* FROM TABLE(nested_table_datatype) t;
( like the above query used in Oracle PL/SQL and 'nested_table_datatype' is a table datatype created in Oracle using 'create type ...' syntax )
=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.
When I'm using SQL server 2005, and ODBC drive €œSQL Native Client€? I get an error when executing the below given code. Error Message: €œTransaction cannot start because more than one ODBC connection is in use.€?
But this works well with SQL Server 2000 with ODBC drive "SQL Native Client" and in SQL server 2005 also when I used ODBC drive €œSQL Server€?.
I used aTrgConn.Execute(€œBegin Transaction€?) and it's working properly. But unfortunately I cannot use this method, because I want to run the same code with Oracle ODBC as well. Is this a known issue in SQL Native client drive with SQL server 2005? Do you know a solution for this?
Please Help..............
Thanks in advance.
Code:
Dim aTrgConn As New ADODB.connection Dim aCmdIns As New ADODB.Command Dim aCmdUpd As New ADODB.Command
aTrgConn.Open sConnStr
Set aCmdIns.ActiveConnection = aTrgConn Set aCmdUpd.ActiveConnection = aTrgConn
