u*********e
发帖数: 9616 | 1 Hi,
I am working on a SSRS report from a store procedure I created.
The returned the dataset is showing in the picture
I create a dataset using tableadapter. I can preview the dataset without any
issue (in pic2)
Later I bind the dataset using objectDataSource, and select the datasource
in the rdlc file.
I drage and drop the values from the field to the table.
When I run the result, the first two columns display values correctly.
However, the rest columns all return empty string.
any idea why? | i****a
发帖数: 36252 | 2 are you using a report wizard?
look at the report in design view and make sure the fields are setup
correctly.
any
【在 u*********e 的大作中提到】
: Hi,
: I am working on a SSRS report from a store procedure I created.
: The returned the dataset is showing in the picture
: I create a dataset using tableadapter. I can preview the dataset without any
: issue (in pic2)
: Later I bind the dataset using objectDataSource, and select the datasource
: in the rdlc file.
: I drage and drop the values from the field to the table.
: When I run the result, the first two columns display values correctly.
: However, the rest columns all return empty string.
| u*********e
发帖数: 9616 | 3 yes, I was. the fields are directly referenced. I suspect SSRS has issue
with store procedures that do table variable manipulation.
【在 i****a 的大作中提到】
: are you using a report wizard?
: look at the report in design view and make sure the fields are setup
: correctly.
:
: any
| i****a
发帖数: 36252 | 4 no, SSRS doesn't care what you do in the SP
you have Visual Studio (or Business Intelligent Studio if you installed it
with SQL server disk)? check the report in design view.
【在 u*********e 的大作中提到】
: yes, I was. the fields are directly referenced. I suspect SSRS has issue
: with store procedures that do table variable manipulation.
| u*********e
发帖数: 9616 | 5 thanks for responding. I use VS to design the rdlc file. It's was a straight
Fields!Stmt_Created_Date.Value drop in textbox. The field of "Group_Code"
and "Group_Member_Code" can be displayed without any problem but not for the
rest.
I did some research. One person raised the similar issue like mine. His
storeprocedure created a table variable and used cursor to combine different
rows into one row then put into the table variable and select the result as
the return set. That's very much like mine and he can not display all of
his dataset either, only part of them.
I suspect SSRS has some issue with returning values from dataset created
from store procedures like this. I resolved my problem by creating a
physical table instead of a table variable or temptable in the database and
insert value into that physical table then return the dataset. This works.
【在 i****a 的大作中提到】
: no, SSRS doesn't care what you do in the SP
: you have Visual Studio (or Business Intelligent Studio if you installed it
: with SQL server disk)? check the report in design view.
| g***l
发帖数: 18555 | 6 不要用RESULT DATASET，放到TEMP TABLE里去 | u*********e
发帖数: 9616 | 7 could you explain it in detail? Thanks.
【在 g***l 的大作中提到】
: 不要用RESULT DATASET，放到TEMP TABLE里去
| g***l
发帖数: 18555 | 8 先用SP把TABLE建好，填充好，最后SSRS显示一下TABLE就可以了，避免弄一个复杂的
JOIN QUERY去显示。
【在 u*********e 的大作中提到】
: could you explain it in detail? Thanks.
| u*********e
发帖数: 9616 | 9 that was what I did. I created a table variable and just inserted values.
Later I replaced the table variable with temp table but no luck.
Below is my store procedure.
ALTER PROCEDURE [dbo].[SP_REPORT_GET_STATUS_DATES]
@Statement_Year SMALLINT,
@Statement_Month TINYINT
AS
BEGIN
BEGIN TRY
Declare @Curr_Code nvarchar(10)
Declare @Group_Code nvarchar(10)
declare @Group_member_Code nvarchar(10)
declare @Date_Submitted smalldatetime
declare @SuppDates nvarchar(200)
declare @CompDates nvarchar(200)
declare @ApproveDates nvarchar(200)
declare @Date_Approved nvarchar(10)
declare @Results table (Group_Code nvarchar(10) not null,
Group_Member_Code nvarchar(10) not null,
Stmt_Date_Created nvarchar(10) null,
Stmt_Date_Updated nvarchar(10) null,
Stmt_Date_Approved nvarchar(10) null,
Supp_Date_Entered nvarchar(200) null,
Composite_Date_Pushed nvarchar(200) null,
Group_Composite_Date_Approved nvarchar(200) null)
------------------------------------------------------------------------ | g***l
发帖数: 18555 | 10 你这个SP写得够烂的，怎么这么多的变量，还那么多CURSOR，难怪会出问题。多搞几个
TEMPTABLE也别用CURSOR，否则容易出错，时间比较用DATETIME，不要倒成VARCHAR去比
，容易出错。就是出个REPORT么，还CATCH什么。 | | | u*********e
发帖数: 9616 | 11 thanks for the suggestion. Not a fan of cursor myself either. I will see
what I can do to improve the sp.
the question I have is that, what could cause the report not returning all
the data even though the sp itself returned the data as expected? I thought
SSRS just treated all returned dataset as strings. | g***l
发帖数: 18555 | 12 你可以让DBA给你弄个固定的TABLE，你每次TRUNCATE，填充一下，你就什么都不用
RETURN了，我觉得你的STRING COMBINATION可能有问题
thought
【在 u*********e 的大作中提到】
: thanks for the suggestion. Not a fan of cursor myself either. I will see
: what I can do to improve the sp.
: the question I have is that, what could cause the report not returning all
: the data even though the sp itself returned the data as expected? I thought
: SSRS just treated all returned dataset as strings.
| g***l
发帖数: 18555 | 13 你把这个TABLE VARIABLE换成TEMP TABLE就可以了。date都用DATETIME，没事不要弄变
量，CURSOR全去掉。不要用SELECT *,有什么COLUMN就写什么COLUMN
declare @Results table (Group_Code nvarchar(10) not null,
Group_Member_Code nvarchar(10) not null,
Stmt_Date_Created nvarchar(10) null,
Stmt_Date_Updated nvarchar(10) null,
Stmt_Date_Approved nvarchar(10) null,
Supp_Date_Entered nvarchar(200) null,
Composite_Date_Pushed nvarchar(200) null,
Group_Composite_Date_Approved nvarchar(200) null) | u*********e
发帖数: 9616 | 14 gejkl,
Thank you very much for helping me answering my question. I was doing
research myself and figuring out the issue. You are right. I rewrite the
query to get rid of cursor and use PATH XML instead.
One interesting thing I observed is that after I updated my sp and run the
report, it gave out an error msg:
"An error occurred during local report processing.Exception has been thrown
by the target of an invocation. String was not recognized as a valid
DateTime.Couldn't store <> in Stmt_Date_Created Column. Expected type is
DateTime."
Which is much better than before when the report ran but returned no data
for this field. So I checked out the dataset and found out the data type for
Stmt_Date_Created was set as DateTime instead of String. This is quite
interesting because in the sp I declared the column as string and all the
rest Date columns were set as String as well and returned without issue.
So I went to the table adapter and manually set the datatype of Stmt_Date_
Created as String and vola, the reports display all data as I want.
Thanks again for your help.
Below is my updated sp. Hope it can help others. I was referering to this
wonderful article by Anith Sen. http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
ALTER PROCEDURE [dbo].[SP_REPORT_GET_STATUS_DATES]
@Statement_Year SMALLINT,
@Statement_Month TINYINT
AS
BEGIN
-- BEGIN TRY
Declare @Curr_Code nvarchar(10)
Declare @Group_Code nvarchar(10)
declare @Group_member_Code nvarchar(10)
declare @Date_Submitted smalldatetime
declare @SuppDates nvarchar(200)
declare @CompDates nvarchar(200)
declare @ApproveDates nvarchar(200)
declare @Date_Approved nvarchar(10)
declare @Results table (Group_Code nvarchar(10) not null,
Group_Member_Code nvarchar(10) not null,
Stmt_Date_Created nvarchar(10) null,
Stmt_Date_Updated nvarchar(10) null,
Stmt_Date_Approved nvarchar(10) null,
Supp_Date_Entered nvarchar(200) null,
Composite_Date_Pushed nvarchar(200) null,
Group_Composite_Date_Approved nvarchar(200) null)
------------------------------------------------------------------------
-----------------------
-- Insert the Statement Dates for a particular Group, month, Year in to
the final return table
------------------------------------------------------------------------
-----------------------
Insert into @Results (Group_Code,Group_Member_Code, Stmt_Date_Created,
Stmt_Date_Updated, Stmt_Date_Approved)
Select Group_Code,Group_Member_Code,
isnull(convert(nvarchar(2),datepart(mm,Date_Created)) + '/' +
convert(nvarchar(2),datepart(day,Date_Created)),''),
isnull(convert(nvarchar(2),datepart(mm,Date_Updated)) + '/' +
convert(nvarchar(2),datepart(day,Date_Updated)),'') ,
isnull(convert(nvarchar(2),datepart(mm,Date_Approved)) + '/' +
convert(nvarchar(2),datepart(day,Date_Approved)),'')
From vw_Active_Members M
left join vw_statement s
on m.member_identity_id = s.dealership_id and statement_year = @
Statement_Year and statement_month = @Statement_Month and original_Statement
_specification_id = 0
left join vw_statement_specification ss
on s.statement_specification_id = ss.statement_specification_id
and ss.manufacturer_id = m.manufacturer_id and ss.manufacturer_id not in (40)
------------------------------------------------------------------------
----------------------------
-- Insert Supplemental Date
------------------------------------------------------------------------
----------------------------
Select Group_Member_Code, IsNull(convert(nvarchar(2),datepart(mm,Date_
Created)) + '/' + convert(nvarchar(2),datepart(day,Date_Created)),'') as '
Date_Created'into #tbSuppDate
From vw_Active_Members M
left join vw_statement_specification ss
on ss.manufacturer_id = m.manufacturer_id and ss.
manufacturer_id not in (40)
left join supplemental_values sv
on sv.statement_specification_id = ss.statement_
specification_id
and sv.supplemental_month = @Statement_Month
and sv.supplemental_year = @Statement_Year
and sv.dealership_id = m.member_identity_id
-- where group_id = @Group_ID
Group By group_member_code, isnull(convert(nvarchar(2),
datepart(mm,Date_Created)) + '/' + convert(nvarchar(2),datepart(day,Date_
Created)),'')
order by group_member_code,date_created
SELECT Distinct p1.Group_Member_Code,
( SELECT Date_Created + CASE WHEN Date_Created='' THEN '' ELSE ', '
END
FROM #tbSuppDate p2
WHERE p2.Group_Member_Code = p1.Group_Member_Code
ORDER BY Date_Created
FOR XML PATH('') ) AS Date_Supp into #Temp
FROM #tbSuppDate p1
GROUP BY Group_Member_Code
update @Results
Set Supp_Date_Entered = case when LEN(t.Date_Supp)>0 then LEFT(t.
Date_Supp,LEN(t.Date_Supp)-1) else '' end
from #Temp t inner join @Results r on t.Group_Member_Code=r.Group_
Member_Code
drop table #tbSuppDate
drop table #Temp
------------------------------------------------------------------------
----------------------------
-- Insert Composite Date
------------------------------------------------------------------------
----------------------------
Select Group_Member_Code, isnull(convert(nvarchar(2),datepart(mm,status_
date)) + '/' + convert(nvarchar(2),datepart(day,status_date)),'') as '
Status_Date' into #tbCompPushed
From vw_Active_Members M
left join composite c
on m.member_id = c.member_group_id and c.composite_data_type_id
= 1 and c.composite_month = @Statement_Month and c.composite_year = @
Statement_Year
left join status_history sh
on c.composite_id = sh.status_identity_id and sh.status_id = 5
Group By group_member_code,isnull(convert(nvarchar(2),datepart(mm,
status_date)) + '/' + convert(nvarchar(2),datepart(day,status_date)),'')
order by group_member_code,status_date
SELECT Distinct p1.Group_Member_Code,
( SELECT Status_Date + ','
FROM #tbCompPushed p2
WHERE p2.Group_Member_Code = p1.Group_Member_Code
ORDER BY Status_Date
FOR XML PATH('') ) AS Date_Comp into #Temp1
FROM #tbCompPushed p1
GROUP BY Group_Member_Code
update @Results
Set Composite_Date_Pushed = LEFT(t1.Date_Comp,LEN(t1.Date_Comp)-1)
from #Temp1 t1 inner join @Results r on t1.Group_Member_Code=r.Group
_Member_Code
drop table #tbCompPushed
drop table #Temp1
------------------------------------------------------------------------
----------------------------
-- Insert Composite Approved Date
------------------------------------------------------------------------
----------------------------
Select
Group_Code, cast(convert(nvarchar(2),datepart(mm,status_date)) + '/' +
convert(nvarchar(2),datepart(day,status_date)) as nvarchar(10)) as 'Date_
Approved' into #tbCompApproved
From vw_Active_Members M
left join composite c
on m.member_id = c.member_group_id and c.composite_data_type_id
= 1 and c.composite_month = @Statement_Month and c.composite_year = @
Statement_Year
left join Group_Composite_Detail gc
on c.Composite_ID=gc.Composite_ID
left join status_history sh
on gc.composite_id = sh.status_identity_id and sh.status_id = 23
Group By group_code,cast(convert(nvarchar(2),datepart(mm,status_date)) +
'/' + convert(nvarchar(2),datepart(day,status_date)) as nvarchar(10))
order by group_code,Date_Approved
SELECT Distinct p1.Group_Code,
( SELECT Date_Approved + ','
FROM #tbCompApproved p2
WHERE p2.Group_Code = p1.Group_Code
ORDER BY Date_Approved
FOR XML PATH('') ) AS Date_Approved into #Temp2
FROM #tbCompApproved p1
GROUP BY Group_Code
update @Results
Set Group_Composite_Date_Approved = case when Len(t2.Date_Approved)>
0 then LEFT(t2.Date_Approved,LEN(t2.Date_Approved)-1) else '' end
from #Temp2 t2 inner join @Results r on t2.Group_Code=r.Group_Code
Select * from @Results order by Group_Code,Group_Member_Code
drop table #tbCompApproved
drop table #Temp2
END | g***l
发帖数: 18555 | 15 你的QUERY还是过分繁琐，其实根本就没必要用变量，传进来的就两个参数，为什么要
弄一大堆变量在中间，为什么要用XML输出，那个慢地很，SSRS就是给SQL SERVER
TABLE设计的，创建TABLE的时候要先把类型定义好，别用ISNULL +STRING这种，给个
VARCHAR 255不就可以了么，你的TABLE VARIABLE也不好，搞个实在的TEMP TABLE吧，
TABLE VARIABLE放在内存里，快，但是你的TABLE定义的很乱，造成SSRS TYPE
MISMATCH，所以最好别用它。不明白你为什么要把DATETIME转成STRING，完全没必要的
繁琐。你不是就是要个确定的DATE么
CONVERT (DATETIME, @STARdATE,101)就可以了。我要找12/1/2010到1/3/2011，你这个
会不会出错呢。SUNQUERY可以用CTE替代，不会的可以用TEMP TABLE | u*********e
发帖数: 9616 | 16
The user does not want the full date time. What we want is a string of date
time with month and day
only .
The select input for temp table is
GroupCode, Date
AA 5/7
AA 6/8
The output is "AA 5/7,6/8,9/12..."
【在 g***l 的大作中提到】
: 你的QUERY还是过分繁琐，其实根本就没必要用变量，传进来的就两个参数，为什么要
: 弄一大堆变量在中间，为什么要用XML输出，那个慢地很，SSRS就是给SQL SERVER
: TABLE设计的，创建TABLE的时候要先把类型定义好，别用ISNULL +STRING这种，给个
: VARCHAR 255不就可以了么，你的TABLE VARIABLE也不好，搞个实在的TEMP TABLE吧，
: TABLE VARIABLE放在内存里，快，但是你的TABLE定义的很乱，造成SSRS TYPE
: MISMATCH，所以最好别用它。不明白你为什么要把DATETIME转成STRING，完全没必要的
: 繁琐。你不是就是要个确定的DATE么
: CONVERT (DATETIME, @STARdATE,101)就可以了。我要找12/1/2010到1/3/2011，你这个
: 会不会出错呢。SUNQUERY可以用CTE替代，不会的可以用TEMP TABLE
| g***l
发帖数: 18555 | 17 那是USER不对，我怎么知道你要去年不是今年的。
12/31-1/1，我知道你是哪一年到哪一年的。即使USER没有，你里面也要，否则很容易比较出错，最后出REPORT的时候可以用MONTH（） DAY（）
date
【在 u*********e 的大作中提到】
:
: The user does not want the full date time. What we want is a string of date
: time with month and day
: only .
: The select input for temp table is
: GroupCode, Date
: AA 5/7
: AA 6/8
: The output is "AA 5/7,6/8,9/12..."
| i****a
发帖数: 36252 | 18 agree
haven't looked at LZ's code, but basic idea should be, retrieve data, leave
formatting to SSRS.
易比较出错，最后出REPORT的时候可以用MONTH（） DAY（）
【在 g***l 的大作中提到】
: 那是USER不对，我怎么知道你要去年不是今年的。
: 12/31-1/1，我知道你是哪一年到哪一年的。即使USER没有，你里面也要，否则很容易比较出错，最后出REPORT的时候可以用MONTH（） DAY（）
:
: date
| u*********e
发帖数: 9616 | 19 Thanks for the feedback.
I cleaned up the variables and use smalldatetime instead of nvarchar for the
comparison.
ALTER PROCEDURE [dbo].[SP_REPORT_GET_STATUS_DATES]
@Statement_Year SMALLINT,
@Statement_Month TINYINT
AS
BEGIN
declare @Results table (Group_Code nvarchar(10) not null,
Group_Member_Code nvarchar(10) not null,
Stmt_Date_Created nvarchar(10) null,
Stmt_Date_Updated nvarchar(10) null,
Stmt_Date_Approved nvarchar(10) null,
Supp_Date_Entered nvarchar(200) null,
Composite_Date_Pushed nvarchar(200) null,
Group_Composite_Date_Approved nvarchar(200) null)
------------------------------------------------------------------------
-----------------------
-- Insert the Statement Dates for a particular Group, month, Year in to
the final return table
------------------------------------------------------------------------
-----------------------
Insert into @Results (Group_Code,Group_Member_Code, Stmt_Date_Created,
Stmt_Date_Updated, Stmt_Date_Approved)
Select Group_Code,Group_Member_Code,
isnull(RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, Date_Created)
), 2) +'/'+RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(dd, Date_Created)), 2),''
) ,
isnull(RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, Date_Updated)
), 2) +'/'+RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(dd, Date_Updated)), 2),''
) ,
isnull(RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, Date_Approved
)), 2) +'/'+RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(dd, Date_Approved)), 2),
'')
From vw_Active_Members M
left join vw_statement s
on m.member_identity_id = s.dealership_id and statement_year = @
Statement_Year and statement_month = @Statement_Month and original_Statement
_specification_id = 0
left join vw_statement_specification ss
on s.statement_specification_id = ss.statement_specification_id
and ss.manufacturer_id = m.manufacturer_id and ss.manufacturer_id not in (40)
------------------------------------------------------------------------
----------------------------
-- Insert Supplemental Date
------------------------------------------------------------------------
----------------------------
Select Group_Member_Code, cast(date_created as smalldatetime) as 'Date_
Created'into #tbSuppDate
From vw_Active_Members M
left join vw_statement_specification ss
on ss.manufacturer_id = m.manufacturer_id and ss.manufacturer_id
not in (40)
left join supplemental_values sv
on sv.statement_specification_id = ss.statement_specification_id
and sv.supplemental_month = @Statement_Month
and sv.supplemental_year = @Statement_Year
and sv.dealership_id = m.member_identity_id
Group By group_member_code, cast(date_created as smalldatetime)
order by group_member_code,date_created
SELECT Distinct p1.Group_Member_Code,
( SELECT cast(RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, Date_
Created)), 2) +'/'+RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(dd, Date_Created)
), 2) as nvarchar(10)) + CASE WHEN Date_Created=Null THEN '' ELSE ', ' END
FROM #tbSuppDate p2
WHERE p2.Group_Member_Code = p1.Group_Member_Code
ORDER BY Date_Created
FOR XML PATH('') ) AS Date_Supp into #Temp
FROM #tbSuppDate p1
GROUP BY Group_Member_Code
update @Results
Set Supp_Date_Entered = case when LEN(t.Date_Supp)>0 then LEFT(t.
Date_Supp,LEN(t.Date_Supp)-1) else '' end
from #Temp t inner join @Results r on t.Group_Member_Code=r.Group_
Member_Code
drop table #tbSuppDate
drop table #Temp
------------------------------------------------------------------------
----------------------------
-- Insert Composite Date
------------------------------------------------------------------------
----------------------------
Select
Group_Member_Code, cast(status_date as smalldatetime) as 'Status_Date'
into #tbCompPushed
From vw_Active_Members M
left join composite c
on m.member_id = c.member_group_id and c.composite_data_type_id
= 1 and c.composite_month = @Statement_Month and c.composite_year = @
Statement_Year
left join status_history sh
on c.composite_id = sh.status_identity_id and sh.status_id = 5
Group By group_member_code,cast(status_date as smalldatetime)
order by group_member_code,status_date
SELECT Distinct p1.Group_Member_Code,
( SELECT cast(RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, Status_
Date)), 2) +'/'+RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(dd, Status_Date)), 2
) as nvarchar(10)) + CASE WHEN Status_Date=Null THEN '' ELSE ', ' END
FROM #tbCompPushed p2
WHERE p2.Group_Member_Code = p1.Group_Member_Code
ORDER BY Status_Date
FOR XML PATH('') ) AS Date_Comp into #Temp1
FROM #tbCompPushed p1
GROUP BY Group_Member_Code
update @Results
Set Composite_Date_Pushed = case when LEN(t1.Date_Comp)>0 then LEFT
(t1.Date_Comp,LEN(t1.Date_Comp)-1) else '' end
from #Temp1 t1 inner join @Results r on t1.Group_Member_Code=r.Group
_Member_Code
drop table #tbCompPushed
drop table #Temp1
------------------------------------------------------------------------
----------------------------
-- Insert Composite Approved Date
------------------------------------------------------------------------
----------------------------
Select
Group_Code, cast(status_date as smalldatetime) as 'Date_Approved' into
#tbCompApproved
From vw_Active_Members M
left join composite c
on m.member_id = c.member_group_id and c.composite_data_type_id
= 1 and c.composite_month = @Statement_Month and c.composite_year = @
Statement_Year
left join Group_Composite_Detail gc
on c.Composite_ID=gc.Composite_ID
left join status_history sh
on gc.group_composite_id = sh.status_identity_id and sh.status_
id = 23
Group By group_code,cast(status_date as smalldatetime)
order by group_code,Date_Approved
SELECT Distinct p1.Group_Code,
( SELECT cast(RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, Date_
Approved)), 2) +'/'+RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(dd, Date_
Approved)), 2) as nvarchar(10)) + CASE WHEN Date_Approved=Null THEN '' ELSE
', ' END
FROM #tbCompApproved p2
WHERE p2.Group_Code = p1.Group_Code
ORDER BY Date_Approved
FOR XML PATH('') ) AS Date_Approved into #Temp2
FROM #tbCompApproved p1
GROUP BY Group_Code
update @Results
Set Group_Composite_Date_Approved = case when Len(t2.Date_Approved)>
0 then LEFT(t2.Date_Approved,LEN(t2.Date_Approved)-1) else '' end
from #Temp2 t2 inner join @Results r on t2.Group_Code=r.Group_Code
Select * from @Results order by Group_Code,Group_Member_Code
drop table #tbCompApproved
drop table #Temp2
END |
|
