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.

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!

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

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

Ok, I will look it closer, great thanks for your assist