Query Detail As A Field.
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like:
Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,....
From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID
Where Order.OrderID=10
so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...."
How can I write this query in T-SQL?
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
SQL Master/Detail (Master Query Based On Detail Values)
Hello, I'm new to SQL and need help with a query. Not sure if this is the right place. I have 2 tables, one MASTER and one DETAIL. The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns. I want to return a populated MASTER table with entries based on the DETAIL.value. SELECT MASTER.* FROM MASTER WHERE DETAIL.value > 3 This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry. Hope this makes sense. How can I do this? GrkEngineer
View Replies !
Selecting Detail Based On A Sum Of The Detail Lines
I am listing detail transaction lines in a table sorted by account and order number. the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order. I was trying Group by and Having but this doesn't seem to do what I need. Being relatively new to Reporting services, any nudge in the right direction would be useful.
View Replies !
Query That Accumulate Detail Values
How would be a query that read detailed sales from several dates and accumulated them in a table that has months as fields MSSQL 2005 Table : DETAIL Customer_Date___________Value 1________01-Jan-2008____1,234.00 2________01-Feb-2008____3,456.00 3________01-Mar-2008______500.00 4________01-Apr-2008____1,567.00 1________01-Apr-2008____2,123.00 Table : TOTAL Year__Customer_Jan______Feb______Mar_____Apr ... 2008__1________1,234.00_0________0_______2,123.00 2008__2________0________3,456.00_0_______0 2008__3________0________0________500.00__0 2008__4________0________0________0_______1,567.00 Thanks for your help JG
View Replies !
How To Query For Master-detail Output On Same Page
I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below. OrderNo OrderDate 1001 7/27/2007 + <div to show/hide> Item Description AAA desc_for_itemAAA BBB desc_for_itemBBB + </div> 1002 7/26/2007 + <div to show/hide> Item Description CCC desc_for_itemCCC + </div> I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?
View Replies !
Tricky Query For Joing Master-detail Tables
I need to write a sql query which is a master-detail query. Here's the example structure of tables: Master table: ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint Child table -- Wheel: ColID as longint, ColA as int, ColB as int Child table -- Door: ColID as longint, ColA as int, ColB as int Child table -- Window: ColID as longint, ColA as int, ColB as int ..... etc From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName). My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please. Many thanks, Leonard
View Replies !
SQL Edge 2.0 With Editable Query Result Window And Master-detail Views
SQL Edge™ is a SQL query tool that allows users to execute SQL queries, browse schema information in ER diagrams, and visualize data relationships. With SQL Edge™, users can use only one query to retrieve data in a master table, and then browse data in all related detail tables. Users can also insert, edit, and delete table records in the data grids. Please refer to http://www.baybreezesoft.com for product details. SQL Edge™ has six perspectives. The "Query Perspective" allows users to run any SQL queries and scripts. The query result window is editable if all selected columns are from one table and also include the primary key of the table. There are two relationship perspectives. The "Relationship Table View" displays data in the master-detail style. The "Relationship Tree View" displays master table records as top-level tree nodes. The nodes can be expanded to reveal any level of related detail records. In addition, clicking any node will display the corresponding data in a detail grid. The "Model Perspective" displays tables and their relationships in ER diagrams. The tables can be from different databases. Users can define master-detail relationships among these tables, and print or save the diagram for future reference. The "Execution Plan Perspective" displays the execution plan of a query. It supports Oracle and PostgreSQL execution plans. The following list the major features: (1) Support Oracle, MySQL, Microsoft SQL Server, PostgreSQL and any JDBC compatible databases. (2) Allow users to specify JDBC drivers at run-time, and provide wizards to help setup JDBC connections. Easy to install, and easy to setup. (3) SQL editor supports syntax highlighting and SQL statement formatting. (4) SQL edtior allows users to execute SQL scripts. The query result window is editable if all selected columns are from one table and also include the primary key of the table. (5) Schema perspective displays the table schema information, including column definitions, primary key, indexes, and foreign keys. (6) Relationship table view displays the related data in the master-detail style, with master table data shown in the top grid, and all related detail table data in a list of bottom grids. (7) Relationship tree view displays the master table records as top-level tree nodes, and the related detail records as child nodes. Click any of these tree nodes will display the corresponding record data in a detail grid. (8) Model perspective displays tables and their relationships in ER diagrams. The tables can be from different databases. Users can define master-detail relationships among these tables, and print or save the diagram for future reference. (9) Execution plan perspective displays the execution plan of a query. It supports Oracle and PostgreSQL execution plans.
View Replies !
Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column
I have detail table like date item_id grade in out ------------- --------------- ------- -------- ---------- 01-01-08 001 A 10 0 02-01-08 001 O 8 0 01-02-08 002 O 1 0 03-01-08 001 T 0 10 02-01-08 003 O 20 0 02-01-08 003 T 0 10 02-01-08 003 B 0 8 Result View ======= Item_id A B O T Total 001 10 0 8 -10 8 002 0 0 1 0 1 003 0 8 20 -10 18 where grade could be any Alphabet, the column of result query could varies each grade contain sum of (in-out) of all item_id of detail table. Smartsys
View Replies !
Getting Job Detail
I am trying to create a function that will run when a job step fails. I want it to dynamically capture the name of the job and insert it into a table that I will use to send email alerts for. The reason I need to do this as opposed to sending an email from the job is that if the alert is not responded to after so many minutes it can be escalated to the next operator. Does anyone know how to dynamically capture the name of the current job being run. thanks Tom
View Replies !
Process Detail (T-SQL)
Hello there :-) Just want know where can we find the last T-SQL being executed on a process. This is the one that pops-up when we double-clik a process on the Process Info List. Thank You in Advance :-)
View Replies !
Invoice Detail
Hi I have a invoice that displays the company name in a rectangle on top then it has a list in the list it has details tables my problem is that the invoice detail goes on the next page I need to have the company name as well on the second page I cant put it in the same table as the detail because I have a few tables in the detail so it is actually no header is it possible to have Repeated the Company name information on the next page? Thanks
View Replies !
Master With Two Detail Views
Hello community,I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table) SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] = @id)"For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this: SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @person_id)" .... <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="person_id" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters>But now the problem: how should I declare a parameter @task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks: SelectCommand="SELECT [task_id], [task_name], [task_date], [task_description] FROM [TabTasks] WHERE ([task_id] = @task_id)"@task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.Thank you in advance for your help
View Replies !
Count From Detail Table
HiI've two tablesTableAidnameTableBidtableA_iddescoperAs you can see tableA is a master and tableB is a detail table wherewe can have many records for each related tableA record.I need to get all records for tableA with a count on some oper oftableB.I suppose I can got it with a join or a subselect but I don't use SQLoften so I'm getting crazy with this stupid query...Could somebody help ?Thanks in advanceC
View Replies !
Get One Row From Detail/child Table
Hi,-- ddl/*create table #tmp (col1 int);insert into #tmp values(1);insert into #tmp values(2);insert into #tmp values(3);create table #tmpChild (col1 int, fkCol int, Num int);insert into #tmpChild values(1,1,3);insert into #tmpChild values(2,1,2);insert into #tmpChild values(3,2,1);*/-- get parent and child data (outer join)select *from #tmp t Left Outer JOIN #tmpChild tCON t.col1 = tC.fkCol-- resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 31 2 1 22 3 2 13 NULL NULL NULL(4 row(s) affected)*/-- desired resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 3-- eleminate next row, want only Max(Num) row from the child tbl withsame FK (parentID)-- 1 2 1 22 3 2 13 NULL NULL NULL*/In other words, desired results is as follows:/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 32 3 2 13 NULL NULL NULL*/How to accomplish this task? ENV: MS SQL Server 2000TIA
View Replies !
Header And Detail Records From DTS
I am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be... HEADER DETAIL HEADER DETAIL HEADER DETAIL I am a little confused about how I can stagger header and detail using the same data. I appreciate any help you can give. Hopefully my explanation of the problem is understandable. Thanks, Val
View Replies !
Header And Detail Correlation
HELP! I don't have much more hair to loose. I have a text file that needs to be imported. The file may have 1 to 1000 inovice records with detail lines. However, the detail lines do not hold the invoice number, but the header line does. And of course, the detail can vary from one invoice to another. one can have 2 items, the second can have 5 items. Line 1 is the header line (line type, customer, event ect.) Line 2 is the detail line (line type, item, amount) I,12345,55555 D,abc,50.00 D,def,25.00 And so it goes. How can I get the detail for the appropriate invoice to match up with the header. I would like to use the customer number because the customer will only have 1 invoice per upload. I imported the file into a table in SQL which has an identity column. Help. I am lost with this one. Thanks
View Replies !
Transaction Log Detail Viewing
I have a client who has a database that they have recently moved to another server. The problem is that the transaction log of the previous version of the database is taking over 21GB of space on the old server. All of the transactions are listed with a status of 2 in the log file. Thus I can not simply shrink the file. Is there a utility I can use to view the details of the records in the transaction log? I would also need to ensure the data is committed to the database and then change the status so that the transaction log can be shrunk. This older database will be kept for historical reasons. During the change to the other server only the structure was moved so the data is not in that database.
View Replies !
Master-&&>Detail Reporting
I'd like to put together a summary report for some of my management staff. The report should show the # of jobs that match about 20 different statuses. Each status has different criteria. For example, one might look for a date in a datetime field and the type of job. Another might look at whether a date has passed and the quantity we're shipping. Sort of like this: Status Items Jobs Status #1: 50000 15 Status #2: 25251 3 I want the user to see this summary information, but also have drill down capability. I'd like the ability to print the summary or just a drill-down. I'm also considering using Dundas Charts for RS on the report. Can I accomplish this with Reporting Services? If so, any tips on how to do it? If this a bunch of sub-reports? Can I sum the # of jobs on a sub-report? Should I be looking at BI for this? Or should I be working on a Forms-based? Thanks! Brian
View Replies !
Charting Table Detail And Sum
I have a bar chart that displays the detail of a table with locations along the x-axis and number of employees on the y-axis. Is there any way to have the sum of all of the locations show as the final bar in the chart? (which would be the footer in the table)
View Replies !
Detail Record Header
Hi, I'm new to SSRS. I was just wondering how do I make the header for a detail record appear once per grouping rather than once per detail record? Thanks.
View Replies !
Keep Detail Rows In The Same Page
I am using SSRS 2005. Is there a way not to seperate details Row 1 and details Row 2 (within a same record) into 2 pages? I just want to keep them in the same page. I tried table KeepTogether but doesn't work. Thanks.
View Replies !
Detail And Summary Sub Reports
Hello, I am trying to create two subreports in the main report. One sub report should give detail data and other sub report shuold give sumamry data. Is detail and summary reports are possible in sub reports? Iappreciate your help on this. Thanks,
View Replies !
Up To 2nd And 3rd Level Detail + Activities
So, the main objective for this database/web application is reporting. The issue/situation is: 1. The customer has 10 goals 2. Each goal has at least some detail shown in bullet points via a word document...this is how we'll get the data at first. we're going to develop the UI of the web application after the customer has more specs...main objective, get web reporting! 3. Each detail could be broken down into a further / 2nd level of detail 4. The 2nd level of detail could be further detailed by a 3rd level ...and, not knowing how in depth they'll provide the detail, there will be activities associated to points 2, 3, or 4...depending on how deep/specific the detail. So, you might have one goal of "build a fence". And this goal only goes to the first detail level of say, "make it stucco" and that's it...no other detail. A second goal might be, "landscape the front yard", and it's broken down into further levels of 2nd and 3rd: 1. Make stepping stones **Three 2nd levels of: --1. Make path curvy --2. Use flagstone --3. Lead up to front door 2. Plant trees **Three 2nd levels of: --1. Use 5 Aspens --2. Use 2 dogwoods --3. Use 1 maple 3. Fill in bare spots **Two 2nd levels of: --1. Use bermuda *****example of 3rd level --2. Ensure dirt is fertilized ----1. Use Miracle grow ----2. Must be via hose, not granular Now the tricky thing I'm stuck on is that be it the 1st level of detail, the 2nd, or the 3rd level, each could have activities. So, if the detail stops at the 1st level, activities will be tied to the 1st level. If detail stops at the 2nd level, activities will be tied to the 2nd level...and so on. I was wondering about a detail table coming off the 10 goal/master table which would have columns of: 1. Goal_ID 2. Detail_Level_ID 3. Detail_Level_Desc 4. 2ndLvlDetail_ID 5. 2ndLvlDetail_Desc 6. 3rdLvlDetail_ID 7. 3rdLvlDetail_Desc What I'm wondering is how do I link the activities? Should I just have a 3rd table, "activities", and it would have: 1. ActivityID 2. ActivityDesc 3. LinkID ...and LinkID could be either tied to Detail_Level_ID, 2ndLvlDetail_ID, or 3rdLvlDetail_ID This way I'm not too sure about referential integrity via the DB diagram to cascade deletes & updates because Detail_Lvl_ID could be non-unique. Another option would be to have 4 tables: 1. 10 goal main table 2. 1st level of detail table 3. 2nd level of detail table (links to 1st level) 4. 3rd level of detail table (links to 2nd level) ..and the activites table would now have to be split into three different, i.e. DetailAct, 2ndDetailAct, 3rdDetailAct. Right? Any suggestions are welcome. Like I said, reporting is the main concern at first, and the customer wants to be able to either show/hide activities, so sub-reporting to the detail, 2nd lvl, 3rd level for the activities shouldn't be too bad. Thanks!
View Replies !
Linking Two Tables In A Detail View
I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int. Vic Valentic CEO/President Open Door 2 Elite Dr. #33 Hamilton, Ontario L8W 2N3 905-389-7492 http://www.wlu.ca/next/opendoor
View Replies !
How To Copy Detail Records To Another Header
Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11). So far I have two stored procedures: 1) sp_copyDetailsOne /*Create a recordset of the desired items to be copied*/ CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS SELECT itemId, itemPrice, itemDescription, itemQuantity FROM tblSalesItems WHERE (invoiceId = @invoiceIdFrom) OPEN @outCrsr 2) sp_copyDetailsTwo CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT /*Allocate a cursor variable*/ DECLARE @crsrVar CURSOR /*Execute sp_copyDetailsOne to instantiate @crsrVar*/ EXEC sp_copyDetailsOne @invoiceIdFrom, @outCrsr = @crsrVar OUTPUT /*Loop through the recordset and insert a new record using the new invoiceId*/ FETCH NEXT FROM @crsrVar WHILE @@FETCH_STATUS = 0 BEGIN /*Run the insert here*/ INSERT INTO tblSalesItems (invoiceId, itemId, itemPrice, itemDescription, itemQuantity) VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30) /*Fetch next record from cursor*/ FETCH NEXT FROM @crsrVar END CLOSE @crsrVar DEALLOCATE @crsrVar My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome. Thanks
View Replies !
Stuck On 2nd And 3rd Level Detail Design
So, the main objective for this database/web application is reporting. The issue/situation is: 1. The customer has 10 goals 2. Each goal has at least some detail shown in bullet points via a word document...this is how we'll get the data at first. we're going to develop the UI of the web application after the customer has more specs...main objective, get web reporting! 3. Each detail could be broken down into a further / 2nd level of detail 4. The 2nd level of detail could be further detailed by a 3rd level ...and, not knowing how in depth they'll provide the detail, there will be activities associated to points 2, 3, or 4...depending on how deep/specific the detail. So, you might have one goal of "build a fence". And this goal only goes to the first detail level of say, "make it stucco" and that's it...no other detail. A second goal might be, "landscape the front yard", and it's broken down into further levels of 2nd and 3rd: 1. Make stepping stones **Three 2nd levels of: --1. Make path curvy --2. Use flagstone --3. Lead up to front door 2. Plant trees **Three 2nd levels of: --1. Use 5 Aspens --2. Use 2 dogwoods --3. Use 1 maple 3. Fill in bare spots **Two 2nd levels of: --1. Use bermuda *****example of 3rd level --2. Ensure dirt is fertilized ----1. Use Miracle grow ----2. Must be via hose, not granular Now the tricky thing I'm stuck on is that be it the 1st level of detail, the 2nd, or the 3rd level, each could have activities. So, if the detail stops at the 1st level, activities will be tied to the 1st level. If detail stops at the 2nd level, activities will be tied to the 2nd level...and so on. I was wondering about a detail table coming off the 10 goal/master table which would have columns of: 1. Goal_ID 2. Detail_Level_ID 3. Detail_Level_Desc 4. 2ndLvlDetail_ID 5. 2ndLvlDetail_Desc 6. 3rdLvlDetail_ID 7. 3rdLvlDetail_Desc What I'm wondering is how do I link the activities? Should I just have a 3rd table, "activities", and it would have: 1. ActivityID 2. ActivityDesc 3. LinkID ...and LinkID could be either tied to Detail_Level_ID, 2ndLvlDetail_ID, or 3rdLvlDetail_ID This way I'm not too sure about referential integrity via the DB diagram to cascade deletes & updates because Detail_Lvl_ID could be non-unique. Another option would be to have 4 tables: 1. 10 goal main table 2. 1st level of detail table 3. 2nd level of detail table (links to 1st level) 4. 3rd level of detail table (links to 2nd level) ..and the activites table would now have to be split into three different, i.e. DetailAct, 2ndDetailAct, 3rdDetailAct. Right? Any suggestions are welcome. Like I said, reporting is the main concern at first, and the customer wants to be able to either show/hide activities, so sub-reporting to the detail, 2nd lvl, 3rd level for the activities shouldn't be too bad. Thanks!
View Replies !
Tranact SQL : Get Total Of Detail Table
Hi, I have a master and a detail table as defined : Master : tmpOrder Column_name Type ------------------------------ ------------------------------ OrderID int Date DateTime Detail : tmpOrderItem Column_name Type ------------------------------ ------------------------------ OrderID int Seq int Qty float Amount float How can I get the master fields and sum of qty and amount in one query. I need the following result set OrderID Date TotalQty TotalAmount -------------- ---------- ---------- -------------- Thank you in advance Allex
View Replies !
Obtaining Detail Of DTS Package Contents
I have quite a few DTS's to work through while analysing current SQL databases for a client. Problem is I can't seem to find a way in SQL (Enterprise Manager) to print the contents of a DTS package. This is quite frustrating in that I have to go an "Design" the package, then click on the properties for each step and copy and paste the contents into Notepad/Wordpad. Surely Microsoft have something to ease the pain? If anyone can help me on this one, I would be extremely grateful.
View Replies !
Replication Of Master Detail Tables
Hi, I am new to SQL admin and have been assigned the task to set up replication between our production server and development server. We wish to take snapshots of production in order to keep a "warm" replication of data in our development environment. We're using SQL7 sp2. I've set up the prod box as a distributor so that it will push the snapshots to the dev box. I created an account for replication on both servers, and gave it full NT admin rights as well as full sql admin rights so that there' s no problems with access to NTFS volumes or the data that resides on them. I plan on locking down security tighter once we get replication to work. Using the Northwind database as an example, and following Microsoft's wizards, all goes well until the distribution agent attempts to "push". The resulting error says: "Could not drop object 'Categories' because it is referenced by a FOREIGN KEY constraint." Last command: Drop table "Categories" All the online responses to this issue seem to center around using some kind of SQL statement in order to disable constraints for replication. However, since the database in development will need to keep its foreign keys for when it migrated to production, this is not an option. Also, after running the Push Publication Wizard, I right-click on the published database ('Northwind') from the SQL Enterprise Manager. Clicking on the Articles tab, I have been instructed to click on the Elipse (.) button next to the table name, then the snapshot tab in order to set advanced preferences on how that table should be replicated. One default selection is to DROP existing tables with the same name, then re-create them. Another default is NOT to include declared referential integrity on the primary keys. Instructions that recommend changing this checkbox to INCLUDE referential integrity have no bearing on the above foreign key constraint errors. Thanks in advance! SR
View Replies !
SSIS Header And Detail To A File
Hi Anyone How to export a header and details data from two different table and export it in the below format ? RecordCount = 129 ------------> Header 001|Manager|2399.00|12 ------------> Detail Lines 002|Technican|1800.00|15 003|Mechanic|1500.00|18 ....... Total Amount = 180000.00 ------------> Footer Line I want to use the SSIS to do this job can anyone explain step by step. Thanks, Madhu
View Replies !
Export Master-Detail Records
I need to export data to a text file in the following format: Master Record Detail Record Detail Record Detail Record Master Record Detail Record ... Example would be: Master Record Format: ---------------------------------------------------------------------- RecordType|FirstName|LastName Detail Record Format: ---------------------------------------------------------------------- RecordType|Order#|OrderedItem Sample Data: M|Micheal|Smith D|123|1-123-1 D|123|1-123-2 M|John|Smith D|142|1-444-1 D|142|1-444-3 Could someone direct me how I can acomlish this task using SSIS? Thanks -
View Replies !
Data Replication History Detail
We are using a SQL Server 2000 Replication. I'm using the Merge Agent History Screen to retrive Informacion about replication sessions, is there any other screen to know exaclty which datawas replicated on each session?Or at least to know the script generated on each session? Thanks
View Replies !
Master/Detail Table Insertion.
Hi Experts, I need to know the best approach to save data in master table and then in detail table. I know this method but i know it's not a good approach why i will explain Insertion in Master Table..................................... A Insertion in Detail Table........................................B Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach. So please can any one tell me a good approach for this.
View Replies !
Roll Up Filtered Detail Group
I the following report with one group: Month Number of Sales Total Sales + Jan 2007 100 $1,000.00 When you dril down on MonthYear you get the detail data: Month Number of Sales Total Sales - Jan 2007 10 $610.00 1 $10.00 1 $20.00 1 $30.00 1 $40.00 1 $50.00 1 $60.00 1 $70.00 1 $80.00 1 $100.00 1 $150.00 My question is. I added a filter to the detail data to give the Bottom % =75 of sales. So My detail data only displays the following rows: Month Number of Sales Total Sales - Jan 2007 10 $610.00 1 $10.00 1 $20.00 1 $30.00 1 $40.00 1 $50.00 My problem is the group still displays the total of my dataset (as seen above), but I want it to display the total of the detail data group, like below: Month Number of Sales Total Sales - Jan 2007 5 $150.00 1 $10.00 1 $20.00 1 $30.00 1 $40.00 1 $50.00 If I change the fields in the group to look at the detail data ,for instance =count(Fields!NumberofSales.Value,"Details_Group") I get an scope error. How can I display the totals of the detail data in the parent group after I added a filter to the detail data?
View Replies !
Force Detail Rows For Roster...
I have searched prior to asking but may have missed something. I apologize in advance if this is addressed elsewhere. I am creating a "Course Roster" that will print out who is signed up for a class. It must provide so many additional blank rows for those that show up for the class and who are not registered. For example, five people show up but I want to fill out the page with another X number of rows to allow for others to sign up at the time of the class. Does anyone have suggestions or directions in which they could point me? Thanks James
View Replies !
How Can I Sum By Group Total Instead Of By Each Detail Line
I hope someone can help with this problem. I'm new to SSRS 2005, having used Crystal Reports for several years Our General Ledger Accounts consist of Dept Project AccountCode Each section is 4 digits. 22000000XXXX I have the following sections in the report table: Detail -- amount per transaction per account Detail group -- sum of the transactions per account Dept Project group €“sum of all the transactions for the Dept Project DATE AMOUNT Budget Detail 222100001234 4/5/2008 $100 $20,000 222100001234 4/10/2008 $200 $20,000 Detail group 222100001234 $300 $20,000 Detail 222100005678 4/12/2008 $400 $40,000 222100005678 4/11/2008 $500 $40,000 Detail group 222100005678 $900 $40,000 DeptProjectGroup 22210000 $1200 $60,000 As shown above, I sum the amounts per account in the detail group, and the amounts per DeptProject in the DeptProject Group. This works fine for the amounts. I also placed the budget amount per account in the detail group section. This works also. But when I try to total the budget amount per DeptProjectGroup, I get the total for each transaction instead. In Crystal I would have it total by group, but I don't know how to do that in Reporting Services. Any help would be much appreciated. Thanks, Sue
View Replies !
Can An Image Be Shown In Matrix Detail?
I have a matrix based report running, it shows a pivoted statistical value in the detail cells. Now I'd like 1 of 2 images to show instead, in those cells, based on the value of the statistic. Can this be done in a matrix?
View Replies !
Images Displayed In A Detail Record
I have a problem with a report, and I am running out of ideas on how to approach it. I have a report with groupings of data displayed, and once per group I need to display an image. When I place the image in a record, it shrinks it down to the height of the record and repeats the image for each record despite what formulas I put in the hidden property.
View Replies !
Printing Drill Through Detail Report
Hi, I have created a drill through report (RS 2005). After deploying the report and clicking on a link to drill through to the second report, I try and print the detail report and it prints the parent report instead. Any ideas on this one? I should also mention that we are using an AJAX solution to display the report in a pop-up window. Any help is appreciated. Thanks!
View Replies !
Transaction Processing Detail Records
Hello, I am fairly new to SQL Server 2005 and was curious if this was possible. In my VB applications I always used Begin Transaction, Commit and Rollback to process records. I just found out that I can perform the same functionality in a stored procedure. So the question is, if I have an order record and four line item records is there anyway to pass all that to the stored procedure as a unit. I can pass the order record as individual parameters but it is the four (or however many) detail records that is the reason for my question. How can I pass the detail records at one time? Can I pass these as an array or a dataset or something else or am I just out of luck? SQL Server 2005 has impressed me a few times already with what you can do and I am really hoping this can be accomplished also. Cheers, Richard
View Replies !
Combining Detail Records From Different Tables
Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet. tblShipmentHdr --> tblShipmentDet (Key = ShipmentID) tblAdjustments --> standalone Result: combine tblShipmentHdr + attached tblShipmentDet records with tblAdjustments records. Would the best approach be to use a UNION SELECT? @XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005' AS SELECT SHPH.ProductID, SHPH.ReceivedDate, SHPH.ShipmentNo, SHPD.Vendor, SHPD.Quantity, QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ReceivedDate END, QShipQty = CASE WHEN SHPD.TransCd = 'F' THEN NULL WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121) THEN NULL ELSE SHPH.ShippingQty END, PROD.ProductName, QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null THEN 0 ELSE MEND.OpeningAccrual END FROM dbo.tblShipmentHdr SHPH LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7) LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121) WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))
View Replies !
Simultaneous Entry In Both Master & Detail
Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables? For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?
View Replies !
Max Of Date Field In Query
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
SQL DateTime Field Query
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View Replies !
Empty Field Query
Hi Friends, I have one query that i have to Replace the Empty Value of a filed with some other value how can i do in SQL?? ID Phone Name 1 122 abc 2 xyz 3 444 mmm 4 525 ccc 5 nvb Now i want replace the blank (Empty) filed with some charaters Numbers how can i do that?? any Help Ashu
View Replies !
How Do I Query For Particular Criteria In Every Field In The Db?
I need to query a table for a record only if there is a certain text string in ANY OF THE TABLE'S fields. I know of using the WHERE clause with logical OR (e.g. - WHERE field1 = 'xyz' or field2 = 'xyz' or ...) but there is a limit of 40 expressions with an Access database, and I have 102 fields to look through! That's my dillema. I know I may need to go to MSSQL or Oracle, but I'd rather stay with Access. Is there a syntactical way to express the query I need? Or do I have to look somewhere other than Access for my database needs? Thanks for the advice!
View Replies !
Use Calculated Field In Same Query
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work? Thanks!
View Replies !
SQL Query: Missing Field Value
Suppose in a table, there is some data in a field: 1, 2, 3, 5, 6, 8, 9. I need a sql query which will list the missing numbers: 4,7 (Missing digits) Query: Select * From table1 field ------ 1 2 3 5 6 8 9 Expected query:?? filed1 ------ 4 7 Please help me to get a query which will provide my expected data. Thanks. /Fakhrul(mfhossain@gmail.com)
View Replies !
|
TRACKER
