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.
I want to enable a drill down on a parent child hierarchy, just as it is possible in the Analysis Services.
An Example :
Hierarchy in a company : Jack (1) is the boss, Frank (2) and Andy (3) ar the Semi-Bosses , and Julia (4), Jane (5), Henry (6), Michael (7), Will (8) , Dave (9) are normal employees . Timmy(10) is a trainee. Furthermore Frank is responsible for Julia,Jane and Henry and Andy is responsible for Michael WIll and Dave. Dave is responsible for Timmy. So we get the following tree hierarchy structure.
1 2 3 4 5 6 7 8 9 10
My Employee Dimension Attributes : Name, Parent
I want to make a report, where I show the salary of each employee. But at first the user only sees 1, when he drills down he sees 2 and 3, too. When he drills down once more he sees 4 5 6 and 7 8 9, too (The user must be able to recognize which person is responsible for 456 and which for 789). And the last drill down shows 10, for which 9 is responsible ( exactly as it is possible in the analyis Service in the Cubebrowser)
I have read some posts where are MDX examples which are similar to my problem, but they did not solve my problem.
Hai, Iam new to SSRS please help me, my question as follows:
in my table fields are departmentid, studentexpected, student name, date attendance,sortorder while grouping(drill down) the departmentid column , iam displaying student name, date attendance its working fine. problem: while grouping(drill down) the studentexpected resultant column i need to display student name, date attendance fields.
in my database studentexpected column displaying studentlist like 28, 56,250, while grouping(drilldown) the individual lists like 28,56,250, i need to dispaly student name and date attendance fields.
my problem is while grouping(drill down) the departmentid column iam getting result for studentlist 28 as iam getting 28 student names and 28 attendancedates, this functionality is correct.
but while grouping(drilldown) the studentexpected resultant column iam not getting (assume for studentlist 28) 28 studentnames and 28 attendancedates.iam geting only 2 studentnames, 2 attendance dates, but i need respective list columns
in database iam taking sordorder as 7.
iam facing difficulty at the time filtering the studentname and attendancedates columns in table properties
which data i have to give in filter column for displying 28 student names and 28 date attendances fields at the time of grouping studentexpected column?
take below references:
database prview:
departmentid studentexpected student name date attendance sortorder
101 28 jack 12-31-2007 7
dapartmetid drilldown report preview:
departmentid studentexpected student name date attendance +101 28 jack 12-31-2007 -- --- -- -- -- --- -- -- (assume 28 student names and 28 date attendance) My Problem:
like above preview i have to dispaly respective studentexpected column results,
departmentid studentexpected student name date attendance 101 + 28 jack 12-31-2007 -- --- --- ---- (here while drilldown the studentexpected resultant column like 28,56,250 ,i need to dispaly respective studentnames and respective date attendances)
which data i have to set in filter column in front end , how to solve above problem? help me
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:
I€™m having a problem with Excel 2007 DM and SQL 2005 and I hope someone out there has a solution.
Consider the following environment:
Windows XP SP2 or Windows Vista, Excel 2007, Data Mining Add-in, SSAS 2005 (with session mining models enabled, an AdventureWorksDW cube deployed and drill-through actions available).
Now take the following steps:
1. In Excel 2007 set up a connection to SSAS
2. Connect to the cube and create a new pivot table report (drag and drop whatever you like)
3. Right-click on one of the cell values in the data region and either select a drill-through action, or, select Show Details in the context menu
4. Ensure that you have at least 10 detailed records that are generated on a new worksheet page; you should have a time-based column in your detailed records
5. Select the table of detailed data, then select the Analyze tab (within the Table Tools grouping) which appears in the topmost menu above the ribbon
6. Click the Forecast button in the ribbon and choose both the field which you want to predict as well as the time-based column (from step 4) as well as the number of time periods to forecast
7. Finally click OK.
1. Having followed these steps on both WinXP SP2 and Vista, I keep coming across the exception: HResult:0x800A03EC. Any ideas as to why this exception pops up? If I was using a normal table of data (which was not generated from a Show Details or drill-through action), then the Forecast button works fine.
I googled it and thought the localization settings for SSAS 2005 and Excel 2007 needed to be the same (initially they weren€™t). I€™ve tried removing the auto-filters which appear atop each column in the detailed data table prior to clicking the Forecast button, and, I€™ve also tested for a series of data across a number of time periods with the same result.
Also, a colleague of mine discovered that the column headers that appear by default from a drill-through start with "$[", and, in removing them the Forecast function appears to work.
I would have thought there would be a seamless transition in Excel 2007 between data retrieved from a cube and the DM Add-in featueres (or at the very least, a more meaningful exception message than the one presented).
Is there something I€™ve missed, or, is there a KB article I haven€™t come across yet? As I know for a fact that the problem is reproducible, is there a fix to this problem on its way to us? Is there a useful workaround that doesn't require manual intervention?
1. For U_ID = 1, we have two diffrent U_NM and for one U_NM we have 2 child data.  Need to show parent data order by U_ORD and need to show child data within their parent order by U_DT
2. For U_ID = 2, we don't have child data, hence need to show data order by U_ORD only
SELECT 1 AS U_ID, 'Plan' AS U_NM, '06/01/2015' AS U_DT, 1 AS U_ORD, NULL AS P_U_NM UNION ALL SELECT 1, 'Cust Plan 2', '06/05/2015', 0, 'Plan' UNION ALL SELECT 1, 'Cust Plan 1', '06/10/2015', 0, 'Plan' UNION ALL SELECT 1, 'Design', '06/15/2015', 2, NULL UNION ALL SELECT 2, 'Plan', '06/20/2015', 1, NULL UNION ALL SELECT 2, 'Design', '06/25/2015', 2, NULL
I have a cube that I created in AS2005, I have a hierarchy and drillthrough action defined. When I create a pivot table in Excel based on that cube and I try to drill down on the cell in the pivot table I can not drill down to the next level. I get an error message: Cannot show or hide detail for this selection. My hierarchy is based on Dimension Facility. Facility State is root level, facility zip is next level. When I'm in AS2005 and browse the cube, I'm able to drill down and drill through and see the hierarchy just fine. (Also, I find interesting that when I create a cube in excel, based on a two relational tables, I'm able to drill down. ) I have the Analsysis Services Add-on installed on my machine, so I have the Cube Analysis Menu item. But I can't seem to even drill-through. So, for now all I want to know is why can't I drill-down (i.e. double clicking on a cell and having the data related "explode" in the spread sheet) like it shows in the article written by William Pearson, titled September 12, 2005 Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More. Any help is greatly appreciated!! Queen
I am working on an ASP.NET site to display information about authors and books. I'm working on the Author page right now and I'm having trouble getting it to display the data I want. I'm trying to do everything with declarative controls and as little code-behind as possible. I have a datasource on the main page that selects the Author data and provides this to a FormView. Inside the FormView is another datasource that takes the AuthorID as a parameter and selects the list of books, which is provided to a DataList. Inside the DataList I display some limited Book information. I also want to display some child items of the Book (grandchildren of the author), for example other authors who contributed to that book, or alternative titles. I have tried using datasources in each DataList row with a parameter set to the BookID, and it works just great, but the response time is just not acceptable. Each datasource of possible several dozen is making its own call to the database and it's just too slow. If I use code I can grab all the data in one operation and use relations in the DataBinding event (I believe) to select the data I want, but this is a bit cumbersome and I expect will cause trouble if I want to eventually use an ObjectDataSource. Incidentally, on a display-only page I use the XMLDataSource and it works great, but I need read-write access on the Edit page. Is there a good way to do what I'm trying to do?
i am trying to figure out how i can create an SSIS package to insert into multiple tables. After the first insert, I want to take the ID created (an Identity column) and then use that to insert into other associated (foreign key) tables.
For example, I have a table Users. The primary key is an Identity column. Once the SSIS insert is complete, the bulk load of new users has an identity ID value for each row. What I want to do, during the same SSIS package, is to take each row as it is inserted and add rows to other tables. Like, UserDepartment - it has a foreign key for the user id and a foreign key to the department being added. And, as part of this I will need to get the latest ID value and possibly some other values and store them in variables.
I looked at multi-cast, but I don't know/think that this will work for me.
does anyone know of a good example or article like this?
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.
--In Parent table(@ParentTable) there are 4 and 5 items each for product 101 and 102 respectively
INSERT INTO @ParentTable(ProductID,ItemID) VALUES(101,1234),(101,1578),(101,1590),(101,1237) INSERT INTO @ParentTable(ProductID,ItemID) VALUES(102,5465),(102,5466),(102,5474),(102,5489),(102,6543)
--child products 701 and 901 are derived from parent products(partially) 101 and 102 respectively
INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(101,701,1234),(101,701,1590),(101,701,1578) INSERT INTO @ChildParentTable(ParentID,ProductID,ItemID) VALUES(901,102,5465),(901,102,5474),(901,102,8976)
--Here what I need is
--For product 701 there is one item missing ie 1237 which exists in its parent 101
--and For product 901 there are 3 items missing ie 5466,5489 and 6543 which exists in its parent 102
--and extra item exists ie 8976 so my result table should lokk like this
We have 2 different U_ID (1, 2) and I want a SELECT query to display,
1. For U_ID = 1, we have 2 parent U_NM (Design & Plan) and Plan having 2 child (Cust Plan 1 & Cust Plan 2). 2. I want to display parent U_NM ORDER BY U_ORD 3. If any parent having child element, then need to show immediately under that parent and ORDER BY U_DT 4. For U_ID = 2, we don't have any child, hence display ORDER BY U_ORD
I am trying to insert data into two tables with a SSIS package. One table has a foreign key relationship to the other table's primary key. When I try to run the package, the package will just seems to hang up in bids. I have found two ways around the issue but I don't like either approach. Is there a way to set which table gets insert first?
If I uncheck the check constraints option on the child table, the package will run very quickly but this option alters the child table and basically disables the constraint. I don't like this option because it is altering the database.
The second approach is to set the commit level on both tables to say 10,000 and make sure that the multicast component has the first output path moved to the parent table. I don't like this option because I am not sure if the records are backed out if the package should abend after records have been committed.
Sorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will
I'm trying to use the Ratio to Parent sproc - [URL] .... The problem is that the stored proc appears to be pegged at 4 decimal places (which translates to ##.##%). This is losing precision and isn't adding up to 100% when the ratios are summed individually.
Can this be fixed within SSAS or will i need to modify the assp code? .NET decimals are meant to automagically scale, so not sure why it's coming out as (5,4) but my .net coding knowledge is fairly poor.I get the correct behaviour if hardcode a ratio to parent MDX calc (the resulting ratio has as many decimal points as are required)
I want to allow users to have the ability to drilldown. But it doesn't seem possible outside AS ? am I wrong ? As I have built a cube with drilldown enabled and have tested and is working within AS. However after I've opened it up in excel I and tried to drill down it wasn't actually possible. I could only increase / decrease the report's hierachy and that was all.
Anyone know how I can allow my clients to view drill down values ?
Does anyone know if it's possible to use drill-through function, liked it's used in cubes in Analysis Manager, in a webreport in Reporting Services? With drill-through i mean the functionality to see individual rows from the database for a single cell in the web-interface.
Hi ! I use a OLAP data Source (Analysis Services) I have a problem, I have a bar chart, where I put an action on the data Properties to move to another report B if there is a clik on. My report B need a parameter to execute it self. So In my report A, I add an action which pass the good parameter to the report B. In my report B, I add a parameter (via report menu, reportParameter item), but I don't how link the two (grid and parameter)... because, in my report display, I display a grid, and a text area, in the text area I display the parameter which is pass by the drill through, and when I preview it, the value is ok, but not in my table (grid), I try to add a dimension with ... but I have always an error, and if I put a dimension who is a parameter it is not ok... I don't understand, could you help me !? Thank you
I have a problem on creating a report using Microsoft SQL Server Business Intelligence Development Studio. I am new to this environment, anybody here can help me on how to enable the "DrillDown" on the table?
When I am using the wizard, the option to enable the drill down will appear, but when I am using "Report Server Project", I really don't know how to enable it, and theres was no table properties on how to enabled it.
I have one dotnet applciation in that i am viewing the report in report viewer. In the same report if the user clicks on one field another report will open. I got a problem with this second report. the image placed on the second report is not coming and if the user clicks on the print button which is on the report viewer the first report is only getting printed. How can I print the second report.
I'm developing an application using SQL Integration Services and SQL Reporting Services. I need to have the ability to drill down from a graph created with Reporting Services.
Let me explain: If I have a pie chart, I want to be able to click in one of the slides and see the information details that could be in another graph or in a table.
I was wondering if when you click on the + in a drill down report, does the report requery the database, or is the report just re-rendered without having to query again?
depending on the answer, performance considerations would be different.
I have created a drill down using a table. The drill down has 13 levels in it which causes a big vacant space between the top level and the mesures on the right hand side. I would like to hide the columns so that when i drill down the measure columns should dynamically move towards the right (as i keep on going to the next level) . I have tried using inscope for hiding that particular column by refering to the previous level group in it but it doesn't seem to be working.
is it possible make drill down reports in crystal my requirement is like i have n rows in my report if am clicking the first column in first row it will list details of that row in between first and second row Same for all the rows
For starters i'm using Microsoft analysis services, integration services and reporting services.
All goes well when creating some general overview reports.
Once i get to making drill down reports with only 2000 rows of data is already starts to work very slow (sometimes takes hours). It seems a data cube is only fast for calculations like SUM, PARALLELPERIOD, ... but if it comes to amount of data it is extreemly weak! 2000 rows is not even alot, i've seen reporting tools beeing able to handle a million rows with drill down function.
I'm thinking that this microsoft tool is just not able to handle reports bigger than 100 rows in it combined with drill down. Please proof me wrong because we already paid for the microsoft tools and it would be a shame if we were not able to use them because of bad functionality...
The machine we work on is quite strong (dual core 2.4 ghz, 4 gig ram, ...)
And yes i know you can make reports clickable so they take the value with them to a next report as a parameters but thats not what we are looking for, we need this drill down to work properly with larger amounts of data.
