Initially, this exercise likely feels pretty overwhelming. What this crazy instructor expects me to take eight tables of different types and turn it to this, I don't even know where to start. Well, don't forget that I've tied one hand behind your back, because honestly, this would be easier with some of the other commands that we'll cover later. But even so, this is a pretty easy task if you do just a little bit of pre planning. For that pre planning. Let's start on the Report tab and group various pieces into their similar components, where each component will define our work breakdown.

As I highlight items, I'm going to highlight the appropriate query source and we'll use that to direct our efforts. First, let's start with revenue. revenue will come from revenue or original source. So I'm going to highlight both of these yellow and notice that revenue is split across department. I'm going to highlight all three entries from the report in yellow to signify that they come from the same place. Next is inventory cost.

Since inventory cost only has a value during the month just like revenue, and it's split across all three departments. I'll highlight the inventory cost entries and its query in the same yellow. This will tell me that I'm going to construct a single answer that combines these two. But I'm not quite done with the yellow color. The labor cost follows the exact same pattern is our revenue and inventory cost. So I'm going to highlight those in yellow as well.

Our next entry, the department profit is just a little different. It follows the same pattern as the prior three. However, it does not have a source query on the terms page. The department profit is defined as the sum of the revenue, inventory and labor costs. So we're going to calculate it. And since that's the case, we'll go back to our report, and I'll highlight it in light blue.

The final departments split item is our average revenue over the last 30 days, which follows a different pattern from our prior entries all together. There's no during month entry, only a start of month and an end of month. Finally, there's no source query for this either. Which means that we'll have to calculate it from the revenue source query. So I'll highlight these lines green, and put a little green highlighted marker next to the revenue original source. This covers all of our department entries, but it leaves our corporate section.

Unlike the other entries, these will not change across store numbers. So I'm going to highlight this section plus the corporate figures query in orange. This is covered all of the queries except for the report structure, corporate section, store number selected and operating month selected tables. So what's the plan for them? Well, starting with our selected tables, these contain our drop downs. And this is one of my favorite ways of using the get and transform queries to create dynamic filters with table merges.

I'm going to circle the operating month in a dark red box. And since all of our original source queries, corporate figures, inventory cost, labor cost and revenue are described by operating month, I'm going to circle all of them in the same red box. The store number is slightly Different. It applies to all of these tables except for the corporate figures. So I'll circle these three in green, and the store number select the table in green as well. Finally, this will leave us the layout tables.

Let's flip to the report layout table tab and see what they look like. As their names describe, they have ordered the fields from one to 24 with blank entries to find a make recreating the desired layout fairly easily. Rarely would such tables be provided to you in a real world scenario. So you generally be responsible for creating these yourselves. Since this is not necessarily an obvious step, I've created them for you to give you the bones of the report, you could say. Basically, we're going to link the solutions for each of our highlighted tables into these with a merge to populate the Phantom started month during month and end of month fields.

So I'll circle these queries in black. So we spent about five minutes outlining our general plan which is the hard part. The application is the much easier part to keep this video from getting too long. I'm going to run through the application of our commands pretty quickly and describe what I'm doing along the way. I'll begin with the yellow tables top to bottom, beginning with our inventory cost. And for all of these, I'm going to reference the query to leave the original unaffected.

My goal with each of these tables is to get a during month value, a department column, a store column, and operating month column and an attribute column limited down to my selected values. The attribute will describe what the value is in the during month field for this table, that means that I need to unpick it, do some renaming, perform another on pivot and do some more renaming. And then two pairs of merges, and both of them will be inner merges. After doing the merges, I can get rid of my unnecessary fields, renamed my value as the during month field. And now my inventory cost is complete. So I'll rename it inventory cost calculated, and I'm going to close and load it into a connectional Now it's time for my labor costs.

I don't care about any of the individual employees, just the total at the end. So I'm going to choose the columns, door, department, current month and total, because everything else is unnecessary. And now I'll add my group by and I'll use store department current month, I'll get the sum of total his labor cost. And once here I'll unpick my labor cost column renamed the value to during month and the final step here will be to add my merges again, I'll clear out my unnecessary new columns. And I'll rename the query as labor costs calculated. Again, I'll close and loaded into a connection only.

The next table is the revenues which actually has three outputs that we need of the whole exercise. This is probably the hardest part. So I'll take my time a little bit more here. We want the revenue generated in the current month, the average daily revenue for the current month and the average daily revenue for the prior month. The common differentiator among these Is the operating month filter. So with that in mind, I'm going to create a query tree of sorts the begins off of our revenues table, but then splits off into two different results.

I'll start with a group by on store department sale month and prior month. Now I'll add two calculations revenue as the sum of total sales and average revenue last 30 days as the average of total sales. Then I'll apply a merge to the store field only since I don't want to filter out months yet, and of course, delete my unnecessary new column. And I'll follow this up by and pivoting my revenue and average revenue fields. Now I'm going to rename value as temp calc for a reason that you'll understand shortly. And I'm going to call this z revenue pre counts and load it into a connection.

I'm going to reference the Z revenue precalc to create two separate queries, one for the current month and one for the prior month. This first one will filter on the current month, and then I'm going to delete that column. Then I'm going to rename it current column calculation end loaded into a connection. I'll re reference the xe revenue pre calyx again. And this time I'm going to filter on the prior month. I'm going to rename the value field as start, delete my new column.

And now I'm going to append the revenue current calculation table, filter out the revenue from the attribute field, run a group by on store attribute department and then create start of month as the sum of start and end of month. That's the sum of temp calc. So I'll rename this as revenue average calcs and close it and load it into a connection. My last revenue query will be a reference off of the current calculation, I'm going to filter out the average revenue for the last 30 days and rename the temp calc during month. And then I'll rename this as revenue during calculation and load it into a connection. This covers the yellow and green highlights, at least their base calculations.

So now we need to calculate the light blue department profit field. Now this is the sum of the during month column from three of our queries, so I'll reference the inventory cost to begin. Once here, I'm going to append my labor and revenue calculations, and then do some quick cleanup by department columns need to be merged, then my attribute columns need to be merged. Now I can clear out the other unmerged columns since I no longer need them for any filtering, so I'll choose them out. Now I could sum these up by department and get my department profit, but then I would need these fields again. So I'm going to create another little mini query tree.

This is going to be my z departments during calculated query. So I'll close and load this into a connection only. Now I'll reference this connection. And the first thing I'm going to do is I'm immediately going to do a group by I'm going to group by the department I'm going to make a new column department profit, which is going to be the sum of during month. I'll pivot this field, rename the value to during month and then I'll append the table that we originally referenced are during departments calculated table. Again, I have a little bit of cleaning up to do since my attribute field was poorly named swell, merge them.

And I'll make sure to clean up the name department this time. So now our data is really starting to shape up. As this table contains all of the during month values that we need by department. I'll rename the query department values in Close and Load it to a connection. Our last step is to bring our during values together with our average values. So I'm going to reference my C department values.

And then I'm just going to append our revenue average calcs table again, I have to fix my attributes. And again, I don't need my store number anymore. But now with a little bit of reordering, we have what looks to be the first part of our report. So I'm going to change this name to z departments complete and load it as always into a connection. The last major section is our corporate figures. All we need to do here really is filter out our month field, delete our new column, and then pivot on our timing field.

We'll use the figure for our values. And now we can get rid of our month since we don't need it anymore. renamed during to during month start to start. Start with month and end to end of month, I'll change this name to corporate calculated and loaded into a connection. We've done a ton of reorganization. But the end is now in sight.

We have two main queries, our departments complete, NRZ corporate calculated, that gives us all of the value fields that we need. Now we just need to line it up with our layout tables. So let's go ahead and create them. We'll start by referencing the report structure table here, I'm going to do a merge. And I'm going to merge it with our departments complete table. It's going to be a left outer join.

And I'm going to join between department and field and department and attribute by holding CTRL. I can line up one in one and two and two, so that it merges on both of them. Now I need to expand my new column and I only want my start during an end of month fields. I'm going to rename this as departments formatted and loaded again into a connection. I'm going to do the same thing for the corporate section layout table. I'm going to merge With our corporate calculations, this time only on field and corporate calculation, again, this will be a left outer join.

And I'll expand the new column to again, get my during start and end of month fields. I'm going to append our departments formatted table. And now I'm going to order by the line number a sending, suddenly, we have what looks to be our report, except it's not quite right, our fields are still a little out of sync. So I'll reorganize them. And then I'll rename them to get rid of the new column dot and drop our line numbers since we don't have that showing up in our report. Oh, and I also need to rename the department field again, I'll rename this to z report, and I'll close it and load it into a connection.

That's my final result. So I need to load it into a table. I'm going to clear this table, select cell D eight, right click and choose load to table existing worksheet D eight. Once it loads, I'm going to apply some adjustments to the format and suddenly we have what looks to be a report. So let's compare this against some of our solutions. First solution is our current selection, operating three, store number 774, we're just going to look at the box good department profit to make it quick.

So this has 4090. And our solution has 4090. So that looks good. Now we can change this three to five, and the 774 to a 0554. And now I'm going to select in the table, right click, and choose refresh. Doing so updates all the calculations using these two new filters that we applied at every step of the process.

This gives us a department profit of 1111 180, which ties that to our 1111 180 there. And with that, we've created a fairly simple management dashboard as part of our exercise. Now we've only used a handful of the tools so far, and we'll cover far more in the following chapters. But even still, we've taken five different data sets and turned it into a report that combines all those data sets in a consistent and controlled manner. Hopefully you've had success completing this on your own however, If you haven't, try doing it again after having watched this video to see if you can get a grasp of how to start applying these tools to different problems to bring information together and create usable results. Thanks and I'll see you in chapter three.