Report query range default to unlimited

(1) Share

Report

Posted on by Pete Alberts

3,542

I am trying to build a report with an AOT query. I want my query's ranges to default to SysQueryRangeUtil::valueUnlimited() if the user did not enter a value. I tried a few things and googled a lot. I found this page https://dataqueen.unlimitedviz.com/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/ but it seems like a lot of work :(

For clarity: The query's ranges are shown in the report dialog. And then if they are left open I get zero results in my report. I assumed this is because the query still filters on the empty ranges - I get results if I specify applicable ranges.

I've done a few reports following the DP, Contract and Controller path. But I am trying to do this one with only an AOT query (and View). I reasoned if I can do it this way it might speed up work in future.

Is there a way to default to an unlimited query range if the value is not filled in?

Thanks for reading

Categories:

Development / Customization / SDK

I have the same question (0)

All responses (26)

Answers (4)

Martin Dráb 237,959 Most Valuable Professional on at

Like (1)

Report

I don't think that your assumption is correct. If the ranges are really empty, they aren't filtering anything. SysQueryRangeUtil::valueUnlimited() is used to set the value to empty, which you said you already have, so it wouldn't make any difference.

Therefore you either have non-empty ranges, or you have a different problem with your report and you should stop focusing on these ranges.

Make sure you test your query before using it an a report. If it doesn't work in isolation, trying to use it in a report doesn't make sense; it clearly won't start working by itself and it would merely make testing more complicated.

If the query works in isolation but the report doesn't return any data, you'll know that the query isn't to blame.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report
Thanks Martin

The report query has a single view as datasource. To try and isolate the problem I removed all but one range - the year range. (The view correctly populates an integer field YearDateOccurred.)

On the front end:

1. Select 2018 - report populates correctly

2. Select nothing - report populates nothing

I want "nothing" to mean the result shouldn't be ranged.

I reread my OP and it seems confusing. Does the above description explain my issue better?

And yes I should test it without the report, but I know how to accomplish the above result "normally" - in fact I allready have it in working order on the related form. I just don't know how to do it in this (report) context. This is how I filter by year on the form:

QueryBuildRange yearRange; //initialised in init() [Control("Integer")] class YearInteger { boolean modified() { yearRange.value(this.value() ? this.value() : SysQuery::valueUnlimited()); return super(); } }

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

For the first time I actually checked what SysQuery::valueUnlimited() does and it got me to the following:

Which means that an empty value ===== SysQuery::valueUnlimited(). I did not know that. #believeIt I thought the method does something complex.

But then my problem does not make sense - empty values should mean no range? Is that why you said I should check my query?

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

I''l check the query

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

I checked it. There are 18 test records in the root table. The view displays 18 records with correct info from the root table. I ran the following code with the same query I use in the report and the result is 18 records. So the issue is in the report range. "Nothing" in the report dialog does not result in the same "nothing" as I am used to in query ranges.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,959 Most Valuable Professional on at

Like (0)

Report

That your report doesn't show anything may also mean that the query returns data but the report doesn't display it.

If you think that the problem is really in the query, look at what actual query is used by your report. For instance, you can catch it by extended events in SQL Server. Knowing what's going on it the first step to resolving the problem.

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

I have made some progress. Still referring to Report Parameters originating from a query range.

1. String ranges work normally. No value = no range

2. Integer & enum ranges behave differently. No value = range.value(0)

Was this reply helpful? Yes No
Martin Dráb 237,959 Most Valuable Professional on at

Like (0)

Report

How exactly are you setting values? Can't you simply remove the range?

Was this reply helpful? Yes No
Pete Alberts 3,542 on at

Like (0)

Report

I'll look at it again tomorrow. I read a lot on Creating Reporting Solutions docs.microsoft.com/.../planning-a-report-report-builder I'm not even sure if it's applicable to D365?

But I'm quite sure that the datatype of the query range has an impact. String ranges work fine. For example one range is InventSiteId. If I leave it blank, the result is no range on InventSiteId - correct. But then I have a range on a status enum {None, Recorded, Approved, etc}. If it is left blank I get zero results and I think it is because the range is then defined as statusRange.value(0)... which actually makes sense. The other problematic range is the year range. Which actually does not make sense to me. If it is left blank I also get zero results..... I assume that it also defaults to yearRange.value(0) instead of yearRange.value('')

In any case I have removed the problematic ranges from the query. And then added them as unbound report parameters as string datatypes. Will then filter on them in the tablix (I understand from the link above that is the better practice).

Was this reply helpful? Yes No
Verified answer

Pete Alberts 3,542 on at

Like (0)

Report
Allright I sorted this out.

If you want to use only a query as datasource, you have two options (as far I know) to provide and implement ranges.

Add ranges on the query

Add parameters and then filter in the report design

If a query range is mandatory you don't have any problems. If it is not mandatory and it is of type string, you are also fine. But if the range is non-mandatory and it is an enum or integer, you have issues. The reason is basic programming knowledge:

EmptyString == '' == QueryRangeUtil::valueUnlimited()

EmptyInteger == 0 -> qbr.value('0') != QueryRangeUtil::valueUnlimited()

EmptyEnum == 0 -> qbr.value('0') != QueryRangeUtil::valueUnlimited()

Thus you need a workaround.

For an integer. I guess this is rare. My boss wants a year lookup. I solved this by changing the type to string.

For an enum. Add a AX Enum Provider dataset and a parameter to your report. Link the parameter to the dataset in the parameter's Values property. And then filter in the report. Something in the direction of "if parmEnum.value != 0 then filter on parmEnum.value"

Was this reply helpful? Yes No