REPOST-Need help with On Demand Emailed Report

Daisy_Ramirez · 10-14-2019 08:32 AM

REPOST - Hello, I’m trying to generate a simple call list for the sales team. Manager needs a list grouped by sales rep he can pass out to the reps for them to go down their lists and call prospects. This template is based on the dash_quotes_list slice that has parameters specified in a form based on the parameters table. User selects which report they need to run and the system emails the appropriate listing Report. Can someone assist with the correct expression? I haven’t been able to find a good sample database and can’t access the Google Doc template to reference the correct expression. The example below doesn’t generate any data at all. Thanks

WillowMobileSys

We should determine why the current expression is returning no data. Have you looked into the Log to see if any errors have been reported on the execution of this template?

Once that is figured out, then you can add an ORDERBY() function around the SELECT() to order it by Sales Rep. BUT this will simply list ALL the associated records in that sorted order.

If you need to SEPARATE the list by Sales Rep in some manner, then there will need to be some re-work to the template and maybe the data to accomplish that.

First, check the Log for any reported errors.

Daisy_Ramirez

Hi John, thanks. Yes the logs show the workflow ran successfully but there’s no data in the table. I’ve been using the ClicktoSendEmail as reference and have it setup exactly but I’m getting no data. The slice associated with the report has fields to be filled out in the parameters form so the slice has the following parameter expressions:

AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),

[Quote Date]>ANY(Parameters[Start Date]),

[Quote Date]<ANY(Parameters[End Date])
)

WillowMobileSys

You mentioned dash_quote_list is a Slice. If you go to the Slice definition and tap the View Data button it shows you a list of records?

Daisy_Ramirez

Currently no data appears since its based on the parameters below. I’m running the report from the parameters form (in a dashboard) where I complete the fields for the parameters and then request the report by pressing a “print” action to change the data of my parameters table and trigger the report workflow.

AND(
CONTAINS([Depot],ANY(Parameters[Depot])),
CONTAINS([Assigned to],ANY(Parameters[Assigned to])),
CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])),

[Quote Date]>ANY(Parameters[Start Date]),

[Quote Date]<ANY(Parameters[End Date])
)

I’m wondering if I should be placing the parameters above in the report?

WillowMobileSys

Ok, there is client side activity and server side activity. Workflows ALWAYS run on the server side. Actions activated by the UX will run and create updates on the client side with those updates being sent to the server.

The order in which those updates are applied and triggering of Workflows may not always be in the order you expect.

I suspect that maybe the Workflow that triggers the email is running BEFORE the other updates have been applied that the Slice relies on.

Daisy_Ramirez

ok I think I found the problem. When the parameters are used to query a table view from a parameters form, the user can simply update the dropdowns with “Clear” to indicate all options should be assumed. This same capability is not recognized for the report. The report expects all parameters to have an entry else it returns nothing. I just tested this scenario and did receive some data in the email report.

How else can we indicate “All” when parameters are being requested as in the above?

WillowMobileSys

Change the above to :

OR(ISBLANK([Quote Stage]), CONTAINS([Quote Stage],ANY(Parameters[Quote Stage]))),

BUT of you are saying cleared (which is blank) or any of the selected options needs to be true, then basically is has no impact on the expression as it is always TRUE and then can be safely removed.

Daisy_Ramirez

