EXO - Analysis code report grouping stops working when report starts after 29th Nov
Hey All, Have a really strange issue.
We have a report that provides a summary amount for each analysis code for the selected period.
October - perfect
November - Perfect
December - Analysis codes are no longer in order so it breaks into repeated sections of the analysis codes and creates a multiple page report.
Trying different start dates, it seems to be fine up until the 29th of Nov, selecting that for the start date.
I have attached the Nov report which is how it should be, and Dec where it seems like it is just failing to group the analysis code and I have no idea how it could be doing this.
Attached Nov form which is correct.
And Dec which has them all out of order so it creates multiple for the 1 grouping.
Grouping is set to the code and set to "Keep group together"
Love any thoughts on what could be causing this and why after a certain date it could be happening.
Seems to be working fine all the way up until the report starts after 29th of Nov.
I.e. if I select 28th Nov through Dec, the report is in the correct format.
If I select 29th or 1st dec, the report is messed up like the attached Dec one.
Hi Shadoxity ,
Those kinds of problems are usually caused by your Groups on the report not matching your Order Bys in the SQL Data Pipeline.
Unlike setting a Data Pipeline grouping (which applies to all data before display), Clarity applies its' Group By to the data as it is given it for display to screen.
This means you can get quite different results, depending on if you either:
- Group and Order all data in the Data Pipeline (SQL Query)
- Order all data in the Data Pipeline (SQL Query), and group in Clarity.
- Group some data in the pipeline (SQL Query), and do additional grouping in Clarity
You'll notice the reports in Exo are a mix. One thing to be aware of is that Failing to specify an Order by will use "Native" Ordering, which usually looks kind of like SEQNO order (the order transactions are recorded in), but which actually can't be trusted to be consistent in larger datasets. (Due to the way SQL does its magic behind the scenes.
I've tried to illustrate this quickly with some screenshots from Excel.
Consider, a table with 6 records, and 4 groups:
Records on left, what SQL does in middle, what Clarity would do on the end.
So you can see, you get fully different grouping results if you don't specify an Order by, and just rely on Clarity grouping.
(Performance Tip: Clarity data processing uses more memory than using SQL data grouping, and will be slower. Where possible, the best performance will always come from doing all of your data manipulations in the data pipeline/SQL stage, and just using Clarity to display. For this reason, I mostly use Clarity grouping to collect together lines when I need to display Detail information as well underneath the groups.)
You can specify these options in Clarity by:
- Using the Group (funnel icon) in the data pipeline:
Using the Group option in the data pipeline gives you full control over what SQL does. - Using the Calcs tab in the data pipeline
Usually you can avoid using the GROUP option, just by using the CALCS tab. Clarity will automatically fill in the GROUP tab. - Using the SORT tab in the data pipeline:
Using the Sort/Order By option gives you the ability to control which groups Clarity applies.
I strongly recommend setting specific Order By's for reports, even when using SQL Calculations/Groups to add up your data. If you don't set an Order By, the data just might decide to come out in a different order (Because SQL does whatever is fastest, and sometimes that's not very intuitive.)
I think this covers your question and should give you enough to get your report working properly. If you need further help I strongly recommend talking to your Business Partner, as these kinds of issues are a very extensive area that most businesses find it worthwhile to engage a consultant for assistance with.
