Section Style Tab not working
11 May, 2015
Dear Support,
I'm having issues on setting the Style=Tab for a Report Section
I'm using the field "Membership - Eligibility Sub Reason ID" from table "membershipEligibilityLookup": this table is joined to "Event Facts" using a OUTER join:
When I execute the report with the Section - Tab Style:
I get the list of values, but when I click on each of them the page doesn't get refreshed: it always shows the first velue in the list. I can't switch between values:
If I change the join condition to Inner join:
the Section Tabs work fine, and I can switch between values:
For my analysis I need to have an outer join: any idea on how can I solve the issue? do you think the issue is the Outer Join, or there's something else?
Thanks
Francesca
I'm having issues on setting the Style=Tab for a Report Section
I'm using the field "Membership - Eligibility Sub Reason ID" from table "membershipEligibilityLookup": this table is joined to "Event Facts" using a OUTER join:
When I execute the report with the Section - Tab Style:
I get the list of values, but when I click on each of them the page doesn't get refreshed: it always shows the first velue in the list. I can't switch between values:
If I change the join condition to Inner join:
the Section Tabs work fine, and I can switch between values:
For my analysis I need to have an outer join: any idea on how can I solve the issue? do you think the issue is the Outer Join, or there's something else?
Thanks
Francesca
I don't know why the images have been poster wrongly....here again, in the same order:
Der Support,
could you please have a look at the message? we are delivering not exact data on our dashboard because of the issue!!
thanks
Francesca
could you please have a look at the message? we are delivering not exact data on our dashboard because of the issue!!
thanks
Francesca
Hi Francesca,
which version and build of Yellowfin are you using? The reason I ask is that I remember there was a bug or two with Tabbed Sections a while back, and they have since been fixed.
I tried reproducing this issue in the latest build of 7.1 and wasn't able to.
regards,
Dave
which version and build of Yellowfin are you using? The reason I ask is that I remember there was a bug or two with Tabbed Sections a while back, and they have since been fixed.
I tried reproducing this issue in the latest build of 7.1 and wasn't able to.
regards,
Dave
HI,
we are using
Application Version: 7.1
Build: 20150225
Java Version: 1.8.0_25
We use the Section Tab in plenty of reports, and we too don't have any issue on other views; we have issues only on that one, and just with the fields from the dimension linked through an outer join.
is thre something we can check on the view? is there a way to regenerate it?
thanks
francesca
we are using
Application Version: 7.1
Build: 20150225
Java Version: 1.8.0_25
We use the Section Tab in plenty of reports, and we too don't have any issue on other views; we have issues only on that one, and just with the fields from the dimension linked through an outer join.
is thre something we can check on the view? is there a way to regenerate it?
thanks
francesca
Hi Francesca,
is there any chance we could get hold of a full backup of your YF config DB (i.e. schema & data) and just the schema of your data warehouse (i.e. we won't need the data of your DW)?
I think this will be the best way for us to investigate this strange issue over here - then we can get a developer to step through the code line by line and see what's going wrong.
Please let us know if this is possible.
regards,
Dave
is there any chance we could get hold of a full backup of your YF config DB (i.e. schema & data) and just the schema of your data warehouse (i.e. we won't need the data of your DW)?
I think this will be the best way for us to investigate this strange issue over here - then we can get a developer to step through the code line by line and see what's going wrong.
Please let us know if this is possible.
regards,
Dave
Hi David,
we should be able to sendthe backup tonight.
The vue affected is: "PI - Policy Events".
THe relationship that looks affected is the join between the table "event_facts" and "DHL_MembershipEligibilityLookup": the Tab section works if the join is set as "Inner Join", while doesn't work if it's setup as "Outer Join" (and we need to perform an Outer Join).
You can create a test report dragging on the section the field "Segment Group", or use the report "YTD Applications by Segment Group vs Last Year" as an example.
Hope to hear from you soon!
cheers
Francesca
we should be able to sendthe backup tonight.
The vue affected is: "PI - Policy Events".
THe relationship that looks affected is the join between the table "event_facts" and "DHL_MembershipEligibilityLookup": the Tab section works if the join is set as "Inner Join", while doesn't work if it's setup as "Outer Join" (and we need to perform an Outer Join).
You can create a test report dragging on the section the field "Segment Group", or use the report "YTD Applications by Segment Group vs Last Year" as an example.
Hope to hear from you soon!
cheers
Francesca
Hello David
We have created the backup of the Yellowfin Repository database, and the Data Warehouse schema as requested.
The files are located in the Hosted FTP site set up for our use (Prometheus Information) in a folder called DHL.
Please let us know if you cannot access them.
Thanks
Richard
We have created the backup of the Yellowfin Repository database, and the Data Warehouse schema as requested.
The files are located in the Hosted FTP site set up for our use (Prometheus Information) in a folder called DHL.
Please let us know if you cannot access them.
Thanks
Richard
Hi Richard and Francesca,
thanks for that, I have been able to successfully restore both the DEV_yellowfin and DHL_BI_warehouse databases. If you could now please tell me now which report has the issue then hopefully I'll be able to replicate the issue.
regards,
Dave
thanks for that, I have been able to successfully restore both the DEV_yellowfin and DHL_BI_warehouse databases. If you could now please tell me now which report has the issue then hopefully I'll be able to replicate the issue.
regards,
Dave
Hi David,
from my message above (Monday 12:54 PM):
"
The vue affected is: "PI - Policy Events".
THe relationship that looks affected is the join between the table "event_facts" and "DHL_MembershipEligibilityLookup": the Tab section works if the join is set as "Inner Join", while doesn't work if it's setup as "Outer Join" (and we need to perform an Outer Join).
You can create a test report dragging on the section the field "Segment Group", or use the report "YTD Applications by Segment Group vs Last Year" as an example.
Hope to hear from you soon!
cheers
Francesca
"
from my message above (Monday 12:54 PM):
"
The vue affected is: "PI - Policy Events".
THe relationship that looks affected is the join between the table "event_facts" and "DHL_MembershipEligibilityLookup": the Tab section works if the join is set as "Inner Join", while doesn't work if it's setup as "Outer Join" (and we need to perform an Outer Join).
You can create a test report dragging on the section the field "Segment Group", or use the report "YTD Applications by Segment Group vs Last Year" as an example.
Hope to hear from you soon!
cheers
Francesca
"
Hi Francesca,
unfortunately it looks as though I can't reproduce this issue without some data in the data warehouse:
I had a quick try at adding some dummy data but the view still returned no data, obviously the dummy data isn't fulfilling the join conditions. I'm hoping that you have some dummy data that you could send across to us perhaps? Or maybe you could send across just a sample of the real data so that we can at least return a few rows from the view?
regards,
David
unfortunately it looks as though I can't reproduce this issue without some data in the data warehouse:
I had a quick try at adding some dummy data but the view still returned no data, obviously the dummy data isn't fulfilling the join conditions. I'm hoping that you have some dummy data that you could send across to us perhaps? Or maybe you could send across just a sample of the real data so that we can at least return a few rows from the view?
regards,
David
Hi david
I have uploaded some sample data to the Prometheus FTP drop site. It is in a folder DHL, zip file called DHL_BI_Warehouse_Sample_data.zip
This contains a TSQL file to run using SQLAzureMW which will populate the DHL_BI_Warehouse databse with the sample data
Please let us know if there are any problems
Regards
Richard
I have uploaded some sample data to the Prometheus FTP drop site. It is in a folder DHL, zip file called DHL_BI_Warehouse_Sample_data.zip
This contains a TSQL file to run using SQLAzureMW which will populate the DHL_BI_Warehouse databse with the sample data
Please let us know if there are any problems
Regards
Richard
Hi David,
did you have the chance to have a look at the data?
Thanks
Francesca
did you have the chance to have a look at the data?
Thanks
Francesca
Hi Francesca,
yes, I've migrated the data into our instance of DHL_BI_warehouse:
but when I run the report "YTD Applications by Segment Group vs Last Year" it still shows no data:
so I had a look at the view "PI - Policy Events" and the problem is that even with that test data you supplied, the view still returns no data, this will be because some of the joins are not being fulfilled.
I'm thinking that tomorrow morning I could modify the view by removing all tables and joins that don't have any data, this may or not work....otherwise, I guess we'll need test data for all of the tables used in the view.
regards,
Dave
yes, I've migrated the data into our instance of DHL_BI_warehouse:
but when I run the report "YTD Applications by Segment Group vs Last Year" it still shows no data:
so I had a look at the view "PI - Policy Events" and the problem is that even with that test data you supplied, the view still returns no data, this will be because some of the joins are not being fulfilled.
I'm thinking that tomorrow morning I could modify the view by removing all tables and joins that don't have any data, this may or not work....otherwise, I guess we'll need test data for all of the tables used in the view.
regards,
Dave
Hi Francesca,
I removed all tables from the view except for the 3 that you supplied test data for (DHL_MembershipEligibilityLookup, eventsym, DHL_EligibilityReference) and then created a report with the SEGMENT field as the report SECTION, but now there is only 1 value "YASP" for the SEGEMENT field, thus only one tabbed section shows on the report.
I had a quick look at the view SQL to try and see why I was only getting the 1 value YASP, and was going to try changing the data but I thought that because you understand the data better than me you'd be better able to work out what to do.
I feel we're getting very close, just this final little problem needs overcoming.
regards,
Dave
I removed all tables from the view except for the 3 that you supplied test data for (DHL_MembershipEligibilityLookup, eventsym, DHL_EligibilityReference) and then created a report with the SEGMENT field as the report SECTION, but now there is only 1 value "YASP" for the SEGEMENT field, thus only one tabbed section shows on the report.
I had a quick look at the view SQL to try and see why I was only getting the 1 value YASP, and was going to try changing the data but I thought that because you understand the data better than me you'd be better able to work out what to do.
I feel we're getting very close, just this final little problem needs overcoming.
regards,
Dave
Hi David,
thanks for your effort!
I don't understand why you are getting only one Segment value...here the SQL I used:
SELECT EventTable.[Member Number],EventTable.[Year Month],[End of Month Date]
,EligLookup.*
,EligDescr.Segment,EligDescr.[Dashboard Segment Group]
FROM [eventsym] as EventTable
LEFT JOIN DHL_MembershipEligibilityLookup As EligLookup ON(
EventTable.[Member Number] = EligLookup.memberno
AND EventTable.[End of Month Date] >= EligLookup.StartDate
AND EventTable.[End of Month Date] < EligLookup.EndDate
)
LEFT JOIN DHL_EligibilityReference As EligDescr ON (
EligLookup.EligibilityReasonID = EligDescr.EligibilityReasonID
AND EligLookup.EligibilitySubReasonID = EligDescr.EligibilitySubReasonID
)
here the results I get: 24 rows. MemberNo 993999 doesn't have a Segment or Segment Group, and should still appear on YF results
Here my YF report:
If I drag the Segment or the DashboardSegmentGroup in the Sections, and i setup as "Tab", I can't navigate between tabs.
THe report is called "Report for YF Support - Tab issues".
Let me know if it works and how can I help.
Cheers,
Fra
thanks for your effort!
I don't understand why you are getting only one Segment value...here the SQL I used:
SELECT EventTable.[Member Number],EventTable.[Year Month],[End of Month Date]
,EligLookup.*
,EligDescr.Segment,EligDescr.[Dashboard Segment Group]
FROM [eventsym] as EventTable
LEFT JOIN DHL_MembershipEligibilityLookup As EligLookup ON(
EventTable.[Member Number] = EligLookup.memberno
AND EventTable.[End of Month Date] >= EligLookup.StartDate
AND EventTable.[End of Month Date] < EligLookup.EndDate
)
LEFT JOIN DHL_EligibilityReference As EligDescr ON (
EligLookup.EligibilityReasonID = EligDescr.EligibilityReasonID
AND EligLookup.EligibilitySubReasonID = EligDescr.EligibilitySubReasonID
)
here the results I get: 24 rows. MemberNo 993999 doesn't have a Segment or Segment Group, and should still appear on YF results
Here my YF report:
If I drag the Segment or the DashboardSegmentGroup in the Sections, and i setup as "Tab", I can't navigate between tabs.
THe report is called "Report for YF Support - Tab issues".
Let me know if it works and how can I help.
Cheers,
Fra
Sorry, when I put multiple pictures in the same message they don't get sent correctly, don't know why :(
Here the SQL results (24 rows)
Cheers
Here the SQL results (24 rows)
Cheers
Hi Francesca,
I think the problem with adding multiple images to a forum post might be resolved by clicking the Add Another Image link under the Browse button:
I think my dummy data must be different than yours, here are my results when I run your report SQL directly against our DHL DW:
and I have attached below the data from the 3 tables DHL_MembershipEligibilityLookup, eventsym, DHL_EligibilityReference. Would you be able to check that it's the same dummy data as you've got? Maybe something went wrong when I used that data migration tool.
regards,
Dave
I think the problem with adding multiple images to a forum post might be resolved by clicking the Add Another Image link under the Browse button:
I think my dummy data must be different than yours, here are my results when I run your report SQL directly against our DHL DW:
and I have attached below the data from the 3 tables DHL_MembershipEligibilityLookup, eventsym, DHL_EligibilityReference. Would you be able to check that it's the same dummy data as you've got? Maybe something went wrong when I used that data migration tool.
regards,
Dave
Hi David,
the table DHL_EligibilityReference looks wrong :(
I've attached below the content you should have on it ....let me know if you can upload it on the DB, or we need to send you some extracts again.
Cheers
Fra
the table DHL_EligibilityReference looks wrong :(
I've attached below the content you should have on it ....let me know if you can upload it on the DB, or we need to send you some extracts again.
Cheers
Fra
Hi Francesca,
that's fine, I was able to upload the data, and then I tried to reproduce the issue again, however the tabbed sections on my report are working correctly. I've attached a video to show you.
This leads me to the conclusion that I've got to use the actual report that you were using (YTD Applications by Segment Group vs Last Year) rather than creating my own, but unfortunately, even with the new dummy data for the DHL_EligibilityReference table, the view PI - Policy Events is still returning no data.
Are you able to reproduce the issue by just using those 3 tables in a view (rather than using the proper view)?
regards,
David
that's fine, I was able to upload the data, and then I tried to reproduce the issue again, however the tabbed sections on my report are working correctly. I've attached a video to show you.
This leads me to the conclusion that I've got to use the actual report that you were using (YTD Applications by Segment Group vs Last Year) rather than creating my own, but unfortunately, even with the new dummy data for the DHL_EligibilityReference table, the view PI - Policy Events is still returning no data.
Are you able to reproduce the issue by just using those 3 tables in a view (rather than using the proper view)?
regards,
David
Hi David,
the tabbed sections work fine for other views, is just for that one it's not working, so it would not be useful to create a new view, I think.
Maybe you need some data on the Yearmonth table (that is linked using an inner join); I've attached the data to the message, let me know if you can see any data now.
Thanks
Francesca
the tabbed sections work fine for other views, is just for that one it's not working, so it would not be useful to create a new view, I think.
Maybe you need some data on the Yearmonth table (that is linked using an inner join); I've attached the data to the message, let me know if you can see any data now.
Thanks
Francesca
Hi Francesca,
sorry but more bad news, I inserted the first 50 rows you gave me into the Yearmonth table
(only 50 because it took so long to prepare the data, replaced all tabs with commas surrounded by single quotation marks because most columns require them, but then had to go through the columns that didn't require them and delete them) but that didn't help, the view still has no data:
I think all of tables that have inner joins will need to have some dummy data, what do you think?
regards,
Dave
sorry but more bad news, I inserted the first 50 rows you gave me into the Yearmonth table
(only 50 because it took so long to prepare the data, replaced all tabs with commas surrounded by single quotation marks because most columns require them, but then had to go through the columns that didn't require them and delete them) but that didn't help, the view still has no data:
I think all of tables that have inner joins will need to have some dummy data, what do you think?
regards,
Dave
Hi David,
the tables with inner join are in the view all based on the DW tables Yearmonth (they are SQL view in YF), so you shouldn't need further data.
Could you please send me the SQL behind the view? I will compare it to mine SQL to see what's going wrong. (you find my view SQL attached)
the tables with inner join are in the view all based on the DW tables Yearmonth (they are SQL view in YF), so you shouldn't need further data.
Could you please send me the SQL behind the view? I will compare it to mine SQL to see what's going wrong. (you find my view SQL attached)
ALso,
what if you execute that SQL in Management Studio? do you get any result?
SELECT *
FROM "eventsym" AS "Event Facts"
INNER JOIN "yearmonth" AS "Period Processed"
ON (
"Event Facts"."Year Month" = "Period Processed"."Year Month"
)
LEFT OUTER JOIN "dbo"."DHL_MembershipEligibilityLookup"
ON (
"Event Facts"."End of Month Date" >= "DHL_MembershipEligibilityLookup"."StartDate"
AND "Event Facts"."End of Month Date" < "DHL_MembershipEligibilityLookup"."EndDate"
AND "Event Facts"."Member Number" = "DHL_MembershipEligibilityLookup"."memberno"
)
LEFT OUTER JOIN "dbo"."DHL_EligibilityReference"
ON (
"DHL_MembershipEligibilityLookup"."EligibilityReasonID" = "DHL_EligibilityReference"."EligibilityReasonID"
AND "DHL_MembershipEligibilityLookup"."EligibilitySubReasonID" = "DHL_EligibilityReference"."EligibilitySubReasonID"
)
what if you execute that SQL in Management Studio? do you get any result?
SELECT *
FROM "eventsym" AS "Event Facts"
INNER JOIN "yearmonth" AS "Period Processed"
ON (
"Event Facts"."Year Month" = "Period Processed"."Year Month"
)
LEFT OUTER JOIN "dbo"."DHL_MembershipEligibilityLookup"
ON (
"Event Facts"."End of Month Date" >= "DHL_MembershipEligibilityLookup"."StartDate"
AND "Event Facts"."End of Month Date" < "DHL_MembershipEligibilityLookup"."EndDate"
AND "Event Facts"."Member Number" = "DHL_MembershipEligibilityLookup"."memberno"
)
LEFT OUTER JOIN "dbo"."DHL_EligibilityReference"
ON (
"DHL_MembershipEligibilityLookup"."EligibilityReasonID" = "DHL_EligibilityReference"."EligibilityReasonID"
AND "DHL_MembershipEligibilityLookup"."EligibilitySubReasonID" = "DHL_EligibilityReference"."EligibilitySubReasonID"
)
Hi Francesca,
I ran your query in Management Studio and it returned 0 rows (see attachment).
The SQL code behind the view "PI - Policy Events" is also attached, I imagine it would be exactly the same as yours because I haven't modifed the view.
I'm sorry this is going on and on, would it help if we did a screen-share session? Then you could have a look at my view, I'm sure you'd see where the problem is because you are familiar with the data.
regards,
Dave
I ran your query in Management Studio and it returned 0 rows (see attachment).
The SQL code behind the view "PI - Policy Events" is also attached, I imagine it would be exactly the same as yours because I haven't modifed the view.
I'm sorry this is going on and on, would it help if we did a screen-share session? Then you could have a look at my view, I'm sure you'd see where the problem is because you are familiar with the data.
regards,
Dave
Hi Dave,
I think I got the issue!
THe Yearmonth table in your Database looks different: the field Yearmonth should have format "YYYY-MM-DD", so let's say last value should be "2015-05-01", while I can see you've got "2015-01-05": that's why the query doesn't work!
I think Excel did an automatic conversion of the field....could you udpate the CSV file without converting it to Excel? I've extracted the csv directly from the database, you should be able to upload it without any manipulation!
You just need 6 rows of it, please see new attachment with only the 6 rows
I think I got the issue!
THe Yearmonth table in your Database looks different: the field Yearmonth should have format "YYYY-MM-DD", so let's say last value should be "2015-05-01", while I can see you've got "2015-01-05": that's why the query doesn't work!
I think Excel did an automatic conversion of the field....could you udpate the CSV file without converting it to Excel? I've extracted the csv directly from the database, you should be able to upload it without any manipulation!
You just need 6 rows of it, please see new attachment with only the 6 rows
PS: maybe it's easier to just edit the rows you need, insted of uploading the file again.
Thanks a lot for your patience btw
Thanks a lot for your patience btw
Hi Francesca,
wow, well done, you certainly have a keen eye for detail!
I updated the 1st 50 rows, and now the report is creating a few tabbed sections. But the thing is, the behaviour of the tabs looks correct to me. I've attached a video of the report, could you please have a look at it and let me know what you think.
regards,
Dave
wow, well done, you certainly have a keen eye for detail!
I updated the 1st 50 rows, and now the report is creating a few tabbed sections. But the thing is, the behaviour of the tabs looks correct to me. I've attached a video of the report, could you please have a look at it and let me know what you think.
regards,
Dave
Great! :)
Ok, I think you need to change the view to see the issue: could you please change the join between "Event facts" and "DHL_MembershipEligibilityLookup"?
It should be an outer join, not an inner join, as in your results you want to see also the members without a Segment.
SO your SQL should be:
LEFT OUTER JOIN "dbo"."DHL_MembershipEligibilityLookup"
ON (
"Event Facts"."End of Month Date" >= "DHL_MembershipEligibilityLookup"."StartDate"
AND "Event Facts"."End of Month Date" < "DHL_MembershipEligibilityLookup"."EndDate"
AND "Event Facts"."Member Number" = "DHL_MembershipEligibilityLookup"."memberno"
)
and the view looks like:
Once we setup the outer join, the Tabbed section doesn't work anymore!
Ok, I think you need to change the view to see the issue: could you please change the join between "Event facts" and "DHL_MembershipEligibilityLookup"?
It should be an outer join, not an inner join, as in your results you want to see also the members without a Segment.
SO your SQL should be:
LEFT OUTER JOIN "dbo"."DHL_MembershipEligibilityLookup"
ON (
"Event Facts"."End of Month Date" >= "DHL_MembershipEligibilityLookup"."StartDate"
AND "Event Facts"."End of Month Date" < "DHL_MembershipEligibilityLookup"."EndDate"
AND "Event Facts"."Member Number" = "DHL_MembershipEligibilityLookup"."memberno"
)
and the view looks like:
Once we setup the outer join, the Tabbed section doesn't work anymore!
Hi Francesca,
finally I've got good news for you! I can now reproduce the issue exactly as you have described. So I've raised a product defect (Support Task 197706) and added it to the agenda for the next developers' meeting for discussion and allocation.
We are up to the code-freeze part of our monthly software-cycle which means that the fix will be in next months build (end of July) at the earliest.
Thanks again for working with me to reproduce this issue, and I'm very impressed with your eye for detail!
regards,
Dave
finally I've got good news for you! I can now reproduce the issue exactly as you have described. So I've raised a product defect (Support Task 197706) and added it to the agenda for the next developers' meeting for discussion and allocation.
We are up to the code-freeze part of our monthly software-cycle which means that the fix will be in next months build (end of July) at the earliest.
Thanks again for working with me to reproduce this issue, and I'm very impressed with your eye for detail!
regards,
Dave
:) thanks a lot to you for your support, it's good we could finally reproduce the issue!
Hopefully it will be solved soon, we'll wait for some news from you.
Regards,
Francesca
Hopefully it will be solved soon, we'll wait for some news from you.
Regards,
Francesca
