|
technet
Starting Member
3 Posts |
Posted - 2011-01-18 : 11:33:06
|
| Yes pzajkowski. I am trying to create a dataset that populates a parameter with months and years till current month and year in SSRS (like Jan 2006,Feb2006,Mar 2006,.....Nov 2011,Dec 2011,Jan 2011) and then pass a single specific month/year based on the user's selection as you said. Please help me . Here is the stored proc below where the dtCashHoldings field should be made as a parameter dropdown with dates in SSRS but the datatype used here is varchar(10). I am creating a seperate dataset for this field and trying to populate dropdown with dates (months/years) in SSRS. (This @dtCashHoldings field is from another database called Holding and from another table called Position.Effective_Date . My concern is this field contains huge number of dates from year 2000 to current thats the reason I want to make it as month and year to be mentioned in the dropdown ).Any other advice would appreciate. CREATE PROCEDURE dbo.i_spExcessiveAccountCashHoldings @dtCashHoldings VARCHAR(10), @PercGT decimal(6,3) = 15, --Percent Greater Than @PercLT decimal(6,3) = 100, --Percent Less Than @Age int = 90 --Aging AS DECLARE @EligibleAccounts TABLE (Entity_id VARCHAR(8), AccountActivationDate DATETIME ) DECLARE @Results TABLE (ShortName VARCHAR(10), LegalName VARCHAR(200), CustodianAccountNumber VARCHAR(15), AccountActivationDate DATETIME, ProductCode VARCHAR(10), Cash_Value DECIMAL(15,2), Total_Market_Value DECIMAL(15,2), CashPercentage DECIMAL(9,2) ) INSERT INTO @EligibleAccounts SELECT DISTINCT Entity.Entity_ID, EntityDate.Date FROM Rules..Entity AS Entity INNER JOIN Rules..PRP_Entity_Dates AS EntityDate ON Entity.Entity_ID = EntityDate.Entity_ID AND EntityDate.Date_Type = 'Account Activation Date' WHERE Entity.User_Field5 not in('SPC') --Entity.User_Field5=FirmReference code. --Don't use Entity.User_field2 to elimiate the firms. And Entity.User_field7 NOT IN ('SEC','RAM','CSH','TAP','CHD','MT3','BO2') --Entity.User_Field7=ManagerID --Entity.User_field4=Product Reference Code. --Extract the product Reference Code from --Entity_product table --using Manager ID or Entity.User_field7 And Entity.User_field4 NOT IN ( 'AKI11','CSH01','RAM01','SEC01','SEC02','SEC03', 'SEC04','SEC05','SEC06','SEC07','SEC08','SEC09', 'SEC10','SEC11','SEC12','SEC13','SEC14','SEC15', 'SEC16','SEC17','SEC18','SEC19','SEC20','SEC21', 'SEC22','SEC88','SEC98','SEC99','TAP01','TAP02', 'TAP03','TAP04','TAP05','TAP06' ) AND Entity.User_Field10 NOT IN (90,99,98) --Entity.User_Field10=RR Code AND EntityDate.Date < CONVERT(VARCHAR(10), DATEADD(DAY, -@Age, GETDATE()), 101) INSERT INTO @Results (ShortName, LegalName, CustodianAccountNumber, AccountActivationDate, ProductCode, Cash_Value, Total_Market_Value, CashPercentage ) SELECT Entity.Entity_ID, Entity.Legal_Name, Entity.Custody_Bank, Accounts.AccountActivationDate, Entity.User_Field4, SUM(LotLevelPosition.Market_Value_Income), Position.Total_Market_Value_Income, CASE WHEN ISNULL(Position.Total_Market_Value_Income,0) = 0 THEN 0 ELSE ((SUM(LotLevelPosition.Market_Value_Income) / Position.Total_Market_Value_Income) * 100) END FROM Rules..Entity AS Entity INNER JOIN @EligibleAccounts Accounts ON Entity.Entity_id = Accounts.Entity_id INNER JOIN Holding..Position AS Position ON Accounts.Entity_id = Position.Entity_ID INNER JOIN Holding..Lot_Level_Position AS LotLevelPosition ON Position.Position_ID = LotLevelPosition.Position_ID AND Position.Effective_Date = @dtCashHoldings AND LotLevelPosition.Security_Alias = 10409 GROUP BY Entity.Entity_ID, Entity.Legal_Name, Entity.Custody_Bank, Accounts.AccountActivationDate, Entity.User_Field4, Position.Total_Market_Value_Income SELECT ShortName AS 'Short Name', LegalName AS 'Legal Name', CustodianAccountNumber AS 'Custodian Account Number', AccountActivationDate AS 'Account Activation Date', ProductCode AS 'Product Code', Cash_Value AS 'Cash_Value', Total_Market_Value AS 'Total_Market_Value', CashPercentage AS 'Cash Percentage' FROM @Results WHERE CashPercentage >= @PercGT AND CashPercentage <= @PercLT |
 |
|
SSRS