Please let me know how to implement the below logic.
I need to have 2 listboxes as filter. First filter (a dropdown) has years 2019, 2020 and 2021 and second filter also a dropdown which has months in it (Jan to Dec). All these are static or harcoded values (using inline table) and not coming from any field association i.e. no date fields/columns are associated to these filters. The issue is when Iam selecting 2019 and then March, all the 3 years are selected for March and shown in the report, whereas I just need to show only March values for only 2019 in the report. How to achieve this?
Can you be specific with some example. This could help me solve this at the earliest. I cant post too much of data due to restrictions but the main report is a list report with some shipment data. Can you please help me understand the resolution which you are proposing.
Loading 2 fields from inline may work differently if :
those fields are connected to your data model or
are not linked to your data model (lets call it "island tables")
judging by what you have described 1st option is not in place and as general rule it is not great. It means that you have tables not associated with each other in your data model and it may have huge implications on the performance of your app if you load it with large data volume
assuming you are having Island tables in your model for Year and Month and you want to use those for your selections you need to have a fields you want to compare those values to in your fact table or rest of your mode. Normally you would have some sort of date field and what you want to do is to derive during load script from that date field columns for Month and Year. Normally you would do it by use of following functions:
Year(YourDateField) as Year_Key,
Month(YourDateField) as Month_Key,
Now when you have those 2 new fields created you can now start using them in your expressions like in charts like:
Now what I dont understand is what you are saying here:
...The issue is when I am selecting 2019 and then March, all the 3 years are selected for March and shown in the report, whereas I just need to show only March values for only 2019 in the report..
Based on the fact that you have no associations between your model and "filter year and month" fields when selecting Year=2019 and Month=March your report in your current state should show unfiltered data because as you are saying there is no association. If selection is already filtering data it means that your description above is not giving us full information.
In the expressions I mentioned above I create the associations in them by using set analysis and P (possible) function. There are also other ways of handling it, but this one is good to show you how it works. So the concept is that by having Year_Key and Month_Key fields created and associated in your model now you can pass on them selections using set analysis and P function.
If you need more detailed explanation I suggest you attach a sample and provide more detail description of how it needs to work, limitations you may have and reasons why you have chosen to go with the island table approach.
cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Hi Thanks for your explanation. But I have a bit different requirement here.
When I select the year, the shipment comments gets changed as shown in the snapshot (no filterations happen as its giving a perspective of the shipment sail info and shipment comments is derived with an if construct using start date, end date and the year filter that's passed as a parameter to load script. ). But now when I click on any of the months from the month filter, ideally the result should be shown for that specific year's month in selection but clicking on month shows other years also for that month in the report. Here the outstanding issue is to get results only for that year's month in final output.