CODEWITHSUNDEEP
sqlreporting-services
scareysam
scareysam

Reputation: 1

No fields in appearing ssrs dataset - dynamic SQL

I'm trying to run this through SSRS using the database name as @CATALOG parameter but the field list won't populate. Any advice would be much appreciated. 

DECLARE @SQLScript AS NVARCHAR(4000)

SET @SQLScript = 
'USE ' +@CATALOG + CHAR(13) +
'SELECT
    LD.ProvSpecMon_A AS Dept,
    LD.ProvSpecMon_C AS SubjArea,
    LD.ProvSpecMon_B AS CourseCode,
    L.LearnRefNumber AS PersonCode,
    CASE 
        WHEN L.PlanEEPHours + L.PlanLearnHours >= 540 THEN 'FT' 
        ELSE 'PT'
    END AS LearnerType,
    LD.AimType,
    LD.LearnAimRef AS LearningAim,
    AIM.LearnAimRefTitle AS LearningAimTitle,
    EFA.OnProgPayment,
    EFA.StartFund
FROM Valid.Learner L
INNER JOIN Valid.LearningDelivery LD
    ON LD.LearnRefNumber = L.LearnRefNumber
LEFT JOIN Rulebase. DV_Learner AGE
    ON AGE.LearnRefNumber = L.LearnRefNumber
LEFT JOIN Rulebase.EFA_LEARNER EFA
    ON EFA.LearnRefNumber = L.LearnRefNumber
LEFT JOIN Rulebase.EFALearningDeliveryLARSInput AIM
    ON AIM.AimSeqNumber = LD.AimSeqNumber
    AND AIM.LearnRefNumber = LD.LearnRefNumber
WHERE
    EFA.StartFund = 1
    AND LD.AimType = 5
ORDER BY
    L.LearnRefNumber'

EXECUTE sp_executesql @SQLScript

Upvotes: 0

Views: 261

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

If you want to parametrize your catalog you have to parametrize your connection string.

In datasource properties select Embedded connection and put a connection string expression with your parameter.

enter image description here

You can use parameters in the connection string:

="Data Source=" & Parameters!ServerName.Value & ";Initial Catalog=" & Parameters!Catalog.Value

Upvotes: 1

Related Questions