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?
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
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.
I have a report with two groups and a detail row (subtotals & totals to follow). When I add the child (detail row) it pushes out to the right of the parent column. Is there any way to start the detail row all the way back to the left hand side of the page? I lose a lot or real estate with the group descriptions.
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.
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.
I wrote a stored procedure. It work properly. But I want to catch if any error occurs while executing it. And I want to make roll Back on error .And send the error OUTPUT How can I roolBack the command below and How can I send the error over OUTPUT parameter? execute(@cmdS) Thanks.
Hi, I'm using data flow as flat file source -> derived column ->Data Conversion-> oledb destination I have a fifty thousand record in a delimited file. while processing i got error in 45000th row. In database only 40000 records are there(why is there no all 45000 records). Is there any way to roll back all the 40000 records.
I have an application that allows users to synchronize SQL CE data with the server. I would like to implement the functionality wherein if the synch process hits an error, the whole synchronization rolls back.
Is it possible to roll back the Synchronization process? I am using Merge replication over web in the application.
Hello, I do not know how to implement transaction roll back in asp.net application. I am using SqlHelper class to communicate with my sql db.Thanks, junior
Hi, I'm planning to install the cumulative hotfix (build 2187) on my sql 2000 clustering server (SP4, 2040). And I would like to know if the cumulative hotfix is able to roll back. If possible, please provide me any information about that. Thanks in advance.
I have a situation where i need to insert or update the data from a flat file to a sql server database. The flat file contains nearly one lakh records.
I am using transactions. If all the rows are inserted or updated successfully i am commiting. If there is any error i am rollbacking the transaction.
when rollbacking the transaction it is taking more than 3 to 4 hours.
Hi again, In ASP.net, is there any elegant way to handle a set of time inserts from a form when the 2nd time is past midnight? Specifically, I have a form with 2 textboxes on it (startTime and endTime) that are set up to accept time values (using AJAX MaskedEditExtender for formatting/validation - pretty cool). This data is posted to a sub that enters the data into a table (T_Details). However, I've noticed that the data inserted as part of the record (SQL field is smalldatetime) doesn't take into account the fact that a time value past 23:59:59 in the "endTime" textbox is a time on the next day - it simply rolls to an A.M. date for the same day as the date for the pre-midnight value from the "startTime" textbox. I'm sure that I can simply do some conditional coding and modify the date if necessary but is there a better way to do it? Thanks as always...this forum is a great resource
hey guys i need help urgently i just ran an update statement without a where statement by mistake and i need to rollback this changes
i;m runnning sql express sp2 and the database is set to recovery model simple
i hace the mdf and ldf file the mdf is 1.5 gb ledf is 65 mb and the last changed date is the same time i ran the update statement so i think the changes are there in the ldf file but i just need to roll back to 1 minute b4 i run the update
The package is corrupt, who knows why, who knows what. It was running fine but there was a bug in an active script. Having failed miserably to debug this (note the ability to put a breakpoint in a script would be nice and dear god do not ask for edit and continue in here, it is bad enough as it is!) i eventually stuck a ref to the winforms namespace and debugged with a hundred messagebox.show statements. I did try to fire up an event that would write to an event tlog and i'm pretty sure that that is where the main problem lies as the package is still trying to load/use/connect to some log options but it did run for a while after i abandoned this path. Either way, I need some clue as to what bits of the xml file i can hack cos that is all i can get access to. 4 miles of xml, somwhere within the errors below are related. Clue me please or its curtains. Note also, the package is corrupt everywhere - in bin, in deployment, could not the last good compile get saved somewhere for recovery purposes as opposed to being overwriten with the bust one?
Error 1 Error loading Copy of pckSplitSourceNQ.dtsx: Element "{AD446434-4355-42C7-AD07-3D64A37C1671}" does not exist in collection "LogProviders". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 2 Error loading Copy of pckSplitSourceNQ.dtsx: Element "{F43808C7-03B1-4A09-86B1-08274073DA5C}" does not exist in collection "Executables". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 3 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading value "<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" IDREF="{F43808C7-03B1-4A09-86B1-08274073DA5C}" DTS:IsFrom="0"/>" from node "DTS:Executable". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 4 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading value "<DTS:PrecedenceConstraint xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="Value">0</DTS:Property><DTS:Property DTS:Name="EvalOp">2</DTS:Property><DTS:Property DTS:Name="LogicalAnd">-1</DTS:Property><DTS:Property DTS:Name="Expression"></" from node "DTS:PrecedenceConstraint". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 5 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading value "<DTS:SelectedLogProvider xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:InstanceID="{AD446434-4355-42C7-AD07-3D64A37C1671}"/>" from node "DTS:SelectedLogProvider". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 6 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading value "<DTS:LoggingOptions xmlns:DTS="www.microsoft.com/SqlServer/Dts"><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">0</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8">2,10,OnProgress,23,ScriptComponen" from node "DTS:LoggingOptions". G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 7 Error loading Copy of pckSplitSourceNQ.dtsx: Error loading a task. The contact information for the task is "Performs high-performance data extraction, transformation and loading;Microsoft Corporation; Microsoft SQL Server v9; (C) 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails. G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
Error 8 Error loading 'Copy of pckSplitSourceNQ.dtsx' : The package failed to load due to error 0xC0010014 "One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.". This occurs when CPackage::LoadFromXML fails. . G:Visual Studio 2005 ProjectsSSISDataCleanseDataCleanseCopy of pckSplitSourceNQ.dtsx 1 1
I need to include pre k kids in the is script for one school and exclude them for the others. I have them all excluded with the last statement in the where clause. How would I go about accomplishing this.
Code: SELECT DISTINCT C.Student_ID, isnull(left(dbo.capfirst(rtrim([first_name])),12),' ') as First_Name, isnull(left(dbo.capfirst(rtrim([last_name])),17),' ') as Last_Name, LEFT(Middle_Name, 1) as Middle_Initial, RTRIM(CONVERT(CHAR,Birth_Date,101)) AS DOB,
I am having some difficulty in constructing outer joins. I havesimplified what I need to do and have included sample SQL statements:create table tab_a (id int, descr varchar(10), qty int)insert into tab_a values (1, 'item one', 10)insert into tab_a values (2, 'item two', 20)insert into tab_a values (3, 'item three', 30)insert into tab_a values (4, 'item four', 40)create table tab_b (id2 int, descr2 varchar(10), qty2 int)insert into tab_b values (1, 'item one', 10)insert into tab_b values (2, 'item two', 20)insert into tab_b values (3, 'item three', 30)insert into tab_b values (4, 'item four', 40)Here is the statement that I have:SELECT tab_a.id,tab_a.descr,tab_a.qty,tab_b.id2,tab_b.descr2,tab_b.qty2FROM tab_a LEFT OUTER JOIN tab_bON (tab_a.id = tab_b.id2 )WHERE tab_a.qty <= 30 ANDtab_b.qty2 > 20What I am trying to do is left outer join between tab_a and tab_b afterthey have been filtered based on the qty column. (for tab_a: qty <=30; and for tab_b: qty > 20).How would I go about that? I would like to do this efficiently sincethe two tables have about a million records and several other columnseach.
Hi all,I’m a bit new to this so hope this is not too obvious!I am running a query like so (simplified form)Select Cus_no, Cust_Name, course_idWhere Cust_id = 2From course tableHowever not all the Cust_Id’s have been entered therefore so using a twofilters out the information I might need.Is there a way I can get around this?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hello, I'm Designing sql server 2005 SSIS Packages. According to my requirment i have a sequence container. It has few data flow task, on success of one next one is running. If any one of them get failed then it should roll backed all the transaction. Each Data flow task transfering a data from one server to another server in similar table.
There are special instructions for installing service packs on desktop edition installations -- command line options etc. How do you install hotfix roll-ups? Does build 2187 apply to desktop edition?
In asp.net 3.5..... To get the number of rows returned when the select is executed....... Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected Session("MessageText") = e.AffectedRows & " Profile(s)"End Sub How do you get the number of rows returned when you apply a filter expression to a selection of rows? Thanks Craig
I'm just trying something that I haven't tried before, and I'm wondering if anyone has done it before. I'm building a report with three charts. This chart is going to go inside of Microsoft CRM. One of the charts has to use CRM filtered views, so that a manager can see his whole team's numbers, but a team member will only see his numbers. I can't do this any other way because of the complexity of the query, which has 3 select statements joined together as tables. It would be too complex to try to use a parameter throughout all of those tables, and I'm not sure how I would set up a parameter to show all of the data if the user were the manager, and only the single user's data if it were someone else.
The other two charts use filtered views as well, but they have similar joins, and I had to hard code a column with meaningless data into each SELECT table so that I could use it to join, as the tables had no other similarities.
The problem is that when you upload a report into CRM, if it uses filtered views, you don't have to go into Report Manager and change the data source from a shared to a custom data source. But on reports where I don't use the filtered views, I find that they always break when upload them, because they use the shared data source. This is the case even when I have created the report without the shared data source. So I usually have to go in and change the data source from shared to custom every time I upload a report without filtered views.
Because of the situation I described, this report uses both filtered views and non-filtered views (for the hard-coded columns). When I upload it into CRM, it won't work either way, with a shared or a custom data source.
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, ...
My question is how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails. Using TransactionScope with single connection in DataObject. I am trying to insert row in two dataTable using its own tableAdapter (two tableAdapter). I have Product table with ProductID primary key with incriment identity. and that ProductID is used to insert row in ProductHistory Table. Lets say Product table has the last ProductID=8 (8 rows) and the next ProductID will be 9. When I insert row in both table and if the second table insert fails both gets roll back (which is good). but when I insert again another time the Product ID=10 not 9. Is there any way to roll back the ProductID in Product table so when i insert next time it has incriment number instead of gap.
Hello, I have problem for insert multiple query for insert in differenr tabels for a single record. I have mail record for candidate and now i wants to insert candiate labour info, candidate passport detail in diff tabel like candidatLabour and candidatePassport, i used two store procedure for it and i write code for it.and it works fine,but i think that if one SP executed and one record inserted but then some problem occure and 2nd SP not executed then........... so plz help me Thanks
