none
SSRS concatenate two parameters into one RRS feed

  • Question

  • Hello Team,

    I have currently two parameters and they refer to their own individual datasets in MS SQL report builder.

    Investor Name references dataset ReportElement 28 - Investor Name field and Investor No references ReportElement1070 - InvestorNo field.

    The two parameters are essentially the same thing, Name and no. I'd like to combine the two for one parameter selection. Having the investor number alone is not very useful and the investor no. is requirement downstream.

    How do i create a paramter for end user selection by combining the two parameters.

    Tuesday, December 10, 2019 9:11 PM

All replies

  • Hi Myranthiru,

    For this request, I would recommend to add a new dataset, that JOIN those two dataset together. Then make a parameter that get its value from the combined dataset.

    Not quite sure about the table structure, but you could join the two tables on the keys. Use InvestorName and InvestorNo. for parameter value and parameter label.

     

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 11, 2019 1:29 AM
  • Hi Lukas,

    Are you able to help develop the join statement between the two datasets. This is what I came up but seems incomplete. Not sure how to include the parameters.

    From ReportElement28 JOIN ReportElement1070
    ON (ReportElement28.InvestorKey=ReportElement1070.InvestorKey

    Dataset:

    ReportElement28

    DECLARE @DimensionElementsList AS AdHocMetadataTableType; 
    DECLARE @DimensionElementsList2 AS AdHocMetadataTableType; 
    DECLARE @AdHocValuesTableType AS AdHocValuesTableType; 

    INSERT INTO @DimensionElementsList (columnDisplayName,value, valueFK, sortOrder, ColumnFilterOperation, considerForRowSuppression) values ('cFundGroup','DimFund\FundGroupCode','FundKey',0,'=',1)  
    INSERT INTO @AdHocValuesTableType (value, columnUniqueIdentifier, isGlobalFilter)  

    Select Distinct FundGroupCode ,'cFundGroup',1 from DimFund where FundGroupCode in (@cFundGroup)
    INSERT INTO @DimensionElementsList (columnDisplayName,value, valueFK, sortOrder, ColumnFilterOperation, considerForRowSuppression) values ('cFund_Name','DimFund\FundName','FundKey',1,'=',1)  
    INSERT INTO @AdHocValuesTableType (value, columnUniqueIdentifier, isGlobalFilter)  

    Select Distinct FundName ,'cFund_Name',1 from DimFund where FundName in (@cFund_Name)
    INSERT INTO @DimensionElementsList2 (value, sortOrder,MultiplyByNegativeOne,considerForRowSuppression,SubTotal,sortType,columnDisplayName,valueFK) values ('DimInvestor\InvestorName',0,0,0,0,'asc','InvestorName','InvestorKey') exec dbo.[spv_getAdHocData] @DimensionElementsList, @DimensionElementsList2, @AdHocValuesTableType ,@UserId ,0 

    Dataset: ReportElement1070

    DECLARE @DimensionElementsList AS AdHocMetadataTableType; 
    DECLARE @DimensionElementsList2 AS AdHocMetadataTableType; 
    DECLARE @AdHocValuesTableType AS AdHocValuesTableType; 

    INSERT INTO @DimensionElementsList (columnDisplayName,value, valueFK, sortOrder, ColumnFilterOperation, considerForRowSuppression) values ('cFundGroup','DimFund\FundGroupCode','FundKey',0,'=',1)  
    INSERT INTO @AdHocValuesTableType (value, columnUniqueIdentifier, isGlobalFilter)  

    Select Distinct FundGroupCode ,'cFundGroup',1 from DimFund where FundGroupCode in (@cFundGroup)
    INSERT INTO @DimensionElementsList (columnDisplayName,value, valueFK, sortOrder, ColumnFilterOperation, considerForRowSuppression) values ('cFund_Name','DimFund\FundName','FundKey',1,'=',1)  
    INSERT INTO @AdHocValuesTableType (value, columnUniqueIdentifier, isGlobalFilter)  

    Select Distinct FundName ,'cFund_Name',1 from DimFund where FundName in (@cFund_Name)
    INSERT INTO @DimensionElementsList (columnDisplayName,value, valueFK, sortOrder, ColumnFilterOperation, considerForRowSuppression) values ('cInvestor_Name','DimInvestor\InvestorName','InvestorKey',2,'=',1)  
    INSERT INTO @AdHocValuesTableType (value, columnUniqueIdentifier, isGlobalFilter)  

    Select Distinct InvestorName ,'cInvestor_Name',1 from DimInvestor where InvestorName in (@cInvestor_Name)
    INSERT INTO @DimensionElementsList2 (value, sortOrder,MultiplyByNegativeOne,considerForRowSuppression,SubTotal,sortType,columnDisplayName,valueFK) values ('DimInvestor\InvestorNo_2',0,0,0,0,'asc','InvestorNo_2','InvestorKey') exec dbo.[spv_getAdHocData] @DimensionElementsList, @DimensionElementsList2, @AdHocValuesTableType ,@UserId ,0 

    Thursday, December 12, 2019 8:41 PM
  • Hi, 

    Sorry for late reply, have you resolved this? It seems we don't even need join for this, they are just inserted values we could just union the lines.

    If you have not resolved this, just let us know :)

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 20, 2019 6:17 AM