September 13, 2011 at 3:25 pm
Hello all,
I am working on a ssrs report that displays trends in file size over a given period of time for two databases, one that stores long term files and one that stores short term files. The report has a multi valued parameter called "Database" in which you can select either one or all. It also has a second multivalued parameter call Extension in which you can select one, many, or all of the file types.
So I am almost finished with the report, but I still have one problem to fix. When I select either the long term Database, or the short term db parameter by themselves, an accurate graph is presented for each one(I know its accurate because I hardcoded in some test variables in the SP and my numbers match up exactly when one database is selected). However, When I "Select ALL" in the database parameter, It renders an inaccurate graph that is way off of the numbers it should be hitting. Here is my SP:
USE [DFLT]
GO
/****** Object: StoredProcedure [dbo].[DigitalFolderTrends] Script Date: 09/13/2011 16:08:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Brad Estep
-- Create date: 8/31/11
-- Description:SP for Digital Files
-- =============================================
ALTER PROCEDURE [dbo].[DigitalFolderTrends]
@BeginDate datetime,
@EndDate datetime,
@Database VARCHAR(30),
@Extension VARCHAR(30)
--Declare @BeginDate datetime
--Declare @EndDate datetime Hardcoded for Testing
--Declare @Database VARCHAR(30)
--Declare @Extension VARCHAR(30)
--SET @BeginDate='1/1/10'
--SET @EndDate='6/1/11'
--SET @Database='DFLT,DFST'
--SET @Extension='.PDF,.WAV'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF CHARINDEX('DFLT', @Database) > 0 AND CHARINDEX('DFST', @Database) > 0
BEGIN
SELECT
((CAST(Filesize AS DECIMAL(12,4))/1024)/1024) AS Filesize,
YearAndWeek=case
WHEN LEN(DatePart(wk, DateModified)) = 2
THEN (substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(DatePart(wk,DateModified) as varchar(5)))
ELSE
(substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(0 as varchar(1)) + (cast(DatePart(wk,DateModified) as varchar(5))))
END, RIGHT(Filename, 4) AS Extension, TotalFiles='TotalFiles'
FROM
[DFST].[dbo].[CustFileTBL_All]
WHERE
DateModified BETWEEN @BeginDate AND @EndDate
AND RIGHT(FileName, 4) IN (SELECT LTRIM(RTRIM(value)) AS value FROM GMSGP.dbo.fn_Split(@Extension,','))
UNION ALL
SELECT
((CAST(Filesize AS DECIMAL(12,4))/1024)/1024) AS Filesize,
YearAndWeek=case
WHEN LEN(DatePart(wk, DateModified)) = 2
THEN (substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(DatePart(wk,DateModified) as varchar(5)))
ELSE
(substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(0 as varchar(1)) + (cast(DatePart(wk,DateModified) as varchar(5))))
END, RIGHT(Filename, 4) AS Extension, TotalFiles='TotalFiles'
FROM
[DFLT].[dbo].[CustFileTBL_All]
WHERE
DateModified BETWEEN @BeginDate AND @EndDate
AND RIGHT(FileName, 4) IN (SELECT LTRIM(RTRIM(value)) AS value FROM GMSGP.dbo.fn_Split(@Extension,','))
ORDER BY YearAndWeek
END
ELSE IF CHARINDEX('DFST', @Database) > 0
BEGIN
SELECT
((CAST(Filesize AS DECIMAL(12,4))/1024)/1024) AS Filesize,
YearAndWeek=case
WHEN LEN(DatePart(wk, DateModified)) = 2
THEN (substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(DatePart(wk,DateModified) as varchar(5)))
ELSE
(substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(0 as varchar(1)) + (cast(DatePart(wk,DateModified) as varchar(5))))
END, RIGHT(Filename, 4) AS Extension, TotalFiles='TotalFiles'
FROM
[DFST].[dbo].[CustFileTBL_All]
WHERE
DateModified BETWEEN @BeginDate AND @EndDate
AND RIGHT(FileName, 4) IN (SELECT LTRIM(RTRIM(value)) AS value FROM GMSGP.dbo.fn_Split(@Extension,','))
ORDER BY YearAndWeek
END
ELSE IF CHARINDEX('DFLT', @Database) > 0
BEGIN
SELECT
((CAST(Filesize AS DECIMAL(12,4))/1024)/1024) AS Filesize,
YearAndWeek=case
WHEN LEN(DatePart(wk, DateModified)) = 2
THEN (substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(DatePart(wk,DateModified) as varchar(5)))
ELSE
(substring(cast(DateModified as varchar(30)), 8, 4) + substring(cast(DateModified as varchar(30)), 4, 1) + cast(0 as varchar(1)) + (cast(DatePart(wk,DateModified) as varchar(5))))
END, RIGHT(Filename, 4) AS Extension, TotalFiles='TotalFiles'
FROM
[DFLT].[dbo].[CustFileTBL_All]
WHERE
DateModified BETWEEN @BeginDate AND @EndDate
AND RIGHT(FileName, 4) IN (SELECT LTRIM(RTRIM(value)) AS value FROM GMSGP.dbo.fn_Split(@Extension,','))
ORDER BY YearAndWeek
END
END
In the dataset section of the report under "Parameters" I have used the =Join(Parameters!Database.Value,",") so the proper string should be passed to the report, in fact I know it is because I also use this for the Extensions Parameter and they are isolating out just fine when I select an individual database parameter, its just the "Select All" that is skewing my graph for some reason, which is weird because select all works just fine for the Extension Parameters.
So basically I need to find out why the Select all option is not giving me the proper graph. In my SP i harcoded some data in to give me the total file size for the two databases when they are unionized(58,328 gigs), and to give me that same total for each db individually(58, 328, and 0(is zero because the dates I have selected aren't covered in the short term db) respectively, which is what they both should be). When you add up each individual total file size you get the exact number that the unionized database select statements give you. So my stored procedure is accurate. Why is it producing a much lower total for the graph? Anybody else run in to this. Thanks
September 14, 2011 at 10:06 am
Hi,
It is a little difficult to say for sure without some test data, but there are some things I can point out:
1. Using the following statement as the parameter that is passed to your proc is incorrect: Join(Parameters!Database.Value,",")
By default, the multi-select dropdown box will concatenate all the selected values into a comma-separated string, when used as an input parameter for a dataset. Performing the join operation is unnecessary.
2. I don't know how many possible values you may have for the "Database" parameter, but the length (30) of that parameter may cause some values to be cut off from the value that is passed.
3. A better way to do this would be to split the received values in the stored procedure, and to populate a temp table/table variable with the values. That will effectively eliminate the need for hard-coding values and make the whole procedure less cluttered.
Without some test data (including expected output), it is a little difficult to tell if there is anything else wrong in the proc.
September 15, 2011 at 7:19 am
I don't know how or why but it started working all of the sudden, can't explain it
September 15, 2011 at 7:21 am
bestep420 (9/15/2011)
I don't know how or why but it started working all of the sudden, can't explain it
SOMETHING changed. There's no luck in unchanging code ;-).
September 15, 2011 at 7:25 am
Ninja's_RGR'us (9/15/2011)
bestep420 (9/15/2011)
I don't know how or why but it started working all of the sudden, can't explain itSOMETHING changed. There's no luck in unchanging code ;-).
And it isn't Christmas yet...so it can't be Santa 😛
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply