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
How to pass one restriction value to another
Options
Go to last post Go to first unread
Previous Topic Next Topic
reach2rv  
#1 Posted : Tuesday, March 20, 2018 1:31:14 PM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 8
India
Location: Mumbai

Thanks: 1 times
I have a report which uses query and master table to get data from database. I need to have to restrictions date between and less than date as main query contains union and need between date for some union and less than in some.. how I can take first date of between restriction and pass as lower than date as it will be treated as opening date.

I wanted to achieve this as user will enter only 1 restriction

will this work if added in Preload script of Model
Code:
model.GetRestrictionByName("Ldt").Value1 = model.GetRestrictionByName("Dt").Value1;


Regards

Arvind
Back to top

Wanna join the discussion?! Login to your Seal Report Forum forum accountor Register a new forum account.
Back to top  
epf  
#2 Posted : Tuesday, March 20, 2018 2:39:08 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, but for a date it is

Code:
model.GetRestrictionByName("Ldt").Date1 = model.GetRestrictionByName("Dt").Date1;


you can use Date1,Date2,Date3,Date4
for a numeric or string it is Value1,Value2,Value3,Value4
For a enumerated list it is a List<string> called EnumValues

Code:
model.GetRestrictionByName("Ldt").EnumValues = model.GetRestrictionByName("Dt").EnumValues;

Edited by user Wednesday, March 28, 2018 8:38:09 PM(UTC)  | Reason: Not specified
Back to top
reach2rv  
#3 Posted : Wednesday, March 21, 2018 3:40:01 PM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 8
India
Location: Mumbai

Thanks: 1 times
Thanks it works, but how add restrictions for inner select query of union.

Edit:

Will try this and update

https://sealreport.org/f...=posts&t=278#post630

above solutions seems working only thing is my restriction is Date

Code:
table.Sql = ((string)model.Report.Tag).Replace("31-Dec-2017",model.GetRestrictionByName("Dt").Date1);


it returns error datetime can not convert to string. I tried with Tostring() at the end of restriction but it does not work.

Change 'Tostring()' to 'ToString()' works

Edited by user Wednesday, March 21, 2018 4:37:32 PM(UTC)  | Reason: Got answer in another post
Back to top
reach2rv  
#4 Posted : Wednesday, March 21, 2018 4:41:30 PM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 8
India
Location: Mumbai

Thanks: 1 times
Code:
table.Sql = ((string)model.Report.Tag).Replace("31-Dec-2017",model.GetRestrictionByName("Dt").Date1.ToString());
returns
'31/12/2017 12:00:00 AM' whereas we need to format it to only Date "31-Dec-2017".

how to achieve this??

Error detail:
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

Edited by user Wednesday, March 21, 2018 5:00:29 PM(UTC)  | Reason: Not specified
Back to top
reach2rv  
#5 Posted : Wednesday, March 21, 2018 7:32:59 PM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 8
India
Location: Mumbai

Thanks: 1 times
Solved
Back to top
epf  
#6 Posted : Thursday, March 22, 2018 9:07:25 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 well done, another clever approach could be to modify the JOIN Clause of the joins used by the engine...
You have to select the Join used from the Source of the Model,
then you can modify the Join Clause it for your purpose.
I made a quick try on SQL Server with a restriction named 'Date' and a Join having name starting with 'Purchase':

Code:
	var join = model.Source.MetaData.Joins.FirstOrDefault(i => i.Name.StartsWith("Purchase"));
	if (model.Tag == null) { //Save the orginal clause in the Tag
		model.Tag = join.Clause;
	}
	join.Clause = ((string)model.Tag) + string.Format(" AND getdate() > '{0:yyyy-MM-dd HH:mm:ss}'", model.GetRestrictionByName("Date").Date1);
	log.LogMessage("New Clause: " + join.Clause);

Edited by user Thursday, March 22, 2018 10:07:00 AM(UTC)  | Reason: Not specified
Back to top
thanks 1 user thanked epf for this useful post.
reach2rv on 3/22/2018(UTC)
reach2rv  
#7 Posted : Thursday, March 22, 2018 11:37:00 AM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 8
India
Location: Mumbai

Thanks: 1 times
Originally Posted by: epf Go to Quoted Post
Yes well done, another clever approach could be to modify the JOIN Clause of the joins used by the engine...
You have to select the Join used from the Source of the Model,
then you can modify the Join Clause it for your purpose.
I made a quick try on SQL Server with a restriction named 'Date' and a Join having name starting with 'Purchase':

Code:
	var join = model.Source.MetaData.Joins.FirstOrDefault(i => i.Name.StartsWith("Purchase"));
	if (model.Tag == null) { //Save the orginal clause in the Tag
		model.Tag = join.Clause;
	}
	join.Clause = ((string)model.Tag) + string.Format(" AND getdate() > '{0:yyyy-MM-dd HH:mm:ss}'", model.GetRestrictionByName("Date").Date1);
	log.LogMessage("New Clause: " + join.Clause);


Thank you for your support. I will try this with my next report as I have to make several reports with this tool. Thanks again..

Back to top
Users browsing this topic
Guest (3)
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.

Powered by YAF.NET 2.2.4.11 | YAF.NET © 2003-2024, Yet Another Forum.NET
This page was generated in 0.120 seconds.