Group data affecting Sum total of unique count

Hi Freddy,

Thanks for reaching out.

The version/build can be found under the Application Details section in your info.jsp file, which can be accessed by appending '/info.jsp' to your login URL. For example, localhost:8080/info.jsp. You can also find this info under Administration > System Information.

In terms of the issue at hand though, probably the best way to begin troubleshooting this would be by comparing the resulting SQL queries for each report setup. Can you supply both the SQL query for when you're seeing 792 results as well as for when you're seeing 738? If they differ, when you run the queries directly against the RDBMS, do you see the same results as you see in Yellowfin? You can find the SQL query either towards the bottom right-hand size of the report where it says "View SQL", if in the Data stage while editing the report, and if the report is already published or you're in Design stage when editing, you can click the 'i' in the toolbar > SQL Statement:

Please let me know what you find.

Regards,

Mike

Hi Mike,

Thanks for the pointer. I may have to redact some of this but here's an example of the SQL when the results aren't grouped by date:

SELECT DISTINCT

"[view]"."session_start_date",

COUNT(DISTINCT("[view]"."user_booking_id_text"))

FROM "public"."[view]"

WHERE (

"[view]"."session_start_date" BETWEEN '2017-10-01 00:00:00.0' AND '2017-10-31 23:59:00.0'

)

GROUP BY

"[view]"."session_start_date"

and here is the SQL when the session start date is grouped:

SELECT DISTINCT

CASE

WHEN 1 = 0 THEN CAST( "[view]"."session_start_date" AS VARCHAR )

WHEN "[view]"."session_start_date" BETWEEN '2017-10-01 00:00:00.0' AND '2017-10-31 23:59:00.0' THEN E'oct 2017'

ELSE CAST( "[view]"."session_start_date" AS VARCHAR )

END,

COUNT(DISTINCT("[view]"."user_booking_id_text"))

FROM "public"."[view]"

WHERE (

"[view]"."session_start_date" BETWEEN '2017-10-01 00:00:00.0' AND '2017-10-31 23:59:00.0'

)

GROUP BY

CASE

WHEN 1 = 0 THEN CAST( "[view]"."session_start_date" AS VARCHAR )

WHEN "[view]"."session_start_date" BETWEEN '2017-10-01 00:00:00.0' AND '2017-10-31 23:59:00.0' THEN E'oct 2017'

ELSE CAST( "[view]"."session_start_date" AS VARCHAR )

END

Thanks,

Freddy

Hi Freddy,

So the SQL queries are indeed different. As such, I'm sure if you executed each query directly against the RDBMS you'd see the same results you're seeing in Yellowfin. I'm not sure what your intended end result is, but it may be easier to alter the SQL within your RDBMS to the desired result, then work backwards from the desired SQL within Yellowfin. Ultimately, if the generated SQL is returning different results, it's a matter of report setup.

Regards,

Mike

Hi Martin,

I'm relatively new to Yellowfin so am not familiar with the terminology. What are you referring to when you mention RDBMS?

Thanks,

Freddy

Hi Freddy,

No problem. RDBMS stands for Relational Database Management System. Examples of these include MySQL, Oracle Db, Micrsoft SQL Server. In short, Yellowfin runs atop an RDBMS and generally, the way reporting works in most BI tools, but certainly Yellowfin, is that it simply passes the SQL query generated from the report directly into one of the RDBMS, meaning the results should match between what's being seen in Yellowfin and the database itself. Which further means that if the query results match, that it's simply a matter of report setup that's the issue, i.e., the report is not setup in a way that gets you to your desired results, and not say, a product defect. I hope this explanation makes sense.

If you append /info.jsp to your login URL, for example localhost:8080/info.jsp, then scroll down to Configuration Database under Application Details, you'll see what RDBMS you're currently using.

Ultimately, I'm afraid Support can't really assist much with report creation, as that would fall more in the realm of Consulting. If you're interested in discussing with a Consultant, I'm happy to reach out to your Account Manager on your behalf to discuss further. However, we do have several self-assisted options: Yellowfin University, our Wiki page, and the Community site you're on now to assist and guide you with report creation. Please let me know if you have any follow-up questions or concerns.

Regards,

Mike

Hi Mike,

I’m afraid I wouldn’t have access to the RDBMS as the system I’m reporting on is hosted externally by a 3rd party. They created a series of ‘views’ for us based on different sections of the system, from which we can pick fields to build a report. I’m not able to influence the SQL behind those views in any way. I’ve made contact with the supplier and am expecting a call tomorrow. Feel free to close the query.

Thanks,

Freddy Hunter

Hi Freddy,

Sounds good. I can keep this open until you hear back, then check back in on you in a couple days if I don't hear from you again. That won't be a problem.

Regards,

Mike

Hi Freddy,

I just wanted to check in and see if you needed anything else here or if we should indeed go ahead and close this out.

Thanks,

Mike

Hi Mike,

You can close the call I’ve managed to get to the bottom of it.

Thanks,

Freddy Hunter

Student Systems Analyst | Student Information Systems| Student Administrative Services | University of Warwick

From: Yellowfin Support <support@yellowfin.bi>

Sent: 21 April 2020 22:09

To: Hunter, Freddy <Freddy.Hunter@warwick.ac.uk>

Subject: New Comment in "Group data affecting Sum total of unique count"

Hi Freddy,

That's great to hear! Thanks for confirming. Please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike