1 Topic by mikee

  • mikee
  • Member
  • Offline
  • Registered: 13-03-2013
  • Posts: 33

Topic: How to filter a report grid by a value in a column.

We need to show the results of a survey using the Survey Report, as a report grid.  We need to only show rows that contian a specific value (string) in one of the columns. I'm thinking this should be possible using the Columns Configuration List in the Report Settings section by specifying a filter value for the relevent column.

Suppose we have a column with a field name, "attr_4_4" which contains values derived from a term list.  We only want to show rows that contain a specific value from that list.

Pointers as to how to achieve this would be welcome.

Mike

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: How to filter a report grid by a value in a column.

Hi Mike
You will need to set the datatype attribute of each <column> element you want to be able to filter on. Then the report grid will add a filter row at the top that you can type a filter string into. Take a look at library/occurrences/explore_list_2.xml as one example of this being done. There is also documentation at http://indicia-docs.readthedocs.org/en/ … rmat.html.

Best wishes
John

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by mikee

  • mikee
  • Member
  • Offline
  • Registered: 13-03-2013
  • Posts: 33

Re: How to filter a report grid by a value in a column.

Thanks for replying John but, we don't want the report *user* filterable.  We want to filter the report depending on the logged in user's access rights.

As a example we wouldn't want sensitive recods visible to all users, only to priveleged users.  Or a user can see only a subset of a survey.  These filters would be implemented when the form is designed and not be changable by regular users.

Mike

4 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: How to filter a report grid by a value in a column.

Hi

How about you create two similar forms and you make one visible to regular users and the other visible to privileged users, using the View Access Control checkbox plus normal Drupal permissions control. Then you can use different Preset Parameter Values on the two forms to present different results.

Alternatively you could code a modified form that accessed the user role and set the parameter programmatically.

The report you are using would need to include a parameter for the attribute you want to filter on.

Jim Bacon.

5 Reply by mikee

  • mikee
  • Member
  • Offline
  • Registered: 13-03-2013
  • Posts: 33

Re: How to filter a report grid by a value in a column.

Hi Jim

That was exactly my plan and I've already done that for displaying a submitters results to  an ordinary user, ie only their own results, and a report that displays all the results to a coodinator/admin etc.

The problem now is that how do I filter on a parameter (a column in the report grid) value.  Can I do this in the form edit page or will I have to delve into the code?

I've tried  putting attr_4_4="WWBIC" in the Preset parameter values: box in the report settings, which has no effect on the output.  With both single and double quotes.

Incidentally shouldn't these options appear in the "Report paramters" when viewing the report?

Mike

6 Reply by Jim Bacon

  • Jim Bacon
  • Veteran
  • Offline
  • From: BRC
  • Registered: 03-11-2008
  • Posts: 980

Re: How to filter a report grid by a value in a column.

Hi Mike

I hadn't done this before but I find that, provided the report you are using supports it, you can do this in the form edit page with preset parameters.

I experimemted with the report library>samples>samples list. The important thing was that, in the list of parameters, there was one with datatype='smpattrs'.

I first added some preset parameters to match all of the parameters defined in the report as follows

survey_id=83
date_from=
date_to=
smpattrs=228,230,231
sample_method_id=

Obviously that is my survey id and my sample attribute ids which you would replace with your own.
My 3 attributes fields are lookup, text and integer.

Then, referring to the documentation that John pointed us at, but specifically at the section on optional custom attributes I found that the following additional preset parameters filtered on the sample attributes.

attr_sample_228=1982
attr_sample_230='01:00'
attr_sample_231=1

Attribute 228 is a lookup so I filter on the id of the item in the lookup list that I want.
Attribute 230 is text and I found this needed to be in single quotes to work.
Attribute 231 is an integer.

What appears in the "Report parameters" when viewing the report is the list of parameters declared in the report file. I can see that choosing a report that does what you need poses a problem when the descriptions are rather scanty. However you can see what they all do by looking in the repository as per my link above.

Jim Bacon.

7 Reply by mikee

  • mikee
  • Member
  • Offline
  • Registered: 13-03-2013
  • Posts: 33

Re: How to filter a report grid by a value in a column.

Hi Jim

My first error was using the wrong report.  I't looks like I need occurrences_list.xml and not occurrences_download.xml, (in which survey_id is defined as survey so I couldn't filter by it using  survey_id.  I'd consider that to be a bug by the way).

Anyway.

Having found the code and now using the 'correct report' I now get most of what I want.  Settings as below.

Preset parameter values:
survey_id=3
taxon_group_id=1
smpattrs=6,7,8,3
occattrs=4
attr_occurrence_4=119

Default parameter values:
<EMPTY>

It took me while to realise the filter needs to be in the Preset parameter values and *not* in the Default parameter values section.

Thanks for your help.  I'd never have found that documentation or the correct file/report without your advise.

Regards Mike Evans