Dashboard Style Reporting

[AdamH]

I'd like to get my head around some basic reporting within the PowerApp I'm building.

The App itself is a staff database around staff development.

I have some fields within the PowerApp that I would like to have a Dashboard style page report on.

The first two being:

Each staff record has an 'Active' drop down field that has Yes / No option. My first two would be How many Active Staff do we have & How many Inactive staff do we have and report back a figure like the picture below.

When a user selects; for example, 'Total Active Staff', I want this to navigate to a new window & gallery to show those results. 

Thanks 🙂

oh and a shout out to @WarrenBelz for taking the time out of his evening and helping me solve a problem that was sending me to an early grave!  

[RandyHayes · Accepted solution]

@AdamH 

So in general, this is all about data shaping. 

You've not provided a lot of detail on the data that you have, so I will assume a few things.

For your Gallery, you would have something along these lines for the Items property:

Table(
    {Title: "Total Active Staff",
     Items: Filter(yourData, Active.Value = "Yes")
    },
    {Title: "Total Inactive Staff",
     Items: Filter(yourData, Active.Value = "No")
    },
    {Title: "NQP 12 Month Review",
     Items: Filter(yourData, criteriaFor12MonthReview)
    },
    ...etc..
)

In the Gallery, a Label for the Title  ThisItem.Title

A label for the Count:  CountRows(ThisItem.Items)

Now, in the OnSelect of the Gallery, navigate to the screen you want to show the List of associated items.

In that screen...a Gallery with an Items property of yourDashboardGallery.Selected.Items

That will show only those that meet the criteria.

I hope this is helpful for you.

[AdamH]

@RandyHayes 

I only wanted to tackle a few items at a time to save it all getting complicated and any misunderstanding.

The above image is made from Labels only, I have not inserted a gallery here - Maybe this is not the best way of going around this type of thing?

Please correct me if I'm wrong, I wanted the larger numbers to report the totals based on the set query but the 'OnSelect' navigate to a gallery showing that query in a gallery view. 

[RandyHayes]

@AdamH 

No, the Gallery is Perfect!  You don't want to do this in a control by control way as you will then have a ton of controls to fiddle with forever.  Use a Gallery, do the labels and etc. the way you want and all the rest is done.

In this case, I would say if you want that look, use a Gallery with a WrapCount set to 4.

Use the Items property like I showed.

That will give you exactly what you are looking for.  Set the label sizes, fonts, colors, etc as you like.

Beside the OnSelect of the Gallery to navigate to another screen, there is nothing else you need than what was provided.

[AdamH]

OK - I have changed that and it's all working well. 

Now because I'm calling the same records up as in my original Gallery from when creating this app, the 'Right Arrow' that would normally take you to the Record Details screen 'Select(Parent)' is not working on these new galleries. 

On my original galleries the right arrow opens the DetailsScreen1 and DetailForm1

[RandyHayes · Accepted solution]

@AdamH 

So, let's discuss that a bit.  

The Table of records for your Dashboard gallery is not related to any particular record or dataset. It is very custom.  It DOES have the filtered list of records that match that criteria.

Since you have this information, you can do this...

In the OnSelect of your Gallery set a global variable.  We need to do this because you will likely edit a record down the road and your datasource will change.  This will cause the Gallery to reevaluate its items property and thus the selected item in the Gallery will change.  So, setting a snapshot of the current record will be helpful.

So, OnSelect:  Set(glbCurrentRecord, ThisItem); Navigate(yourBrowseScreen)

