I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty -------------------------- Kit A | 15 Kit B | 25 Item 4 | 100
I have sql server 2000 that was working fine until today,today I opened EM and under sql server group where used to be all my information now there is NO ITEM, nothing shows(database,Nothing) .no items under SQL SERVER GROUP. But my application connected with sql server is running without any problem insert record and show all data. ERROR MESSAGE: A connection could not be established to sql server-2000- Reason: Cannot open user default database. Login failed
Please vrify SQL Server is running and check your SQL server registration properties(by right clicking on the Win-2000-Server node and try again)
PLEASE what that happens. please iam afraid help me now iam waitting now Thanks
I am using SQL Server 2000. I am logging on local machine. When I open "SQL Server Enterprise Manager," I only see following: Console Root --> Microsoft SQL Servers --> SQL Server Group --> (No items)
The server is running properly and applications are able to access the database. Also, I can run "Query Analyzer" and view all databases.
Please help to resolve the issue to view items in SQL Server Group. Thanks, Suraj.
select top 15 count(*) as cnt, state from table group by state order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:
Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName, o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount from orders o, customers c, Employees e, Shippers s, [Order Details] od where o.CustomerID = c.CustomerID
[Code] ....
Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode 1A 1B 24 28 26 310 312 410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10 - 2 and 11 - 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
Hi. First, I am VERY new to SQL Queries and Reporting. A co-worker is "mentoring" me, but I am trying not to fill his day with questions.
I HAVE read the help files, searched the forums, looked at books, and done general web searches, but any answers I have found have either no addressed my issue, or the answers are way over my head.
Furthermore, the (SQL 2000) DB is built into proprietary software (ISS Proventia Intrusion Prevention System), and the database may NOT be modified outside of the software.
With that said, I am querying multiple tables within the DB. I am using Business Intelligence Dev Studio, and placing my queries on a reporting server maintained by my co-worker. My goal is not only to get a solution, but also to UNDERSTAND it so I can continue to learn. Of course, the solution takes precedence over my understanding!
My Primary key is dbo.SensorData1.SensorDataID. dbo.SensorDataAVP.AttributeText returns a different number of rows, containing different data depending upon the value of dbo.SensorData1.AlertName. I need to return all rows, hence the Left Joins.
Depending upon my query, I might have 1000 events, and due to the many rows of data from dbo.SensorData1.AlertName I might return 20,000 rows (or more.)
I would like to return a report that "groups" events by dbo.SensorData1.SensorDataID., BUT, rather than simply providing these in groups, provides me single rows with a plus sign next to each even, that can be expanded for the additional data.
My co-worker has discussed sub-tables, but since I cannot modify the DB, it will be difficult / complex to do so, AND, for me to understand.
One of my queries follows. I have thirteen queries, total, that use various groupings of attributes. I have chosen one of the more complex combinations so I can generally apply the concept to the queries with fewer parameters more easily.
Note, I'll be asking the same question on www.sqlservercentral.com in the hopes of getting an answer I can understand one of these two places - If you answer here, there's obviously no need answering there answering there.
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty -------------------------- Package A | 15 Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
I have a table with a row group "Sales Area" that lists customers per sales area. There is one column with the sales per customer and another column with the planned sales per customer.A third column "Under Plan" is a simple calculation that compares the two Report Items of the sales to the plan and puts a 1 there if plan is higher. My issue is how to get the total of the group "Sales Area", to display the group total of all customers that are under plan. SSRS doesn't let me use aggregate functions on group totals;Unfortunately I cannot pre-calculate the "Under Plan" figure in the query, since this example is a simplified overview (the customers is a distinct count for example...)
How can I calculate a subtotal for a Report Item? I have a textbox(lets call it "PlusMinus") in the detail section of my table, which is a calculated textbox of two others (lets call them "Budget" and "Spent"). So, PlusMinus = (Budget - Spent). What I would like to do is get a subtotal for PlusMinus. I have tried several ways, using Sum() or RunningValue, even tried to write code, but I can't seem to get it right. Any ideas??
I have a query that returns the data about test cases. Each test case can have multiple bugs associated to it. I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
We have SQL Server 7.0 running on a NT 4 sp6 server and non of its items track in performance monitor. Under the SQL program tab, when performance monitor is clicked, it comes up and has items for SQL, but they all track as zero. You can "add" other items such as processor items and they track fine. Any ideas???
Thanks in advance!
Ray Peterson EPA Seattle 206-553-1682 peterson.ray@epa.gov
I'm new to visual studio and reporting services. We have report models, reports and data sources on a temporary server that need to get moved to a new report server. I have been able to upload certain reports to the new server but I'm still having problems with other items. What is the easiest way to move over these items to the new server? Is there a way from visual studio to connect to the old server? I want to save the items to my local drive and then upload them from there. Any suggestions will be highly appreciated. Thanks!
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
I need to copy all objects (views, procedures, tables, functions, etc -- no data) that are under a specific Schema in a database, but the Generate Script option in SSMS doesn't give me the option to do this. How I can generate a script that's Schema specific?
Hi... Please help. I am having problem with my hard drive so I git a new one. I installed a new instance of SQL Server 2005 and copy over all my projects from the old hard drive into the new.
The problem is, when I open my packages specifically the data flow task it is empty. All my dataflow items are gone.
I am not sure what I am missing during the copy. Please help how I can recover my complete packages.
I have designed a report that allows the user to select multiple values to be passed to SSRS. When I deploy and run the report from Report Manager I can select 1 to n values and then run the report. It works great. Now I attempt to call Reporting Services from my Web Site. I format the URL and attempt to pass the mutliple values in the URL. An example would be http://YadaYada&rs:Command=Render&rs:Format=HTML4.0&rcarameters=false&OrderTypeID='1,2' The report uses a Stored Procedure that is expecting @Ordertype Varchar(1000). And Yes, the Stored Procedures handles the multiple values being passed to it. What I receive is an error message (rsReportParameterTypeMismatch). Any suggestions on how to pass these values?
How you are handling the replication of the many instance-level objects/items (logins, linked servers, server roles, database mail, operators, on and on) to the replicas in an AlwaysOn topology.
I'm especially curious about DBAs managing larger SQL Server environments. In my current environment, we have approximately 80 production SQL instances containing about 650 databases that require high availability and disaster recovery.
We use mirroring today and have a solid, home-grown solution for replicating the instance-level items from production to disaster recovery. AlwaysOn changes things a bit since we'll have multiple replicas and of course the database could be active on any one of those at any time. So my concern is about instance-level items being created in one instance but never deployed to the other instances participating in the AG group.
We have a process that uses a stored procedure: sp_MysteryProcedure....
The problem I am having is: I am 95% certain the issue lies with this stored procedure, but I cannot find the process that calls this procedure. It could be a SQL Server Job that calls the procedure directly, it could be an SSIS (*.dtsx) process that calls this procedure, or some other random process.
One of the big issues is we have tons of *.dtsx packages that call a bunch of stored procedures, but it doesn't seem a normal Windows Search (Start --> Search) searches these files (or perhaps something else is going on with this search).
So my question is multi-part.....
1). How would one go about finding a rouge process that loads data via a stored procedure if we believe we know the stored procedures name? 2). How do you search *.dtsx files?
I am looking for a way to show how I can have a result set that shows a record with one item and the number of records where it was purchased or sold. Below is my sample data.
Use tempdb go create table #ItemLedgerEntry ( ENTRYNO INT NOT NULL , ITEMNO VARCHAR (50) NOT NULL , POSTINGDATE DATETIME NOT NULL , ENTRYTYPE INT NOT NULL
[code]....
I know something like this will give me results but I'd like to run one query.
select itemno, count(*) from #ItemLedgerEntry where entrytype = 0 group by itemno order by count(*) desc
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
I have developed a custom report item that works fine in design and preview mode while in Visual Studio. I cannot get it to show up on my deployed reports. Here's what I have done so far:
1. Deployed the report using Visual Studio
2. updated the rsreportserver.config file with the following entry:
I've also tried using the StrongNameMembershipCondition with no better results.
4. The dll and its dependencies are copied to the bin directory of the report server.
5. When I load a report with this custom report item on it, the report loads fine with no errors or warnings in the log file (even with verbose tracing). The area where the custom item should be is just white. It's almost like Reporting Services isn't registering the item correctly.
This is particularly frustrating because the report works fine in Visual Studio - apparently I configured that correctly. Any suggestions would be greatly appreciated. I'm stumped.
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.