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

Notification

Icon
Error

Options
Go to last post Go to first unread
reach2rv  
#1 Posted : Tuesday, March 20, 2018 1:31:14 PM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 7
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

epf  
#2 Posted : Tuesday, March 20, 2018 2:39:08 PM(UTC)
epf

Rank: Administration

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

Thanks: 7 times
Was thanked: 68 time(s) in 66 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

reach2rv  
#3 Posted : Wednesday, March 21, 2018 3:40:01 PM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 7
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

http://forum.sealreport.org/def...=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

reach2rv  
#4 Posted : Wednesday, March 21, 2018 4:41:30 PM(UTC)
reach2rv

Rank: Newbie

Groups: Registered
Joined: 1/29/2016(UTC)
Posts: 7
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

reach2rv  
#5 Posted : Wednesday, March 21, 2018 7:32:59 PM(UTC)
reach2rv

Rank: Newbie

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

Thanks: 1 times
Solved
epf  
#6 Posted : Thursday, March 22, 2018 9:07:25 AM(UTC)
epf

Rank: Administration

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

Thanks: 7 times
Was thanked: 68 time(s) in 66 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

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: 7
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..

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.

Powered by YAF 2.0.0 | YAF © 2003-2018, Yet Another Forum.NET
This page was generated in 0.121 seconds.