logo

Warning: The forum is now for consultation only. Please use GitHub Discussions to post any questions or comments.


Welcome Guest ! To enable all features please Login or Register.

Notification

Icon
Error

Options
Go to last post Go to first unread
gzarzar  
#1 Posted : Friday, September 28, 2018 7:52:31 PM(UTC)
gzarzar

Rank: Newbie

Groups: Registered
Joined: 6/21/2018(UTC)
Posts: 0
Lebanon

Diagram
Database Diagram


This is my database diagram, i have built a Repository that reflects exactly the diagram by adding the table from the catalog on the seal server manager.
the problem is, when i create a report using three related table
  1. FactRegisteredStudent
  2. DimClass
  3. DimAcademicYear


the generated query looks like the following:

Code:
SELECT DISTINCT
  Reporting.DimClass.ClassId AS C0,
  Reporting.FactRegisteredStudent.StudentId AS C1,
  Reporting.DimAcademicYear.AcademicYear AS C2
FROM 
(Reporting.DimClass INNER JOIN 
(Reporting.FactLeftSchool INNER JOIN 
(Reporting.DimAcademicYear INNER JOIN Reporting.FactRegisteredStudent
 ON Reporting.DimAcademicYear.AcademicYearId = Reporting.FactRegisteredStudent.AcademicYearId)
 ON Reporting.DimAcademicYear.AcademicYearId = Reporting.FactLeftSchool.AcademicYearId)
 ON Reporting.DimClass.ClassId = Reporting.FactLeftSchool.ClassId)
ORDER BY Reporting.DimClass.ClassId ASC,Reporting.FactRegisteredStudent.StudentId ASC,Reporting.DimAcademicYear.AcademicYear ASC


i have tried to use join table to avoid and join table to use properties but the query stay the same, so their is any way to make the report designer correct the generated query without setting the form clause manually?

Thanks

Edited by user Friday, September 28, 2018 7:58:35 PM(UTC)  | Reason: Not specified

epf  
#2 Posted : Friday, September 28, 2018 8:54:26 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Yes it is strange, can you reproduce it with Northwind and upload the sample report ?
Or can you simply upload the report and data source ?
gzarzar  
#3 Posted : Saturday, September 29, 2018 1:57:00 PM(UTC)
gzarzar

Rank: Newbie

Groups: Registered
Joined: 6/21/2018(UTC)
Posts: 0
Lebanon

Kindly find the link below, which contains the following files

SisAssist (Repository)
TestJoin (Report)

https://drive.google.com...iAWyDNB_hosdFZTpjyeDbzHH

Note: the files are grouped and compressed using 7Zip and could be opened using the same program
epf  
#4 Posted : Tuesday, October 2, 2018 7:05:20 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Thank you for the files, I have to investigate why it is like this (perhaps a bug in the Joins engine when there are circular paths, to be fixed for the 4.1).

In the meantime, you can use the following workaround:
Select the 'Views' node, and edit the 'Init Script' of the report:
Code:
@using Seal.Model
@{
    Report report = Model;
    report.Sources[0].MetaData.Joins.RemoveAll(i => i.Name.Contains("LeftSchool"));
}


This will remove the joins defined for LeftSchool and fix your join resolution.

gzarzar  
#5 Posted : Tuesday, October 2, 2018 8:56:15 AM(UTC)
gzarzar

Rank: Newbie

Groups: Registered
Joined: 6/21/2018(UTC)
Posts: 0
Lebanon

thank you very much.

i can't wait to hear from you that this bug has been resolved, i will consider your suggestion as my temp workaround.
epf  
#6 Posted : Tuesday, October 2, 2018 3:21:47 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Another workaround is to play with the 'Is Bi-Directional' flag of your join:
setting this to false for 'Reporting.DimAcademicYear - Reporting.FactLeftSchool' seems to work and generate the correct Join clause.

This flag indicates to consider only one direction Right Table-Left Table
gzarzar  
#7 Posted : Wednesday, October 3, 2018 9:35:37 AM(UTC)
gzarzar

Rank: Newbie

Groups: Registered
Joined: 6/21/2018(UTC)
Posts: 0
Lebanon

Thank you,

i have tried the mentioned workaround, no one work perfectly for me, for the time being i will stick with setting from clause manually and will wait for the next version.
epf  
#8 Posted : Wednesday, October 3, 2018 12:34:01 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Yes, you are right, the simplest is to modify the "From" SQL Statement in your model.
epf  
#9 Posted : Wednesday, October 17, 2018 11:20:44 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
This has been fixed and will be available in 4.1
gzarzar  
#10 Posted : Wednesday, October 17, 2018 11:30:15 AM(UTC)
gzarzar

Rank: Newbie

Groups: Registered
Joined: 6/21/2018(UTC)
Posts: 0
Lebanon

Thanks very much
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.