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
cyracks  
#1 Posted : Saturday, July 4, 2020 12:39:25 AM(UTC)
cyracks

Rank: Member

Groups: Registered
Joined: 6/5/2020(UTC)
Posts: 12
Slovenia

Thanks: 4 times
I have a report with MetaData model which shows rows from only one table.

I wanted to limit the result set by joining base table to some other table, but join is not added to SQL statement.

This is the statement that is executed
Quote:

SELECT Sum(R1.col1) AS C0, R1.col2 AS C1
FROM R1
WHERE (1=1)
GROUP BY R1.col2


despite the setting that report should also use join on table R2 which should limit the result.
Quote:

SELECT Sum(R1.col1) AS C0, R1.col2 AS C1
FROM R1
JOIN R2 on R2.col3 = R1.col3
WHERE (1=1)
GROUP BY R1.col2



Join is added only if I use any of the columns in R2 on the report, but I do not want that. So the only way is to duplicate the same table in datasource reporistory as many times as there is different scenarios for the table, which is also not optimal solution.

For example:
All buyers and supplier are written in the same table. If I want to create separate report for buyer and supplier I have to create two instances of table buyer/supplier (and add additional where clause) because there is no option on the report to limit the table. I could show restriction option buyer/supplier to the user, but I also do not want that.

Is there any good way to have only one table in dataset repository and use it with different where conditions on multiple reports ?

I think two options should be considered for the upgrade
1) add where to the report level
2) if users sets join then apply it even if no columns from joined table is used on the report output
cyracks  
#2 Posted : Sunday, July 5, 2020 12:14:48 PM(UTC)
cyracks

Rank: Member

Groups: Registered
Joined: 6/5/2020(UTC)
Posts: 12
Slovenia

Thanks: 4 times
I found the obvious solution regarding WHERE statement on report level. In Restrictions memo field it can be written whatever we want (like and table.date > '2020-01-01'...) it is not limited to only restriction objects enclosed in [...].

That means problem solved no BUG.
epf  
#3 Posted : Monday, July 6, 2020 7:25:10 AM(UTC)
epf

Rank: Administration

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

Thanks: 14 times
Was thanked: 206 time(s) in 199 post(s)
yes you may also override the 'From Clause' property of the model, this will modify the final SQL generated....
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.