我尝试更新Microsoft报告。它所做的是写出来的有多少客户,其中不包括在转换过程和原因是什么。目前,该方案将所有被删除的客户机到服务器,然后将查询其回补一个专业表的结果。
I am trying to update a Microsoft report. What it does is write out how many clients where excluded from a conversion process and for what reason. Currently the program writes all of the deleted clients back to the server then queries it back to fill a specialty table with the results.
下面是当前查询:
SELECT DeletedClients.Reason,
COUNT(DeletedClients.Reason) AS Number,
CAST(CAST(COUNT(DeletedClients.Reason) AS float)
/ CAST(t.Total AS float)
* 100 AS numeric(4, 1)) AS percentage
FROM DeletedClients CROSS JOIN
(SELECT COUNT(*) AS Total
FROM DeletedClients AS DeletedClients_1
WHERE (ClinicID = @ClinicID)) AS t
WHERE (DeletedClients.ClinicID = @ClinicID)
AND (DeletedClients.TotalsIdent = @ident)
GROUP BY DeletedClients.Reason, t.Total
ORDER BY Number DESC
我想要做的是不写 DeletedClients
到服务器上,因为它已经存在于记忆在我的计划是一个DataTable,它只是减缓报告填充信息的数据库,我们不需要保存。
What I would like to do is not write DeletedClients
to the server as it already exists in memory in my program as a DataTable and it is just slowing down the report and filling the database with information we do not need to save.
我的主要问题是,无论是:
如何查询一个数据表,使具有相同的结果,如果我写出来的SQL服务器内存中的数据表中的一个新的读回与上面的查询?
How do I query a data table to make a new in memory data table that has the same results as if I wrote out the the SQL server and read it back in with the query above?
或
如何在Microsoft报告你做一个group by子句中的Tablix项目以打开 =领域!Reason.Value
=领域!数量.value的
=领域!percentage.Value
成类似返回的结果的东西从上面的查询?
How in Microsoft Reports do you do a group by clause for items in a Tablix to turn =Fields!Reason.Value
=Fields!Number.Value
=Fields!percentage.Value
into something similar to the returned result from the query above?
您可以使用DataTable.Select来查询数据表。
You can use DataTable.Select to query the DataTable.
DataTable table = GetDataTableResults();
DataTable results = table.Select("SomeIntColumn > 0").CopyToDataTable();
或者更复杂的查询,你可以使用LINQ查询数据表:
Or for more complex queries, you can use LINQ to query the DataTable:
DataTable dt = GetDataTableResults();
var results = from row in dt.AsEnumerable()
group row by new { SomeIDColumn = row.Field<int>("SomeIDColumn") } into rowgroup
select new
{
SomeID = rowgroup.Key.SomeIDColumn,
SomeTotal = rowgroup.Sum(r => r.Field<decimal>("SomeDecimalColumn"))
};
DataTable queryResults = new DataTable();
foreach (var result in query)
queryResults.Rows.Add(new object[] { result.SomeID, result.SomeTotal });