Skip to Content
0

Creating Continuous Periods for a given range in Web Intelligence Report

Jan 17, 2017 at 04:11 PM

120

avatar image

Hello All,

I have a need to show all months even though there are no transactions available for every month. My inputs are Start Month and End Month (201601 - 201612). But sicne not transactions available for these period I display only a few columns (201601, 201604, 201609, 201612). But I would like to generate 12 different values like 201601...201612 and display values only where there is a match for the rest of the dummy months I will display 0. Is there any way to do this web intelligence cross tabs?

Thank you,

Arthur.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

AMIT KUMAR
Jan 17, 2017 at 04:36 PM
0

If you have some date object then you can use TIMDIM & MonthPeriod function to generate continuous dates.

https://blogs.sap.com/2014/12/03/demystifying-the-timedim-function/

other option is to add one more webi query and select Month object only and add the same date range prompt condition in this query.

Merge both the Month object and use in the crosstab.

Share
10 |10000 characters needed characters left characters exceeded
ARTHUR ALEXIS Jan 17, 2017 at 06:19 PM
0

Amit,

I already have the time dimension, which is merged with my model to generate the various dimensions of date/time. Yes I can create additional query and display only the month for the prompt and it will return 12 months. But if I merge with the transaction data-month (which I have only for 5 months) with Query2-Month (though it brings 12 months) after Merge will I not get just what is available in the transaction data? (I have tried it and this is what I get when the Merge dimension is used)... It is almost like an outer join result what I should use in my cross tab... (My transaction data is not there for some months, so for missing months I should have 0's or I should create them ins some other way..

Any other ideas?

Query1 Query 2 When Merge Dimension applied

201601 201601 201601

201604 201602 201604

201609 201603 201609

201612 201604 201612

201605

201606

201607

201608

201609

201610

201611

201612

Thank you,

Arthur.

Show 4 Share
10 |10000 characters needed characters left characters exceeded

in document summay check the check box extented merge dimension and use q1 dimension and measure of second query and see

0

after merge dimension if you drag merged dimension then you should get all months..

0

Amit,

When we merge dimensions and check the extended box it should work, but due to my limited time, I added the entry I have issues with another group where some other rows will definitely have all 12 months. SO my issue is resolved in a different way without going thru too much more on this.

Merge Dimension must be the way unless an outer join is done at the universe. I don't know how to choose yrs as the final answer. But I am closing this.

Thank you,

Arthur.

0

Correct Answer

0
ARTHUR ALEXIS Jan 17, 2017 at 08:01 PM
0

Amit,

Yes it may give the result I am looking for, Since I did not have enough time. I have changed (not technically) and added the entry I wanted with another group and got the result I am looking for. I believe what you suggested should work.

Share
10 |10000 characters needed characters left characters exceeded