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
ctrazzi  
#1 Posted : Wednesday, October 4, 2023 4:33:50 PM(UTC)
ctrazzi

Rank: Member

Groups: Registered
Joined: 7/14/2023(UTC)
Posts: 25
Man
Italy
Location: Torino

Thanks: 8 times
Hello, in my report I have add a filter on a date (selected by user) field with the between condition, but I can not include the initial and final date.
For example, my user choose:

min date=2/01/2023
max date=5/03/2023

With this inputs I got all values from 3/01/2023 to 4/03/2023, but I need even the days 2/01/2023 and max date=5/03/2023.

How can I do it with the between condition?

epf  
#2 Posted : Thursday, October 5, 2023 11:05:17 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)
Check the SQL generated for your model:
e.g. if you input 01/01/1997 and 31/12/1997
WHERE (Orders.OrderDate BETWEEN '1997-01-01 00:00:00' AND '1997-12-31 00:00:00')

so it may depends on your date values in the database (if the hours are specified).

so you may input:
31/12/1997 23:59:59
0R
01/01/1998

to achieve what you want

thanks 1 user thanked epf for this useful post.
ctrazzi on 10/6/2023(UTC)
ctrazzi  
#3 Posted : Friday, October 6, 2023 8:46:50 AM(UTC)
ctrazzi

Rank: Member

Groups: Registered
Joined: 7/14/2023(UTC)
Posts: 25
Man
Italy
Location: Torino

Thanks: 8 times
Hi, thanks I understand the problem.
Unfortunately I can not modify the database datas...Can I set some rule in the filter values so that the selected day will be automatically 31/12/1997 23:59:59 like in your example?
epf  
#4 Posted : Wednesday, October 11, 2023 3:20:46 PM(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 can do that in a task:

Code:
@{
    ReportTask task = Model;
    Report report = task.Report;
    var restr = report.AllRestrictions.First(i => i.SQLColumn == "Orders.OrderDate");
    
    
    restr.Date1 = restr.Date1.AddDays(1);
    //or Date2 for a between
}

thanks 1 user thanked epf for this useful post.
ctrazzi on 10/16/2023(UTC)
ctrazzi  
#5 Posted : Monday, October 16, 2023 3:12:21 PM(UTC)
ctrazzi

Rank: Member

Groups: Registered
Joined: 7/14/2023(UTC)
Posts: 25
Man
Italy
Location: Torino

Thanks: 8 times
Thank you, its working for most of my cases but I am stuck in particular situation.
I have a report with 2 different Sources and 2 different models for two peoduct A and B.

In my case I have DB_A and DB_B, the column I have to use in the DBs have these names and I can not change them:
-For DB_A: ProductionDate_A and OrderDate_A
-For DB_A: ProductionDate_B and OrderDate_B

My two models perform data manipulation on Productions and Orders for both products in the same report.
I need to perform the same task you give me but on both products.

I have try to add the OR condition like this:

var restr = report.AllRestrictions.First(i => i.SQLColumn == "Orders.OrderDate_A" || i.SQLColumn == "ProductionDate_A");

Add another variable to manage the two cases:

var restrA1 = report.AllRestrictions.First(i => i.SQLColumn == "Orders.OrderDate_A");

restrA1.Date1 = restrA1.Date1.AddDays(1);

var restrA2 = report.AllRestrictions.First(j => j.SQLColumn == "ProductionDate_A");

restrA2.Date1 = restrA2.Date1.AddDays(1);

And create for different task for each case but it gives me this error: "Sequence contains no matching element"

I have check multiple times and the column name are corrects, no space, underlines,ec...
I have already try to change the execution time of the task but nothing changes...
What can be the problem?

epf  
#6 Posted : Monday, October 16, 2023 4:44:49 PM(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)
You can debug your restrictions names by adding some traces in the task:
Code:

    foreach (var restriction in report.AllRestrictions) {
        task.LogMessage($"Name:{restriction.Name}\r\nSQLColumn:{restriction.SQLColumn}");
    }
ctrazzi  
#7 Posted : Tuesday, October 17, 2023 9:02:08 AM(UTC)
ctrazzi

Rank: Member

Groups: Registered
Joined: 7/14/2023(UTC)
Posts: 25
Man
Italy
Location: Torino

Thanks: 8 times
I have manage to "resolve" partially the problem...I had to modify all my filter parameter to the same name "Date".
Now only the first filter work.

What I can not understand is why I am getting that problem...In order to use:
@{
ReportTask task = Model;
Report report = task.Report;
var restr = report.AllRestrictions.First(i => i.SQLColumn == "Orders.OrderDate");


restr.Date1 = restr.Date1.AddDays(1);
//or Date2 for a between
}

like you suggested, "Orders.OrderDate" must be without dots, underscore or other particular characters?


Another question, I was not able to use your debug instruction...where and how I should use it? For example, in the original script you give me, where I should put and use it?

Last one, is possible to write a task only for a specific model?
For example, in my report I have model1, model2, model3,...,model6, can a task only work for model1, another only on model5, etc..?

Edited by user Tuesday, October 17, 2023 10:49:40 AM(UTC)  | Reason: Not specified

epf  
#8 Posted : Tuesday, October 17, 2023 12:58:23 PM(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)
Code:
@{
ReportTask task = Model;
Report report = task.Report;

foreach (var restriction in report.AllRestrictions) {
        task.LogMessage($"Name:{restriction.Name}\r\nSQLColumn:{restriction.SQLColumn}");
}

//Check Task Samples:
foreach (var model in report.Models) {
    foreach (var restriction in model.Restrictions.Where(i => i.IsDateTime /* i.SQLColumn == "Orders.OrderDate" */)) {
        restriction.Operator = Operator.Between;
        restriction.Date1Keyword = "";
        restriction.Date2Keyword = "";
    }
}


var restr = report.AllRestrictions.First(i => i.SQLColumn == "Orders.OrderDate");


restr.Date1 = restr.Date1.AddDays(1);
//or Date2 for a between
}
thanks 1 user thanked epf for this useful post.
ctrazzi on 10/17/2023(UTC)
ctrazzi  
#9 Posted : Tuesday, October 17, 2023 1:06:12 PM(UTC)
ctrazzi

Rank: Member

Groups: Registered
Joined: 7/14/2023(UTC)
Posts: 25
Man
Italy
Location: Torino

Thanks: 8 times
Hi, thank you a lot for your fast reply.
Can I ask you to explain a little what your script do? I am not quite familiar with razor script...

Especially I do not understand if you have inserted in this script all the aswer at the questions (this is not a complaint), or just to the main one regarding the debug instructions.
epf  
#10 Posted : Tuesday, October 17, 2023 3:43:25 PM(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)
No it is just for Debug instructions.

This shows how to list the restriction for each model:
//Check Task Samples:
foreach (var model in report.Models) {
foreach (var restriction in model.Restrictions.Where(i => i.IsDateTime /* i.SQLColumn == "Orders.OrderDate" */)) {
}
}

It is simple C#, and the objects are described at https://sealreport.org/H...8c-2dd7-449968f4232b.htm
and https://sealreport.org/Reports
You have to learn the C# and you can check all the samples.

Good luck.
thanks 1 user thanked epf for this useful post.
ctrazzi on 10/17/2023(UTC)
Users browsing this topic
Guest
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.