Scenario
5:
Problem:
Showing inventory for selected day, if not selected then show last
day inventory by month wise.
See
images below:
In
the above image showing inventory for 15th
of
every month. Because user entered day as 15. suppose if user does not
enter 15 and leave that as blank then it will show last day Inventory
for every month. See below
Note:
If you enter any thing apart from 1 to 31 then the report will become
blank.
Solution:
Step
1:
Create parameter “Day”
with data type as string. See blow image
Step
2:
Drag Month,
Day
columns to row shelf and right click on Day
parameter select show parameter control.
Step
3:
Create a calculated field as “Max
or selected day”
using below formula.
If
[Parameters].[Day]=''
then window_Max(max([Day]))
Else
int([Parameters].[Day])
End
Note:
In above formula ''
is not double quote it is two single quotes side by side with out
space in between.
Formula
working in this way, suppose parameter [Parameters].[Day]
is
empty means no value entered by user then it will find the max day of
each month other wise it will return user entered day value.
As
we created parameter as string data type so we are converting that
value to integer again in else statement.
Step
4:
Drag this newly created calculated field “Max
or selected day”
to text
marks shelf.
In
above image you can observe that for month of February it showing max
date as 31 which is wrong.
Because
we used window_max it's calculating max day across table by default.
Window_max is a table calculation.
Step
5:
Now right click on “Max
or selected day”
which is there in text marks shelf and go to Compute
using
then select pane(down).
Now
you will see max day for February as 28th.
Step
6:
Create one more calculate field as “Max
day filter”
using following formula.
If
attr(Day)
= [Max
or selected day]
then "True"
Else
"False"
END
Note:
[Max
or selected day] is
an aggregated column which is created in step 3. [Day]
is not an aggregated column. We cant mix aggregated and non
aggregated columns in calculation. So I applied attr
function for non aggregated [Day]
column.
Step
7:
Now drag [Max
or selected day]
column to filter
shelf
and select only “True”.
Step
8:
Remove column “Max
or selected day”
from marks text shelf and add “Stock
in Inventory”
column to marks text shelf.
Finally
the report looks like below. You test report with and without day
value in parameter.
Please
see the report below:
