Forum

This content is now out of date.

Visit Our Community

Sub Query not Grouping

Hi -

I am trying to create a report of actual sales and additional lines (not columns) for budgeted sales.

I am querying the data from out sales system fine and can group the "Metric" dates on each transaction into months using the Group function in the report builder and specifying between dates.

I have created an identically structured csv file with my budget information.

I can successfully create a sub query union to add this information into the report.

What I can not get to work is any sort of grouping on the dates in the same manner as the base query. i.e. '01/01/2015' needs to say Jan 2015 so I can sort it with my actual.

Any help would be greatly appreciated.

Forum image

Here is some more information.

I can not get these two to group together. One record is in the base Query the other is in the Union Sub-Query. I would like to group by month. So the result would be two rows of data still but the date would say January.


Forum image
Hi There,

I'm sorry to hear you are running into issues getting your date formats to match up between the master query and the union sub query.

Do you think that you could verify the date format of the dates that are derived from the data source (master query)? Could you send us a screenshot of this?

Also, what version and build of Yellowfin are you using? This information helps our team replicate potential defects more easily. To check the build of Yellowfin you are using go to System Information under Administration > Admin Console > System Information. The version of Yellowfin is listed next to Application Version.

Thank you in advance for the additional information and we look forward to helping you get to the bottom of this soon.

Cheers,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Hi Dustin,

Thank you for your reply. Ill try my best to answer your questions!
We are on
Application Version: 7.1
Build: 20150528 (I upgraded to a later build to see if that fixed the issue)
Java: 1.8.0_40
OS: Windows Server 2012 6.2 (amd64)

The master query date format is dd/mm/yyyy here is a screenshot of the data view in yellowfin.


Forum image


In the View Fields screen it is setup as a Metric


Forum image


In my CSV file the View Fields screen the date is also setup as a Metric and below is its configuration


Forum image


I am not sure if yellowfin will obey the Master Grouping or if somehow i need to add a grouping in the Union Join? I do not see an obvious option for this.


Forum image


Im sure you will have a good answer or work around for what I'm trying to do. Ive tried to see how I create a virtual table in my main view from a different data source but its not proving successful. Looking forward to your reply

Best Regards,
Justin
Hi Justin,

Thank you very much for the screenshots. I was able to replicate what you have described but unfortunately there isn't much that can be done about it.

This is because of the SQL that is used to group data. Basically, the Master Query is grouping data linked to a specific view/data source and when you add new data from another data source the grouped by clause doesn't apply. Please see the below screenshot:


Forum image


Notice the two separate select statements for each set of data?

Like I said, the bit of bad news here is that there isn't a way to circumvent this problem (I spent a while trying). However I've gone ahead and opened an enhancement request to add the ability to group data that has been joined via an Advanced Sub Query.

Task ID# 195681

In the mean time I think your best bet would be to avoid using grouped data when configuring Advanced Sub Queries or import the .csv data into your Master Sub Query's data source instead.

Cheers,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4

Thanks for your reply Dustin.

Could you guide me on "import the .csv data into your Master Sub Query's data source instead" ?

Regards,
Justin
Hi Justin,

Apologies for the delayed response and for not being more clear in my previous post. I misspoke and meant to say Master Query's data source (not sub query).

Basically my recommendation is to avoid using an advanced sub query all together if you are trying to use "grouped by".

Your best bet is to import your .CSV data into your data source (Oracle, Sql Server, MySQL etc) directly in order to group the field values.

Please let let me know if you need further clarification.

Cheers,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.