I need to create a report with indicator columns. Basically if some criteria is true for a record then X is put into that column for it otherwise leave it blank. The report will have multiple indicator columns.
Here is a simplified example.
Create a report the lists customers who made a purchase in the last month. Include a column that indicates whether they bought Widget A. Include another column that indicates whether they bought Widget B.
So far the only solution I can think of is to create a view in the universe that lists customers who bought Widget A and another view that lists customers who bought Widget B. Then do a left outer join on them. However, I would prefer not to create views like this in the universe. The reports request that came in are fairly varied and I would soon start having a lot of views cluttering up he universe.
Is there a way to do this within the report designer?
I merged and extended but I can’t figure out what to do next.
I added the merged dimension (customer id) to the report. This simply duplicated the customer id in a second column. Next I tried creating a variable and tried checking against null.
=If(IsNull([CustomerId])) Then “X” Else “”
This give me a blank for every record. Of the 900 records in the report there should be about 700 with an X. Next I tried this.
My database doesn’t have a quantity sold field. So I created a variable QuantitySold. I tried making it a detail variable. However, it kept changing it to a measure.
So I made another variable that checks if my new variable, QuantitySold, is greater then 0 and made it a detail variable. I tried making it a detail of several different things. In each case when I added it to the report the result was always a blank. I never got an X.
I thought I’d try to replicate this in e-Fashion but I don’t see a customers or a invoice table.
Yes. However, maybe at this point we should switch from the simplified example I came up with to what I’m actually doing.
I need a report that lists student in Fall 2011 who didn’t return in Fall 2012 or after that. The report is to include demographics and three indicator columns showing whether they took prep math, prep reading, or prep math.
I currently have 4 queries. One to get students id in Fall 2011. One to get students ids in Fall 2012 and beyond. One to get students ids who took prep math. Then the detail query. The detail query gets details for students in the first query but not the second. Then I’m using the merge and extend to try to get the indicator flags in the ‘took prep math’ column. I’ll be adding 2 more queries to get prep reading and prep English if I can get prep math to work.
When I see this, my natural tendency is to break it down into simpler problems, and then work from there.
So, the first hurdle here, is getting the list of students that were there in Fall 2011 and didn’t return in Fall 2012. Do you have this list (correctly) generated in your report so far?
Once this is done, adding the demographics and the flags shouldn’t be too difficult.
If you haven’t generated this list, I would think that using a combined query with the minus operator would be the best way to do so. The only object returned in this query would be “Student ID”.
Yes, I have the list of students correctly generated. I’m working on trying to get the indicator columns added now and I’ve made some progress.
Previously I had two variables
Count of Prep Math (measure since it won’t let me make it detail)
Has Prep Math (detail of student id)
Now I have three variables
Prep Math (details of student id, the formula is simply =[Students Who Took Prep Math].[Enrolled Course Name])
Count of Prep Math (count of the variable Prep Math)
Has Prep Math (measure that checks if count of prep math > 0)
With three variables setup like this, I get the X’s in the right places.
Unfortunately, when I repeated this for prep reading and prep writing the X’s in the 2nd and 3rd column show on the exact same rows that they do for prep math. I’ve triple checked that all my formulas reference the correct queries/variables but I so far can’t find another wrong.
Oh, I figured it out. When I added the Prep Reading and Prep Writing queries it automerged the course name field from the three queries. So whether I used my Prep Math, Prep Reading, or Prep Writing detail variables it always returned the same combined list of course names.
I deleted the merging of course name and it now works perfectly.
(BOB member since 2005-01-06)
(BOB member since 2005-01-06)