Here is my scenario, we have daily history tranaction table with entries of all product sold, we have 30 day history in that table, user typically research by day and list of productID. 30 day history table right now is about 3 million records.
I create view at database, then powerpivot 2010 is used to import that view. I can tell user to do filter by day during the import (say 06/18/2013), this will import records for that day.
However users also want to search by list of product by product id, sometimes it is just 20 product list, sometimes it is 1,000 productID.
Is there a way in PowerPivot that user can drop list of productID, so they can get the result back?
- Changed type Ed Price - MSFTMicrosoft employee, Owner Friday, October 25, 2013 8:08 PM No response
You can bring all records in Powerpivot for Month to current date or for a day.
Create a dummy Search table in Powerpivot to hold ProductIds joined to ProductId of Dimension/fact table you are bringing in Powerpivot model.
Populate/refresh this table from Excel sheet or whichever way you want user maintain the list of product ids they want to see results for.
You can also maintain a similar search table in database and provide a simple frontend for users to update the same. Then you can bring both tables from database to powerpivot for reporting.
Use Search table productids in filter of pivot table/power view report.
Thanks for quick reply. Yes, I import fact fact table into Excel by filtering only current day.
I also create Excel worksheet with one column "ProductID", with all productID as you advice here. I import ProductID worksheet into Main Fact table. Then I join productID of my search table with productID of main fact table. Somehow it is not working when I drop ProductID talbe as filter. Nothing gets filter out.
Where do I miss?
I think I find the issue, when link the search table in excel into powerpivot, then drop as filter in pivot table, it seems there is blank or null value at end of the list, so if I uncheck that null, then it works.
Wondering how to get rid of that.
- Edited by Dz0001 Thursday, June 20, 2013 4:18 PM find issue