from
6 Ad Hoc Reporting
Explication de la tâche .
As a teacher I became passionate about the idea that if my students received good grades in my class it could correlate to them having better salaries down the road. I wondered how I could test to see if that was possible as well as gather interesting information on my students.
I sent out an online survey 3 years after my students graduated and asked them if they could anonymously fill a simple online Google form. This form contained information about their gender, ethnicity, hobby and other interesting control variables.
I’ve always wondered if I had an equal split of students? Could you check for me?
I’ve also learned while studying sociology that sports enthusiasts had better grades on average than the rest. Could you check if in my data this all checks out.
Finally, I’m very curious to check a theory my dad used to tell me for my celibate relationship status when I was in high school. He used to say:
“If you play video games all the time and you don’t leave the house James, you will never find a girlfriend.”
Can you test to see if he was crazy or a smart man for giving me this advice.
In short, This last exercise will require you to find the following answers.
Which gender is the most prevalent in my classes
If sports gives you a better average grade or average salary than other hobbies
And if playing video games affects your possibilities of having relationships?
🌶 Défi épicé . 🌶
Try to make this whole report into one page that holds slicers and macros that change the report to answer each of the questions and let you filter the report to show a filtered report.
Comment faire.
To help with exercise you will need to be comfortable with exercise 1 & 2 since it will require you creating a table and a pivot table. And Inputting the right fields in the ROW, COLUMN or VALUE area.
As simple text information to help what is required in this exercise. The simple questions are just a reminder to help you assimilate the knowledge better. As such I will only leave you some simple text explanation on how to achieve the objectives. If you seek to find some details on how to achieve these things visit the first and second exercises.
The first question will require you to drag the GENDER field into the ROWS and ETHNICITY in VALUES and build a PivotChart Pie Chart to display the split of genders.
The second question will require you to bring the HOBBIES field to the ROWS area and in the VALUES area set the SALARY and GRADE fields. Change their function to display averages.
The final question is a pretty simple one. It will require you to drag HOBBIES field in the ROWS area. Drag RELATIONSHIP STATUS in the COLUMN area. Finally, you simply need a count for the VALUES, you could use GENDER or any field for that.
For the SPICY CHALLENGE!
Now, this is where things get interesting. To achieve this goal we will use 2 extra tools to get everything working together :
Slicers
The Macro recorder
So here is how you can build a report like this one using Macros and Slicers
First Slicers. Easy stuff
You can add slicers to a report simply by clicking on the PIVOT TABLE and going in the inset tab and adding slicers.
Second Macros. Not so Easy Stuff
To add the required macro to this file you will need to get comfortable with troubleshooting yourself through the exercise.
The first thing you need to do is get all your charts and pivot tables working. You have to be able to produce them and understand what is required. You also have to build a design with a button for each question and some area for the slicers. Each Report will require a chart, so make sure to only have 1 PIVOT TABLE but 3 PIVOT CHARTS (one for each report)
The steps to produce these macros with the recorder go as follows:
START MACRO
SHOW ALL IMAGES
HIDE EXTRA CHARTS
CLEAR PIVOT TABLE
FILL PIVOT TABLE INFORMATION
* You cannot drag a field twice. You can only drag a field once into an area.
CHANGE TITLE CELL
SHOW CHART
FIX CHART ISSUES
SELECT A1
STOP RECORDING
Try it out to see if it works! Change your report and run it to see if it goes back to the original one. Once you have done it for one report. Redo these steps to fit with Question 2 and Question 3
Last step will be to assign all these new macros to all the buttons:
