I am creating a SSRS report connecting to a cube , Which has geography as Dimension as Hirerarchy ( the Hirerachy consists of Region, Subregion) and a measure , Now this geography dimension is taken as Parameter
when i apply filters i am getting the filter based on Parameter selection , But how can i get the all children under the same parent
As i need to create a graph based on report parameter (ie i should get all the children under the Select parameter parent)
to be more specfic consider this
Region SubRegion
Asia India
Asia Pakistan
Asia Japan
Australia Melborne
Suppose my report parameter is India , I should get all the values under Asia(Parent of india)
I have made an SSRS report using the recursive parent functionality to show a hierarchical tree of values. The problem I have is that some children have more than one parent, but because (in order to use the recursive parent nicely) I need to group the results by Id, I only see distinct entries. This means that I only see each child once, even if it "should" appear in multiple locations in the report (under each of its parents).
I have the below query which in turn goes in the where clause
Code:
SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) THEN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) END
I am getting below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried to solve this problem with If and else but it is not going well with in-clause
Code: select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) END ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )
I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query
Code: SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
This returns more than one row.I know why is the error message but need to achieve this functionality.
I am working on a project where I have a page that will have a parent record (Product) and then 1 or more children (options available for the product, user enters text to define) displayed in a table/gridview. There is a relationship defined in the database between the product and options table). My question is how can I allow the user to add the product info and then within the same page also add the options and only then save it all? The options will added to a table. Thanks for any help
Include children and exclude children in a single hierarchy in parent child dimension in mdx
*12-parent **20-parent - 9-parent --250-child1 --210-child2 --240-child3 aggregation of 12-parent only aggregation of 20-parent only aggregation of 9 with children
hi. I am working on Multiline Marketing Project.I have to calculate all the childens and display the whole tree..I have save records in tree format...But not able to calculate the all childrens. SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '16%') This query works properly.Problem is that it is not working in the project because in the like I have to pass a variable. SELECT count(*) FROM dfTree WHERE id in (SELECT id FROM dfTree WHERE lineage like '@idl%') Please Suggest me...
Hi all, Im designing a product menu like the one seen here. I need to be able to itterate through all product menu groups in a table and find all children of a specified parent. Sorry for the cryptic Explanation and crappy Diagram!
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another record. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
Finding the court cases where all children associated with that case have a programClosureDate. I can run this query:
CaseInfo Table CaseID, CaseNumber, CaseName
CaseChild Table CaseID, FK to CaseInfo ChildPartyID, FK to PartyID in Party table ProgramClosureDate
Party Table ID, PartyID, Firstname, LastName
SELECT ci.CaseNumber, ci.CaseName, p.firstname+' '+p.lastname AS child, cc.programClosureDate FROM CaseInfo ci JOIN CaseChild cc ON ci.CaseID = cc.CaseID JOIN Party p ON cc.ChildPartyID = p.PartyID
WHERE cc.ProgramClosureDate IS NOT NULL ORDER BY ci.CaseName
But this does not give me the cases where all the children have programCLosureDate IS NOT NULL.
I need to extract data to send to an external agency in their supplied format. The data is normalised in our system in a one to many relationship. The external agency needs it denormalised.
In our system, the parent p has p_id, p_attribute_1, p_attribute_2, p_attribute_3 and the child has c_id, c_attribute_a, c_attribute_b, c_parent_id_fk
The external agency can only use a delimited file looking like
where n is the number of children a parent may have. Each parent can have 0 or more children - typically between 1 and 20.
How can I achieve this using SSIS? In the past I have used custom built VB apps with the ADO SHAPE command but this is not ideal as I have to rebuild each time to alter the selection criteria and and VB is not a good SQL tool.
Given the sample data and query below, I would like to know if it is possible to have the outcome be a single row, with the ChildTypeId, c.StartDate, c.EndDate being contained in the parent row. So, the outcome I'm hoping for based on the data below for ParentId = 1 would be:
1 2015-01-01 2015-12-31 AA 2015-01-01 2015-03-31 BB 2016-01-01 2016-03-31 CC 2017-01-01 2017-03-31 DD 2017-01-01 2017-03-31
declare @parent table (Id int not null primary key, StartDate date, EndDate date) declare @child table (Id int not null primary key, ParentId int not null, ChildTypeId char(2) not null, StartDate date, EndDate date) insert @parent select 1, '1/1/2015', '12/31/2015' insert @child select 1, 1, 'AA', '1/1/2015', '3/31/2015'
I am using the code below to get all the children of a particular product and it is working fine. How to get the particular product's id in the select statement. for example, i need to show 891 in a separate column for all the records returned by the query below.
DECLARE @Hierarchy TABLE (Product_Id INT, Parent_Product_Id INT) INSERT INTO @Hierarchy VALUES (123, 234) INSERT INTO @Hierarchy VALUES (234, 456) INSERT INTO @Hierarchy VALUES (456, 678) INSERT INTO @Hierarchy VALUES (678, 891) INSERT INTO @Hierarchy VALUES (891, NULL)
Does anyone know of a link or list that has all the parameters for the "rs:" section of the URL access parameter, except for the ones in the Microsoft books?
Is it possible to write SSRS 2008 reports to run on an existing SSRS 2005 server?
If yes, what do I need to do to be able to write SSRS 2008 reports on my developer PC that will run on the SQL 2005 Server (which also has SSRS 2005 installed on it)?
I installed Visual Studio 2008 beta on the development PC and it appears that it needs SSRS installed on it too. So I installed SQL 2008 SSRS (SQL Server Reporting Services) and SQL 2008 Workstation Components onto the developer PC. Next I run the SQL 2008 "Reporting Services Configuration" tool. When I click on the "Web Service URL" section it hangs indefinitely and I have to force it closed.
The developer PC is Windows Vista Business.
Any suggestions on making this work, or any other information that you can think of that would allow me to use Visual Studio 2008 and the SQL 2008 "Report Designer Preview" tool to develop SSRS reports that will run on the SQL 2005 SSRS server?
i get a error message like this: Table RENTAL_ITEM in RENTALB23 does not have a matching parent key.
this is the sql: CREATE TABLE RENTALB23/RENTAL_ITEM (RENTALITEM# INT PRIMARY KEY, RENTALTYPE CHAR(3), TITLE CHAR(30), CATEGORY CHAR(15), DUEDATE DATE, FOREIGN KEY(RENTALTYPE) REFERENCES RENTALB23/RENTAL_CHARGE(RENTALTYPE), FOREIGN KEY(CATEGORY) REFERENCES RENTALB23/RENTAL_CHARGE(CATEGORY))
this is the sql for the rental_charge table: CREATE TABLE RENTALB23/RENTAL_CHARGE (RENTALTYPE CHAR(3), CATEGORY CHAR(15), CHARGE DECIMAL(5,2), PRIMARY KEY(RENTALTYPE, CATEGORY))
whats the best way to delete a parent record? for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?my code below: The T-SQL Stored ProcedureALTER PROCEDURE dbo.DeleteInventory ( @InventoryID int ) AS DELETE FROM Booking WHERE InventoryID = @InventoryID
DELETE FROM Inventory WHERE InventoryID = @InventoryID The code in the Code Behind to execute when a button(delete) click Try Dim temp As Integer = SqlDataSource4.Delete If temp > 0 Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp & " Records/Rows Deleted." Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text = "No records Deleted!" End If Catch ex As Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text = "An Error Occured.<br />" & ex.Message.ToString End Try any better methods anyone wants to share?
hi, i have two tables i want the identity value of the parent table to be inserted into the chile table here is my code,but i don't know why it isn't working ! protected void Button1_Click(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string pcontent = TextBox1.Text; string data = TextBox2.Text; addtopic(pcontent,connectionString); addfile(data, connectionString); } public void addtopic(string subject,string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) { SqlCommand command = new SqlCommand("INSERT INTO parent" + "(content)" + "Values(@content)", connection); command.Parameters.Add("@content", SqlDbType.Text).Value = subject; connection.Open(); command.ExecuteNonQuery(); } } public void addchild(string name, string connstring) { using (SqlConnection connection = new SqlConnection(connstring)) {Guid id = Guid.NewGuid(); SqlCommand commandd = new SqlCommand("INSERT INTO child" + "(parentid,data,uniqueid)" + "Values(@@IDENTITY,@data,@uid)", connection); commandd.Parameters.Add("@data", SqlDbType.NVarChar, 50).Value = name; commandd.Parameters.Add("@uid", SqlDbType.UniqueIdentifier).Value = id;
I'm building a page that will display link categories with the links under each one. I have that part working however there is a code issue that I cant quite figure out related to the SQL query. I want to be able to have the category only display if the 'linkcolumn' is set to 1 in the database. (so I can later separate which ones show in each column of the page. However, I get an error if I try to make my 'select' statement have a 'where' in it. I think it does this because it ends up not showing all the records but I'm not sure why. Exception Details: System.ArgumentException: This constraint cannot be enabled as not all values have corresponding parent values. My codebehind file: using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient; using System.Data.Sql;using System.Data.SqlTypes; public partial class Modules_NCG_NCGLinks : System.Web.UI.UserControl {public void Page_Load(object sender, EventArgs e) { //Create the connection and DataAdapter for the Category table.SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["mystringname"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from NCG_Links_Category Where LinkCategory = 'Test'",cnn); //Create and fill the DataSet.DataSet ds = new DataSet(); cmd1.Fill(ds, "NCG_Links_Category"); //Create a second DataAdapter for the Links table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from NCG_Links",cnn); cmd2.Fill(ds,"NCG_Links"); //Create the relation bewtween the Category and Links tables.ds.Relations.Add("myrelation", ds.Tables["NCG_Links_Category"].Columns["LinkCategory"],ds.Tables["NCG_Links"].Columns["LinkCategory"]); //Bind the Category table to the parent Repeater control, and call DataBind.parentRepeater.DataSource = ds.Tables["NCG_Links_Category"]; Page.DataBind(); //Close the connection. cnn.Close(); }private void Page_Init(object sender, EventArgs e) { InitializeComponent(); }private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } }
I have a parent/child relationship in a relational database broken out like this: Table Name: categories[category_id] int (primary_key NOT NULL),[category_name] varchar(50),[parent_fk] int The parent references the category_id in the same table to create the parent/child relationships. I can get all the bottom level categories by doing this: select category_id, category, parent_fk from categories where category_id not in ( select parent_fk from categories) Each bottom-level category has a count attached to it. The problem I have is getting the counts rolled up for each parent of the bottom level. A parent could/will have multiple bottom-level categories (and counts). My sql is a little weak, could you help me out? I can utilize everying in SQL 2000 (stored proc, UDF, anything). Thanks!
i have a dimension named 'sdoc' which has 2 members 1) symptom word( first hierarchical level) and 2) doc id (second hierarchical level)
more than one 'symptom word' can have the same 'doc id'. i want to know which all symptom words have a particular doc id (say 10.) if i give [10].parent then it returns only the first parent whereas it actually has more than one parent. how to retrieve all the parents.
I need to update the price of one row based on another. In this case it takes the price from a sku which has a value in the "ShadowOf" field. Shadow Of is a parent sku. It takes the parent sku price, adds 2% and applies it to the child sku. It's all working well except I'm not sure how to handle it when a parent has more than one child. It updates the first & sets the rest to null.Enclosed is the sample data + query.
I do not want to rollback child procedure when i am rollbacking parent procedure after execution of child procedure. in child procedure i am committing transaction and in parent rollbacking transaction. Is it possible. Is there any solution to avoid rollbacking child procedure when i rollbacking parent procedure.
I want to find all the child of a node in a tree . A child can have multiple parent i.e 2 can be place under multiple parent . The folling is the data:
This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id. Is this a good relational database design ? kindly suggest.
In our database we have a list of devices in a "Device" Table, eachhaving one or more IP's located in the "IP" Table linked through aforein key on the DeviceID Column.I would like to retrieve this information as SuchDeviceID IpAddress1 10.0.0.1, 10.0.0.2, 10.0.0.32 ...345etc.Is it possible to do that without using cursors? Through a query?
I'm working on a report in which I need to group data in a drill down table. Specifically, I'm tracking statistics on how many users are running a specific build. So, right now, my group header displays the results from:
SELECT DISTINCT BuildName as [FriendlyName] FROM DataSource WHERE ProductID = 9 //<- 9 being just for example
Beneath each of these entries, I want the content to reflect the number of occurances of a specified build name, something like:
- Alpha
19 - Beta
144
So,
SELECT COUNT(BuildName) as [Count] FROM DataSource WHERE ProductID = 9 AND BuildName = (The name of the build, as specified by the header row that contains us)
Is there a way to access that last bit of data in VS 2005 Reporting, or do I really have to write a separate count query for Alpha, Beta, etc...
This is my first exposure to both SQL and Reporting. I'm overwhelmed. Please help.
