Access Calculated Column via relation
Access Calculated Column via relation
Hello,
I have a basic report listing some information about Trees (just an ID and GlobalID at the moment). The report uses
tree information from two different databases using two different SQL Connections. One database contains a table
with basic information about trees and the second database contains the same information, but with a Condition
field added. The Condition field is a numeric field containing values from 1 to 100 with 100 meaning that the
tree is in excellent condition and 1 meaning the tree is in a "failed" state.
In order to give a descriptive meaning to the Condition values, I added a calculated field (called ConditionText) with
the following switch statement...
(string)Switch(
TreeData.Condition >= 80,"Excellent",
TreeData.Condition >= 60 && TreeData.Condition < 80,"Good",
TreeData.Condition >= 40 && TreeData.Condition < 60,"Fair",
TreeData.Condition >= 20 && TreeData.Condition < 40,"Poor",
TreeData.Condition < 20,"Failed")
I know I could use a CASE statement in the SQL code to accomplish the same thing as the switch statement, but
this is just a sample report. The real report I'm working with contains a DataTable datasource that I cannot edit - using
a calculated column is the only option I have.
On to my question/issue...
I have established a relation between the two databases in my report using the GlobalID from both the Trees and
TreesCondition tables.
I have added the following fields to my sample report...
DB1.AssetID, DB1.Globalid,DB1.relationtoDB2.GlobalID,DB1.relationtoDB2.Condition,DB1.relationtoDB2.ConditionText
All of the information displays properly except for the DB1.relationtoDB2.ConditionText field - it appears to only show
the ConditionText value from the first record that it comes across.
Is it possible to access a Calculated Column using a relation between two different databases from two different connections?
I've attached a sample report and databases for you to test with. I hope you can provide some direction, or confirm
that it is not possible to do what I'm trying.
I am currently using Stimulsoft.Reports 2018.1.8 from February 2018, ASP.NET, Flex.
Thank You,
Carl
I have a basic report listing some information about Trees (just an ID and GlobalID at the moment). The report uses
tree information from two different databases using two different SQL Connections. One database contains a table
with basic information about trees and the second database contains the same information, but with a Condition
field added. The Condition field is a numeric field containing values from 1 to 100 with 100 meaning that the
tree is in excellent condition and 1 meaning the tree is in a "failed" state.
In order to give a descriptive meaning to the Condition values, I added a calculated field (called ConditionText) with
the following switch statement...
(string)Switch(
TreeData.Condition >= 80,"Excellent",
TreeData.Condition >= 60 && TreeData.Condition < 80,"Good",
TreeData.Condition >= 40 && TreeData.Condition < 60,"Fair",
TreeData.Condition >= 20 && TreeData.Condition < 40,"Poor",
TreeData.Condition < 20,"Failed")
I know I could use a CASE statement in the SQL code to accomplish the same thing as the switch statement, but
this is just a sample report. The real report I'm working with contains a DataTable datasource that I cannot edit - using
a calculated column is the only option I have.
On to my question/issue...
I have established a relation between the two databases in my report using the GlobalID from both the Trees and
TreesCondition tables.
I have added the following fields to my sample report...
DB1.AssetID, DB1.Globalid,DB1.relationtoDB2.GlobalID,DB1.relationtoDB2.Condition,DB1.relationtoDB2.ConditionText
All of the information displays properly except for the DB1.relationtoDB2.ConditionText field - it appears to only show
the ConditionText value from the first record that it comes across.
Is it possible to access a Calculated Column using a relation between two different databases from two different connections?
I've attached a sample report and databases for you to test with. I hope you can provide some direction, or confirm
that it is not possible to do what I'm trying.
I am currently using Stimulsoft.Reports 2018.1.8 from February 2018, ASP.NET, Flex.
Thank You,
Carl
- Attachments
-
- ReportWithMultipleConnections.zip
- (1.02 MiB) Downloaded 170 times
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Access Calculated Column via relation
Hello,
To add relation between data sources from different connections, should be used CacheAllData=true option for the report.
Thank you.
To add relation between data sources from different connections, should be used CacheAllData=true option for the report.
Thank you.
Re: Access Calculated Column via relation
Thanks Lech,
I already have CacheAllData set to true for the report. Any other ideas?
Carl
I already have CacheAllData set to true for the report. Any other ideas?
Carl
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Access Calculated Column via relation
Hello,
Please check the modified report.
Thank you.
Please check the modified report.
Thank you.
- Attachments
-
- TreeReportMultipleConnections_modified.mrt
- (10.93 KiB) Downloaded 234 times
-
- Annotation 2019-07-16 234300.jpg (291.1 KiB) Viewed 3123 times
Re: Access Calculated Column via relation
Thank You Lech, That works for now.
However, I'm curious to know why the Condition Field from the related table shows properly without having a
Master-Detail relationships setup, but the "Condition Text" field (which is a calculated column) does not.
Carl
However, I'm curious to know why the Condition Field from the related table shows properly without having a
Master-Detail relationships setup, but the "Condition Text" field (which is a calculated column) does not.
Carl
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Access Calculated Column via relation
Hello,
There are some limitations with calculated fields. We will try to add that improvement for calculations fields in the next releases.
Thank you.
There are some limitations with calculated fields. We will try to add that improvement for calculations fields in the next releases.
Thank you.
Re: Access Calculated Column via relation
OK - Thank You Lech.
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Access Calculated Column via relation
Hello
We are always glad to help you!
Please let us know if you need any additional help.
Thank you.
We are always glad to help you!
Please let us know if you need any additional help.
Thank you.