How to display only last month's figures

[_jl_]

Hi,

In a reporting package I have a 'All Dates' -dimension under which there's object "Last 12 months" showing objects (at the moment) from '2012/May' to '2013/Apr' in that particular form.

The problem is, I want only the LAST month appear on the report (as of now it should be 2013/Mar). How can I filter to show only last months figures on the report. So it would be like 'last item in "Last 12 Months" - 1' or something like that. Didnt find a way. :(

Someone help, please?

Thanks in advance,
jl

[cognostechie]

You will need to ask the modeler to create another item called 'Last Month'. This would be the expression:

[Database Layer].[All Dates].[Calendar_Date]    >= _first_of_month (  _add_months (  current_date,-1)) and
[Database Layer].[All Dates].[Calendar_Date]    <= _last_of_month (  _add_months (  current_date,-1))

You can also do this in the report if the modeler does not want to add this in the package.

Create a filter in the report as:

[Date]    >= _first_of_month (  _add_months (  current_date,-1)) and
[Date]    <= _last_of_month (  _add_months (  current_date,-1))

[Date] is the data item that contains the Date.

[_jl_]

Filter doesn't seem to work. It's giving me an error:


Any suggestions??

Basically modeller is not an option here, if this is possible doing anyhow on the report side.

[blom0344]

The 'last 12 months' most likely will come from  the best practice 
'Dimensional Modeling for Time Period Analysis' 
document that implements model query subjects within the model to effectively select data from a certain range.
Not sure that a dimensional model accepts SQL-type filters , like the one on current_date.
I would try and add a 'previous month' solution within the model  in the same way the last 12 months is implemented..

[_jl_]

I understand that the best way would be to ask modeller to add "Last Month" object to the packet. But when that's basically not an option, is there another way to show only that month in the report with "Last 12 Months" -object?

[cognostechie]

I had no idea you were using a Dimensional package !

Yes, it can be done in the Report. You don't have to use the Last 12 Months at all. I just did it. Created a Crosstab with Products in Rows, Quantity as the measure and used a Query calculation in the column with this expression:

prevMember(closingPeriod([DMR].[Calendar].[Calendar].[Month]))

Look at the attached screenshot. The 3rd option at the bottom is the one I mentioned above. The 1st two options show 'Last Month' in the model and this also allows to drill down. The 'Last N Months' is also very helpful as you can select how many months you want without having to create more categories in the future.

[_jl_]

Thanks, guess I got it working now! 8)

One more question though... Now the Query Calculation object is showing on the report named "Last Month". Can I set somehow that it tells the actual month object's name it's showing. Like in this case "2013/Mar"?

Tried the way in attachment with no luck. Shows only "--" that way...

[cognostechie]

It does show the month's name '2013/03' in my screenshot. It should show the 2013/Mar if that is the value in your data.

[_jl_]

In your example yes, but not on my report... Did I do something wrong here?

Basically what I did was..
1. I took "All Dates - All Dates - Month" object into the query.
2. Made Query Calculation named 'Last Month' with that expression: prevMember(closingPeriod([Packet Name].[All Dates].[All Dates].[Month]))
3. Placed that 'Last Month' query calculation to be shown on the report.

It shows the correct data, but the topic is just "Last Month", not "2013/Mar"...

[_jl_]

Another problem came up concerning same kind of thing. Different package though.

I should be able to display only yesterday's data. I have only this date information in the package, see attachment. And there's also wrong date information in the model (dates like 12/12/2030). Bad information from the source.

Is there some way I can display only yesterday's data.
???