I have a chart where the user can select what date field to filter on, either Date of Service or Denial Date. I'm struggling with creating a drill down table with more detail. I can't get that select date field parameter to pass to the drill procedure. The dates and all other parameters pass fine.

I've read the forum and documentation and think I need to use a .EVAL but have tried it in all sorts of places and can't figure it out. I've tried -? & to see what is passing for that parameter and nothing is passing, which is why I think I need a .EVAL somewhere. Any thoughts/advice are appreciated!

Cara

This message has been edited. Last edited by: cldiaz,

Can you show us the WHERE tests in the drill down report?

Sure! Here is my chart code:

 -SET &DATE_TYPE = IF &DATE_TYPE EQ 'Denial' THEN FIN_DENIALS.FIN_DENIALS.Denial_Date_MDYY ELSE FIN_DENIALS.FIN_DENIALS.DOS_MDYY;
END


GRAPH FILE fin_denials
-* Created by Info Assist for Graph
SUM CNT.FIN_DENIALS.FIN_DENIALS.DENIAL_ID AS 'Denials by Category'
BY FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.DENIAL_CATEGORY
WHERE FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.ACTION_AREA NE 'PFS';
WHERE FIN_DENIALS.FIN_DENIALS.ENC_TYPE EQ &ENC_TYPE.(|FORMAT=A50V).ENC_TYPE.QUOTEDSTRING;
WHERE FIN_DENIALS.FIN_DENIALS.MED_SVC EQ &MED_SVC.(|FORMAT=A50V).MED_SVC.QUOTEDSTRING;
WHERE &DATE_TYPE GE '&Start_Date';
WHERE &DATE_TYPE LE '&End_Date';
WHERE FIN_DENIALS.FIN_DENIALS.ACTIVE_IND EQ 1;
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 1
ON GRAPH SET GRXAXIS 0
ON GRAPH SET LOOKGRAPH PIERING
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
setPieFeelerTextDisplay(1);
*END
INCLUDE=IBFS:/WFC/Repository/RDFTemplates/Common/sty/angular_modified.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, $
TYPE=DATA, COLUMN=N3, TARGET='_blank', FOCEXEC=IBFS:/WFC/Repository/Finance/Denials/Denials_by_Category_Drill_V1.fex(DENIAL_CATEGORY=FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.DENIAL_CATEGORY Start_Date=&Start_Date.QUOTEDSTRING End_Date=&End_Date.QUOTEDSTRING DATE_TYPE=&DATE_TYPE.EVAL ), $ 

There here is the drill fex code:

SET EXCELSERVURL = ''


-SET &DATE_TYPE = IF &DATE_TYPE EQ 'Denial' THEN FIN_DENIALS.FIN_DENIALS.Denial_Date_MDYY ELSE FIN_DENIALS.FIN_DENIALS.DOS_MDYY

END

TABLE FILE FIN_DENIALS
BY  FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.DENIAL_CATEGORY AS 'Denial Category'
BY  FIN_DENIALS.FIN_DENIALS.DENIAL_ID AS 'Denial ID'
BY  FIN_DENIALS.FIN_DENIALS.FIN
BY  FIN_DENIALS.FIN_DENIALS.ENC_TYPE AS 'Encounter Type'
BY  FIN_DENIALS.FIN_DENIALS.MED_SVC AS 'Medical Service'
BY  FIN_DENIALS.FIN_DENIALS.PLAN_NAME AS 'Plan Name'
BY  FIN_DENIALS.FIN_DENIALS.FIN_CLASS AS 'Financial Class'
BY  FIN_DENIALS.FIN_DENIALS.ATT_PROV AS 'Attending,Provider'
BY  LOWEST FIN_DENIALS.FIN_DENIALS.DOS_MDYY AS 'Date of Service'
BY  FIN_DENIALS.FIN_DENIALS.Remit_Date_MDYY AS 'Remit Date'
BY  LOWEST FIN_DENIALS.FIN_DENIALS.Denial_Date_MDYY AS 'Denial Date'
BY  FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.ACTION_AREA AS 'Action Area'
BY  FIN_DENIALS.FIN_DENIALS.DENIAL_CODE
BY  FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.CODE AS 'Denial Code'
BY  FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.DESCRIPTION AS 'Denial Description'
WHERE &DATE_TYPE GE '&Start_Date';
WHERE &DATE_TYPE LE '&End_Date';
WHERE FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.DENIAL_CATEGORY EQ '&DENIAL_CATEGORY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
  

Thanks!
Cara

Place the following in your code right before your line: TABLE FILE FIN_DENIALS

-TYPE &|Start_Date: &Start_Date
-TYPE &|End_Date: &End_Date
-TYPE &|DENIAL_CATEGORY: &DENIAL_CATEGORY
-TYPE &|DATE_TYPE : &DATE_TYPE 
-EXIT  

The procedure will stop before it actually runs the table request and you can see what the parameters are that were passed to the drill down and see if they are passing correctly.

You also need a semicolon at the end of your -SET in the drill down.

Thanks Hallway! Doing that validated what I saw with -? &.

The &DATE_TYPE parameter isn't getting passed to my drill. Nothing is getting passed. I've tried using .EVAL in various places and I'm not having any luck.

May not be related but...

quote:

-SET &DATE_TYPE = IF &DATE_TYPE EQ 'Denial' THEN FIN_DENIALS.FIN_DENIALS.Denial_Date_MDYY ELSE FIN_DENIALS.FIN_DENIALS.DOS_MDYY;
END

Since you perform the above before your parent calling fex, you lost the original value of DATE_TYPE (you test and set the same variable's name) so in the second test the result will always be the ELSE.

Do you have any DEFAULT set somewhere ?

Does the drilled fex run by itself with parameter prompting ?

Could you test this without DATE_TYPE=&DATE_TYPE.EVAL in your drill down? The drill down report you're calling expects to use start and end dates in the WHERE &DATE_TYPE selection criteria.

QUOTEDSTRING on DATE_TYPE perhaps?

I think that this might be easier to solve than you think.

In the drilldown code, they have two dates being passed via variables. No mention of those failing to be passed. They then have a final one that isn't being passed.

Essentially cldiaz should look at their code and be able to work it out.

A thing to remember is that any value that is not a field (most of the time), should be quoted so that the first part (before any special characters) isn't interpretted as a field name.

T

Thanks ALL!
With all of your help I was able to sort this out.
As some suggested, in my original chart fex in the drill down I included QUOTEDSTRING on my date_type drill:

TYPE=DATA, COLUMN=N2, TARGET='_blank', FOCEXEC=IBFS:/WFC/Repository/Finance/Denials/Denials_by_Category_Drill_V1.fex(DENIAL_CATEGORY=FIN_DENIALS.REF_FIN_REASON_REMARK_CAT_CDS.DENIAL_CATEGORY Start_Date=&Start_Date.QUOTEDSTRING End_Date=&End_Date.QUOTEDSTRING DATE_TYPE=&DATE_TYPE.QUOTEDSTRING ), $
  

Then MartinY had led me to understand I didn't need that SET statement in my drill fex. I removed that from my drill fex and now it all functions perfectly.

Thanks so much for all of your advice!
Cara

This message has been edited. Last edited by: cldiaz,