Rolling Last 12 Months in Report Studio.

[gosoccer]

Hi, :) :)
I'm trying to get my Report Query based on getting the data for the last 12 months from the Year and Month the user has selected. The user only has Year and Month to select and the Fact table Rows are based on only Year and Month. I have to show the Query for the last 12 months from the Year and Month the user selects.

I have the filter for the year as whatever the user enters.
[YEAR] <= (?User_Entered_Year?)

But for the month I have the following code for the Queries for getting the month in my filter.

_months_between(?User_Entered_Month?) <= 12
But it's giving me an errror,
QE-DEF-0260 Parsing error.

Is there a different way to do this?

Thanks a lot for your time

[bus_pass_man]

_months_between takes two parameters.

https://www.ibm.com/support/knowledgecenter/SSEP7J_10.1.1/com.ibm.swg.ba.cognos.ug_md.10.1.1.doc/c_btf_months_between.html

I'm assuming that your prompt is just a dev placeholder for something that would return a date format value.

Also, are you quite certain that the entire error message was 'QE-DEF-0260 Parsing error' and nothing else?

[gosoccer]

Yeah! there is more to the error. Ok, I'll take a look at the link. Thanks a lot.

The Prompt asks for 4 digit Year 2017, and MMM as i.e. August. That is what's returned to the Query in the Report Studio.
So, I have have the Query done in a way that goes after the last 12 months from 2017 August.

Thanks so much!

[gosoccer]

I only have the Year and Month as two different Prompt Page selections. No days involved. The Data in the Fact table is based on Year, and Month only. Two different columns for each Row. year: 2017 and month:August for example.

This is going to be very tricky I think..

Thanks for your time. :) :)

[hespora]

1. If you only have month as text, build a numeric month via search case. Do this for both your database items and your parameter set.

2. for both, build yearmonth as [year]*100 + [numeric month]

3. if you want the rolling 12 *including* the month selected:

Code Select Expand

[yearmonth] > [p_yearmonth]-100 AND [yearmonth] <= [p_yearmonth]

if you want the rolling 12 just before the month selected, then

Code Select Expand

[yearmonth] >= [p_yearmonth]-100 AND [yearmonth] < [p_yearmonth]

[gosoccer]

Excellent. I'll try this today as soon as I get done with the client. Thx a lot

[gosoccer]

My apology for the DUMN question but what does the 100 do and how do we know it's going back one year or 12 month?
Thanks so much

[hespora]

as in the definition of [yearmonth], we're multiplying year by 100, then subtracting 100 reduces the year by 1.

Just humor me and build the yearmonth item in a plain query. I believe you'll see. ;)

[gosoccer]

Yes, this approach works great. I build another query to deal with the numeric month. Added the logic to the Query and works perfectly.

Thanks a lot!! 8) 8)

[hespora]

Wonderful, glad I could help! :)

[gosoccer]

Well, :) Sorry to bother again!!

Stuck in one more thing with this Dag On Report.

They want to see every month even if the data doesn't exist in our Fact Table. Presently shows
the chart based on the attachment but some Months data doesn't show.

I'm trying to Insert a Month in the Framework Manager if the month doesn't exist but no luck so
far.

Unbelievable!! 8)

Thanks for your time.

[hespora]

Well, I'm supposing here that November 16 is not returned because there is no Fact against it; not because it simply isn't there.

What you do is, you run one query *only* against your month field, filtered for the reporting period. That one should hopefully return all months including november. You then outer join this query against your actual data query.

[gosoccer]

WHOOM! very interesting. Yeah! I can perhaps do that in Framework Manager. Outerjoin from Fact Table to the Date Dimension for Month and Year (Since I don't have a Full Date)
Thanks for your time again!

[Invisi]

Consider that if you make the relation between your fact and your date dimension outer join in your framework, it will use this for every situation. Your use case is, I assume, an exception. I've had the same recently with a client who wanted the same on some charts. I also used the outer join in the report as suggested by hespora instead of solving it in the framework, just because I wanted it to be an exception.

[gosoccer]

Excellent Point. Yes, that actually works well within the Report rather than FM. Our ETL guy did something to insert the months with (0,0,0,0) so I don't have to use one extra join at Report Level due to performance issues.

Thanks so much!

[Invisi]

You mean you have a special row in your date dimension?

[gosoccer]

Actually in the FACT Table, he inserted a role through the Stored Procedure we use for our ETL over here. So, when there is no months identified, he is inserting a row for that month.

[Invisi]

What is the purpose for that? The solution provided should work fine without any additional fact rows.