I’m trying to create a report where I need some anonymous information. For example, I’ve got 4 students, and I want to create a report that shows the grades and calculations. But I don’t want to show the names of each student, but a number: 1, 2, 3, 4. I can use RunningCount to create a list, but I want to make sure this number is the same in this specific report. I might have 4 students in a class, and I’m running a report with multiple classes and multiple students. One student might be in 2 of the classes that I’m running, but not in another. So I can’t have RunningCount restart the count on the class because it would easily end up where number 1 in one class isn’t the same student as number 1 in another.
There isn’t a value like this that I have access to in the Universe at the moment, but it’s something we’re looking into.
I can easily create a list of all students in another report tab, but as far as I’ve seen, there isn’t a way to reference that specifically. Maybe there’s a way to create the formula that uses that context of that formula?
We’re using 4.2, SP 8 Patch 9. Been using this for a bit over a year now, and there is so much more to learn. And tricks that I haven’t found for oddities like this.
Why don’t you create a calculated field or column in the Universe which can give you a unique rownum number or something, if you don’t have one coming from the database? Or you can actually create a Derived table with just Student Name and Rownum.
And if you want to make sure that the Unique ID or rownum remains the same for a specific person in subsequent refreshes of the report, then in the logic of creation of that Unique ID, you need to sort the Student Name based on the creation_datetime of that row in the Students table.
Derived table SQL could be like below:
SELECT
ROW_NUMBER() OVER (
ORDER BY creation_datetime, first_name
) row_num,
first_name,
last_name,
city
FROM
Students;
If this number needs to be consistent across reports and instances of the report, you need to add a unique identifier to the database. That is the only way to ensure the identifier remains constant.
With any type of calculation done at the universe or report level, the value is subject to change. Even a row number calculation. If you add or remove a student, the row number will change. You could try using a combo of name, dob, or other fields to create an id, but again… if the data changes, your id changes, plus it won’t be de-identified.
How does your database join parent/child student data? Maybe there is a unique id that just wasn’t added to the universe? If so, adding a field should be an easy task.
If there isn’t an ID field and you absolutely can’t add one to existing tables, can you create a table that stores the student info and a static id you create/autogenerate? Or create an Excel file that with the information that you can use as a data source? Neither is ideal and you have to be very careful joining student data on just name and DOB because though unlikely, it is possible to have two students with the same name and DOB – I’ve actually seen it happen!
Thanks, both of you. I’ll get together with some of the others in my group that know a lot more about Universe work. It’s not something I’ve delved into before now. I suspected that would need to be the way we handle it, but there are definitely creative methods to solve problems, and I thought I might be missing that.