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

Notification

Icon
Error

Options
Go to last post Go to first unread
jovanni  
#1 Posted : Monday, December 1, 2014 1:56:36 PM(UTC)
jovanni

Rank: Member

Groups: Registered
Joined: 10/29/2014(UTC)
Posts: 12
Italy
Location: Schio

Hi,
I got the needing to show the result of the difference between two date-time fields.
I can't add columns to table so I would like to have this calculated field only in my report side.

Shortly, How can I add a new calculated field on a report ?


This is the select who made the right calculation.
SELECT CONVERT(VARCHAR,([FASE2].[DataOra]-[FASE1].[DataOra]),108) As Difference
FROM [dbo].[FASE2], [dbo].[FASE1]
WHERE [FASE2].[IdFa2] = [FASE1].[IdFa1]

I've tried to add a new model and insert the above select but I can't see any new field "Difference"

Thanks
epf  
#2 Posted : Tuesday, December 2, 2014 8:46:30 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 673
Switzerland

Thanks: 12 times
Was thanked: 116 time(s) in 113 post(s)
To add a calculated field you have 2 options:

First one is to add a new column in your table (so edit the data source), then select your table, add column, and enter the calculated field in the Definition Name property.
It would be 'CONVERT(VARCHAR,([FASE2].[DataOra]-[FASE1].[DataOra]),108)'
Change the type of the new column (here it would be a Text)
And the display name and category 'Difference'

Check the SQL, and when ok, you can then use this new column in your model.
Note that for text column, you will not be able to apply aggregat on it and to use it in the Data, I would suggest to create a column that gives a numeric (like number of minutes)


Second one is more or less the same but directly in the model.
In your model add an element and just override its definition in the properties:
Advanced->Custom SQL: CONVERT(VARCHAR,([FASE2].[DataOra]-[FASE1].[DataOra]),108)
Options->Data Type: Text
Definition->Name: Difference

You may also view this post on formula

I hope it helps....

Edited by user Tuesday, December 2, 2014 8:48:07 AM(UTC)  | Reason: Not specified

j.torres@tewis.com  
#3 Posted : Thursday, May 2, 2019 9:14:53 PM(UTC)
j.torres@tewis.com

Rank: Member

Groups: Registered
Joined: 4/7/2019(UTC)
Posts: 21
Spain

Thanks: 6 times
Hello. Is not possible to use the power of Razor engine to create calculated fields?
epf  
#4 Posted : Friday, May 3, 2019 5:28:01 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 673
Switzerland

Thanks: 12 times
Was thanked: 116 time(s) in 113 post(s)
One good place to do that is to use the 'Final Script' of the model where you can change the Result tables...
Check the sample '54-Final Script'.

You can also use the 'Post Load Script' to change the value of the result table got just after the SELECT but before the creation of the result tables with Page Splitting and Cross Table.
j.torres@tewis.com  
#5 Posted : Monday, May 20, 2019 2:23:58 PM(UTC)
j.torres@tewis.com

Rank: Member

Groups: Registered
Joined: 4/7/2019(UTC)
Posts: 21
Spain

Thanks: 6 times
Hello. We're playing with the Post Load Script in order to manipulate the datatable. Here is the code. Razor is OK but we when we execute the report, a message says that the specified conversion is not valid. I guess that the base cell is an object. How can we get it? Thanks


...Ok...The problem is due to when trying to assign value to null cells. I'm looking for the type to initialize null cells, something similar to:
table.Rows[row][1] = new CellValue(intValue);

Thanks.

@using Seal.Model
@using System.Data
@{
ReportModel model = Model;
DataTable table = model.ResultTable;
ReportExecutionLog log = model.Report;

//Script executed to modify the model result table after it has been loaded from the database
//Modify values in the current result table, rows can also be added and deleted.
//Note that other assemblies can be used by saving the .dll in the Repository 'Assemblies' sub-folder...
log.LogMessage("Modifying table with the model 'Post Load Script'...");

for (int row = 1; row < table.Rows.Count; row++)
{

table.Rows[row][1] = (int)table.Rows[row][1] + (int)table.Rows[row-1][1];
table.Rows[row][2] = (int)table.Rows[row][2] + (int)table.Rows[row-1][2];
table.Rows[row][3] = (float)((int)table.Rows[row][1] - (int)table.Rows[row][2])/(int)table.Rows[row][1];

}

}

Edited by user Monday, May 20, 2019 9:49:02 PM(UTC)  | Reason: Not specified

epf  
#6 Posted : Tuesday, May 21, 2019 6:01:30 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 673
Switzerland

Thanks: 12 times
Was thanked: 116 time(s) in 113 post(s)
ResultCell is an object, check this post to see the properties: https://sealreport.org/f....aspx?g=posts&t=1967
the value of the cell is stored in Value (type object):
table.Rows[row][1].Value = 1234;


The version 5.0 will come soon with a better documentation...
j.torres@tewis.com  
#7 Posted : Tuesday, May 21, 2019 6:42:47 AM(UTC)
j.torres@tewis.com

Rank: Member

Groups: Registered
Joined: 4/7/2019(UTC)
Posts: 21
Spain

Thanks: 6 times
Thanks epf!.
DataTable cells doesn't have a property nor method called "Value". I think you're refering to ResultTable, but is not my case as in the PostSQL script the table I need to modify is the DataTable.
However, I've found the problem. I was using if (cell == null)...and is not correct. The right method is if(Convert.IsDBNull(cell))....So solved!

Edited by user Thursday, June 6, 2019 11:51:58 AM(UTC)  | Reason: Solved past issue but a new one now.

epf  
#8 Posted : Tuesday, May 21, 2019 7:55:44 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 673
Switzerland

Thanks: 12 times
Was thanked: 116 time(s) in 113 post(s)
Sorry I mismatch DataTable and ResultTable...
DataTable are standard .Net Object (refer to https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/dataset-datatable-dataview/datatables)

Concerning your rounding problem, perhaps try Convert.ToDecimal()...

Edited by user Tuesday, May 21, 2019 7:56:40 AM(UTC)  | Reason: Not specified

thanks 1 user thanked epf for this useful post.
j.torres@tewis.com on 5/21/2019(UTC)
j.torres@tewis.com  
#9 Posted : Tuesday, May 21, 2019 8:39:36 AM(UTC)
j.torres@tewis.com

Rank: Member

Groups: Registered
Joined: 4/7/2019(UTC)
Posts: 21
Spain

Thanks: 6 times
Thanks epf.
As you recommended, I've CAST to Decimal from the Select and now is working. The problem came from the conversion types that the SQL connector does. Specifically, Real types are converted to single. That's the problem.

Thanks.

Edited by user Thursday, June 6, 2019 11:53:52 AM(UTC)  | Reason: Not specified

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.