all right so in this video let's tackle a very common problem that people have so
basically we have sales recorded in this table and every salesperson that we have is in this one
table so we want to create some reports but as we create our reports we want to make sure that
each sales person can only see their own data without being able to see other people's data
now you might be thinking now we need to create a bunch of spreadsheets and do import ranges and
make sure that we only get the data from one person and then based on that build reports
but what if i told you that we can do this with a single report using google data studio so to be
able to do that we need to first add a column to our data and the way we're going to do that first
let's just replace one of these wraps i'm gonna take olivia and i'm gonna just call it this guy
so that would be me in this case so i'll do match entire cell contents and just replace
all so there it is so then what i'm going to do i'm going to add another tab
i'm going to call it salespeople and what i'll do here i'll get a nice clean list of sales
people from here so i'm going to go here and do equals unique go to new data and select this
data right here drop the end reference close parenthesis hit enter and that's our list
i don't want this to be a formula i'm just gonna copy and paste those values only and that would be
sales reps name so what i'm gonna do in this next column i'm gonna add their emails
and that email would be the email they used as their google account
so let's just make something up
and this would be my email to be able to test this
so now at this point what i need to do i need to make sure i have this column over here in my data
so i'm going to create another column i'm going to call it email and i'll just do a vlookup to just
transfer those names over here so i'll just look up these and in this table f4 comma two
comma zero for exact match and this should allow me to just copy those names over to the other tab
now this only worked for the first one because i didn't do ctrl shift enter or command shift
enter it's not an array formula yet but before i do that i also want to just add an
if statement here and i'm going to say if this range of data equals blank then let's just leave
it blank otherwise let's just use that lookup just like that close parentheses ctrl shift enter
command shift enter to get array formula around this and hit enter to just apply this formula
so now if i just do command down see we have all of these emails so this is the column
we're gonna need to make this happen and those emails again have to match the right account
so once i got all of this done now i'm gonna go to data studio so that would be datastudio.google.com
that should get you to the right spot i'm gonna just create a blank
report
and the first thing we're going to do we're going to connect this to google sheets
and in this screen you're going to have to find the spreadsheet so this is my spreadsheet name
right here this is that particular spreadsheet so i'm going to click on that and if you remember i
had that new data which was my original data and then i made the sales people
which was that supporting lookup table we don't need that supporting lookup table it was just to
bring over those emails i'm just gonna click on this first one right here and click add
and add to report
so right now this is just going to bring over the entire data set and it gives me counts for
each region as you can see now we want to make sure that this only filters to the right
person so what i'm going to do i'm going to go back to this data source and click edit here
and right here there is this option filter by email i'm going to go ahead and click on that
and here i'm going to click on this filter data by viewer email and we're going to
choose that column we've created in our data set as the field which i named email
so now because we chose that as a user of this
report i need to grant access so i'm going to click allow
and if i done now this report should be only filtered to that person so let's just
do a different report here so this report that it created it seems to be a table report so i'm
going to click on this we're going to go here and we're going to add some dimensions to this
so i'm just going to go ahead and click on add dimension and we'll just
add some of those columns that we used to look at i guess i'll start with date
drag it up as the first column i'm gonna just keep adding some more columns
we definitely want that sales rep i guess so we can see that this actually works
and as you can see the only ones i'm getting here is the one that says
this guy because we filtered to that particular user and i'm just going to continue with this
and add a couple of other fields under metrics i'll go ahead and add sales
and probably cost of goods sold and i'm going to move this up and remove this record count there we
go now we can just resize this columns make them the way we like them we can redesign this thing to
make it look different see this one is doing 100 rows per page uh we can do more we can do less
it would probably be also nice to sort this by this date column to have the latest one on top
so i'm just going to scroll down and do sort and instead of coast of goods sold we're gonna
do by date and descending latest on top and it keeps going down and you could play around
with this as much as you like but if i just wanted to display the data for that person
this would pretty much be that i will change a couple of things with design i'll go to style
and for the header background i'm going to choose a different color
actually let's go lighter on that let's choose a different border color here you can also
color code to have different grid lines like this if you wanted to this is completely up to you
and this is our font color for our headers i think i'm going to remove this row numbers on the left
again these are completely optional things so one other thing i would like to do in case you
have blanks you probably want them to show as blanks instead of null so you can change this
instead of null to be a blank and that should be a regular table so this point if we just view this
report see this would show just the data for that person we would probably want to just resize these
columns to make them nicer we probably also don't need this sales rep column anymore because that
just filters to that particular wrap technically but if you wanted to keep that obviously you can
so i'm going to click view there it is so now i have the report for just me and if somebody
else logs in to the same report it's going to filter to their email and therefore it's gonna
be just their records so any other report we add to this we'll still use the same filter so
if i go back to add it if i want let's say a summary for our sales person right well i
could do it on the same page but i think i'm gonna just add another page here add a page
what i'm gonna do i'm gonna just do some sort of let's say pivot table put it in here and again
that's going to link to that same data source which is again just filtered to this person so now
i can just build my report so for my metric i'm going to go ahead and add let's say sales amounts
record count would not be bad too as you can see it tells this guy right here on top and that's
because i have column as a dimension here i don't really need that sales rep as a dimension because
we just have one but if you wanted to keep that you could obviously this breaks it down by region
for that data so if i just go back to that page one this is pretty much the data we're working
with so at this point we can decide how we want to slice and dice this maybe we want the report
by date or maybe you want a report by category i don't know whatever it might be i'm going to go
back to this what i'm going to do in this case i'm probably just going to have date as a dimension
and i'm gonna change that a little bit i'm gonna click on this edit go back to this
date and go under this date and time and maybe we'll do something like year month sort of thing
and i'm just going to say month here i guess or you can say year month
just a column name for this and i probably would like the latest ones on top
i don't want to sort this by record count i want to sort it by date and that would be
see this is the first dimension here this is the second thing i did in rows because i still kept
here region in rows and year here so we might want to keep that or maybe not i'll come back to that
but for now i'm going to scroll down and for the second one i'm going to go to this sorting and
instead of record count we're going to sort it by year month and we're gonna make sure that's
descending right there so one way to represent this a little better i guess would be to maybe
take the region and pull it below your month so it breaks it down by region for each year and month
and that should show that report
our sales amounts are here we probably want to represent them in some sort of dollar amounts
so i'll click and add it on the sum go to type and choose currency for this
there it is again you can restyle this do background
colors whatever it's still under here under styles
i guess one thing i'm gonna change is the size of this text here let's make it a little bigger
you might want this region here as a breakdown or you might also possibly want to just add it as a
filter instead that might be better but the main point here is that i can build my report and this
report would be filtered to just that person so if i go under view let me zoom out a little bit
and there it is now i have page one which gives me this data which is our more granular data
right here for that person i can go to page two i have my summarized report this doesn't seem
to be sorted anymore i thought i did sort it but maybe after i moved things around
that sort order broke so let's just take a look see right here now year month is the
first one so let's just switch this to your month descending so then latest should show up first
and you could add your slicers filters
dicers whatever else you need to do so i can add a control and add a drop down list here
and here i could choose actually region and remove the region from here possibly to make this a
little nicer so then i can filter by region if i wanted to but i don't want to see that
region break down here so let's just take a look what this looks like if i go under view see
we have region now we can pick whichever region we like that will filter our report
just like that and we could add another drop down maybe for category or something like that we allow
more flexibility on this another drop down and for this one i'm going to choose control field to be
category and here we can just go ahead and click view and you'll see i should
be able to now filter by category as well so if i wanted to maybe look at
certain type of dresses i should be able to do that so let's say we want to look at
formal addresses wedding dresses and some night out dresses or something like that
and finally we'll just edit this rename this again if you want to learn more about data studio
and how to manipulate these things i have videos covering data studio but this would be our sales
rep report and we could just share this one report for everybody and everybody would see their own
data and that should be it thanks for watching please subscribe and i'll see the next video