I need to hide columns based on a field value, which isn't working, What's the best way to accomplish this? Grouping, subreport, Multiple datasets, or something else?

Question

I need to hide columns based on a field value, which isn't working, What's the best way to accomplish this? Grouping, subreport, Multiple datasets, or something else?

craig.bobchin

SSCrazy

Points: 2298
More actions
June 2, 2017 at 9:58 am

#337448

So I'm developing this report and I'm not sure if the best way to do what I want is via subreports of if there's a way of grouping them using one data set or if I need to create multiple data sets.
What I want to accomplish is this: I want a report by PCP (Doctor#) which shows the admits and discharges for each doctor on a single page (or more if the data overflows)
I want to put the doctor name and fax number on the top (page break)
then group all of their admits first then the discharges.
The Discharge section has two more columns that I want hidden on the admit section (Discharge Date and Disposition Description) based on teh value in the NotificationType field
I've tried creating a report with one data set grouping by Notification type, and added an expression (=IIf(Fields!NotificationType.Value = "Discharge",False,True) ) into Column visibility property of the two columns but it just hides it completely ignoring the field value.
Any help is appreciated.

Finally the query:
    IF OBJECT_ID('tempdb..#tempTbl1') IS NOT NULL              /*Then it exists*/              DROP TABLE #tempTbl1 CREATE TABLE #TEMPTBL1 ( RCO# INT , RICO# INT , RRFTO# INT , RRFTON VARCHAR(25) , RSER01 VARCHAR(3) , RSER02 VARCHAR(3) , RMBR# VARCHAR(12) , RSUB# VARCHAR(3) , RMLNAM VARCHAR(20) , RMFNAM VARCHAR(12) , RIFMDT DATETIME , RITODT DATETIME , RAUTH# VARCHAR(15) , RRFBYN VARCHAR(25) , RDIAG1 VARCHAR(8) , RPRIM VARCHAR(40) , RADMIT VARCHAR(1) , RFLG02 VARCHAR(10) , RGLOBE Varchar(10) , RCASE1 VARCHAR(64) , RCASE2 VARCHAR(64) , RCASE3 VARCHAR(64) , RCASE4 VARCHAR(64) , RCASE5 VARCHAR(64) , RCASE6 VARCHAR(64) , RCASE7 VARCHAR(64) , RCASE8 VARCHAR(64) )
    DECLARE @QRY1 VARCHAR(8000)     SET @QRY1 = 'INSERT INTO #TEMPTBL1 SELECT * FROM OPENQUERY(AS400,''     SELECT      A.RCO#     , A.RICO#     , A.RRFTO#     , A.RRFTON     , A.RSER01     , A.RSER02     , A.RMBR#     , A.RSUB#     , A.RMLNAM     , A.RMFNAM     , CASE WHEN A.RIFMDT <> ''''0001-01-01'''' THEN A.RIFMDT ELSE NULL END AS RIFMDT     , CASE WHEN A.RITODT <> ''''0001-01-01'''' THEN A.RITODT ELSE NULL END AS RITODT     , A.RAUTH#     , A.RRFBYN     , A.RDIAG1     , A.RPRIM     , A.RADMIT     , A.RFLG02     , A.RGLOBE     , B.RCASE1     , B. RCASE2     , B. RCASE3     , B. RCASE4     , B. RCASE5     , B. RCASE6     , B. RCASE7     , B. RCASE8     FROM MC#FILEACM.MCPREFL A     LEFT OUTER JOIN MC#FILEACM.MCPNORX B         ON A.RCO# = B.RCO# AND A.RAUTH# = B.RAUTH#     WHERE     A.RADMIT = ''''Y''''     AND     A.RENTDT >= ''''2017-01-01''''     AND     A.RFLG02 <> ''''DIAMOND''''     '')'
    EXECUTE (@QRY1)
/*Create Temporary Disposition Code temp table*/
IF OBJECT_ID('tempdb..#TempDispCode') IS NOT NULL      /*Then it exists*/ DROP TABLE #TempDispCode CREATE TABLE #TempDispCode ( [DCO#] INT, [DKEY] Varchar(50), [DISPCODE] Varchar(2), [DDESC] Varchar(100) ) Insert Into #TempDispCode SELECT Distinct    [DCO#]   ,[DKEY]      ,right(adisp,2) DISPCODE      ,[DDESC] FROM [dbo].[MCPACTY]     INNER JOIN MEDMC.dbo.[MCPDESC] with (Nolock)         ON ACO# = DCO# AND right(DKEY,2) = ADISP AND DKEY like 'DISPAT%'
Select distinct NotificationType ,AdmitType ,FacilityName ,Fax# ,PatientName ,Birthdate ,AdmitDate ,DischargeDate ,LOS ,CurrentLoc ,PCP ,AttendingMD --,Product --,AdmitDX ,DXDescription ,DispostionDescription
From( /* Select Admission records for the report. Data is anyone admitted to a facility that has not yet been discharged*/     SELECT --Distinct      'Admission' AS NotificationType , CASE         WHEN (A.RSER01 LIKE 'SN_' OR A.RSER01 = 'TCU') THEN 'SNF'         WHEN (A.RSER01 = '23'AND A.RGLOBE = 'Y') THEN 'OBSERVATION'         ELSE 'ACUTE'      END AS AdmitType     , A.RRFTON AS FacilityName     , convert(varchar(5),f.DOACD) + '-' + convert(varchar(10),left(f.DOPHN,3)) + '-' + convert(varchar(10),Right(f.DOPHN,4)) Fax#     , RTRIM(A.RMLNAM) + ', '+ A.RMFNAM AS PatientName     , IsNull(convert(varchar(10),C.MBIRDT,101),'') AS Birthdate     , CASE WHEN A.RIFMDT IS NOT NULL THEN convert(varchar(10),A.RIFMDT,101) ELSE '' END AS AdmitDate     , CASE WHEN A.RITODT IS NOT NULL THEN convert(varchar(10),A.RITODT,101) ELSE '' END AS DischargeDate     , CASE WHEN A.RIFMDT = A.RITODT THEN 1          WHEN A.RITODT IS NULL AND A.RIFMDT = GETDATE() THEN 1          ELSE DATEDIFF(DAY, A.RIFMDT, COALESCE(A.RITODT, GETDATE())) END AS LOS     , COALESCE(A.RSER01, A.RSER02) AS CurrentLoc     , C.MDOC#1     , IsNull(C.MDOCNM,'') AS PCP     , A.RRFBYN AS AttendingMD     --, B.Product1 AS Product     --, A.RAUTH# AS AuthNumber     --, A.RDIAG1 AS AdmitDX     , A.RPRIM AS DXDescription     --, '' AS DispostionCode     , '' AS DispostionDescription     --, B.CompanyShortName CompanyName     --, CASE WHEN A.RCASE1 LIKE '[%%]%' THEN A.RCASE1 + A.RCASE2 + A.RCASE3 + A.RCASE4 + A.RCASE5 + A.RCASE6 + A.RCASE7 + A.RCASE8 ELSE '' END AS Comments
    FROM     #TEMPTBL1 A with (nolock)         LEFT JOIN MCPINSR_EXT B         ON A.RCO# = B.ICO# AND A.RICO# = B.IINS#     LEFT JOIN MEDMC.dbo.MCPMEMB C with (nolock)         ON A.RCO# = C.MCO# AND A.RICO# = C.MICO# AND A.RMBR# = C.MMBR# AND A.RSUB# = C.MSUB#     Inner join MCPDCTR f      on c.MDOC#1 = f.DCTR# --and f.DPRI = 'Y'
    WHERE      ((A.RSER01 = '23'AND A.RGLOBE = 'Y')      AND      A.RITODT is NULL     -- AND     --A.RIFMDT Between dateadd(day,-1,getdate()) AND Getdate()      AND      B.Active = 'Y')          OR     (RADMIT = 'Y'      AND      A.RITODT is NULL      AND     --A.RIFMDT Between dateadd(day,-1,getdate()) AND Getdate()     -- AND      B.Active = 'Y')      Union
/* Select Discharged records for the report. Data is anyone who has been discharged in the past 4 days*/         SELECT --Distinct      'Discharge' AS NotificationType , CASE         WHEN (A.RSER01 LIKE 'SN_' OR A.RSER01 = 'TCU') THEN 'SNF'         WHEN (A.RSER01 = '23'AND A.RGLOBE = 'Y') THEN 'OBSERVATION'         ELSE 'ACUTE'      END AS AdmitType     , A.RRFTON AS FacilityName     , convert(varchar(5),f.DOACD) + '-' + convert(varchar(10),left(f.DOPHN,3)) + '-' + convert(varchar(10),Right(f.DOPHN,4)) Fax#     , RTRIM(A.RMLNAM) + ', '+ A.RMFNAM AS 'PatientName'     , convert(varchar(10),C.MBIRDT,101) AS Birthdate     , CASE WHEN A.RIFMDT IS NOT NULL THEN convert(varchar(10),A.RIFMDT,101) ELSE '' END AS AdmitDate     , CASE WHEN A.RITODT IS NOT NULL THEN convert(varchar(10),A.RITODT,101) ELSE '' END AS DischargeDate     , CASE WHEN A.RIFMDT = A.RITODT THEN 1          WHEN A.RITODT IS NULL AND A.RIFMDT = GETDATE() THEN 1          ELSE DATEDIFF(DAY, A.RIFMDT, COALESCE(A.RITODT, GETDATE())) END AS LOS     , COALESCE(A.RSER01, A.RSER02) AS CurrentLoc     , C.MDOC#1     , C.MDOCNM AS PCP     , A.RRFBYN AS AttendingMD     --, B.Product1 AS Product     --, A.RAUTH# AS AuthNumber     --, A.RDIAG1 AS AdmitDX     , A.RPRIM AS DXDescription     --, D.ADISP AS DispositionCode     , E.DDESC AS DispostionDescription     --, b.CompanyShortName AS CompanyName     --, CASE WHEN A.RCASE1 LIKE '[%%]%' THEN A.RCASE1 + A.RCASE2 + A.RCASE3 + A.RCASE4 + A.RCASE5 + A.RCASE6 + A.RCASE7 + A.RCASE8 ELSE '' END AS Comments
    FROM     #TEMPTBL1 A with (nolock)         --MCPREFL A with (nolock)         LEFT JOIN MCPINSR_EXT B         ON A.RCO# = B.ICO# AND A.RICO# = B.IINS#     LEFT JOIN MEDMC.dbo.MCPMEMB C with (nolock)         ON A.RCO# = C.MCO# AND A.RICO# = C.MICO# AND A.RMBR# = C.MMBR# AND A.RSUB# = C.MSUB#     Left JOIN MEDMC.dbo.MCPACTY D with (nolock)         ON A.RCO# = D.ACO# AND A.RICO# = D.AICO# AND A.RMBR# = D.AMBR# AND A.RSUB# = D.ASUB#     INNER JOIN #TempDispCode E with (Nolock)         ON D.ACO# = E.DCO# AND D.ADISP = E.DISPCODE     Inner join MCPDCTR f      on c.MDOC#1 = f.DCTR# --and f.DPRI = 'Y'
    WHERE      ((A.RSER01 = '23'AND A.RGLOBE = 'Y')      AND      A.RITODT between dateadd(day,-4,getdate()) and getdate()      AND      A.RIFMDT IS NOT NULL      AND      B.Active = 'Y')      OR ((RADMIT = 'Y' )      AND      A.RITODT between dateadd(day,-4,getdate()) and getdate()      AND      A.RIFMDT IS NOT NULL      AND      B.Active = 'Y') ) AS AdmitDischarge Where Fax# <> '0-0-0'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

The query isn't likely to be the source of any grief here. Your expression, however, is dependent on what report section that formula appears in, as well as what type of report we're looking at. Is this a tablix column? You likely need a GROUP section where you can control this at the group level. Thus the expression would also need to be in some way group based.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Lowell SSC Guru Points: 323518 More actions · Answer 2

for me, your expression looks like the problem, but of course it depends on your data.
=IIf(Fields!NotificationType.Value = "Discharge",False,True
that is evaluated each row, right? so potentially some rows toggle visibility, and some don't... so is it the first row that is evaluated, the last row?
it needs to be more like of something exists, hide the column, unless the data is already grouped.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

craig.bobchin SSCrazy Points: 2298 More actions · Answer 3

Thanks for the ideas. I actually solved it a while ago by putting the report inside another table that is grouped by NottificationType. Based on the NotificationType, I'm able to hide or show the fields I need.