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
lvg  
#1 Posted : Wednesday, August 10, 2016 1:57:16 PM(UTC)
lvg

Rank: Newbie

Groups: Registered
Joined: 8/10/2016(UTC)
Posts: 1
United Kingdom
Location: South Yorkshire

I would like to use prompted restriction values to filter a subquery in the FROM clause, and was wondering if this is possible and, if so, what would be the correct syntax. So far I get 'Invalid column name' errors both if I try to use the restriction value directly as a parameter, or if I try to declare it as a variable before the select statement.

The (simplified) query looks like:
SELECT
DatesTab.DatesCol AS C0,
SUM(CASE
WHEN AssetsTab.ArchiveDate < DatesTab.DatesCol
THEN 0
WHEN AssetsTab.PurchaseDate > DatesTab.DatesCol
THEN 0
ELSE 1
END) AS C1
FROM
(select * from
(select DATEADD(month, number, '1900-01-01') as DatesCol
from master..spt_values
where type = 'P' and number > 0 and number < 1000) AS rawDates
where rawDates.DatesCol Between StartDate and EndDate )
AS DatesTab
CROSS JOIN
(select
Assets.AssetID AS AssetID,
Assets.ArchiveDate AS ArchiveDate,
Assets.PurchaseDate AS PurchaseDate,
from Assets
where Assets.PurchaseDate <= StartDate and Assets.ArchiveDate >= EndDate
WHERE (1=1)
AND (1=1)
AND (1=1)

---
and in this case two of the restrictions are named StartDate and EndDate respectively, and both are prompted at execution DateTimes, 'Value Only', and with UseAsParameter set to true.

Any advice would be much appreciated.
epf  
#2 Posted : Thursday, August 11, 2016 1:18:09 PM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
It is probably possible to do so using the Pre-Load Script of the model (check samples 51 and 52 to have a clue).
However, it is not so clear how you have defined your tables in your Data Source, and what is the SQL generated (F7 in the model).
Perhaps can you build up a sample based on Northwind to reproduce the question...
xexplorea  
#3 Posted : Tuesday, August 23, 2016 12:49:28 AM(UTC)
xexplorea

Rank: Newbie

Groups: Registered
Joined: 8/20/2016(UTC)
Posts: 1
China
Location: shanghai

Dear Epf
I'm facing the same problem,and it troubles me for a few days about how to pass a parameter into the from clause.
I check the sample 51 and 52 as you advised,but the code does not involve to the situation (restrict the value of a specific column),and I barely know C# and the seal report class series.

eg.

select
amount
from
(
select
fdate
,sum(amount) as
from table_one
where hour<parameter_hour
group by
fdate
) a
where fdate=parameter_date


Report web user can pass value to the parameter_date from the web,if I add a restriction to the "Drop Retrictions" blank of the Model.
The problem is how to mke sure report web user can pass a value to the parameter_hour...
You said I can code in the Pre load Script,but I hive little idea of how to use seal report class and how to code by C#,

Would you kindly show me how to restrict the value of a certern column just like the example above,so I can learn how to code the subquery restriction by C# in other complex cases.


At last,seal report is a wonderful software,you did a great job.
I appreciate your dedication .
Thanks a lot!
epf  
#4 Posted : Monday, August 29, 2016 8:52:51 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Hi, I did a sample to show how to do this on Northwind (filtering a sub table on a value given for employee ID), here are the steps:

1. In SealMasterTable, use the following SQL Statement to define your table:
Code:
select * from (select * from employees where employeeId > 123456789) a


2. In your Model, drop Employee Id and Last Name in the row panel to display them in the report.

3. Select Employee ID and drop it in the restriction panel, then define the restriction properties:
Operator: Value Only
Operator Label: >
Use as parameter: true

4. Finally change the SQL definition of your table in the Pre-Load Script of your model:
Code:

	//Save original SQL in the report Tag 
	if (model.Report.Tag == null) {
		model.Report.Tag = model.Source.MetaData.MasterTable.Sql;
	}
	
	model.Source.MetaData.MasterTable.Sql = ((string)model.Report.Tag).Replace("123456789", restrictions[0].Value1);


When you execute the report, the value is prompted, then used to modify the Sql of the SealMasterTable...
You have to adapt this sample for your needs (using date time values or numeric, etc.)
I hope it helps.

Edited by user Monday, August 29, 2016 8:54:34 AM(UTC)  | Reason: Not specified

mirekm_m  
#5 Posted : Thursday, October 27, 2016 7:34:24 PM(UTC)
mirekm_m

Rank: Newbie

Groups: Registered
Joined: 9/28/2016(UTC)
Posts: 4
Poland
Location: warsaw

Thanks: 4 times
Hi,
this piece of code was very usefull for me:

if (model.Report.Tag == null) {
model.Report.Tag = model.Source.MetaData.MasterTable.Sql;
}


but, can You tell me how can I modify sql code of tables others than Master Table. For Example I have table named "MyQuery", code:

string MySql =model.Source.MetaData.MyQuery.Sql;

doesn't work.

Thanks for Your great job

regards Mirek

Edited by user Thursday, October 27, 2016 7:35:03 PM(UTC)  | Reason: Not specified

epf  
#6 Posted : Friday, October 28, 2016 6:48:51 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
If you are talking about selecting another MetaTable from you source model,
here is some code that selects Employees from Northwind:

Code:
	
MetaTable table = model.Source.MetaData.Tables.FirstOrDefault(i => i.Name == "Employees");
if (table != null) {
    log.LogMessage("Gotcha =>" + table.Name);
}

Edited by user Friday, October 28, 2016 6:49:39 AM(UTC)  | Reason: Not specified

thanks 1 user thanked epf for this useful post.
mirekm_m on 10/28/2016(UTC)
mirekm_m  
#7 Posted : Friday, October 28, 2016 7:04:13 AM(UTC)
mirekm_m

Rank: Newbie

Groups: Registered
Joined: 9/28/2016(UTC)
Posts: 4
Poland
Location: warsaw

Thanks: 4 times
epf Thanks for Your quick support. It works perfectly, though can You tell me where can I store original sql? code that doesn't work:

if (table != null) {
log.LogMessage("Gotcha =>" + table.Name);

//Save original SQL in the Table Tag
if (table.Tag == null) {
table.Tag = table.Sql; //<--- there is no table.Tag property
}
}

regards
Mirek

Edited by user Friday, October 28, 2016 7:04:43 AM(UTC)  | Reason: Not specified

epf  
#8 Posted : Friday, October 28, 2016 9:20:17 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
No the Tag object is just in the report....
you can use it to create a list of SQL per table using a dictionary:

Code:
if (model.Report.Tag == null) {
    model.Report.Tag = new Dictionary<string, string>();
}
var dictionary = model.Report.Tag as Dictionary<string, string>;
dictionary[table.Name] = table.Sql;
log.LogMessage("Dico has {0} value(s)", dictionary.Count);


it is just the power of C#
thanks 1 user thanked epf for this useful post.
mirekm_m on 10/28/2016(UTC)
mirekm_m  
#9 Posted : Friday, October 28, 2016 10:28:37 AM(UTC)
mirekm_m

Rank: Newbie

Groups: Registered
Joined: 9/28/2016(UTC)
Posts: 4
Poland
Location: warsaw

Thanks: 4 times
there is a problem while execution in line

model.Report.Tag = new Dictionary<string, string>();

error:
Error in model 'MyModelName': Error when executing 'Pre Load Script'.
You can cast object type 'System.Collections.Generic.Dictionary`2[System.String,System.String]' to type 'System.String'.
epf  
#10 Posted : Friday, October 28, 2016 11:58:21 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
It works fine for me (Tag is an Object, you can assign what you want), check which line causes the error (by tracing and commenting)...
thanks 1 user thanked epf for this useful post.
mirekm_m on 10/28/2016(UTC)
mirekm_m  
#11 Posted : Friday, October 28, 2016 12:48:42 PM(UTC)
mirekm_m

Rank: Newbie

Groups: Registered
Joined: 9/28/2016(UTC)
Posts: 4
Poland
Location: warsaw

Thanks: 4 times
Ok, I will look it closer, great thanks for your assist
epf  
#12 Posted : Friday, June 28, 2019 6:35:10 AM(UTC)
epf

Rank: Administration

Groups: Administrators
Joined: 12/20/2013(UTC)
Posts: 1,209
Switzerland

Thanks: 14 times
Was thanked: 205 time(s) in 198 post(s)
Update with the 5.0 version, you can use Common Restrictions and Common Values.

Please check Common Restrictions for more information

Edited by user Friday, June 28, 2019 6:35:41 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.