REPORTS - Creation and Formatting Questions

[vladt]

Hello Support Team,

While working on reports, I’ve got the following questions:

1. Is it possible to create a report based on a parameter query?
   For example, I’m building a Sales Rep Performance Report, which shows the performance of several sales reps through the entire available history (see below).

I want the user to be able to choose a single sales rep and a period of time, for which to display performance data for that sales rep. How do I do this?

2. Is it possible to add only vertical or only horizontal lines dividing rows and columns in the report? If yes, how?

3. Is it possible to visually separate rows of the report with a background on every other line (striped rows)? If yes, how?

4. Is it possible to create a report based on multiple queries (i.e., one query provides the details and another one provides a summary such as totals of the appropriate columns that have numbers)? If yes, how?

Thank you,

Vlad

[RileyAnderssen]

Hello Vladt,

SalesRepExample.fdf (2.9 MB)

Here is an FDF displaying all your questions.

BREAKDOWN:
Creating a Report Based on a Parameter Query

I have created a dashboard which consists of 4 processes, 1 of which is a lookup query to select the desired sales rep, 2 are date selectors to select the desired start and end dates (I have made my start and end dates in relation to the date a sale was made), and finally, a button to generate the report.

Above is the code used for the Select End Date process. It essentially sets a variable containing the value the user chose for the end date upon selecting it with the process.

The same principle has been applied to the start date and sales rep key processes, however, the sales rep key process is a bit more complex as it requires the use of a back end function to determine the name of the sales rep using an SQL Query, I will let you follow along with this one in the FDF but please let me know if there is any issues.

Above is the code I have used for the query to generate the report where it takes in the start date, end date, and sales rep key as parameters to produce an output tailored to these selections within the dashboard (be sure the name of these variables, that is, {{five.variable.NAME}}, match with the variables you previously set). Ensure the query is also set to live.

I have also created a library for each of the variables so that I am able to display them within the title of my report.

Finally, I have created my report template. As you can see at the top, I have included the sales rep name, start date, and end date within the title of the report, this uses the previously created libraries.

Below this, I have implemented a table which outputs the results of the query.

How to do this:

1. Click in table then click the pen button
   

   

2. Enter the following into the box then click insert
   

   

Where the fields section is the fields from the query you wish to display.

Finally, to ensure the values show within the title and the table, you will need to include the required data sources and libraries.

To generate this report, I have attached this function to the button process within the dashboard. Ensure the text within the selectAction function aligns with the actionID of your report.

At this point, you should be able to run your application, select a sales rep, start date and end date, press the generate report button and a report should display with the corresponding values (provided you have created the dashboard and attached it to a menu).

QU2:

You are able to split rows horizontally and vertically using this feature^^

QU3:
I am not sure how familiar you are with CSS but this requires CSS manipulation within the report template.

By clicking the button in the top left of the report template, you will be presented with the code used to produce the template.

To add the alternating line colours, I have added lines 1 - 9 and modified lines 17, 34, 35, 36, and 37 to use the classes created within the first 9 lines.

QU4:
To provide, for example, a total of all the sales values within a given query on the report, there is an in built function within five that allows for this.

1. Click into the table cell you wish to add the value then click the pen
   

   

2. Select the following and press insert
   

   

Where the Fields section is the field you which to get the sum of (total of).

3. Ensure the value is not within the /_each function
   
   

Note: In my images, I have created the second table to show the walkthrough of how to create the table, the FDF only includes the first table (which was made using all the steps I have presented).

SUMMARY
I know this all seems very complex so please let me know if you run into any issues. Be sure to look through the FDF I have attached as well. I hope this helps!

Thanks,
Riley.

[vladt]

Hello Riley,

Thank you so much! Your detailed instructions and especially the program that you created made things happen! - I managed to create the report based on a query with three parameters that also displays the totals.

In regards to the Question 2, I believe I didn’t explain it properly. I wanted to find out how to create horizontal and vertical lines as shown on the screenshot below:

Thank you again!

Vlad

[RileyAnderssen]

Hello,

This will require some CSS again, before I do an example, would you like the horizontal row to only apply to the line under the first row, or to all rows?

Thanks,
Riley.