ssrs-2008 - SSRS 报告中的空数据
问题描述
我正在处理 ssrs 报告。我能够看到我的存储过程的数据或结果。不幸的是,当使用与我的数据集相同的报告时,我无法看到数据,而是得到 0 条记录。可能是什么原因?我的报告结构将如下图所示:
以下是我的程序:
ALTER Proc [dbo].[SP_Get_CIPPSubjectMarks_New_HTSTEST] -
-7,'1,17,8','2537,2555,2558,2568'
(
@ReportId int=7,
@SubjectId varchar(200),
@SectionId varchar(200)
)
AS
BEGIN
Create table #temp (Name Varchar(500),Class varchar(50),Section
Varchar(20),enrollno varchar(500),SubjectName varchar(500),TermName
varchar(500),TestName varchar(500),TestGroupName varchar(500),Weightage
int,IsWeight bit,Marks varchar(20),MaxMarks int,IsAbsent bit,SubjectOrder
Varchar(200))
Insert into
#temp(Name,Class,Section,enrollNo,SubjectName,
TermName,TestName,TestGroupName,Weightage,IsWeight,Marks,
MaxMarks,IsAbsent,SubjectOrder)
SELECT DISTINCT CONCAT(d.name,' ',d.surname),cls.Value,sec.Value,
e.enroll_no,
CASE WHEN ISNULL(cxs.subject_alias,'')='' THEN CASE WHEN rtv.value='Second
Language' then '2ND LANGUAGE:' + b.Name WHEN
rtv.value='Third Language' THEN '3rd Language:'+b.name else b.name end
ELSE cxs.subject_alias end as SubjectName,
z.str_termname,c.str_termtestname,i.str_testgroupname,
i.str_testweightage,i.is_weighted_average,a.marks,max_marks,a.is_absent,
CASE WHEN rtv.value='Second Language' THEN 'Second Language' WHEN
rtv.Value='Third Language' THEN 'Third Language' When
ISNULL(cxs.subject_alias,'')='' THEN b.Name
ELSE cxs.subject_alias end as SubOrder
FROM marks_entry_HTS a JOIN subject b ON a.fk_subject_id=b.Id and a.marks
is not null
LEFT JOIN subjectCategory_HTS l ON l.Id= b.subject_categoryID
JOIN class_term_test_mapping_HTS c ON a.fk_class_term_test_mapping_id=c.id
-- added by me
JOIN class_term_test_category_HTS ctc on c.fk_termcategoryid = ctc.id
JOIN reference_type_value rtv ON rtv.id=a.fk_subject_type_id
-- close
JOIN Term_Test_Subject_AssessmentType_HTS m ON m.fk_term_testID=c.id and
m.fk_SubjectID=b.Id
JOIN class_report_types_mapping_test_HTS k ON
k.fk_class_term_test_mapping_id=c.Id
JOIN class_term_mapping_HTS z ON z.id=k.fk_class_term_mapping_id
JOIN Term_Test_Testgroup_aggregate_HTS i ON
i.Id=c.fk_testgroup_aggregateID
JOIN TestGroup_HTS j on j.Id=i.fk_TestGroupID
JOIN student d ON a.fk_student_id=d.Id JOIN student_enroll_no e ON
e.fk_student_id=d.id and IsNULL(e.is_deleted,0)=0
JOIN student_academic f on f.fk_student_enroll_no_id=e.id and
f.fk_academic_year_id=c.fk_academic_year_id
JOIN reference_type_value cls on cls.Id=f.fk_class_id
LEFT JOIN reference_type_value sec ON sec.Id=f.fk_section_id
LEFT JOIN max_marks_entry_HTS h on h.id=a.fk_max_marks_entry_id
join class_xref_subjects cxs ON h.fk_subject_id=cxs.fk_subject_id and
cxs.fk_subject_type_id=h.fk_subject_type_id and
IsNull(cxs.is_deleted,0)=0
and cxs.fk_class_id=h.fk_class_id and
cxs.fk_academic_year_campus_id=h.fk_academic_year_campus_id and
cxs.fk_curriculum_segment_id=h.fk_curriculum_segment_id
where k.fk_class_report_types_mapping_id=@ReportId
and h.fk_section_id in (select * from SplitStringByChar(@SectionId,','))
and a.fk_subject_id in (select * from SplitStringByChar(@SubjectId,','))
select Name,Class,Section,enrollNo,SubjectName,TermName,
TestGroupName as TestName,
Case WHEN IsWeight=1 THEN Round(Cast(((avg(CAST(Marks as
float)/cast(MaxMarks as float)))*Weightage) as decimal(10,0)),0)
else Round(Cast(((cast(max(Marks) as float)/cast(max(MaxMarks) as
float))*Weightage) as decimal(10,0)),0) ENd as Marks ,
SubjectOrder ,sum(maxmarks) as maxmarksare INTO #temp1 from #temp
GROUP BY Name,Class,Section,enrollNo,SubjectName,
TermName,SubjectOrder,IsWeight,Weightage,TestGroupName
Insert into #temp
(Name,Class,Section,enrollNo,SubjectName,
TermName,TestName,Marks,SubjectOrder)
select
Name,Class,Section,enrollNo,SubjectName,TermName,'Total',
SUM(Marks),SubjectOrder from #temp1
GROUP BY Name,Class,Section,enrollNo,SubjectName,TermName,SubjectOrder
Insert into #temp
(Name,Class,SubjectName,Section,enrollNo,TermName,TestName,Marks)
select Name,Class,'Total',Section,enrollNo,TermName,'Total
Marks',SUM(Marks)
from #temp1
GROUP BY Name,Class,Section,enrollNo,TermName
Insert into #temp
(Name,Class,SubjectName,Section,enrollNo,TermName,TestName,Marks)
select
Name,Class,'Total',Section,enrollNo,TermName,'Percentage',
SUM(Marks)*100/sum(m
axmarksare) from #temp1
GROUP BY Name,Class,Section,enrollNo,TermName
select *from #temp
drop table #temp
drop table #temp1
end
解决方案
由于有很多事情可能是错误的,我会做以下事情。
- 创建报告的副本
- 如果需要,请删除现有数据集和 tablix。
- 创建一些只获取基本数据的新数据集(例如
SELECT top 10 * FROM marks_emtry_HTS
)。不要使用您的参数,因为我们只是想测试我们可以获得基本数据。 - 在您的报告中添加一些表格以显示正在返回的数据
- 添加一个数据集以测试您是否通过使用数据集查询正确地传递和解析参数
select * from SplitStringByChar(@SectionId,',')
,然后将 tablix 放在您的报告上以显示结果。 - 尝试修剪您的参数值
SET @SectionId = LTRIM(RTRIM(@SectionId))
,以确保您没有在拆分函数中错误地处理前导或尾随空格。 - 如果任何部分不起作用,请在运行报告时在 SQL Server 上运行跟踪,并查看服务器上正在执行的确切内容。
我知道很多这些步骤你会认为可能是不必要的,但要花时间去做,至少你可以确定,你可以将这些基本检查排除在你的调查之外。
推荐阅读
- sql-server - IIS 无法运行查询?
- javascript - 当我点击不同的按钮时如何显示另一个图像?
- bash - bash 脚本错误:无法与其他选项结合使用
- r - 如何使闪亮的 selectInput 下拉菜单在多个数据帧之间进行选择
- android-edittext - `android:editable` 已弃用:使用 `
` 使其可编辑 - java - 是否可以使用 bazel 查询来输出隐式(或任何)规则的完整语法?
- python - 在由 2 个字段分组的 DataFrame 中,从第一个分组字段内的第二个分组字段计算行 n 和 n-1 之间的差异
- reactjs - 如何使用 React Router 将信息从子组件传输到父组件 React.js
- r - 用于 Leaflet/R 的 addLegend 中的组函数未正确分离图例
- c - 将函数内部的指针分配给参数并在函数内部设置指向 Null 的指针是可以的