Rank: Member
Groups: Registered
Joined: 6/5/2020(UTC) Posts: 12  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
|
|
|
|
|
|
Rank: Member
Groups: Registered
Joined: 6/5/2020(UTC) Posts: 12 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.
|
|
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 12/20/2013(UTC) Posts: 1,209  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....
|
|
|
|
|
|
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.
