February 7, 2018 at 9:57 am
I can't see the forest through the trees. I am passing parameters from SSRS to a SP. One if the parameters is called @AssettSize, which means that the user can select if they want to see everything from Asset Size 0-1M, 1M-5M, or >5M. I have been struggling to figure out the best way to pass this to the stored procedure and return results based on those ranges. A condensed version of the code and the process I began, but it is not quite working right.
If there is a better and more efficient way, please let me know. I have worked this too long that I have tunnel vision and seem stuck on the path I have been pounding. Thanks in advance for any insight/hints.
--Testing
DECLARE @PracticeAreaArray varchar(2000)= '1,2,3,4,5,6,7,8,9,10,11'
,@MonthIDArray varchar(500) = '201801'
,@ClientType varchar(500) = '1,2,3,4,5,6,7,8,9,10,11,12'
,@AssetSize bigint = 5000000
,@MinAsset int = 0
,@MidAsset int = 1000001
,@MaxAsset bigint = 50000001
SELECT csl.SolutionGroupName
,csl.PracticeAreaName
,csl.SolutionGroupSortOrder
,csl.PracticeAreaSortOrder
,csl.SolutionGroupKey
,csl.PracticeAreaKey
,fr.CustomerTypeKey
,fr.AssetSize AS AssetSize
,d.MonthOfYearNum
,d.MonthNameLong
,SUM(fr.RevenueAmount) AS Revenue
,0 AS YTDRevenue
,0 AS YOYRevenue
,0 AS Receivables
,0 AS CYBookedBacklogAmount
,0 AS FYBookedBacklogAmount
,0 AS GoalAmount
,0 AS Pipeline
,0 AS Probability
,ISNULL(fr.TotalWorkOrders,0) AS TotalWorkOrders
FROM [reporting].[vFactRevenueLine] fr
LEFT OUTER JOIN reporting.vDimProject p
ON fr.ProjectKey = p.ProjectKey
LEFT OUTER JOIN [reporting].[vDimPracticeArea] csl
ON fr.PracticeAreaKey = csl.PracticeAreaKey
LEFT OUTER JOIN [reporting].[vDimDate] d
ON d.DateKey = fr.RevenueDateKey
OR d.DateKey = fr.RevenueDateKeyOverride --Future deposit to be revenue in an earlier month
WHERE d.MonthID IN (SELECT Item FROM [dbo].[DelimitedSplit8K](@MonthIDArray,','))
AND csl.PracticeAreaKey IN (SELECT Item FROM [dbo].[DelimitedSplit8K](@PracticeAreaArray,','))
AND fr.IsPullThrough=1
AND d.DateKey = isnull(fr.RevenueDateKeyOverride,fr.RevenueDateKey)-- to include any 012018 payments as 122017 revenue
AND fr.CustomerTypeKey IN (SELECT Item FROM [dbo].[DelimitedSplit8K](@ClientType,','))
AND ((fr.AssetSize >= @MinAsset and fr.AssetSize <= @AssetSize)-- 0 and 1000000
OR (fr.AssetSize BETWEEN @MidAsset AND @AssetSize) --1000001 and 5000000
OR (fr.AssetSize BETWEEN @MaxAsset and @AssetSize)) -- 5000001 and 9000000000
February 7, 2018 at 10:28 am
Dynamic SQL is how I would go (guarding against SQL Injection, obviously). That will allow you to remove the ORs completely because you will KNOW what was passed in because you interogate it as you build your SQL statement. This will get you the best plan for each execution too.
Whatever solution you pick I would include OPTION (RECOMPILE) at the end of it too. You are SOOO exposed to parameter sniffing/widely-varying-input-parameter issues here.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 7, 2018 at 10:46 am
You could try this sort of thing (untested). Instead of passing the asset sizes, pass in a code and use that:
DECLARE @PracticeAreaArray VARCHAR(2000) = '1,2,3,4,5,6,7,8,9,10,11'
,@MonthIDArray VARCHAR(500) = '201801'
,@ClientType VARCHAR(500) = '1,2,3,4,5,6,7,8,9,10,11,12'
,@AssetSizeCode TINYINT = 1;
--AssetSizeCode can be any of 1,2,3
DROP TABLE IF EXISTS #AssetSizes
CREATE TABLE #AssetSizes (
AssetSizeCode TINYINT PRIMARY KEY CLUSTERED
,MinAssetSize INT
,MaxAssetSize INT
);
INSERT #AssetSizes (
AssetSizeCode
,MinAssetSize
,MaxAssetSize
)
VALUES (
1
,0
,1000000
)
,(
2
,1000000
,5000000
)
,(
3
,5000000
,990000000
)
SELECT...
FROM reporting.vFactRevenueLine fr
LEFT OUTER JOIN reporting.vDimProject p ON fr.ProjectKey = p.ProjectKey
LEFT OUTER JOIN reporting.vDimPracticeArea csl ON fr.PracticeAreaKey = csl.PracticeAreaKey
LEFT OUTER JOIN reporting.vDimDate d ON d.DateKey = fr.RevenueDateKey
OR d.DateKey = fr.RevenueDateKeyOverride --Future deposit to be revenue in an earlier month
JOIN #AssetSizes asi ON asi.AssetSizeCode = @AssetSizeCode
WHERE d.MonthID IN (
SELECT Item
FROM dbo.DelimitedSplit8K(@MonthIDArray, ',')
)
AND csl.PracticeAreaKey IN (
SELECT Item
FROM dbo.DelimitedSplit8K(@PracticeAreaArray, ',')
)
AND fr.IsPullThrough = 1
AND d.DateKey = ISNULL(fr.RevenueDateKeyOverride, fr.RevenueDateKey) -- to include any 012018 payments as 122017 revenue
AND fr.CustomerTypeKey IN (
SELECT Item
FROM dbo.DelimitedSplit8K(@ClientType, ',')
)
AND fr.AssetSize >= asi.MinAssetSize
AND fr.AssetSize < asi.MaxAssetSize
If performance is an issue, I would also consider creating & populating temp tables for @MonthIDArray and @PracticeAreaArray and using these temp tables in your final SELECT.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 7, 2018 at 11:47 am
Phil
Thanks, I think this one will work best due to what I am working with here. Performance is not an issue as these are fairly small databases and these reports are for internal use only. Again, thanks for the input. I am the only developer, so I don't have a go to person when I need a sounding board, etc lol.
February 7, 2018 at 11:57 am
Cheryl McLaughlin-385812 - Wednesday, February 7, 2018 11:47 AMPhil
Thanks, I think this one will work best due to what I am working with here. Performance is not an issue as these are fairly small databases and these reports are for internal use only. Again, thanks for the input. I am the only developer, so I don't have a go to person when I need a sounding board, etc lol.
Good stuff. It struck me afterwards that it might be better to create and populate the #AssetSizes table as a permanent table. Your code could reference it in the same way as shown above & you'd be able to change the boundary values without having to change code.
It's a lonely job being the sole developer, I don't envy that.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 7, 2018 at 1:02 pm
Phil
Already ahead of you on that one 🙂 I did create a permanent table because of the number of SPs that would be using this table.
February 7, 2018 at 1:20 pm
Cheryl McLaughlin-385812 - Wednesday, February 7, 2018 1:02 PMPhil
Already ahead of you on that one 🙂 I did create a permanent table because of the number of SPs that would be using this table.
Don't forget that you can use the permanent table as a source for your drop-down in SSRS also...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply