In this episode, Laura Rogers demonstrates how to create a dynamic Excel Web Access web part, by creating a web part connection to a library full of spreadsheets. She then teaches how to create a pivot table with a filter parameter in the spreadsheet, and use that parameter to pass information about the currently logged in user to the Excel Web Access web part as well. This can be done in the enterprise version of SharePoint 2007, 2010, 2013, and SharePoint Online with Office 365.
[00:00:00] Hello everyone and welcome to SharePoint power hour our this is our weekly show that we do every Wednesday at 11 Central where we stream live and talk about SharePoint Office 365 and business solutions. Business solutions are not a bunch of programming they’re not writing code and you’re not core server admins. All those other topics that fall into even. So, this is our sixty-eighth episode I think. So, we’ve got there are lots and lots of different topics in SharePoint that we’re able to cover, covering a wide span. Today our demo is going to be Excel web access, something else fancy that you can do with Excel Access web part. We’ve had one or two power hours in the past where we talked about Excel services but there’s just so much you can do with them and so we’re going to do another one. So, I just wrote a blog post and posted that about the thing that I’m about to demo and we’re going to go through and introduce ourselves quickly. If you’re watching on YouTube there’s a way that you can click the little yellow link on the YouTube video that will take you to the Google Hangout and in the Google Hangout you have a couple of little panels that you can look at. So, by default I think it’s just a little panel on the right that’s got what’s called the showcase and that shows links that we’ve shared in there but then it’s got some little squares at the top right.
[00:01:23] You can click on it and that lets you switch between acts so if you switch over to the Q&A app that’s how you can communicate with us and just say “hi”, say, “hey”, ”Happy Wednesday”, or ask us questions as we go. That’s sort of the fun part. That’s why we bring you this live so that we can have it be interactive and have you ask questions as we go and just interact with us and each other. So again, I’m Laura Rogers, SharePoint MVP, and this is my team at Rackspace. We do SharePoint hosting consulting on training and I’m going to get everyone in the room to go cross introduce themselves: Kevin then Ryan, Joelle, Stephen, Tanya
[00:02:08] I am Kevin who’s an architect here at Rackspace based out of Kansas City and been with Rackspace not a very long time but have been working with SharePoint for a substantial number of years since 2007. Microsoft’s solutions. Ever since 1995. So, sit around for a bit. My role here is to gather requirements figure out our clients’ needs and come up with the solution to deliver the answers to all their ill
[00:02:44] All right Ryan. Hi I’m Ryan Keller. I am the branding guy on the team here Rackspace. My job is to basically make SharePoint look a little less ‘SharePointy’ and a little more like what the customer wants it to look like. I’ve been working with SharePoint also since 2007, and I am based out of Colorado.
[00:03:09] Hi I am Joelle Farley. I am a SharePoint consultant on the business solutions team I am based out of Atlanta, I’ve worked for SharePoint since SharePoint 2007 was released and I mainly focus a lot on out of the box solutions as well as planning and design to things like information architecture search metadata all that good stuff. Looking forward to the sessions.
[00:03:39] Hi, I’m Stephen Wilson, I am also a member of the business solutions team. I do a lot of our installations and troubleshooting on the administrative side, as well as helping users sort of get started when they first get into SharePoint making sure that they don’t make some of the really big mistakes that we’ve seen people make in the past when we just sort of threw them into the deep water without any sort of guidance.
[00:04:06] All right.
[00:04:07] Tanya you didn’t know it but you’re next.
[00:04:12] Tanya Keyser here, I’m a new member to Laura’s team and fairly new user to SharePoint so I’ve been using SharePoint for a little over two years. I’m a SharePoint admin for our internal SharePoint forum here at Rackspace so I primarily work with end users and their help requests, everything from ‘what kind of SharePoint site do they need’, ‘how do I get this list working?’, ‘how do I edit a page?’. It runs the gamut from never before to trying to do very advanced features that we’re not going to allow on our internal forum. I also maintain the forum we’re working through some legacy migrations and then just some general maintenance and governance on the forum, and all that entails
[00:04:55] Awesome. I’m going to have to put you on the spot one of these days and they do like a pure point of adoption and helping end users.
[00:05:02] Is something like that. I also recommended practices are always a big topic.
[00:05:07] Yes. OK so the demo today I shared the link to it in a little showcase app there in the Google Hangout and the demo today is Excel web access Web Part and just something else cool you can do with it. So as we’ve probably seen in the past and I know if you might have seen one that Joelle did. You can use the Excel web access web part to display a spreadsheet on a page in SharePoint, and that is for people who have the enterprise version. This has been around since SharePoint 2007 so 2007, 2010, 2013 and even Office 365 as long as you have the enterprise version you can do the thing that we’re going to demo today. So this pretty much stands just about anybody could possibly be watching. Except if you get SharePoint 2003 and then I feel sorry for you. OK.
[00:06:20] OK
[00:06:21] So all right I think you can see it now re my spreadsheet everyone. Ok cool.
[00:06:31] All right so this is what we’re going to show you how to do. This is a page that I created in SharePoint with a library full of spreadsheets and basically a list of spreadsheets is on the right. And as you click between spreadsheet I mean the list spreadsheets is on the left and you click between each one. It changes the web part on the right to show that spreadsheet that you clicked on. So, this is an example for a customer where they had people putting on the spreadsheet and SharePoint and they needed just able to quickly flip through them. Now as a as a consultant and as someone who likes to use the best practices for everything I try to do in SharePoint I my first inclination was to say Okay where is this data coming from.
[00:07:21] Is there a more efficient way to do this than to have it in a bunch of separate spreadsheets should we use SSR ass or some other product or you know all these things were going through my head about different ways we could do something with this data and SharePoint but when we went ahead and just you know we’re working with the end users and we’re working with what they have at this point we’re not going to try and retrain them to do anything. So, we’re just going to make use of what we have and just do you know get that work in shape. So, I’m going to go ahead and show you how to do this and then I get to show you a bonus.
[00:07:56] I’ve got another little I logged in as myself on this other thing and I’ve got three of the arms running a controller machine one server and the other.
[00:08:11] So what I did on this one is the same concept except I added something to it. I made it so that whoever the logged in person is a put a pivot table in the same spreadsheet. And so, it defaults to like all products and not only can they flip between the different named ranges in here like here’s a chart and here. But I created this pivot table and it automatically looks at whoever the logged in user is on this page and sends that filter information to my data in the spreadsheet. So, I’ve got a column in my spreadsheet called sales person so this is like a sales report so it’s like the product the salesperson the customer and then you know some numbers. So, if so. So that’s something that you can use another fancy way that you can use. So, Web access and web part connections to be able to pass that filter information of who the logging user is so they can see stuff that’s the Civic to them. So, this is only for me as I go to the spreadsheet. Another thing that you can do that I’ll show you is you know you can have the same spreadsheet. This part in different places on the page so that they don’t have to flip between the views so that maybe they can see them. But I’ll go ahead and show you how to do the web part connections and then I’ll talk about different ways this can be accomplished.
[00:09:34] And don’t forget to you know ask questions as you go so can you guys see my screen. It’s got it says you are screen sharing and then it says kind a button that says stop in the sky button that says present to everyone. So that looks like it’s something new in the screen sharing. Ok I just went ahead and clicked present to everyone hopefully everybody somebody in the chat window shouted down so that you can see my screen. And I’m just going to keep going because I know there’s kind of a delay. All right I’m screen sharing and presenting to everyone. So here we go. All right suddenly create a new page. So just and I like web part pages as opposed to wiki pages. Or you know publishing pages just because they are less buggy. So, the quickest way that you can just go create a web page. You don’t you know you can have a whole separate library to keep those in but if you want to you can just go to your little out-of-the-box site pages library and in the file tab up here you can click new document and just choose web page. So, I’m going to do.
[00:10:41] The sales dashboard 3 OK.
[00:10:50] And I even ask the library. Thanks Jack. Thanks for the feedback. Even asking me what library I want to put it in as I’m going so that I can pick a different place sometimes I’ll create a library called dashboards and I’ll put them on their site. All right so now I’ve got my web page so I click Add a part on the left and then this is where I’m going to put my spreadsheets.
[00:11:15] OK so this is the case of this is the document library that I just I called it spreadsheets and I just put spreadsheets in it but you can call your document library rarely whatever you want to.
[00:11:30] All right. OK someone gave me some feedback there when I clicked present to everyone. It basically covers up all you guys at the bottom so it like fills up the whole screen so that’s probably a good thing right. You can pick your nose or do whatever you want to do. Now it doesn’t matter.
[00:11:46] OK so I went ahead and add the spreadsheet document library to the you know to the web page and this is pretty basic stuff so far. And of course, you could as you as you see that you can go change this view you know the view to show whatever columns you want to show there. All right so now on that click when I use this middle column to show my cell of access web part. So, I’m going to go to my business data category over here and choose Excel web access and. All right. So now it’s things like the workbook so what I would normally do is go up in the tool pane and go pick what specific workbook I want to show. But I don’t need to do that in this case. All I need to do is create the web part connection so I click this little drop-down box at the top right corner of the web part and I choose connections and I choose. Get work. You are out from. So, then whatever I call my document library mine just happens to be called spreadsheets. That’s the one you click on. Right. So what field in my document library is going to give me the you are Owl that I can pass to that other web part. So, I have to pick it set to choose document you are out to me you or else the magic wand that’s the one that’s going to say whenever you click on it’s going to see that you are out to the Excel of access web part. So, watch this so imagine.
[00:13:20] All right so now I go in stop editing and now there’s also a little just default functionality in the Excel web access part that sends the name of whatever your item is to be the title of the web part. So now even as I click through each one of these using this little double black arrow it even changes the title of the web part to that thing. So, you can see it here. I’ve got another got a chart in the spreadsheet. And then I let people switch between it. All right. Done. All right.
[00:14:00] We’re 14 minutes now getting this. So, we’re going to I’m going to go ahead and just add on a couple of things to this and I’m to show you. Go back over to my server and I going to show you some fancy stuff in Excel that I did because a lot of the times when I demo this it’s more or you know it’s supposed to be a SharePoint demo. Don’t get too much into the Excel advance stuff. But I’ve got plenty of time so I’ve got here my four spreadsheets I already opened them up on. You know just on this computer. And so, this first one is the line that I had the pivot table to get into the pivot table and the other ones yet because I want to show you how to do it. OK so this table is where all my data is. So, what you can do in Excel is when you select an entire range and I just I put my cursor in somewhere in this table and do a control shift. And that selects my table for me and I don’t have to scroll down to the bottom and find where the bottom is and all that stuff. So that’s a low.
[00:15:02] Just a quick trick for you guys to select your table and then I use to be an Excel geek so I don’t recommend all the time at some point years ago probably everybody probably all of you have at some point your computer nerd like me and then I go ahead and if I want to name something all I have to do is once I select that range I just go in this box up here and I just type what I want to be called that’s the named range box so I can literally go like type Pappy’s into something like this. And I could select those and I call it monkeyrange.
[00:15:42] So now I have it named range. So now when I go select monkey range it selects those cells so that’s how you name a range. That’s how I named this table. And then this chart has a name as well as sales data is what I call the chart see. Right. So that’s another named thing in Excel. All right so that’s how I mean those two things. And then I go delete this get of this monkey’s thing then. OK.
[00:16:12] So that’s how you name things. And then when you are putting something in SharePoint and Joellele did this in her other power hour that we did I don’t know Joellele can you put the link in the little arm showcase app straight to the other power hours that have that. So, to go to the little backstage view here in Excel and choose browser options. So, this is where I can choose. So, when people are viewing my data in the browser in so of access web are pretty much what do I want to allow them to see. So, by default it says entire workbook but I’d like to go in here and choose items in the workbook. And so, I can be more specific about what I want them to be able to see. And that’s what’s going to show up in that little drop down box at the top right corner of the web part. So that’s how you do that. And as soon as you’ve done this and when you say that to SharePoint then that’s all you have to do to make those things available. And the Excel web access web part. Those are all those will automatically be available in the little dropdown in the web part. So real quick Michel we’re that setting is you can control that if you want to. So maybe I don’t want them to be able to flip between the items in here.
[00:17:33] I only want it to show sales data and not the all products view. You can go get my page.
[00:17:42] And so first of all you can make that range you can uncheck the box and excel and make it not available in browser view. But another thing you can do is you can say I think it’s complex sales day. Keep in mind that all four of these spreadsheets have the exact same named ranges the exact same as pretty much everything the same amount except the actual data that’s in those rows. OK. My option in here though is the named item dropdown list so I can uncheck that if I don’t want people to be able to switch between the named things in the in the web part.
[00:18:22] So now that drop-down box is gone. So, I literally could put one part with sales data and other web part with that table in a range and another par with a pivot table or something all the same spreadsheet on the same page if I wanted to. OK so it is. So, I took the drop-down box away.
[00:18:41] McGraw I had to put it back in the check box that’s there.
[00:18:50] Yeah.
[00:18:55] I think that since I chose sales data as the default little you know named item that little named item in the web part properties that’s just you just must remember what you named it in the spreadsheet.
[00:19:11] Because it’s not going to be the web part is not going to give you a drop-down box of what your named items are when you’re putting that value in the web part. So, I just had to know what it was to type sales data in there but now I think when I flip the tween them all it should just show the sales data one by default for all of them since I put that is the web part setting. See us. No, it’s not doing that. So that’s fine. It’s just basically putting up the first one the default just the first one in the list up here. As I flip between the spreadsheets and since even though I put that in the web properties it’s not something I’m remembering as I go across. Good to know.
[00:19:51] All right so now all she had to do a little fancy parameter to pass the you can not only pass somebody’s name in here that it can be like a department name maybe you have a department call them Department departmental you know data in your spreadsheet area are you have a department or an office location or a city or state anything like that. That’s going to be a profile property that SharePoint knows about that currently being user or you can pass that information using the current user or filter into the Excel with access web part. So, go ahead. I did this in the Q1 when I had this give it a go ahead and do it in the Q2 spreadsheet do the same thing.
[00:20:36] So I’m going to go to my insert tab and choose pivot table and then I’m going to say I want my range to be this whole table on here that they are all I had to do is type all products apparently and then I’m going to say a new worksheet.
[00:21:01] So it’s going to add any worksheets so I mean drugs that are there.
[00:21:05] Don’t recall it.
[00:21:08] I think that’s what I call the other one. And then I have my information that I want to show in the pivot table off it like the products in the four corners and then here’s what I can do.
[00:21:20] I can take this sales person. That’s it. That was a column in my data and drag it over to this filter. So now look at that. So that’s where I can get my sales person dropdown so I can go pick Joellele.
[00:21:34] Oh Joellele you did awesome. You sold like $292. So there’s Brian don’t laugh so let’s see how you get it. So how are you guys names in your butt. This is my little my little fake people in my little fake stuff. All right so that’s how I can create a field that will have whatever value is that just happens to be a name again it can be a department or in a city or state or whatever. But that’s the value them to be passing from the current user filter web part to this. So that’s us we’re part to AutoMap the show up in use in user their stuff by default. So, what I can do is when I go to the backstage view again I go to browser view options. Here’s where my parameters tab is. So, what happens when I click that button there are no defined names in this workbook that can be used as parameters to set the parameters you must name the single cells that are getting values in the worksheet. So, I need to have a single cell that contains a value that I can use to word that one that that value in that one cell is going to be passed to it.
[00:22:48] So that’s why I must go make this one cell a parameter I must go see what I call it and then the other one so I want to try and make it have the same name here sales program.
[00:23:02] So I call it my sales program.
[00:23:04] So I’m going to just click on the cell right here all I must do is instead of it being on the one that helps better now look at that.
[00:23:14] Now wherever I am in the spreadsheet I click sales Parama up here and that takes me and now I have a cell a single cell that’s going to be my parameter where I’m going to pass that information to it. So, could it. I’ll put it back to everybody here. OK.
[00:23:30] So now when I go file and I go browser options I have parameters when I click to add it automatically sees that’s the only option it gives me to pick from because that’s the only named range that’s a single cell in my spreadsheet makes sense so far. Anybody have questions. And you guys asked me questions here 20 of you out that are there that are very quiet. OK so you don’t have a question just like say hello. Shout out say I love Excel or something just so. You guys are out there. OK so here a sale for him and I click OK I click OK and the spreadsheet is it exists in SharePoint and I can see right here that where it is it’s in the marketing site in my spreadsheets library. So, all I really do is have it is hit save. And now that saved my changes up to SharePoint so I can go do the same thing over here in my Q3 and Q4. So now if you didn’t catch what I was doing before I’ll go ahead and go through the steps again.
[00:24:30] I’m going to select my range. I don’t know if my little control shift now at that little short cut doesn’t work when you’re trying to select a range from within this little dialog box select mid-range products. I was going to type our products for the next room and try and remember to do that new worksheet and I call it the Hey thanks Deborah.
[00:25:00] Thanks for your feedback. Appreciate it. All right. And then again it depends on what you want to see in your pivot table. Totally up to you so you can make it display a salesperson in there.
[00:25:11] You want to get that the product in the four quarters. And again, the sales person I’m putting the filter. So that puts that little drop down box up there.
[00:25:21] Then I click on that box and call it and then I browser view options parameters Whoops I want to make sure it’s going to show my new pivot table to remember to do that.
[00:25:40] So maybe I can add my parameter up quick and then I’ll make sure that I’m putting my pivot table in these so that I can make sure that it’s displayed.
[00:25:51] So pivot table 4 is what it’s called Let me see what I call it in the first one because I think I gave that a prettier name than that. I call it sales that is someone to go back to my Q2 spreadsheet. And how do I name a pivot table so that’s actually easier than it is for the chart. So I just go to my little pivot table tools up here and then pivot table name so sales.
[00:26:16] There you
[00:26:18] And then so now it’s got a pretty name. So.
[00:26:22] So this is something if you’re going to be doing this you’re probably going to want to have do this once for one spreadsheet and have that be the same template that they use every quarter every month or whatever they’re creating the spreadsheet right like you’re not going to want to have to go through 100 spreadsheets and do all the stuff that I’m doing you’re going to want to have that all that stuff exists and have the right names and everything all ready. Right.
[00:26:44] So I can show you how to do that as you know.
[00:26:49] Now that was the right name. And then make sure that the parameter in this one.
[00:26:56] Yeah, I did that. OK. And save it.
[00:27:01] And then Q4 give it time. Insert pivot table.
[00:27:06] OK so our product is new worksheet I love troubleshooting.
[00:27:15] No live demo on that. All right.
[00:27:21] And then again product 1 2 three four in sales person is filter and sales hareem.
[00:27:33] So Frank OK now I go to my browser view all options and make sure I to get to get my pivot table the same pretty name that I gave all the other ones so sort of pivot table 6 sales David
[00:27:53] OK.
[00:27:54] And then browser view options. And now that’s available. So I could just go double check through. I you to save it you could probably just probably want to go double check through all those and make sure that I selected that and they would go in and select it on that one.
[00:28:13] All right. We’re good now.
[00:28:17] All right so now how do we do the current user filter thing. So this is my little sales to go back to my new dashboard that I created. Make sure to pick that one. So when I go to my site pages and this is my sales demo dashboard three this is the one that I just created for you guys. So right now I’m logged in the system account though over in my this is on my server. I’m going to go back when I put the current user filter on here I want to have some useful information in it because there is no account there’s no sales person call system account. So it wouldn’t show me accurate information. So I want to go do this as logged in as me over in this other.
[00:29:00] All right.
[00:29:01] So make sure go to the right dashboard page and if I want to take my sales demo dashboard page and put it in the quick launch real quick. Watch this. I can hit at it links. Just take the title of it drag it right here and say. So that’s just a quick way to take whenever dashboard you’ve created and stick it over there as a like. All right. And at page so what I’m going to use for this is call the current user filter web part. And I’ve got a lot of blog posts I’ve written over the years about the filter web parts that came out in SharePoint 2007 and they’re super useful.
[00:29:41] So the current user filter is going to pass the information about the currently logged in user by default. It has just like domain’s last username as was the value that it’s storing.
[00:29:57] But what I can do is I can go pick some other field about that logged in person pick the name and its name is going to be the first name last name and that’s the one that I’m going to.
[00:30:11] That’s the way the data is formatted in the sales person column in the spreadsheet. So the names are going to have to be typed exact same way in the spreadsheet that they happen to be type an active directory with the first name last name. So I’ve seen like people type like Bob versus Robert and stuff like that and it not work. So that’s one sort of caveat to this.
[00:30:33] Someone has said my filter value to my Excel web access web part and then it’s going to ask me what exact what parameter do I want.
[00:30:46] Actually I mean me not send it let me receive it because when I do receive the connection it gives me see gives me more options. Get that used for multiple print multiple parameters from. And then I’m just going to pick it that way for some reason when you’re creating web connections you get sort of different options depending on how you start from the which web part the one that’s consuming or sending the data. So now it automatically shows me that sales premiere’s the only parameter that I have in that spreadsheets. So it’s going to send the current user to the Excel web access web part and look at that case when we go and stop editing.
[00:31:26] All right so this is all products you does not have anything about. You know it doesn’t have my pivot table that looks at the currently logged in these are but I can go to sales pivot and look at that. So now it’s got me showing in here and I if I wanted to I could go change it to somebody else. And then when I go to Q2 I can go over to see I didn’t remember in the browser view options and I was trying to remember to make sure to check the boxes next to all of them in Q2 and I didn’t remember to check the box the sales pivot. So it’s not showing as an option in here.
[00:32:04] So let’s go to the three month the Q 3 1 doublechecked that sells it there it is.
[00:32:13] And so now it’s showing my Laura right there so that other one the one I have spent a little time making it kind of pretty and I made it so you know we had some pretty colors on it in my little sort of pivot table design that I had in Excel. So there’s my sales for that so it looks nice and pretty.
[00:32:33] Another thing I can do is you know how do I know when to use a current user filter How do I know exactly what it’s spitting out. What in the world is going on in that part because you can’t really see.
[00:32:47] Like I just happen to know that the name field has got the first name last name but I’m going to show you how you can go about testing this current user Filcher web part. I wrote a separate blog post about this. Also test it to see what it’s getting out. So I’m just going to go remove this connection where I’m sending it to the Excel web access web part and show you how you can just straight up just test it to see what is coming out of it. So many go. And another part called the on and I think a block or it was called testing the current user filter. What part. Somebody use the text filter part. Now ladies a lot of posts I wrote about this are really old but they you know it’s all still applicable it’s still in there. All right so you make your text fields tilter web part get default values from the current user filter that way whatever is in the current user filter.
[00:33:43] That’s what I’m going to see in the text filter where part. So that’s how I know that the name field is Laura Rogers. So I change this to something else in mind.
[00:33:54] At my current user to read part and instead of saying I’m going to put department that’s OK. All
[00:34:08] right so now I have to refresh it for a Tuason this new data and the web part. I’m
[00:34:13] just going to go refresh the page and now look at that I can see that information technology is that’s the way the two part Department is going to come across. So Michelle you another kind of just fun thing with the using the department and the text filter. And this is completely unrelated to the Excel web access web part. But since I have this on this page I want to show you. So what I did was I created a promoted linked list so the promoted links list is this in in 2013 an Office 365. It’s this little list of links that you can create. That’s got just you know it’s dynamic it’s got a title it’s got a little description and it’s got Place reason but what pictures are associated with it. So here’s what I did in my promoted linked list site contents.
[00:35:12] There you heard it from me. Ok so here the promoted links list and I know that I think you know we did a whole Power Hour right Kevin about promoting links. And Kevin showed us how you can do all kinds of fancy stuff with it to make it show certainly I’m not going to get into too much but I’m going to show you what I added to this which is going to add to this whole dashboard concept of showing someone’s stuff that’s specific to them.
[00:35:36] So what I did was I actually added a new column to this this is just a list of links. So go ahead and do modify those few. And I called it cantoso Department. So this is where I just typed the name. So whenever I create a new promoted link I have them put what department. That link is going to be targeted to like that. So and on the syntax that I’m using is the same syntax that we use an active directory so instead of I-T or whatever and this is information technology now has to match. So the can department is a column in my promoted links list. So what I can do is pass the information about the current user to the promote and links list.
[00:36:32] So when I go on this page ever says Laura we’re using web parts utilizing current user filters. Do you ever experience performance issues performance issues? No. I mean I guess that would depend on if you have like a large quantity of data. But I do experience when I’m working with a wiki page I find that those filter web part seem to disappear randomly. So that’s the. So Deborah yet I don’t know about performance issues but I know that they do randomly disappear off a wiki page and like this. I know this is it right here over on the right. This obscure little line. So that’s what I hate wiki pages because a little stuff like this where just kind of randomly disappears. But I would go and do the developer dashboard thing. If you’re having performance issues on a web page you can really dig in and see which web part is causing the problems and developer dashboard is something that you have to use the use power to be able to turn on. And it gives you like an extra little button at the top right. Gibble to show like this extra pain at the bottom.
[00:37:39] When a web page loads. It actually has all this data in it. About how long each part took to load and stuff like that so that might be something you might want to try to look to dig into from issues on a web page that will help.
[00:37:55] So if I go if I see I know up at the user filter apartment to the wiki page I can edit web part on some other web part and then all of a sudden look at that. There it is. So what I’m doing is I’m sending the in my current user filter web part.
[00:38:12] I picked the department as the field and I’m sending that as a filter.
[00:38:19] So this promoted links list is getting the filter value from the current user filter and the policies list is getting the filter value from the current user filter so it’s basically all these other web parts are getting that information from the current user filter. I can also put on this go back to my dashboard concept. I plan on doing a whole other power hour about these filter web parts. So I do I don’t want to get you know spend too much time on that.
[00:38:47] But let me go back over to my marketing site where I did these other set sales dashboards.
[00:38:55] So what you could do is you could actually put a couple of different current user Filcher web parts on that page if you wanted to pass different information to different web parts so I could have the in the name of the person being passed to this web part over here. And then if I have another spreadsheet or another you know little view of the same spreadsheet I could have another web part and have another current user filter passing the person’s name or department so I can do.
[00:39:23] I could put multiple ones of these on the page like one of them has a department that has their name one of them has their bosses name or you know just all kinds of different stuff like that.
[00:39:34] So let’s see. Any other questions about all this fun stuff that I did in Excel all these spreadsheets and how to make you know the ranges or the views or the park connections or anything like that or how you would use this in the real world was anybody getting ideas but you are getting ideas about maybe how what you could do. So I clicked the name on it actually opened a spreadsheet and any guys in the room who dealt with clients that asked for this type of thing or he’d see people using spreadsheets on SharePoint. Tanya do you see him point me out. I’m going to go ahead and put my stock chairman’s screen for from minute so we can chat about this.
[00:40:30] Tom’s OK. Thank you.
[00:40:34] All right. So Tanya you deal a lot with our end users and Rackspace and them you know each having little business solutions are trying to accomplish and you know seeing what kind of files they’re putting in SharePoint and things like that. So do you see this as something that maybe people have asked for or that could be useful for the particular departments you’ve seen.
[00:40:56] Yes. A lot of our users use Excel because that’s what they were using before. So they haven’t you know revamped a lot of their internal forms and processes specifically for SharePoint so they’re just working with the same Excel document even getting them to new stuff inside the web apps. Sometimes a challenge because it’s just not you know second nature to them. The concept of not having to download and edit a document in re-upload it is brand new even in 2015. So having some of these you know more advanced capabilities and out inside the browser would definitely be useful to them.
[00:41:30] Awesome. And we have a question. Josh any says General question about Excel or Access.
[00:41:38] What part it seems like any workbook data you update in Excel it automatically updates that the pivot table doesn’t update unless you manually refresh all from the data tab in Excel. Have you seen this before? I wish Travis was here because he would probably be better be a better one to answer that Tannis actually I actually did a whole other power hour about having data in Excel where with access part refresh and how to configure all that and where. So I don’t remember off the top of my head. It was some pretty complicated stuff he had to do in the spreadsheet in the connections portion in a spreadsheet to be able to set that up he had to do some special things and here he even had to do some special things like with the secure store in the account that’s authenticating and all kinds of stuff like that. So it was on the data tab though and the connections button in the spreadsheet. But I don’t remember that off the bat. That’s odd that it would do that only for those that I don’t remember off the top of my head.
[00:42:44] What why would it do that for the to the pivot table not the other stuff. I’m sorry.
[00:42:52] OK so if you don’t have any questions right now go ahead and demo the other portion that I mention is just using this in the real world and the concept that people are going to want to be able to quickly put their you know January data in a spreadsheet and not have to do all those little reconfigurations and things that just want to be able to save it as January spreadsheet and save it to SharePoint and not have to do a bunch of work to create that you know the ranges and all that every single month.
[00:43:23] So I’ll go ahead and do that part an inch screen.
[00:43:29] Stacy has a question in 2010 when you have an active connection and there’s no data displays an error is there a way to have a default to something when there’s no data and the user doesn’t see an error. The chip was broken it was doing that to me a minute ago when I was testing it and it was saying you know basically System account doesn’t exist in there.
[00:43:47] So I think that Stacey I don’t know a way of changing to not display an error but all I can think of is trying to account for having all the data correct or having some kind of replacement data if there isn’t a value in some field like having some kind of other default value like instead of sales person being blank have the word nun in there.
[00:44:19] Or little things like that but it would depend on what exactly the data is in which place it’s coming from to be able to figure out just kind of some solution that there isn’t really a way to configure here the error to be prettier.
[00:44:35] Let me double check that in the little web part properties but it pretty much the web part properties for this is just letting you know kind of what buttons and things you want to give people and available to them.
[00:44:49] One thing you can do though is if you don’t want it to display the default like the very first thing in Excel and Access web part when you know as soon as you load your spreadsheets it’s going out by default whatever however you have this sorted it’s going to show the first thing in here is going to be the connection that it sends.
[00:45:09] If you don’t want to do that you have a rule checkbox in your grief and there are no rules checkboxes to send web connection to send first row to connected web parts.
[00:45:29] When page loads. So you can uncheck that if you wanted to if you don’t want it to show whatever that first thing is. Or you can make sure that the first thing that it shows Stacy is a spreadsheet that you know has all the correct information in it like have it however you sort it have like January is the first line maybe. And make sure that Januarys always be correct so at least when they hit the page the first thing that gets passed to the web part is going to have the correct information.
[00:46:00] So what happens when I unchecked that box and then go here.
[00:46:07] And reload the page.
[00:46:14] How does that work. So it shows me a report. You can see none of these are actually connected and showing information.
[00:46:24] So in the web part properties itself I can set it to point to a specific file as you know the default setting in the web part. And then it’s not going to send a spreadsheet over there it’s just going to have you know just in the web settings. Whatever I pick that’s the one that it’s going to show there. That helps Stacey. Just let me know.
[00:46:47] But what I was going to show you with this whole template concept is going to file new.
[00:47:04] And this is just basically a content type of thing so this is where my spreadsheet would exist so product sales person customer 1 2 blah blah blah.
[00:47:21] And then I would have my little chart over here and I would have my pivot table on the other tab I would go ahead and name all my ranges and just not have like have maybe like one row in here to just show them where to put their product information. But this could be your template. So hopefully I can just say this to my best.
[00:47:42] Sales template snails right and then this is where you’re going to use a Content-Type.
[00:47:59] So I’ve got my template and then I’ve got to create a content type. So what I want to happen is every time somebody clicks to create a new file in this library I want it to be using that template so that they don’t have to you know get some maybe older version that might be on their computer or feel like they have to start all over or what hours. That’s where I can go into my six minutes my content types and I know that we’ve done other power hours on tintypes. You guys probably are into this stuff but of course it will spread. This is I know it’s not a document it’s a spreadsheet but document content type is like the generic for just like any office files that you’re creating. So I create the content type and then my advanced settings I go get that template that I just created on my desktop and I upload that in here. You could make it let it point to an existing template just directly to your elbow if you want to but I’m just uploading it into my content type. And then from then on in my library so I have my library called spreadsheets and we to my library settings and Vantz settings to turn on the ability to manage content types.
[00:49:20] Click OK and then I’m going to add from existing site content types and this is going to be my spreadsheet.
[00:49:28] And I just created and now and I can even like change just button so it doesn’t show that other one anymore. And now when people go to my library to start filling out a new whatever their monthly spreadsheet is now it pulls up that template that I already created.
[00:49:48] So I don’t have to. Again all the work is done with the parameters in the named ranges and all that stuff and all I have to do is fill it and paste in the data or whatever they’re doing.
[00:49:58] OK Stacey says she’ll try that she has a project that’s connected to a pivot chart for status of an amount invoice to date when there hasn’t been anything invoice there’s no data in the pivot chart displays the error rather than a blank chart. I’d have to. Yeah, you’d have to maybe have zeros in there by default or something like that. Maybe so that at least have a value.
[00:50:20] That’s all I can think of. Cool. Well thanks. Any other question that has stumped me sharing my screen again. OK. Anybody else out there have any questions. If not.
[00:50:35] Again every single Wednesday at 11:00 Central we have SharePoint power hour and any announcements you guys can think up. We have SFE tech coming up. That’s the SharePoint technology conference.
[00:50:48] This time it’s going to be in Austin Texas which will be a lot of fun that’s a new venue for them and that’s going to be February 8 through 13 8 8 through eight through 11. So I don’t like that yeah 311 and I will be there and several of my coworkers will be there.
[00:51:09] And it’s going to be a lot of fun. April there’s a conference call of evolution’s conference in London.
[00:51:17] They’re actually having a contest too far to get news and new speaker to I think that where they’re going to pick from entries as to just kind of whoever wants to try and be a speaker they’re going to be there though it’s going to be in London in April shipment evolution’s conference and then in May is the big Sure the big Microsoft ignite conference where they took the SharePoint conference and exchange in like Lincoln you know all the different product conferences Office 365 and everything and crammed together in one conference. And that’s going to be like May 4th through eight weeks.
[00:51:55] So the Zermelo announcements. One more little comment from the gallery here. Frank says you just got a site request today that they can utilize for updating a shared Excel document among other things so they’ll be able to use this demo right away. That makes me so excited. Awesome Frank. I’m glad you have fun with that too. So that’s the one thing I love about this demo is that it applies to pretty much everybody because you can do as long as you have the enterprise version. Awesome. Thanks Deborah. Cool. OK we’ll I’ll let you go again. I will be here next week because I’ll be teaching my online SharePoint power user training class all week long. SharePoint are next. They start concept training and so out I think Joelle’s doing them on next week but I’ll be back the week after that. Larry have a good week.