Will “ISBLANK([Quote Stage]” return all records?

WillowMobileSys

Sorry, your question was how else can you indicate “All”?

Before we get to that, I just realized that your expression may not be what are expecting

I am wondering if your CONTAINS() portion is working as you expect.

The ANY() part of the expression will only return the first value from the list. So ANY(Parameters[Quote Stage]) returns the first value from the Parameters table for the [Quote Stage] column. Then CONTAINS() is really just checking if those two values are equal.

Is that what you intended?

Daisy_Ramirez

Ok tested ISBLANK in the slice and it unfortunately returned no data. Blank field parameters generating an “all” effect on data in table view but not acknowledged in report.

In your experience, if an ENUMLIST is used, will the slice include all options in the data. If yes, the user would then need to select all options one by one Yes?

WillowMobileSys

You may not have seen my prior post as we submitted at almost the same time. I think we should back up and look at the expression you posted earlier. I’m concerned it may not be doing what you think it is.

What are the CONTAINS() part the expressions expected to do?

Daisy_Ramirez

CONTAINS() points to the field within the slice that should be matched with the data inserted into the Parameters corresponding field.CONTAINS([Quote Stage],ANY(Parameters[Quote Stage])). When I use this in a table view it works as expected and returns the values I expect to see.If I remove CONTAINS(), I receive error: subexpressions must be Yes/No conditions

WillowMobileSys

In other words, you want to see if the selected [Quote Stage] value is one of the possible values found in the the list Parameters[Quote Stage]?

Daisy_Ramirez

yes

WillowMobileSys

Ok, then you want to use the IN() expression. It operates on a List. CONTAINS() operates on string/text values which is why you had to add the ANY() statement to make it pass the syntax check.

So you really want your expression to be:

AND(
IN([Depot], Parameters[Depot]),
IN([Assigned to], Parameters[Assigned to]),
IN([Quote Stage], Parameters[Quote Stage]),

[Quote Date]>ANY(Parameters[Start Date]),

[Quote Date]<ANY(Parameters[End Date])
)

Update your expression to the above and see if you get data in the template

Just a side note to illustrate the difference:

CONTAINS(“xyz”, “12345xyz6789”) = TRUE - “xyz” is contained with the string “12345xyz6789”
CONTAINS(“xyz”, “123x45y67x89”) = FALSE - “xyz” is NOT a substring

IN(“xyz”, LIST(“abc”, “def”, “ghijk”, “xyz”)) = TRUE - value “xyz” in list of values
IN(“xyz”, LIST(“abc”, “def”, “ghijk”, “123xyz”)) = FALSE - value “xyz” NOT in list of values

Daisy_Ramirez

Got it thanks for the descriptions. I inserted the statements above and my table view works like the report does now - does not recognize “when the parameter is left blank we want all options in the drop down to be selected”. If a parameter field is left blank the table/report returns blank. I’m looking for the reverse - “when the field is left blank, include all drop down options in the report”

WillowMobileSys

Ok, good. Sorry for the number of posts before it clicked in me what you needed. I guess I was focused more on the template.

Now, on to the ALL option. First, back to my previous question. Do you ALWAYS expect a selection for [Quote Stage] to be “ALL” or one of the available options? In other words, is “No Option” a valid choice?

If its not then you can use blank to represent “All” just fine.

You can update the expression like I had suggested before:

AND(
IN([Depot], Parameters[Depot]),
IN([Assigned to], Parameters[Assigned to]),
OR(ISBLANK([Quote Stage]), IN([Quote Stage], Parameters[Quote Stage])),

[Quote Date]>ANY(Parameters[Start Date]),

[Quote Date]<ANY(Parameters[End Date])
)

Your expression is just checking for valid values. If blank is represent “All” then it is valid as well as one of the choice from the Parameters list, then adding the ISBLANK() check will allow it to be considered valid.

Daisy_Ramirez

No problem. I thought the problem was the template too

I’d like blank to equal “All”.

Same reaction - no records when field is blank

WillowMobileSys

Do you have the same issue with [Quote Date]? What does Parameters[Start Date] and Parameters[End Date] have in the Parameters table?

And what are the data types of the 3 date columns - [Quote Date], [End Date], [Start Date]

Daisy_Ramirez

Sorry John had to take a call.

No issues with the Quote Dates as those are required fields and will always contain data. These are date/time fields

Depot & Assigned to are REF fields
Quote stage is an ENUM text field

WillowMobileSys

Oh, so [Depot] & [Assigned to] are Ref columns, let’s make sure you are comparing apples to apples with the IN() statement between the Quote record and the Parameters table.

To make things maybe quicker, Can you provide a screen shot of -

a quote record showing these columns - Depot, Assigned to, Quote Stage and Quote Date?
the Parameters table showing columns - Depot, Assigned to, Quote Stage, Start Date, End Date.

Daisy_Ramirez

The fields are spread across multiple forms based on the user’s needs.

We know the IN() expression works - when I select data in Depot, Assigned to, Quote Stage and Quote Date Start & End - the appropriate data appears on screen.

If I leave any of the 3 key fields empty Depot, Assigned to or Quote Stage empty nothing appears at all.

WillowMobileSys

Ok, fair enough. I was concerned about the ANY() statement with the Dates. If there is only ever a single date for Start Date and End Date in Parameters table then your good.

Looking at the IN() statements part of the expression, I see nothing wrong with them. I would remove them and test with only the Dates part just to confirm that is working as expected.

Daisy_Ramirez

Yep. Good email report when only start/end date are the parameters

Daisy_Ramirez

Hi John, just wanted to post the ending expression. This one works successfully for both the form and the email template. Thanks so much for your help today.

AND(OR(ISBLANK(Parameters[Depot]),CONTAINS([Depot], ANY(Parameters[Depot]))),
([Quote Date]>=ANY(Parameters[Start Date])),
([Quote Date]<=ANY(Parameters[End Date])),
OR(ISBLANK(Parameters[Assigned to]),CONTAINS([Assigned to],ANY(Parameters[Assigned to]))),
OR(ISBLANK(Parameters[Quote Stage]),CONTAINS([Quote Stage],ANY(Parameters[Quote Stage]))))

WillowMobileSys

You are welcome.

I see you have re-inserted the CONTAINS() statements. Maybe I didn’t completely understand your data design. I do recommend to test thoroughly as I am still concerned the CONTAINS() statements are not doing what you expect them to.

Daisy_Ramirez

Will do. Tons of testing to do on this one

Thanks again!