Opened 9 years ago

Closed 8 years ago

Last modified 8 years ago

#87 closed Enhancement (Can't Duplicate)

POPULATE_MISSING_VALUES in query results

Reported by: Lois Haggard Owned by:
Priority: -Low Milestone:
Component: View-App Version: 2.0
Severity: Keywords:
Cc:

Description (last modified by Lois Haggard)

NM, Utah, NJ, and probably others would like the zero-value records to appear in the results tables. These are records that are not in the dataset that View gets from IBIS-Q.

For instance, if I query teen births by county, and there were no teen births in two small counties, those counties will not appear in the results tables. This wreaks havoc with our standard graphs and tables in Excel and for the NM EPHT group. The "template" expects to see 34 rows (33 counties plus total).

I think the xslt\html\query\module\result\Values.xslt and Values2d.xslt files can be modified to use the dimension files to create a record for each dimension value, and populate records that don't have a match in the IBIS-Q xml with zero values.

Lois can probably figure this one out eventually if Garth can't get to it.


5/3/2011 - This really didn't work well in the back-end .def files. I have asked Garth to integrate it into the View application.

Attachments (2)

ZeroRecordsinResults.doc (178.0 KB) - added by Lois Haggard 9 years ago.
POPULATE_MISSING.PNG (117.5 KB) - added by Lois Haggard 8 years ago.
POPULATE_MISSING_VALUES query result table

Download all attachments as: .zip

Change History (18)

comment:1 Changed 9 years ago by Lois Haggard

Owner: changed from ? to Not assigned

comment:2 Changed 9 years ago by Lois Haggard

Owner: changed from Not assigned to unassigned

comment:3 Changed 9 years ago by Lois Haggard

Description: modified (diff)
Owner: changed from unassigned to Garth

comment:4 Changed 9 years ago by Garth Braithwaite

There probably should be another module element/option added to the XML to control this as some might not want this behavior - e.g. some might only want to display data that has actual data. Also if you want the charts to handle this then the java chart controller code will need to be addressed as well.

Changed 9 years ago by Lois Haggard

Attachment: ZeroRecordsinResults.doc added

comment:5 Changed 9 years ago by Lois Haggard

I added a document that helps to clarify what IBIS is vurrently doing. In NM, our EPHT folks need the zero records in the xml that is returned by IBIS-Q - so this may have to totally be a back-end data issue.

comment:6 Changed 9 years ago by Lois Haggard

Owner: changed from Garth to lois & tong

comment:7 Changed 9 years ago by Lois Haggard

Milestone: IBIS-PH View2Ideas for Version 2 Enhancements That Are Not on the Current ToDo List

comment:8 Changed 8 years ago by Lois Haggard

Description: modified (diff)
Owner: changed from lois & tong to Garth

comment:9 Changed 8 years ago by Garth Braithwaite

Code added SVN: 2785. Needs testing and possible refinements but first pass is complete 6/5/2011.

comment:10 Changed 8 years ago by Lois Haggard

Resolution: duplicate
Status: newclosed

comment:11 Changed 8 years ago by Lois Haggard

Resolution: duplicate
Status: closedreopened
Summary: Zero-value records in Query Results TablesPOPULATE_MISSING_VALUES in query results

I just got the new <POPULATE_MISSING_VALUES> feature working in my NM dev env.

I'm using Tomcat 7.0.14.

I put this code into one of my module.xml files:

		<CONFIGURATION> 		
			<NAME>FBRCA_lt_50AgeRate</NAME>
			<TITLE>Age-adjusted Incidence Rate of Breast Cancer in Females Less Than 50 Years of Age</TITLE>
			<DESCRIPTION>Age-adjusted Incidence Rate of Breast Cancer in Females Less Than 50 Years of Age</DESCRIPTION>
			<POPULATE_MISSING_VALUES_DIMENSION_NAMES>
			<POPULATE_MISSING_VALUES_DIMENSION_NAME>GeoCnty</POPULATE_MISSING_VALUES_DIMENSION_NAME>
			</POPULATE_MISSING_VALUES_DIMENSION_NAMES>
			<MISSING_VALUE>0</MISSING_VALUE>

It worked. Here is what I'm seeing:

  1. It worked with my actual dimension name (GeoCnty?) but not with the Proxy var dimension name (GeoProxy?). This is okay/fine.
  2. It inserted the additional rows at the bottom of the table. This will cause an extra step for some of my users. This is related to ticket #n, about having an optional sort order for use in the dimension files.
  3. I used !<MISSING_VALUE>0</MISSING_VALUE>, but the value cell was blank. It did this even after I changed by !<XSLT_FORMAT_PATTERN> to #,##0.0

Changed 8 years ago by Lois Haggard

Attachment: POPULATE_MISSING.PNG added

POPULATE_MISSING_VALUES query result table

comment:12 Changed 8 years ago by Lois Haggard

Owner: changed from Garth to Developer - Java
Priority: majorminor
Status: reopenednew

Ticket #108 was the ticket re: sort order for dimension values. Garth commented on it on 6/5/11 - saying it was not trivial and it would slow down the query result significantly - similar to what this POPULATE_MISSING_VALUES thing is doing.

My recommendations:

  1. It is OK as is.
  2. If would be improved if it sorted the results table rows according to the original sort order (which I believe to be the dimension value).

comment:13 Changed 8 years ago by Lois Haggard

Resolution: worksforme
Status: newclosed

comment:14 Changed 8 years ago by Lois Haggard

Sorry - one more thing. The <MISSING_VALUE>0</MISSING_VALUE> element is a child of the <MEASURE> element. If I want to make it "0", I must also set the <XSLT_FORMAT_PATTERN> to use the zero: #,##0.0. This is okay - but it would be even better if I could supply the value for the numerator, too.

comment:15 Changed 8 years ago by Lois Haggard

Milestone: Version 2.x+1IBIS-PH View-2

comment:16 Changed 8 years ago by (none)

Milestone: IBIS-PH View-2

Milestone IBIS-PH View-2 deleted

Note: See TracTickets for help on using tickets.