Power Pivot :: Parent-Child Aggregation In Report
Jun 5, 2015
In my data warehouse, I have one level of Parent-Child hierarchy in DimEmployee table - Employee and Manager, linked to each other using a column, ParentEmployeeKey. This table also has historical data stored - a slowly changing dimension of Type 2. I also have a Fact table that gives me information related to Sales - FactSales. In this table, we have SalesAmount stored at Employee level, one Employee can have multiple Sales. Besides, FactSales is also related to DimDate table using DateKey.
I have a requirement wherein I need to make a PowerPivot report out of a PowerPivot data model, that is capable of getting filtered using a timeline, and displays SalesAmount for the Sales occurred in the date range selected on the timeline for a Manager as well as his subordinates in the same report, a Manager's SalesAmount should be an aggregated sum of Sales made by the Manager himself and the Sales made by his subordinates. When the selected date range in the timeline changes, the report must reflect this change.
The approach I tried was adding the ParentEmployeeKey column to FactSales in the data Warehouse, and then adding another roleplaying dimension 'DimManager' in my data model to connect to FactSales based on ParentEmployeeKey of fact with EmployeeKey of DimManager. The problem with this approach is this will only give me Manager related aggregations and I will have to create two separate reports for subordinates and managers.
The following table is a merged view of data from DimEmployee and FactSales. On report, I need to display either of the following:
Column A (applicable SalesAmount)Columns B1 (Manager's SalesAmount) & B2 (Employee's SalesAmount)
View 9 Replies
ADVERTISEMENT
May 19, 2015
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'
[Code] .....
View 6 Replies
View Related
Mar 10, 2006
When creating a parent child dimension I am not using the primary key of the underlying table. I define the key when I create the dimension. The parent/child relationship works fine but my measure aggregation does not work.
The dimension has a regular relation type to the measure and the primary key from the underlying dimension table is used in the relationship to join in the measure. Since I have not used the primary key in the dimension and have defined my own key the define relationship page warns me that I have selected a non-key granularity attribute and I must directly or indirectly relate all other attributes to it. When I attempt to relate the attributes on the dimension structure tab I receive errors that I have created an attribute loop.
Any ideas?
View 3 Replies
View Related
Nov 3, 2015
I have several reports in a Power View Gallery. In Gallery view, most of the reports show the "Open New Excel Workbook", the "Create Power View Report", and the "Manage Data Refresh" buttons on the right side of the report list. Why would some reports not have these buttons available? In the attached image you can see one report with the buttons and one without the buttons.
View 5 Replies
View Related
Nov 6, 2007
Hi,
I have a report which I have created 3 or 4 child reports from.
If I make a change to the format of an object within the parent report (e.g. the colour of a text box) then the change appears on the child reports as I would expect.
The problem is that if I make a change to the properties of the report (e.g. the margins or the page size) then the change is NOT picked up in the child reports.
Does anybody have any solution to this annoying problem.
Regards,
James
View 1 Replies
View Related
Feb 20, 2008
Hello,
I want to create a report in SSRS against a cube in SSAS 2005.
I have a Sales fact table, a Products dimension which is related directly to the fact table, and a Categories dimension which is related to the fact through Products (referenced dimension).
Fact --> Products --> Categories
In the Categories dimension i have also implemented a parent-child hierarchy. Only leaf category members are joined to Products.
Is it possible to create a report, drilling down from parent categories to child categories to products?
I 've already implememted the categories parent-child report but when i add the Products dimension in the flattened rowset, the Products seem to be joined to non-leaf category members.
Thanks.
View 4 Replies
View Related
Feb 19, 2008
Hello,
I want to create a report based on an AdventureWordsAS parent-child dimension.
I created a new report item.
In the Data tab i created a new dataset using AnalysisServicesAS data source.
I dragged and dropped into the designer [Organization].[Organizations] and [Measures].[Financial Reporting].[Amount] from the metadata pane.
How can i configure a table control in the Layout tab, to create a drilldown report?
How do i set the Group On and Parent Group fields if i have only one attribute?
Thanks in advance
View 1 Replies
View Related
Sep 28, 2013
I have a VM with SP2013 and SQL Server 2012 SP1. I have installed the powerpivot plugin SP1.But when I run the PowerPivot for SharePoint 2013 configuration tool.
View 3 Replies
View Related
Mar 30, 2008
Hello Folks:
I was wondering if it's possible to create a report using lists and not tables that uses hierarchial data structure. I know how this would be done if I were to use a table but I like to use lists because of the freedom of object placements within them. Below is the standard recursive(hierarchial) example query from the AdventureWorks database that you would use for a table with detail grouping.
Select C.FirstName + ' ' + C.LastName + ' - ' + E.Title As Name, E.EmployeeID, E.ManagerID From HumanResources.Employee As E Join Person.Contact As C On E.ContactID = C.ContactID Order By E.ManagerID
Does anyone know how I would use just lists without using tables? Thanks.
View 4 Replies
View Related
Aug 2, 2015
I am trying to show images in a product listing in power view.I work with an excel 2013 desktop version based on an office 365 pro account.I did the following steps:
import of an excel file with an article list via power query and loading the data to the data model import jpg images from a folder via power query, setting content as binary type and loading the data to the data modellinking both tables in power pivot--> manage via the image namesetting the table behavior for the images table under power pivot --> manage --> Advanced (e.g. Default Image: Content)opening power view and building article cards with article number and imageProblem: only a camera icon shows up in power view
Is there a solution with a desktop version?Can I use my Office 365 Pro account to make it work? How?Why is there no solution showing images in a pivot table?Link to Dropbox with power pivot files
View 9 Replies
View Related
Feb 6, 2008
Hi,
I have a problem with a reporting services 2005 report.
The data source is OLEDB for OLAP 9.
The report contains all the members of a parent child dimension. An example of the implementation is defined in the post following the forum msdn:
Http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=138549&SiteID=1.
The report works fine in web. The problem is when the report is exported as Excel, the groups disappeared and the entire dimension is ragged down.
Normally toggled groups in reporting services are exported to Excel with the appearance '+' or '-' on the left side of the sheet. This feature is very useful to hide / show lines.
It seems that with the implementation specified in the msdn post above,
It is not possible to export correctly the report in Excel.
So my questions are:
Has someone already encountered this problem?
Are there solutions with a different implementation?
Thanks,
Gurvan
View 2 Replies
View Related
Mar 26, 2008
Hi All,
I am working on SQL server 2005 Reports.
I have one report, one dataset is assigned to it, and one table which displays it.
Now I come accros requirement that, the column value in the filter condition for the table is present in one textbox.
I can not use textbox i.e. reportItems in filter condition. Can someone suggest me how to use textbox value in filters?
I want to display parent/child records on report. I am not getting the proper solution.
The data is like this:
Sequence ItemCode IsParent
1 XYZ 0 'do not have child record
2 PQR 1 'have child records with sequence no 3
3 ASD 0
3 AFDGE 0
3 VDC 0
4 ASR 1 'have child records with sequence no 5
5 ASR 0
If IsParent = 1, that record has child records with sequence = parent sequenece + 1
I think u can understand the data I need to bind, and it is like:
XYZ
+ PQR
ASD
AFDGE
VDC
ASR
On + click we can do show/hide of child records.
I m not getting how to achive this in SQL server report. Can u give some hint?
Thanks in advance
Pravin
View 1 Replies
View Related
Nov 20, 2015
I have a situation where I have created a report using Power Pivot data Model Manage and linked to a SQL View using external sources from SQL Server. Once the View was linked from SQL server, I created the report in Power View from Inserted --> Power View. Since the business user requested the same data to be replicated in the excel worksheet in the form of excel, I created a version of excel report by selecting Data tab--> From other sources-->SQL Server --> connected to View (Same view that I connected in Power Pivot Data model for Power View report).
Once I created all the reports in my local excel and proceeded to upload in Office 365 share point. I followed the below steps:
1. Create a data source using the admin power bi website
using the connection string..Excel sheet Data tab --> Properties --> Connection String
2. Once the Data Source was created, Uploaded the excel report to the Power BI website.
3. Scheduled for the daily refresh updates.
Expected result:Since the connection set up was all successful and no issues with the refresh schedules, I was expecting to see my reports in both Worksheet reports in excel version and Power View reports to be refreshed on a daily basis. And daily transactions to appear on the reports on both Power view and excel worksheet reports.
Actual Result:I now only see the data refreshed in Power view reports and not the reports linked through Data--> sql server--> view ( i.e., excel worksheet reports). What surprises to me eve more is my refresh schedule was always successful. I do not see the refresh failed message.
I wanted both the power view and excel worksheet reports to get refreshed on a daily basis.
View 6 Replies
View Related
Oct 5, 2015
I need to create a data model for powerview report but I don't have data right now. The data will be loaded later after I create the report. What technique should I use??
View 2 Replies
View Related
Oct 28, 2015
I have a power pivot with 2 multi valued report filters student_branch & blood_group. These report filters are used to fetch the data set that contain below result set
student_branch blood_group count
Everything works fine. But, what i am looking for , is there any way to show the what are all the report filters that are selected currently by , separated in a separate cell ? below is the image for output reference.
View 2 Replies
View Related
Jul 25, 2014
Basically i have three Tables
Request ID Parent ID Account Name Addresss
1452 1254789 Wendy's Atlanta Georgia
1453 1254789 Wendy's Norcross Georgia
1456 1254789 Waffle House Atlanta Georgia
Bid_ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E Request_ID Parent ID
45897 Incentive 10 N/A N/A N/A 1452 1254789
45898 Incentive 10 N/A N/A N/A 1453 1254789
45899 Incentive 10 N/A N/A N/A 1456 1254789
Bid_Number Bid_Name Request_ID Parent ID
Q789456 Wendy'Off 1452 1254789
Q789457 Wendy'Reba 1452 1254789
Q789456 Wendy'Off 1453 1254789
Q789457 Wendy'Reba 1453 1254789
Q789456 Wendy'Off 1456 1254789
I want the Result
Parent ID Bid_Type Bid_Volume Bid_V Bid_D Bid_E AutoGeneratedCol
1254789 Incentive 10 N/A N/A N/A 1
1254789 Incentive 10 N/A N/A N/A 2
Bid Number AutoGeneratedCol_Link
Q789456 1
Q789457 1
Q789456 2
Request ID AutoGeneratedCol_Link
1452 1
1453 1
1456 2
View 1 Replies
View Related
May 28, 2015
I am using excel 2010 and SQL server as a data source. When I create a report with the User name "demo_user" which has db_writer access, and mail it to the colleague , he could not refresh the data with the "demo_user" credentials and apparently its throwing user name or password invalid. The one who created only could be able to refresh the data.
What should I do If other people want to refresh the data? How could I fix this issue? We are using SQL server 2012 and excel 2010 powerpivot.
View 2 Replies
View Related
Jun 26, 2015
I have a table with below kind of data,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ItemDate DATE, ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ItemDate, ParentItemName, ItemOrder, ReportId)
VALUES ('Plan', '2015-06-01', NULL, 1, 20),('Design', '2015-06-01', NULL, 2, 20),('Test', '2015-06-20', NULL, 3, 20),('Complete', '2015-06-30', NULL, 4, 20),
('Design child A', '2015-06-02', 'Design', 1, 20), ('Design child B', '2015-06-01', 'Design', 2, 20),
('Test child A', '2015-06-10', 'Test', 1, 20), ('Test child B', '2015-06-09', 'Test', 2, 20), ('Test child C', '2015-06-08', 'Test', 3, 20),
('Test grand child A', '2015-06-08', 'Test child B', 1, 20), ('Test grand child B', '2015-06-08', 'Test child B', 2, 20)
select * from @TBL
Here I want,
1. to display all parent with ORDER BY ItemOrder (no need to sort by ItemDate)
2. display all child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
3. display all grand child row right after their parent (ORDER BY ItemOrder if ItemDate are same, else ORDER BY ItemDate)
Looking for below output ...
View 3 Replies
View Related
Oct 19, 2015
I have below tables in my power pivot.Fact 1 & Fact 2 - connected directly to Mainframes - Data is from the same table broken up due to size of the data.Date Table - Relation ship between both the fact tables.How do i create a summary pivot to get the number of tasks that have been completed in each month.
Month Count
July
August
September
October.
View 3 Replies
View Related
Aug 17, 2015
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
View 3 Replies
View Related
Jul 20, 2015
I've imported a number of excel sheets into a Power Query Table. All seems to appear ok until I load the data. Of the 15k rows around 2k have a similar error where it cannot convert an integer to type string as below example
Expression.Error: We cannot convert the value 40 to type Text.
Details:
Value=40
Type=Type
The columns in question are all of integer type, I've looked through the M query and there is no conversion to string taking placeThe values where we don't get the error are also integers hence the intriguing question is why does the error occur on a subset and not the others. I suspect there is a limit to the number of errors also somewhere internally M query is converting the column to text for some reason.
View 2 Replies
View Related
Nov 18, 2015
Slow loading issue with an if statement. In the raw data the field [Location] is a text field e.g. 0010. I have a parameterised query that get a Location_Value from Excel and passes it to the PQ query using:
#"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
This works fine if you chose a single location. However I wanted to be able to select all locations and text is horrible to work with so in PQ I used the change type function to change the location column into whole numbers. I changed excel to also pass a number as Location_Value. I was therefore surprised when the same query took 2.5 times longer to refresh????
My PQ now looks like this
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Location", Int64.Type}}),
#"Filtered Rows1" = Table.SelectRows(#"Changed Type", each ([SalesMode] = 0) and ([SalesType] = 0) and ([Location] = Location_Value))
I'm wondering if I need to do something to the ([Location] = Location_Value) bit as maybe it still thinks [Loation] is text and it is trying to compare it to a number. I st assumed the step above meant that [Location] was now a number, but maybe you still have wrap it with some kind of VALUES or TEXT function?
View 9 Replies
View Related
Apr 10, 2015
I have data like below
Country State Rank
India Kerala 1
India Kerala 2
India Kerala 3
India Tamil Nadu 1
India Tamil Nadu 2
India Orissa 1
India Orissa 2
US Florida 1
US Florida 2
US NewYork 1
I have to generate rank like this in power pivot. How can I achieve it?
View 3 Replies
View Related
Jun 11, 2015
When updating a Power Query Source in Power Pivot, I'm getting the following error message:
Basically saying that OLE DB or ODBC-error occured when:
- Connections have been imported from a different workbook or
- the workbook has been created in a newer Version of Excel
None of which is the case here. What can cause this?
System: Virtual machine (VMware) on Windows 2008 R2, Office 2010, 64bit, using temporary profile.
View 5 Replies
View Related
Sep 9, 2015
I have opened an account in [URL] and taken the 60 days trial for power bi pro. I've developed power pivot and generated power views in share point 2013. But, I'm new to Power BI desktop. I have created a report in power BI desktop and published to [URL]. Also, I have uploaded an excel file directly to [URL] and created the report from the workspace available there itself and pinned the report to dashboard also. Everything is fine till this. But, I need to refresh the file which I have uploaded. I have some dummy data in excel sheet.
ZipCode State ZipName
2345 AA AA
456 BB BB
6787 CC CC
This has been created as a table and then added to data model. So, power pivot has been created for the same. Then I have uploaded this file to [URL] site. But, I'm getting an error message while trying to schedule refresh for the same.
"You cannot schedule refresh for this dataset because it does not contain data model connections. You cannot schedule refresh on worksheet connections or linked tables. To schedule refresh the data must be loaded into the data model."
How can I create a data model connection? How can I schedule refresh for an excel file like this?
View 3 Replies
View Related
Jul 25, 2015
I'm a relative newcomer to Power View. I've been playing with charts and have been struggling to combine both line and bar on the same chart. It would appear this functionality is not available. Considering this is basic functionality when it comes to charting, how to achieve this....
View 3 Replies
View Related
Jul 16, 2015
I'm looking to replace text in a given column given a set of conditions in the other columns. Please see below the M query in the advance editor and in particular the bold text. Here I've created a new entry that would appear in the query applied steps window in the power query editor that I have called "Replace Values". The logic is if Data.Column4 column equals "London" then replace null values in Data.Column5 with London. However when I save the query below I get the error
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
I plan to change the expression to test for multiple conditions however I need to get the basic expression working first. The other frustration i had with the "if" statement is it had to have an else even though I didn't require it, am i doing something wrong here?
let
FullFilePath = "C:PermanentDwellings.xlsx",
Source = Excel.Workbook(File.Contents(FullFilePath)),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16",
[Code] ....
View 8 Replies
View Related
Aug 26, 2015
I want to show on Power BI Dashboard a moving average - for example, I want to always show the last 30 measurement of body temperature but it looks like Power BI dashboard shows all measurements I have and compress them - which makes the dashboard ugly.
I tried to customize the X-axis properties but I dont know what I should change the default start/stop properties to (where the default property value is automatic).
[URL]
View 3 Replies
View Related
Jul 8, 2015
I have two data tables:
1) Production data with column headers: Key, Facility, Line, Time, Output
2) Costs data with column headers: Key, Site, Cost Center, Time, Cost
The tables have a common key named obviously as Key. The data looks like this:
Key
Facility
Line
Time
Output
Alpha
I would like to have two pivot tables which I can filter with ONE slicer based on the column Key. The first pivot table shows row labels Facility, Line and column labels Time. Value field is Output. The second pivot table shows row labels Site, Cost Center, and column lables Time. Value field is Cost.How can I do this with Power Pivot? I tried by linking both tables above to a table with unique Keys in PowerPivot and then creating a PivotTable where I would have used the Key from the Keys table.
View 5 Replies
View Related
Oct 13, 2015
Can I force the following measure to be visible for all rows in a pivot table?
Sales Special Visibility:=IF(
HASONEVALUE(dimSalesCompanies[SalesCompany])
;IF(
VALUES(dimSalesCompanies[SalesCompany]) = "Sales"
;CALCULATE([Sales];ALL(dimSalesCompanies[SalesCompany]))
;[Sales]
)
;BLANK()
)
FYI, I also have other measures as well in the pivot table that I don't want to affect.
View 3 Replies
View Related
Oct 14, 2015
I have a simple pivot table (screenshot below) that has two variables on it: one for entry year and another for 6 month time intervals. I have very simple DAX functions that count rows to determine the population N (denominator), the number of records in the time intervals (numerator) and the simple percent of those two numbers.
The problem that I am having is that the function for the population N is not overriding the time interval on the pivot table when I use an ALL function to do so. I use ALL in other very simple pivot tables to do the same thing and it works fine.
The formula for all three are below, but the one that is the issue is the population N formula. Why ALL would not work, any other way to override the time period variable on the pivot table.
Population N (denominator):
=CALCULATE(COUNTROWS(analyticJudConsist),ALL(analyticJudConsist[CurrentTimeInCare1]))
Records in time interval (numerator):
=COUNTROWS(analyticJudConsist)
Percent:
=[countrows]/[denominatorCare]
View 13 Replies
View Related
Aug 17, 2015
How can I apply "Min" formula under a "new measure" (calculated field) within a pivot table under Power pivot 2010?Can see that neither does it allow me to apply "min" formula directly "formula box" nor could find any other option.Intent formula: "=Min(1,sum(a:b))" this isn't allowed so all I can do is "=sum(a:b)".
View 3 Replies
View Related
