February 9, 2012 at 3:14 am
I'm new to SQL server and reporting services - used to work with Delphi client/server app running on Sybase.
We made use of a lot of stored procedures to generate our reports and due to the nature of our business, had to ensure multiple users could access the reports at the same time. Therefore, we could not have normal tables (started off like this and had some freaked out users when their data included other department's data), but rather we made use of hash tables in the stored proc.
The last action of the stored proc, would be a select statement. The result of the stored procedure was thus a dataset of multiple columns and rows.
I'm trying to convert this report to SQL Server 2008 reporting services, but I'm not having any luck. Herewith an extract of the stored procedure:
CREATE PROCEDURE [dbo].[spu_report_1000067_test]
@divisionid int,
@startissue date,
@endissue date,
@count int,
@sectionid int = NULL,
@repid int = NULL,
@existingonlyyn char(1) = NULL,
@compareyn char(1) = null,
@contactyn char(1) = NULL
AS
/*Generated by SQL Server Migration Assistant for Sybase version 5.0.9198.*/
DECLARE
@peopleid int,
@oldpeopleid int,
@totalrate money,
@totalspace float(24),
@adid int,
@sellingprice money,
@rate money,
@space float(24),
@description varchar(50),
@rowcount int,
@previousendissue date,
@previousstartissue date
/* create temporary tables*/
CREATE TABLE #temp_GetAdsCurrent
(
AdvertiserID int NULL,
Rate money NULL,
DecimalSize real NULL
)
CREATE UNIQUE INDEX Index_Advertiser
ON #temp_GetAdsCurrent (AdvertiserID)
CREATE TABLE #temp_GetAdsPrevious
(
AdvertiserID int NULL,
Rate money NULL,
DecimalSize real NULL
)
CREATE UNIQUE INDEX Index_Advertiser
ON #temp_GetAdsPrevious (AdvertiserID)
/* create display table */
CREATE TABLE #temp_ShowGrowth
(
AdvertiserID int NULL,
Company varchar(50) NULL,
PreviousYearSales money NULL,
PreviousYearPages float(24) NULL,
CurrentYearSales money NULL,
CurrentYearPages float(24) NULL,
GrowthInSales money NULL,
GrowthInPages float(24) NULL
)
CREATE UNIQUE INDEX Index_Advertiser
ON #temp_ShowGrowth (AdvertiserID)
/* calculate issues*/
SELECT @previousendissue = dateadd(mm, -12, @endissue)
SELECT @previousstartissue = dateadd(mm, -12, @startissue)
/* fetch current year data*/
INSERT #temp_GetAdsCurrent
select M.AdvertiserID, sum(D.RateSold), sum(L.DecimalSize)
from Booking_Master M
left outer join Lookup_Rep R on R.RepID = M.RepID
join Booking_Detail D on D.MasterID = M.MasterID
join Lookup_Size L on L.SizeID = D.SizeID
join Lookup_Section S on S.SectionID = D.SectionID
where M.BookingStatusID = 1000001
and D.ActiveFlag = 1
and M.DivisionID = @divisionid
and M.Issue between @startissue and @endissue
and ((@sectionid IS NULL) OR (D.SectionID = @sectionid))
and ((@repid IS NULL) OR (M.RepID = @repid))
group by M.AdvertiserID
/* get previous year data*/
INSERT #temp_GetAdsPrevious
select M.AdvertiserID, sum(D.RateSold), sum(L.DecimalSize)
from Booking_Master M
left outer join Lookup_Rep R on R.RepID = M.RepID
join Booking_Detail D on D.MasterID = M.MasterID
join Lookup_Size L on L.SizeID = D.SizeID
join Lookup_Section S on S.SectionID = D.SectionID
where M.BookingStatusID = 1000001
and D.ActiveFlag = 1
and M.DivisionID = @divisionid
and M.Issue BETWEEN @previousstartissue AND @previousendissue
and ((@sectionid IS NULL) OR (D.SectionID = @sectionid))
and ((@repid IS NULL) OR (M.RepID = @repid))
group by M.AdvertiserID
/* add to main table - will add the other is comparison is required */
INSERT #temp_ShowGrowth
SELECT DISTINCT
#temp_GetAdsCurrent.AdvertiserID,
NULL,
0,
0,
0,
0,
0,
0
FROM #temp_GetAdsCurrent
INSERT #temp_ShowGrowth
SELECT DISTINCT
#temp_GetAdsPrevious.AdvertiserID,
NULL,
0,
0,
0,
0,
0,
0
FROM #temp_GetAdsPrevious
WHERE #temp_GetAdsPrevious.AdvertiserID NOT IN
(
SELECT #temp_GetAdsCurrent.AdvertiserID
FROM #temp_GetAdsCurrent
)
/* insert the current details*/
UPDATE #temp_ShowGrowth
SET
CurrentYearSales = C.Rate,
CurrentYearPages = C.DecimalSize
FROM #temp_ShowGrowth S,
#temp_GetAdsCurrent C
WHERE S.AdvertiserID = C.AdvertiserID
UPDATE #temp_ShowGrowth
SET
PreviousYearSales = P.Rate,
PreviousYearPages = P.DecimalSize
FROM #temp_ShowGrowth S,
#temp_GetAdsPrevious P
WHERE S.AdvertiserID = P.AdvertiserID
/* calculate*/
UPDATE #temp_ShowGrowth
SET
GrowthInSales = #temp_ShowGrowth.CurrentYearSales - #temp_ShowGrowth.PreviousYearSales,
GrowthInPages = #temp_ShowGrowth.CurrentYearPages - #temp_ShowGrowth.PreviousYearPages
/* display the result */
if @compareyn = 'Y'
begin
/* if only existing customers remove all with no sales in previous year*/
IF @existingonlyyn = 'Y'
BEGIN
DELETE #temp_ShowGrowth
WHERE PreviousYearSales = 0
END
/* if only new customers remove all with no sales in previous year*/
else if @existingonlyyn = 'N'
begin
DELETE #temp_ShowGrowth
WHERE PreviousYearSales <> 0
end
SET ROWCOUNT @count
SELECT AdvertiserID, Company,
PreviousYearSales, PreviousYearPages,
CurrentYearSales, CurrentYearPages,
GrowthInSales, GrowthInPages
FROM #temp_ShowGrowth
ORDER BY CurrentYearSales DESC
SET ROWCOUNT 0
end
else
begin
/* if only existing customers remove all with no sales in previous year*/
IF @existingonlyyn = 'Y'
BEGIN
DELETE #temp_ShowGrowth
WHERE PreviousYearSales = 0
END
/* if only new customers remove all with no sales in previous year*/
else if @existingonlyyn = 'N'
begin
DELETE #temp_ShowGrowth
WHERE PreviousYearSales <> 0
end
/* display data*/
SET ROWCOUNT @count
SELECT AdvertiserID, Company, CurrentYearSales, CurrentYearPages
FROM #temp_ShowGrowth
ORDER BY CurrentYearSales DESC
SET ROWCOUNT 0
end
/* drop temporary table*/
DROP TABLE #temp_GetAdsCurrent
if @existingonlyyn is not null DROP TABLE #temp_GetAdsPrevious
DROP TABLE #temp_ShowGrowth
RETURN
GO
Executing the stored procedure using the following params:
EXEC[dbo].[spu_report_1000067_test]
@divisionid = 1000002,
@startissue = '03/01/2011',
@endissue = '02/01/2012',
@count = 50,
@sectionid = 1000005,
@repid = null,
@existingonlyyn = null,
@compareyn = 'Y',
@contactyn = null
Would then give me a result like this:
AdvertiserIDCompanyCurrentYearSalesCurrentYearPages
157264NULL1045590.4033
159308NULL1000554.964.5
227777NULL377192.009
I'm trying to get his into Reporting services, but I don't get an option to set the fields (or even select the sp from the list). When I used this in Delphi, I would call Proc.Open in stead of Proc.Exec and I could loop through the dataset.
Can anyone shed some light onto this? Should I be using something else?
Many thanks
s
February 9, 2012 at 3:29 am
You should be able to execute a stored procedure in a report just fine. Change the command type of the query from text to stored procedure.
Regarding your set-up:
you could use a normal table. Include a column containing the department and then add a WHERE clause with the parameter @departmentID (or something like that). When you run the report for a certain user, get his department and then the SELECT clause will only get data for his department.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply