如何用存储过程创建rdlc报告？ [英] How to create rdlc report with stored procedure?
问题描述
我有这个存储过程的问题，我无法创建RDLC报告，请帮助我。谢谢。
I have problem with this stored procedure and i can not create RDLC report fromthat please help me. thanks.
USE [mystored]
GO
/****** Object: StoredProcedure [dbo].[Sp_RepReceptionServicesParts] Script Date: 04/13/2016 11:16:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[Sp_RepReceptionServicesParts]
(
@UserType as char(1),
@UserCode as varchar(5),
@DateFrom as varchar(10),
@DateTo as Varchar(10) ,
@DealerNo as varchar(5),
@DealerNoCombo as varchar(5),
@VehicleCode as varchar(5) ,
@ChassisNo as varchar(30),
@ReceptionCode as varchar(10),
@FactorNo as varchar(10),
@ServiceUserCode as varchar(30),
@PartNo as varchar(30) ,
@CostCenter as char(1) ,
@PartOrService as char(1),
@ReceptionType as char(1),
@VehicleType as char(1)
)
as
begin
declare @strParts as varchar(max)
declare @strServices as varchar(max)
declare @strFinal as varchar(max)
set @DateFrom = '''' + @DateFrom + ''''
set @DateTo = '''' + @DateTo + ''''
set @strParts = '
select ReceptionCode,RecDate,FactorNo,rp.DeliverDate FactorDate,VehicleFarsiName VehicleName, case when s.Imported = 1 then ''notok'' else ''ok'' end as VehicleType ,RecKilometer ReceptionKilometer, ''Parts'' as Type,
PartFarsiName as Name,''-'' as ServerName,PartNo as Code, Warranty=case HasWarranty when 1 then ''Yes'' when 0 then ''No'' end,
max(UnitPrice) UnitPrice,sum(Number) Qty,Sum(ExtraPrice) ExtraPrice,Sum(SpecialDiscount) Discount, Sum((UnitPrice*Number)+ExtraPrice - SpecialDiscount ) as Price ,ChassisNo,MotorNo,s.ActualDeliveryDate ''date start''
from V_RecParts rp
left join
V_LastSubscribers s
on rp.SubScriberCode = s.SubScriberCode
where RecDate between ' + @DateFrom + ' and ' + @DateTo + ' --DealerNoRMN --VehicleCodeRMN --ChassisNoRMN --ReceptionCodeRMN --FactorNoRMN
--PartNoRMN --CostCenterRMN --ReceptionTypeRMN --VehicleTypeRMN
group by ReceptionCode,RecDate,FactorNo,rp.DeliverDate,VehicleFarsiName,RecKilometer,HasWarranty,PartFarsiName,PartNo,ChassisNo,MotorNo,s.ActualDeliveryDate, s.imported '
set @strServices = '
select ReceptionCode,RecDate,FactorNo,rs.DeliverDate FactorDate,VehicleFarsiName VehicleName, case when s.Imported = 1 then ''notok'' else ''ok'' end as VehicleType,RecKilometer ReceptionKilometer, ''Services'' as Type,
FarsiName as Name,ServerName,ServiceUserCode as Code, Warranty=case HasWarranty when 1 then ''Yes'' when 0 then ''No'' end,
max(UnitPrice) UnitPrice,sum(Number) Qty,Sum(ExteraPrice) ExtraPrice,Sum(SpecialDiscount) Discount, Sum((UnitPrice*Number)+ExteraPrice - SpecialDiscount ) as Price ,ChassisNo,MotorNo,s.ActualDeliveryDate ''date start''
from V_RecServices rs
left join
V_LastSubscribers s
on rs.SubScriberCode = s.SubScriberCode
where RecDate between ' + @DateFrom + ' and ' + @DateTo + ' --DealerNoRMN --VehicleCodeRMN --ChassisNoRMN --ReceptionCodeRMN --FactorNoRMN
--ServiceUserCodeRMN --CostCenterRMN --ReceptionTypeRMN --VehicleTypeRMN
group by ReceptionCode,RecDate,FactorNo,rs.DeliverDate,VehicleFarsiName ,RecKilometer ,HasWarranty,FarsiName,ServerName,ServiceUserCode,ChassisNo,MotorNo,s.ActualDeliveryDate,s.imported '
if @PartOrService = 'P' -- output is just Parts
set @strFinal = @strParts
else
if @PartOrService = 'S' -- output is just Services
set @strFinal = @strServices
else
set @strFinal = '( '+ @strParts + ') union ( ' + @strServices + ' ) '
set @strFinal = @strFinal + ' order by ReceptionCode '
if @UserType <> 'A'
set @strFinal = replace(@strFinal ,'--DealerNoRMN', ' and ReceptionCode / 100000 = ' + @DealerNo )
else
if @DealerNoCombo <> -1
set @strFinal = replace(@strFinal ,'--DealerNoRMN', ' and ReceptionCode / 100000 = ' + @DealerNoCombo )
else
set @strFinal = replace(@strFinal ,'--DealerNoRMN', '' )
if len(@vehicleCode) > 0
set @strFinal = replace(@strFinal ,'--VehicleCodeRMN', ' and VehicleCode = ' + @vehicleCode )
else
set @strFinal = replace(@strFinal ,'--VehicleCodeRMN', '' )
if len(@ChassisNo) > 0
set @strFinal = replace(@strFinal ,'--ChassisNoRMN', ' and ChassisNo like ''%' + @ChassisNo + '%''' )
else
set @strFinal = replace(@strFinal ,'--ChassisNoRMN', '' )
if len(@ReceptionCode) > 0
set @strFinal = replace(@strFinal ,'--ReceptionCodeRMN', ' and ReceptionCode = ' + @ReceptionCode )
else
set @strFinal = replace(@strFinal ,'--ReceptionCodeRMN', '' )
if len(@FactorNo) > 0
set @strFinal = replace(@strFinal ,'--FactorNoRMN', ' and FactorNo = ' + @FactorNo )
else
set @strFinal = replace(@strFinal ,'--FactorNoRMN', '' )
if len(@PartNo) > 0
set @strFinal = replace(@strFinal ,'--PartNoRMN', ' and PartNo like ''%' + @PartNo + '%''' )
else
set @strFinal = replace(@strFinal ,'--PartNoRMN', '' )
if len(@ServiceUserCode) > 0
set @strFinal = replace(@strFinal ,'--ServiceUserCodeRMN', ' and ServiceUserCode like ''%' + @ServiceUserCode + '%''' )
else
set @strFinal = replace(@strFinal ,'--ServiceUserCodeRMN', '' )
if @CostCenter = 'G' -- output is just guaranty
set @strFinal = replace(@strFinal ,'--CostCenterRMN', ' and HasWarranty = 1' )
else
if @CostCenter = 'C' -- output is just Customer
set @strFinal = replace(@strFinal ,'--CostCenterRMN', ' and HasWarranty = 0' )
else -- 'B' output is Customer and guaranty
set @strFinal = replace(@strFinal ,'--CostCenterRMN', '' )
if @ReceptionType = 'C' -- output is just Closed Reception
set @strFinal = replace(@strFinal ,'--ReceptionTypeRMN', ' and QCFlag = 1 and FactorNo is not Null' )
else
if @ReceptionType = 'O' -- output is just Open Reception
set @strFinal = replace(@strFinal ,'--ReceptionTypeRMN', ' and (QCFlag = 0 or FactorNo is Null)' )
else
set @strFinal = replace(@strFinal ,'--ReceptionTypeRMN', '' )
if @VehicleType = 'F'
set @strFinal = replace(@strFinal ,'--VehicleTypeRMN', ' and s.Imported = 0 ' ) -- Imported = 0 --> Factory
else
if @VehicleType = 'N'
set @strFinal = replace(@strFinal ,'--VehicleTypeRMN', ' and s.Imported = 1 ' ) -- Imported = 1 --> Not Factory
else
set @strFinal = replace(@strFinal ,'--VehicleTypeRMN', '' )
--print @strFinal
exec (@strFinal)
end
0
下来投票
接受
i可以通过以下代码向datagridview显示数据：
public DataTable MySelect（string UserType，string UserCode，string DateFrom，string DateTo，string DealerNo，string DealerNoCombo，string VehicleCode，string ChassisNo，string ReceptionCo de，string FactorNo，string ServiceUserCode，string PartNo，string CostCenter，string PartOrService，string ReceptionType，string VehicleType，string HasWarranty）{
if（openConnection（）== true）
{
Cmd =新的SqlCommand（Sp_MyFullShowing，Con2）;
//Cmd.CommandText =Sp_MyFullShowing; < br $>
Cmd.Parameters.AddWithValue（@ UserType，UserType）;
Cmd.Parameters.AddWithValue（@ UserCode，UserCode）;
Cmd.Parameters.AddWithValue（@ DateFrom，DateFrom）;
Cmd.Parameters.AddWithValue（@ DateTo，DateTo）;
Cmd.Parameters.AddWithValue （@ DealerNo，DealerNo）;
Cmd.Parameters.AddWithValue（@ DealerNoCombo，DealerNoCombo）;
Cmd.Parameters.AddWithValue（@ VehicleCode， VehicleCode）;
Cmd.Parameters.AddWithValue（@ ChassisNo，ChassisNo）;
Cmd.Parameters.AddWithValue（@ ReceptionCode，ReceptionCode）;
Cmd.Parameters.AddWithValue（@ FactorNo，FactorNo）;
Cmd.Parameters.AddWithValue （@ ServiceUserCode，ServiceUserCode）;
Cmd.Parameters.AddWithValue（@ PartNo，PartNo）;
Cmd.Parameters.AddWithValue（@ CostCenter， CostCenter）;
Cmd.Parameters.AddWithValue（@ PartOrService，PartOrService）;
Cmd.Parameters.AddWithValue（@ ReceptionType，ReceptionType）;
Cmd.Parameters.AddWithValue（@ VehicleType，VehicleType）;
Cmd.Parameters.AddWithValue（@ HasWarranty，HasWarranty）;
Cmd.CommandType = CommandType.StoredProcedure;
DT = new DataTable（）;
Adapter = new SqlDataAdapter（Cmd）;
Adapter.Fill（DT）;
close连接（）;
}
返回DT;
}
和在按钮中单击此代码：
dt = sql.MySelect（，，lbl1.Text，lbl2.Text，，， ，，txtPaziresh.Text，，，，，，，，cmbGarant.Text; elDataGridView3.DataSource = dt.DefaultView;
但是我无法从中创建rdlc报告。任何人都不能helppppppp ????
我尝试过的事情：
我有这个存储过程的问题，我无法创建RDLC报告，请帮助我。谢谢。
0
down vote
accept
i can show data in to datagridview by this code:
public DataTable MySelect(string UserType,string UserCode,string DateFrom,string DateTo, string DealerNo,string DealerNoCombo,string VehicleCode,string ChassisNo,string ReceptionCode, string FactorNo,string ServiceUserCode,string PartNo,string CostCenter,string PartOrService, string ReceptionType, string VehicleType, string HasWarranty) {
if (openConnection() == true)
{
Cmd = new SqlCommand("Sp_MyFullShowing", Con2);
//Cmd.CommandText = "Sp_MyFullShowing";
Cmd.Parameters.AddWithValue("@UserType", UserType);
Cmd.Parameters.AddWithValue("@UserCode", UserCode);
Cmd.Parameters.AddWithValue("@DateFrom", DateFrom);
Cmd.Parameters.AddWithValue("@DateTo", DateTo);
Cmd.Parameters.AddWithValue("@DealerNo", DealerNo);
Cmd.Parameters.AddWithValue("@DealerNoCombo", DealerNoCombo);
Cmd.Parameters.AddWithValue("@VehicleCode", VehicleCode);
Cmd.Parameters.AddWithValue("@ChassisNo", ChassisNo);
Cmd.Parameters.AddWithValue("@ReceptionCode", ReceptionCode);
Cmd.Parameters.AddWithValue("@FactorNo", FactorNo);
Cmd.Parameters.AddWithValue("@ServiceUserCode", ServiceUserCode);
Cmd.Parameters.AddWithValue("@PartNo", PartNo);
Cmd.Parameters.AddWithValue("@CostCenter", CostCenter);
Cmd.Parameters.AddWithValue("@PartOrService", PartOrService);
Cmd.Parameters.AddWithValue("@ReceptionType", ReceptionType);
Cmd.Parameters.AddWithValue("@VehicleType", VehicleType);
Cmd.Parameters.AddWithValue("@HasWarranty", HasWarranty);
Cmd.CommandType = CommandType.StoredProcedure;
DT = new DataTable();
Adapter = new SqlDataAdapter(Cmd);
Adapter.Fill(DT);
closeConnection();
}
return DT;
}
and in button click this code:
dt = sql.MySelect("", "", lbl1.Text, lbl2.Text, "", "", "", "", txtPaziresh.Text, "", "", "", "", "", "", "", cmbGarant.Text); elDataGridView3.DataSource = dt.DefaultView;
but i can not create rdlc report from that. anyone can not helppppppp????
What I have tried:
I have problem with this stored procedure and i can not create RDLC report fromthat please help me. thanks.
推荐答案
由于您使用的是动态查询，因此您不会在RDLC报告中获取列详细信息。
您可以这样做： br />
1.在报告前评论动态查询。
2.使用动态查询中提到的相同文件。
3.执行程序。
4.现在在报告中使用此程序。
5.在这里，您将看到您在新查询中提到的所有字段。
6.现在删除/评论您的新查询并取消注释您的动态查询。
7.执行程序。
然后运行你的程序或报告你会找到你的结果。
Ashish
You will not get columns details in RDLC report because you are using dynamic query.
you can do like Below :
1. Before making report comment your dynamic query.
2. Make a new query(don't make this query dynamic) with the same fileds as mention in dynamic query.
3. execute procedure.
4. Now use this procedure in your report.
5. here you will see all your fields which you mention in your procedure in new query.
6. Now remove/comment your new query and uncomment your dynamic query.
7. execute procedure.
Then run your program or reports you will find your result.
Ashish
这篇关于如何用存储过程创建rdlc报告？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
