So, fairly new to SSRS..have been told that when creating a dataset, it will automatically use the last query (select statement) to generate a list of fields that are available for use viz a viz that dataset. All of the tables i create are temp tables..though don't think this should matter to my issue as other datasets I've created work 'as advertised'.
So my code code first creates a Summary table - using a CREATE TABLE command with 5 fields, then inserts 4 records into the Summary table - then the code goes on to create some more tables (using SELECT INTO), does some updates and at the last DML statement, updates data into the 4 records of the Summary table. Then the very last code line is a Select statement on the Summary table (SELECT * FROM #SUMMARY)
So, when i create a dataset object and embed my code into the query box and click OK, it builds the list of fields available per the code I embedded. I have 3 other datasets and all of them build the available fields list based on the last select query I'd entered into the embedded code; with the exception of this last 'Summary' dataset.
It looks like its entering fields from the #Summary table, but also from other temp tables I've created in the code. I tried dropping them right before the Select * From #Summary statement, which, quizzically enough, gets rid of the #Summary fields!!!
Any help would be appreciated, code below:
CREATE TABLE #SUMMARY
(
PROV_POS_ID VARCHAR(3)
,PROV_POS_DESC VARCHAR(255)
,PROV_COUNT INT DEFAULT 0
,DENOM_COUNT INT DEFAULT 0
,NUMER_COUNT INT DEFAULT 0
,RATE2 FLOAT DEFAULT 0
)
IF 1=0 SELECT * FROM #SUMMARY
--add constant values to rows..(1st letter added for sorting if necessary..
INSERT INTO #SUMMARY (PROV_POS_ID, PROV_POS_DESC)
VALUES ('Z-T','Totals')
INSERT INTO #SUMMARY (PROV_POS_ID, PROV_POS_DESC)
VALUES ('B-H','High - Above Statistical Variance')
INSERT INTO #SUMMARY (PROV_POS_ID, PROV_POS_DESC)
VALUES ('C-M','Average - Within Statistical Variance')
INSERT INTO #SUMMARY (PROV_POS_ID, PROV_POS_DESC)
VALUES ('D-L','Low - Below Statistical Variance')
/*********************************************************/
/*********************************************************/
/*********************************************************/
--now get ttl rate and upper/lower limits
--this code will be bogarted from the 'detailed' code..
DECLARE @tm INT = NULL
DECLARE @tbl VARCHAR(511) = NULL
DECLARE @incl VARCHAR(MAX) = NULL
DECLARE @prov VARCHAR(255) = NULL
DECLARE @loc VARCHAR(255) = NULL
DECLARE @setting INT = NULL
DECLARE @sql VARCHAR(MAX) = NULL
DECLARE @sqldt NVARCHAR(500) = NULL
DECLARE @dt1 DATETIME = NULL
DECLARE @dt2 DATETIME = NULL
DECLARE @ParmDefinition NVARCHAR(500) = NULL
/*************************************************************************************************/
/******************************* Cursor **********************************************************/
/*************************************************************************************************/
IF OBJECT_ID('tempdb..#TempT') IS NOT NULL DROP TABLE #TempT
CREATE TABLE #TempT
(
ID_TemplateMeasure INT
,Desc_TemplateMeasure VARCHAR(511)
,ID_TemplateProject INT
,Desc_TemplateProject VARCHAR(511)
--
,LOC_ID NUMERIC
,LOC_NAME VARCHAR(255)
--
,PERIOD DATETIME
--
,PROV_ID VARCHAR(25)
,PROV_NAME VARCHAR(255)
,PROV_TYPE VARCHAR(255)
,PROV_CLARITY_SPECIALTY VARCHAR(255)
,PROV_ROSTER_SPECIALTY VARCHAR(255)
,PROV_ROSTER_PROVIDER_GROUP VARCHAR(255)
,PROV_ROSTER_MEDICAL_GROUP VARCHAR(255)
,PROV_ROSTER_NOTES VARCHAR(511)
--
,NUM FLOAT
,DENOM FLOAT
,RATE FLOAT
--
,Flag_Ambulatory INT
)
--Run Cursor for each Provider Group using Dynamic SQL. This will create a different view per group.
DECLARE CUR CURSOR STATIC FOR
SELECT
ID_TemplateMeasure
,DESC_SummaryTable
,Dep_Inclusion
,Dep_ProviderField
,Dep_LocationField
,ID_ProjectSetting
FROM
vr.dbo.CFR_Like_Template
WHERE
FLag_Valid = 1
AND ID_TemplateMeasure IN (@TemplateMeasure)
ORDER BY ID_TemplateMeasure ASC
OPEN CUR
FETCH NEXT FROM CUR INTO @tm,@tbl,@incl,@prov,@loc,@setting
WHILE @@FETCH_STATUS = 0
BEGIN
-- Try to see if the Query Works--------------------------
BEGIN TRY
--the below was added to get the date range to pull data for
--from the summary table. it is NOT meant specifically to
--catch errors..the error part was added in case a wonky
--date was added by the user as a bad date param..
SET @sqldt = N'(SELECT @dt2Out = MAX(PERIOD) FROM '+@tbl+')';
SET @ParmDefinition = N'@dt2Out DATETIME OUTPUT';
BEGIN TRY
EXECUTE sp_executesql @sqldt, @ParmDefinition, @dt2Out=@dt2 OUTPUT;
SET @dt1 = DATEADD(MM,(@NbrMonths-1)*-1,@dt2)
SET @dt1 = DATEADD(MM,@MonthLag*-1,@dt1)
SET @dt2 = DATEADD(MM,@MonthLag*-1,@dt2)
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
IF @setting = 1 -- Ambulatory
BEGIN
SET @sql =
'
INSERT #TempT (PROV_ID, LOC_ID, PERIOD, NUM, DENOM, ID_TemplateMeasure,Flag_Ambulatory)
SELECT '+@prov+', NULL AS LOC_ID, PERIOD, NUM, DENOM, '+CAST(@tm AS VARCHAR(6))+',1
FROM '+@tbl+'
WHERE
1=1
AND PERIOD BETWEEN '''+CONVERT(VARCHAR(25),@dt1,120)+'''
AND '''+CONVERT(VARCHAR(25),@dt2,120)+'''
'+ CASE WHEN @incl IS NULL
THEN ''
ELSE 'AND '+@incl
END
END
IF @setting = 2 -- Hospital
BEGIN
SET @sql =
'
INSERT #TempT (PROV_ID, LOC_ID, PERIOD, NUM, DENOM, ID_TemplateMeasure,Flag_Ambulatory)
SELECT '+@prov+', '+@loc+', PERIOD, NUM, DENOM, '+CAST(@tm AS VARCHAR(6))+',0
FROM '+@tbl+'
WHERE
1=1
AND PERIOD BETWEEN '''+CONVERT(VARCHAR(25),@dt1,120)+'''
AND '''+CONVERT(VARCHAR(25),@dt2,120)+'''
'+ CASE WHEN @incl IS NULL
THEN ''
ELSE 'AND '+@incl
END
END
IF @sql IS NULL SET @sql = 'SELECT top X'
--PRINT @sql
EXEC(@sql)
END TRY
-- Else Flag as need fixing --------------------------------
BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
------------------------------------------------------------
FETCH NEXT FROM CUR INTO @tm,@tbl,@incl,@prov,@loc,@setting
END
CLOSE CUR;
DEALLOCATE CUR;
UPDATE t
SET t.RATE = CASE WHEN t.DENOM = 0 THEN 0
ELSE t.NUM/(t.DENOM*1.) END
,t.ID_TemplateProject = c.ID_TemplateProject
,t.Desc_TemplateMeasure = c.Desc_MeasureName
,t.Desc_TemplateProject = c.Desc_ProjectName
,t.PROV_NAME = dp.CLARITY_PROV_NAME
,t.PROV_TYPE = dp.CLARITY_PROV_TYPE
,t.PROV_CLARITY_SPECIALTY = dp.CLARITY_SPECIALTY
,t.PROV_ROSTER_SPECIALTY = z.Specialty
,t.PROV_ROSTER_PROVIDER_GROUP = z.Enhanced_Provider_Group
,t.PROV_ROSTER_MEDICAL_GROUP = z.Medical_Group
,t.PROV_ROSTER_NOTES = z.Provider_Notes
,t.LOC_NAME = loc.LOC_NAME
FROM
#TempT AS t
LEFT JOIN vr.dbo.CFR_Like_Template AS c
ON t.ID_TemplateMeasure = c.ID_TemplateMeasure
LEFT JOIN vr.dbo.DIM_PROVIDERS AS dp
ON t.PROV_ID = dp.CLARITY_PROV_ID
LEFT JOIN vr.dbo.CLARITY_LOC AS loc
ON t.LOC_ID = loc.LOC_ID
--
OUTER APPLY
(
SELECT TOP 1
l.*
FROM
vr.dbo.VR_Combined_Provider_List AS l
WHERE
l.PROV_ID = t.PROV_ID
AND l.Discontinue_Date IS NULL
ORDER BY l.RANK_PROV ASC
) AS z
SELECT
ID_TemplateMeasure
,Desc_TemplateMeasure
,ID_TemplateProject
,Desc_TemplateProject
--
,LOC_ID
,LOC_NAME
--
,PERIOD
--
,PROV_ID
,PROV_NAME
,PROV_TYPE
,PROV_CLARITY_SPECIALTY
,PROV_ROSTER_SPECIALTY
,PROV_ROSTER_PROVIDER_GROUP
,PROV_ROSTER_MEDICAL_GROUP
,PROV_ROSTER_NOTES
--
,NUM
,DENOM
,RATE
FROM #TempT
WHERE
--LOC_ID IN (@Locations)
--
--OR
Flag_Ambulatory = 1
ORDER BY ID_TemplateProject,ID_TemplateMeasure,PROV_ID,PERIOD
--the above code marks the end of john's code, now, need to add my code below it and feed off of what i can
--from john's code to make mine marvel..
--we are going to start with the main code - added from the DataSets object in the FP1: DSDetail Data and keep adding that code till it is finished..
--DECLARE @TargetLineVal FLOAT = .2
IF OBJECT_ID ('tempdb..#FINAL_OUTPUT') IS NOT NULL DROP TABLE #FINAL_OUTPUT
CREATE TABLE #FINAL_OUTPUT
(
ID_PROJECTDIM INT
,ID_TEMPLATEMEASURE INT
,ID_PROVIDER VARCHAR(50)
,CLARITY_PROVIDER_NAME VARCHAR(255)
,MEDICAL_GROUP VARCHAR(255)
,SPECIALTY VARCHAR(255)
,PHASE INT
,PHASE_DESC VARCHAR(20)
,SUM_DENOM INT
,SUM_NUMER INT
,RATE_PHASE2 FLOAT
,RATE_PHASE1 FLOAT
,TTL_RATE FLOAT
,UPPER_LIMIT FLOAT
,LOWER_LIMIT FLOAT
,VAR_LOWER_UPPER_LIMIT FLOAT
,TARGETLINEVAL FLOAT
,STATISTICAL_PLACEMENT VARCHAR(255)
,DIRECTION INT
)
IF 1=0 SELECT * FROM #FINAL_OUTPUT
IF OBJECT_ID ('tempdb..#TTL_RATE') IS NOT NULL DROP TABLE #TTL_RATE
SELECT '' AS ID_PROJECTDIM
,ID_TEMPLATEMEASURE
,1 AS PHASE
,SUM(NUM) / SUM(DENOM) AS TTL_RATE
INTO #TTL_RATE
FROM #TEMPT
GROUP BY ID_TEMPLATEMEASURE
/**********************************************************************/
--this next pass will probably also need adjusting as we move along..
IF OBJECT_ID('tempdb..#TEMP_RATE_LIMITS_BY_PROV') IS NOT NULL DROP TABLE #TEMP_RATE_LIMITS_BY_PROV
SELECT Z1.ID_ProjectDim
,Z2.ID_Provider
,Z2.Clarity_Provider_Name
,Z2.Medical_Group
,Z2.Common_Specialty
,SUM(Z2.CT_DenominatorCount) AS SUM_DENOM
,SUM(Z2.CT_NumeratorCount) AS SUM_NUMER
,round(SUM(CAST(Z2.CT_NumeratorCount AS FLOAT)) / SUM(CAST(Z2.CT_DenominatorCount AS FLOAT)),3) AS RATE
--,Z1.ID_MeasureDim
,Z1.ID_TemplateMeasure
,Z1.PHASE
,round(Z1.TTL_RATE,3) AS TTL_RATE
--get upper limit..
,Z1.TTL_RATE + (1.96 * (POWER(CAST(CAST(SUM(Z2.CT_DenominatorCount) AS FLOAT) AS FLOAT),-.5)))
* (POWER(CAST((Z1.TTL_RATE * (1 - Z1.TTL_RATE)) AS FLOAT), .5)) AS UPPER_LIMIT
--get lower limit..
,Z1.TTL_RATE - (1.96 * (POWER(CAST(CAST(SUM(Z2.CT_DenominatorCount) AS FLOAT) AS FLOAT),-.5)))
* (POWER(CAST((Z1.TTL_RATE * (1 - Z1.TTL_RATE)) AS FLOAT), .5)) AS LOWER_LIMIT
--get variance between upper and lower limits..
,CAST(Z1.TTL_RATE + (1.96 * (POWER(CAST(CAST(SUM(Z2.CT_DenominatorCount) AS FLOAT) AS FLOAT),-.5)))
* (POWER(CAST((Z1.TTL_RATE * (1 - Z1.TTL_RATE)) AS FLOAT), .5)) AS FLOAT)
-
CAST(Z1.TTL_RATE - (1.96 * (POWER(CAST(CAST(SUM(Z2.CT_DenominatorCount) AS FLOAT) AS FLOAT),-.5)))
* (POWER(CAST((Z1.TTL_RATE * (1 - Z1.TTL_RATE)) AS FLOAT), .5)) AS FLOAT) AS VAR_LOWER_UPPER_LIMIT
INTO #TEMP_RATE_LIMITS_BY_PROV
FROM
(
SELECT *
FROM #TTL_RATE AS TR
) AS Z1
JOIN
(
SELECT T.PROV_ID AS ID_PROVIDER
,T.PROV_NAME AS CLARITY_PROVIDER_NAME
,T.PROV_ROSTER_PROVIDER_GROUP AS MEDICAL_GROUP
,ISNULL (T.PROV_ROSTER_SPECIALTY, T.PROV_CLARITY_SPECIALTY) AS COMMON_SPECIALTY
,T.ID_TEMPLATEMEASURE
,T.DENOM AS CT_DENOMINATORCOUNT
,T.NUM AS CT_NUMERATORCOUNT
,1 AS PHASE
FROM #TempT AS T
) AS Z2
ON Z1.ID_TemplateMeasure = Z2.ID_TemplateMeasure
GROUP BY Z1.ID_ProjectDim
,Z2.ID_Provider
,Z2.Clarity_Provider_Name
,Z2.Medical_Group
,Z2.Common_Specialty
--,Z1.ID_MeasureDim
,Z2.ID_TemplateMeasure
,Z1.ID_TemplateMeasure
,Z1.PHASE
,Z1.TTL_RATE
/****************************************************************/
/****************************************************************/
/****************************************************************/
--now back to our regularly scheduled code program of getting summary data..
--add calc where prov falls per upper/lower limits..
SELECT *
,CASE
WHEN RATE BETWEEN LOWER_LIMIT AND UPPER_LIMIT THEN 'C-M' --middle (average)
WHEN RATE > UPPER_LIMIT THEN 'B-H' --high
WHEN RATE < LOWER_LIMIT THEN 'D-L' --low
END AS PROV_POS
INTO #TEMP_RATE_LIMITS_BY_PROV2
FROM #TEMP_RATE_LIMITS_BY_PROV AS TR
/*******************************************************/
/*******************************************************/
/*******************************************************/
--union the summary and detailed together..
--summary - totals all providers together
SELECT 'Z-T' AS PROV_POS_TTL
,COUNT(*) AS TTL_PROVIDERS
,SUM(SUM_DENOM) AS TTL_DENOM
,SUM(SUM_NUMER) AS TTL_NUMER
,CAST(SUM(SUM_NUMER) AS FLOAT) / CAST(SUM(SUM_DENOM) AS FLOAT) AS TTL_RATE
INTO #SUMMARY_UPDATE
FROM #TEMP_RATE_LIMITS_BY_PROV2 AS T
WHERE T.PHASE = 1
UNION
--breakout by provider position- gives details of provider by their stat placement..
SELECT
PROV_POS
,COUNT(*) AS TTL_PROVIDERS
,SUM(SUM_DENOM) AS TTL_DENOM
,SUM(SUM_NUMER) AS TTL_NUMER
,CAST(SUM(SUM_NUMER) AS FLOAT) / CAST(SUM(SUM_DENOM) AS FLOAT) AS TTL_RATE
FROM #TEMP_RATE_LIMITS_BY_PROV2 AS T1
WHERE T1.PHASE = 1
GROUP BY T1.PROV_POS
ORDER BY 1
--update values into summary table..
UPDATE S
SET S.DENOM_COUNT = SU.TTL_DENOM
,S.NUMER_COUNT = SU.TTL_NUMER
,S.RATE2 = SU.TTL_RATE
,S.PROV_COUNT = SU.TTL_PROVIDERS
FROM #SUMMARY AS S
JOIN #SUMMARY_UPDATE AS SU
ON S.PROV_POS_ID = SU.PROV_POS_TTL
SELECT * FROM #SUMMARY ORDER BY PROV_POS_ID