Sub Query not Grouping
29 May, 2015
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.
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.
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.
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.
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
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.
In the View Fields screen it is setup as a Metric
In my CSV file the View Fields screen the date is also setup as a Metric and below is its configuration
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.
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
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.
In the View Fields screen it is setup as a Metric
In my CSV file the View Fields screen the date is also setup as a Metric and below is its configuration
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.
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:
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
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:
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
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
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