Dashboard: Inquiry "Normal" Levels

You can grab that data into your GI with a join to CSAnswers that looks like the screenshot below. Replace ATTRIBUTEID with the correct ID.

If you are using the @me parameter to show data specific to the logged in user, you will need to connect to the USER table. You can do this through the userID or email field in the employee table.

You would use the @me filter/parameter on the user.PKID field.

So in the Dashboard setup the “normal” value drop down is empty.  How do I designate values to be used in that field?

Normal and Alarm are only there to display a specific color or to define a threshold where the color should change. Say you have 100 shipments in the queue, and on a normal day you 80. You could define your normal level as 80. You also know that at 110 shipments will really backup your team, so you might define 110 as your alarm level. This widget will offer the warning color as you are approaching the alarm level. 

There really is no functionality beyond that. It’s just visual.

​@bwhite49

So I am trying to create a Gauge on how close the Sales Person is to reaching their quota.  It will need to be different for each login.  Stored in an Attribute under each employee table.  

In the setup, it looks like you can change the Fixed Value to an Inquiry Field, but the drop down is blank and I am not sure how to designate something to be used there.

Again, thanks for all your help!

Got it, the issue is probably a field schema disagreement.

If your field is a text field, you will need to convert it to an integer or decimal. In the GI editor, you can click on the pencil icon to edit the field. Wrap you field in a CDEC([table.field]) or CINT([table.field]) formula and then give that field a caption. This should make the field available in the drop down. 

So the field is converted:

Is there anything else that needs to be done in the GI to designate it to be used in the Inquiry Field?  Tried both the CDEC and CINT.  

That’s a bummer. Looking at your screenshot, it looks like you are grabbing the field from the EPEmployee table rather than the CSAnswers table. The data is actually stored in CSAnswers, and you are limited with what you can do with the projected field in the EPEmployee table. This could be the issue.

In my initial comment on this post, I posted a screenshot to pull the data from the value field in the CSAnswers table. I would try that. 

​@bwhite49 

So we are making progress, we do have the field now showing up in the inquiry field drop down.  It is also showing the right quota amount in the GI when I run.  Now the issues seems to be it is summing up the Quota instead of applying the MAX.  The Meter Widget is set to Sum the Margin $ but I don’t see where I can control the aggregate function on the actual inquiry field.  I have set the GI result to MAX for that field but it doesn’t seem to be transitioning to the Widget.  Is there a way to just pull in the static field independent upon how many records the GI pulls.

Hmm, you could modify the GI to be a summary table. So instead of using the format of the current GI, you could make a copy of the GI for this widget so that it displays the way you intend.

So instead of listing out all of the projects, you could group on employee ID/CD so that each employee has a single row. When you group on employeeCD, you will notice the margin will automatically be summed, but so will the goal attribute. To fix, you can add the goal attribute into the grouping so that it displays the correct value.

I think this will correct the widget display.

​@bwhite49 ​@chrislower you just saved the day! I stumbled across this community piece just as I’m doing the same thing 2 weeks after it was posted. Thank you!!

I had to make some changes to our GI to make it work (listed below). And I have some questions on making the GI Widget pull the inquiry field drop down → At present I can’t make this happen even with CDEC.

1. Our GI needed the relations posted by ​@bwhite49 reversed:

Two relations for two different attribute columns → a normal quota and an alarm level for the two fields in the Widget:

=CDEC([EmployeeMonthlyQuota.Value]) and =CDEC([EmployeeMonthlyQuotaAlarm.Value]) 

2. Our widget will not pull these fields into the Inquiry ​@chrislower how did you do that part 

3. For the “Fixed Values” it works great using this same GI but I need this using the Inquiry Field Value because we have 12 reps using one dashboard which is already set up for dynamically logged in use. 

   

    

​@astramathis30 ​@bwhite49 

So I ultimately got a little busy the last two weeks and haven’t got back to this.  I am not 100% of the way there yet,  

I was able to get the inquiry field to populate, but could never get it not to Sum.

To get it to populate, I ended up utilizing the following value (with help):

=CDec(IsNull([CSAnswers.Value],0))

and the following schema:

The value populates on each row but sum’s in the widget.

I tried doing a grouping on Sales Person and set the aggregate function to Sum and Max on the appropriate fields but it only seems to give me the first Project in the list instead of correctly summing the margin and setting max to the quota.