On that screen, you will have a Gallery (let's call it Gallery2).  The Items property of the Gallery will be set to : glbCurrentRecord.Items

As a bonus, you can put a Label on the screen as well and set its text property to : glbCurrentRecord.Title

In that Gallery, you can set the OnSelect to: Navigate(yourEditFormScreen)

On that screen you have an EditForm with a Datasource of your current datasource and an Item property of: Gallery2.Selected

In the OnSuccess of the EditForm:  Back()

This should give you what you are looking for.

[AdamH]

---

@RandyHayes 

Ok this has confused me - to many galleries to think about.

My current setup is:

Dashboard Screen

This has two galleries on it. One gallery (Gallery5) with WrapCount2 and another (Gallery5_1) with WrapCount4.

The right (selection) arrows on these are set to navigate to Dashboard Report1 Screen or Dashboard Report2 Screen. 

Dashboard Report 1 

Has a gallery called 'Gallery2' which has the typical vertical gallery on it which displays the filtered results from the Dashboard Screen (Gallery5)

Dashboard Report 2

Has a gallery called 'Gallery2_1' which has the typical vertical gallery on it which displays the filtered results from the Dashboard Screen (Gallery5_1)

My original Browse, Detail and Edit Screens which are connected to my Data Source
BrowseScreen1 has 'Gallery1'

DetailScreen1 has 'DetailForm1'

EditScreen1 has 'EditForm1'

---

Since you have this information, you can do this...

In the OnSelect of your Gallery set a global variable.  We need to do this because you will likely edit a record down the road and your datasource will change.  This will cause the Gallery to reevaluate its items property and thus the selected item in the Gallery will change.  So, setting a snapshot of the current record will be helpful.

So, OnSelect:  Set(glbCurrentRecord, ThisItem); Navigate(yourBrowseScreen)

---

---

On that screen, you will have a Gallery (let's call it Gallery2).  The Items property of the Gallery will be set to : glbCurrentRecord.Items

As a bonus, you can put a Label on the screen as well and set its text property to : glbCurrentRecord.Title

This is where I am getting confused -

Set(glbCurrentRecord.........) needs to be set on the Dashboard Screen galleries 5 and 5_1  and then navigate to Dashboard Report or my original BrowseScreen1??

Then :glbCurrentRec....... needs to be set on the Dashboard report screens Gallery 2 and 2_1 ??

[RandyHayes]

@AdamH 

I'm not sure the confusion.  What I suggested was two galleries 1) the dashboard and 2) the list of items for the selected dashboard item.  Yours now has 5 galleries??

Let me walk through it again.

1) Your main screen has One Vertical Gallery with a wrap count of 4.  

   The Items property is:

Table(
    {Title: "Total Active Staff",
     Items: Filter(yourData, Active.Value = "Yes")
    },
    {Title: "Total Inactive Staff",
     Items: Filter(yourData, Active.Value = "No")
    },
    {Title: "NQP 12 Month Review",
     Items: Filter(yourData, criteriaFor12MonthReview)
    },
    {Title: "NQP 18 Month Review",
     Items: Filter(yourData, criteriaFor12MonthReview)
    },
    {Title: "NQP 14 Month Review",
     Items: Filter(yourData, criteriaFor12MonthReview)
    },
    {Title: "Staff without Mentor/Preceptor",
     Items: Filter(yourData, criteriaFor12MonthReview)
    },
    {Title: "BLANK",
     Items: Filter(yourData, criteriaFor12MonthReview)
    },
    {Title: "BLANK",
     Items: Filter(yourData, criteriaFor12MonthReview)
    },

)

In that Gallery you have two Labels.  One that has the Text property set to ThisItem.Title and the other that is set to CountRows(ThisItem.Items)

To get the look you have, I would set the Title Label to a large Height and set the Fill property to the light grey color you have.  Also set the VerticalAlign to Top and the Align to Center.  Adjust font sizes and such as you wish.

I see no arrow as you say, and it is not really needed.  Just set the OnSelect property of your Gallery to : Set(glbCurrentRecord, ThisItem); Navigate('Dashboard Report 1')

2) On the Dashboard Report 1 screen - a Gallery (Gallery2) that has its Items property set to: glbCurrentReport.Items

3) The OnSelect property of your Gallery2, Navigate(DetailScreen)  or EditScreen depending on what you want to happen when someone clicks on an item in that list.

4) The Item property of all of your forms should be set to Gallery2.Selected

That's it.  You don't need multiple Report screens or two galleries on the main screen.

[AdamH]

@RandyHayes 

Made adjustments as your recommendation, Unfortunately PowerApps is not happy with something:

 This image is from Dashboard Screen - OnSelect property 

 This image is from Dashboard Report Screen - Items property 

I did mention that these screens are an addition to an existing app which already has Browse, Detail and Edit screens, if I change my Detail and Edit forms to work from Gallery2.Selected, the original screens will fail to function. Maybe this will help you see where I am coming from?

So the idea of the Dashboard is to break the data down (filter) and then I want to be able to edit that if needed, but I wanted to be able to use the original Detail and Edit screens. 

[RandyHayes]

@AdamH 

Something is not right on your first formula.  Where are you putting this formula?  It should be on the OnSelect action of the Dashboard Gallery.  I am noticing the formula editor is stating a datatype of Boolean...that is not normal for an OnSelect.

What error are you getting on the other forms?