nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Filter by actual and next month

Bordihn

Hi,
 
IÂ´m trying to filter my results within a report by the actual month + next month. Within my Data Set IÂ´m using the operator Between (Static example: between "2015-12-01" and "2015-12-31"). I also tried (dynamic example: between "new Date()" and "new Date(getMonth() + 1)"), but the second value (add one month to the current one) is not working. I found no fitting solution for this, what am I missing?
 
Thanks in advance and best regards.

Find more posts tagged with

Comments

jfranken

You could try the built in BIRT functions:
 
Now:
BirtDateTime.now()
 
One month from now:
BirtDateTime.addMonth(BirtDateTime.now(), 1)

Bordihn

Thanks jfranken,
 
that worked pretty well.
 
In a final step (cherry on the cake) I want now also to include my results from the last month, but just if they have a specific value.
Lets take oders as example. I want to show all orders with the due date of this month + the next month (that allready works), but also orders from the last month if they have the status "open" (thats the value, let`s say it can be only "open" and "closed"). In this way, orders which are still open appear in the report and donÂ´t go by the board.
 
Any ideas for that? Thanks in advance and best regards.

pricher

Hi,
 
Using the ORDERS table in ClassicModels, the following filter expression will return all orders between the parameter date and a month before where status is "Resolved" and all orders between the parameter date and a month in the future. I have also attached a report design. Hope this helps.
 
<div>(BirtComp.between(row["ORDERDATE"],BirtDateTime.addMonth(params["pDate"].value,-1),params["pDate"].value) && BirtComp.equalTo(row["STATUS"],"Resolved")) ||</div>
<div>BirtComp.between(row["ORDERDATE"],params["pDate"].value,BirtDateTime.addMonth(params["pDate"].value,1))</div>
<div> </div>
<div>P.</div>

Bordihn

Hi pricher,
 
your solution with the parameter worked pretty well, I just used instead the expression "BirtDateTime.firstDayOfMonth(BirtDateTime.today())" as default value for the Parameter and raised the value in the second row in the Data Set Filter to "BirtComp.between(row["defaultBaseplan.endDate"],params["pDate"].value,BirtDateTime.addMonth(params["pDate"].value,2))"
 
This way the user gets always the actual month and the report works as expected.
 
Thanks for your help and best regards.

Bordihn

Hi,
 
I just recognized the fact that the posted filter expression filters not just the orders from last month with the status closed but rather also the orders from the actual month with the status closed - meaning, the filter is not limited to the last month.
 
This is the actual dataSet Filter expression:
(BirtComp.between(row["ORDERDATE"],BirtDateTime.addMonth(params["pDate"].value,-2),params["pDate"].value) && BirtComp.equalTo(row["STATUS"],"Resolved")) || 
BirtComp.between(row["ORDERDATE"],params["pDate"].value,BirtDateTime.addMonth(params["pDate"].value,2))
 
As you can see we have now 4 months. When I generate the report for December, it shows the penultimate month (October), the last month (November), the actual month (December) and the next month (January).
The mentioned expression above now hides closed orders for October (as wished), but also hides closed orders for November (issue). December and January works as expected (no filtering by status).
 
How can I avoid the "all or nothing" filter? All my trials with a data Set Filter or with the visibilty function failed, so that my expression filters all closed orders or all open orders or nothing (by status).
 
Thanks again in advance and best regards.

pricher

Hi,
 
You can change the filter expression so that the logic for each month (penultimate, last, actual and next) is treated separately. The following expression will filter has 3 parts: 1) Show only the "Shipped" orders for the penultimate month; 2) Show all orders for the last month; 3) Show all orders for the actual and next month. 
 
<div>(BirtComp.between(row["ORDERDATE"],BirtDateTime.addMonth(params["pDate"].value,-2),BirtDateTime.addMonth(params["pDate"].value,-1)) && BirtComp.equalTo(row["STATUS"],"Shipped")) ||</div>
<div>(BirtComp.between(row["ORDERDATE"],BirtDateTime.addMonth(params["pDate"].value,-1),params["pDate"].value)) ||</div>
<div>BirtComp.between(row["ORDERDATE"],params["pDate"].value,BirtDateTime.addMonth(params["pDate"].value,2))</div>
<div> </div>
<div>Hope this helps,</div>
<div> </div>
<div>P.</div>

Bordihn

Hi pricher,
 
your filter expression solves the issue exactly, I will keep the seperation logic in mind.
 
Thanks a lot and happy holidays

amrutaraut99@gmail.com

Hi,
Could you please give me a suggestion on how to filter a data day wise?
 
Thanks in advance