Help with multiple items in Drop Down List

[Neal Sonnenberg]

Hi all,

Hope everyone had a nice Thanksgiving - I always learn a lot from this group so wanted to ask what I think should be a relatively easy question.

I am developing a simple Looker report for Course Selection for our district.  In my source spreadsheet, I have a grade column that lists the possible grade levels that are eligible for a course - so the grade might be 9th grade only or it might be 9th, 10th, 11th etc (See picture below).

I want users to be able to select the unique grade level (i.e 9 or 10 or 11 or 12) from a drop down list in the Looker report but I am not sure how to make this work.  Basically there are 3 questions/ problems:

1. Do I need to modify how I enter the data in my source spreadsheet?  (Is comma separated the way to go?)
2. When the data comes over, if it is listed in my spreadsheet as 9,10 or 9,10,11, it comes over as null/blank in my report (See the business courses).  I have tried bringing the 'grade' field over as both text and as a number and neither appeared in the report
3. Most importantly how do I get my 'Grade' control box to list the 4 grades (9,10,11,12) and then select all courses that meet the criteria?

Here is the demo report I have created (it's still a work in progress, I know there are many things that still need fixing).  

While any solution would be appreciated, I am certainly not an expert in Looker, so the simpler the better ..

Thank you in advance for your help .. 

Neal Sonnenberg Technology Integration Specialist, Medfield Public Schools (508) 359-4367 x11063 |nsonn...@email.medfield.net pronouns he/him/his     MassCUE Featured Educator - January 2019

Get a signature like this: Click here!

This electronic transmission is for the intended recipient only and may contain information that is privileged, confidential, or otherwise protected from disclosure. Any review, dissemination, or use of this transmission or any of its contents by persons other than the intended recipient is strictly prohibited. If you receive this transmission in error, please notify the sender immediately upon receipt and delete or destroy the communication and its attachments. Thank you for your cooperation.

[Laura Tilton *]

Hi Neal, here's an example where I did something similar. My solution (this was a while back, before I knew about parameters) was to use a text entry control using the Contains option, so the user could enter the grade level to filter the list.

If you really need a drop down list, another option would be to create a parameter with the desired grade levels. You could then create a calc field called "Include" that looks to see if the selected parameter value is in (CONTAINS_TEXT) the Grade field, and if so return a 1, if not return a 0. Then put a filter on your table to only show records where Include=1

How is your Grade field formatted in Sheets? That may be why it isn't coming over correctly. I'd format it as Text and bring it over as Text. (It's probably a number which is why single-grade values are coming through but the multi-grade one aren't.)

If you need more detail let me know but hopefully that gets you on the right track!

Laura

--
You received this message because you are subscribed to the Google Groups "Data Studio in Education" group.
To unsubscribe from this group and stop receiving emails from it, send an email to googleschool-data...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAPhsf5MqdOqBdLJfXioEqDjzaRRUVpY8zne9deFtiJ1c6%3DcXEQ%40mail.gmail.com.

--

Laura Tilton

Education Data Professional

Twitter: @tiltondata

[Paczkowski, Kyle]

Hello Neal, 

Laura's approach of using a text box for the user to enter the grade is a nice solution. But if you are using contains how does it differentiate when someone searches for Grade "1" that 10,11,12 aren't included. 

1. When the data comes over, if it is listed in my spreadsheet as 9,10 or 9,10,11, it comes over as null/blank in my report (See the business courses).  I have tried bringing the 'grade' field over as both text and as a number and neither appeared in the report

I've encountered this issue before and what Laura mentioned about the format was the issue, but it is the format in your original data source. If you are using a spreadsheet make sure that column is set to text instead of "Automatic" even though it looks okay to you when data studio brings it in it only reads one data type. So formatting them all as the text will bring in all the values properly. The single grades as text as well as the comma-separated as text. 

Kyle Paczkowski

Data Quality Analyst

Office of Data and Accountability | Boston Public Schools  

2300 Washington Street, Boston MA 02119

kpacz...@bostonpublicschools.org

Data Request Form

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAO_um7tC2_WHC6w533OyTkfUvUuuxV4K-uy31E-Ej-Z4Kv3H8w%40mail.gmail.com.

[Anthony Claypool]

I concur with Kyle re data type, and one additional idea to piggyback off of Laura (which may be a solution, I'm not 100% sure) is to use CASE WHEN arguments that cascade through options that address the strings that contain multiple values first.

CASE WHEN REGEXP_CONTAINS(Grade, "11") THEN "11"

WHEN REGEXP_CONTAINS(Grade, "10") THEN "10"
...
WHEN REGEXP_CONTAINS(Grade, "1") THEN "1"
...
ELSE NULL END

To view this discussion on the web visit https://groups.google.com/d/msgid/googleschool-datastudio/CAHOJ1%3DfsYQVWzxb3_1ycT0zcyr-Rca%2B8uoowsjn6z6uG1%3DbJFA%40mail.gmail.com.

--

Anthony Claypool Director of Data and Assessment, Distinctive Schools (312)332-4998   www.distinctiveschools.org acla...@distinctiveschools.org 910 W. Van Buren Street, Suite 315, Chicago , IL 60607 IMPORTANT: The contents of this email and any attachments are confidential. They are intended for the named recipient(s) only. If you have received this email by mistake, please notify the sender immediately and do not disclose the contents to anyone or make copies thereof.