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

All responses (26)

Answers (4)

Pete Alberts 3,542 on at

Like (0)

Report

RE: Report query range default to unlimited

Thanks Martin

I feel a bit in over my head. I have a working solution. I would rather leave this as it is.

Thank you for the help, much appreciated! :)
Pete Alberts 3,542 on at

Like (1)

Report

RE: Report query range default to unlimited

Thank you Nikolaos

I put in some effort to show the process I followed. I usually assume you guys know everything (really), but I guess AX is so broad, no-one will ever get there. But I think the fact that I got so much flack for this post is because you and Martin were not aware of this process, as you mentioned. (Please, no disrespect intended in any way. The help I get on this forum is crucial to me. And I truly am very thankful.)

Here is the process I followed.

A screenshot of the ranges for the AOT query:

(This query produces the data for the report)

After the query is dragged into the report's dataset node. You wait about 20 seconds (hence that hardware post) and then you get the result as such:

(The query ranges are included in the Dataset parameters)

Also, the ranges are added as Report Parameters:

And then these parameters (the query ranges), are shown in the report dialog:

(This screenshot is after I added two other parameters and grouped them - but I guess enough to illustrate the result)

And then you get to the issue I described in the OP. And then I solved the issue in the post I marked as answered.

I would not say that the "issue" is a bug. It makes sense.

Further, I am definitely not the first person to have this issue. In the OP, I provided a link of someone describing a solution to a related issue. I would say mine is very much of a better solution.

Thank you again Martin and Nikolaos.

Kind regards
Verified answer

Martin Dráb 233,004 Most Valuable Professional on at

Like (0)

Report

RE: Report query range default to unlimited

Great, now when you finally told us what you did, I'm able to reproduce your problem.
You're right, when Dynamics Filters = False, VS creates report parameters for query ranges (which will cause AX to create dialog fields) and query parameters linked to these report parameters (which will filter the data).
I assume that if we want this filter not to actually filter anything, the parameter should be nullable and the report should be called with null value. But since AX doesn't support nullable primitive types, it won't ever do it.
Now when we've isolated the problem, I think we can finally ask others to share their experience. Maybe somebody's already played with these query parameters in SSRS reports called from AX / D35FO.

By the way, when I mentioned workarounds, I meant what you called workaround yourself, such as changing the data type of your field.
Verified answer

nmaenpaa 101,158 Moderator on at

Like (0)

Report

RE: Report query range default to unlimited

I'm not very experienced with SSRS reports so I don't have any experience on dragging a query into a report and getting automatically a dialog with ranges.

If such thing really exists, then I think your approach is "standard" and this sounds that there might be a bug in the system. Note that this comment is based purely on the information that you provided and it might not be true.

If you suspect there is a bug in how the system works, you can create a support request and discuss it with MS. Maybe they will fix it.

And in general I think all developers are in position to discuss and question the spec if they feel like it. Nobody knows everything so it's always good to bring up any potential issues or new ideas. If the spec is written by a non-developer (or technical architect), then not much technical knowledge might have been involved, and it's good to bring up if you think something might be done differently.
Pete Alberts 3,542 on at

Like (0)

Report

RE: Report query range default to unlimited

And then on the spec. I'm not really in a position to question. But I will.

But then again I would agree with the spec as it is more user friendly to select a few parameters instead of opening the SysQuery form. But my opinion isn't really relevant - no experience. But to me it seems like a better option.
Pete Alberts 3,542 on at

Like (0)

Report

RE: Report query range default to unlimited

Hi Nikolaos.

I wouldn't say it is a workaround. If AX provides this functionality, then it's "standard"? I mean I didn't provide a dialog or parameters. I simply drag in a query into a report and then I get a dialog with parameters linked to my query ranges. I don't understand how that is a work around as you and Martin mentioned. But I'll definitely take your word over mine.
Martin Dráb 233,004 Most Valuable Professional on at

Like (0)

Report

RE: Report query range default to unlimited

Yes, your previous reply made clear that you're not simply setting values of query range.

All right, so you have some parameters. But what you're doing with them? Because just having parameters wouldn't filter anything. Are you directly using them for filters inside your SSRS report data set or even for filter expressions inside your design? And you have no logic for dealing with empty values, right? I assume you do something inside the report because you said your don't have any code. An alternative would be dealing with ranges in a controller class, where you could add and remove ranges as needed or you could use valueUnlimited(), if you want.

Please realize that we depend on information from you. Try to make easier for us to help you.
Suggested answer

nmaenpaa 101,158 Moderator on at

Like (0)

Report

RE: Report query range default to unlimited

I think the spec should be questioned. Why build your own handling for these ranges when there is already an easy out of the box way for doing it. Now you spend time dealing with issues that do not exist in the standard ranges.
Pete Alberts 3,542 on at

Like (0)

Report

RE: Report query range default to unlimited

In response to your last post.

Now I understand what you mean by "just removing the ranges". As I just mentioned, I don't use that functionality, I have my own parameters. (Not my choice - part of the spec.) So AX produces this from the ranges in my query:

And then on string vs integer/enum ranges. I can only explain it by showing the result on a form of mine. I assume the same type of thing happens on the report dialog.

I assume that I get no values in the report because the range is applied as the first yearRange option in my code above.

Hence my assumption that the reason an empty integer/enum range on the report dialog results in a range on zero.
Martin Dráb 233,004 Most Valuable Professional on at

Like (0)

Report

RE: Report query range default to unlimited

All right, so you've finally confirmed my suspicion that you're doing something else than setting query ranges values (despite of talking about query ranges). I'm bit sad that I've just wasted so much time with it. I didn't ask for details for fun.

Anyway, if you still want our help, please tell us at least now what you've actual done. Note that normal solution works correctly, therefore thing again if you want to spend time building workarounds for your problematic solution or you should throw it away completely. You're right that using the default value of a type (e.g. 0) as a range value is something else than not having any range at all, but your solution clearly doesn't do what you want from it.

Regarding screenshots, if you can't make any screenshots that don't show anything sensitive and you can't use any dummy data, you can always hide some parts of the picture. Like this: