Hello,
Recently, I came across interesting blog in crystal reports space from Jamie where he designed a report to display data in calendar format. So I took this same idea and wanted to reproduce this in Webi. So, lets start on how to do this in webi.
Step # 1: You need have date field from your DB and create a four variables Viz.. Day, Month, Week.Day in text
Day : FormatDate([date];”d”) (Create as measure)
Month: Month([date])
Week: Week([date])
Day in text: FormatDate([date];”Dddd”)
Step 2: Lets drop a cross tab and assign the above four variables.
Horizontal axis: Day in Text
Vertical axis: Month and Week
Body axis: Day
Step # 3: Just do some sorting on Month,week and day in text as needed and you will see now that crosstab looks like a calendar
In Step#1 you point out:
..but you don’t actually specify what this [Future Date] object or function is?
NMG
Oops, I’m sorry..its a typo. replace that [Future Date] object with [date] field object on which you’re reporting.
Thanks,
Abhi
Hi,
I am not able to see the Month & Year in the given calender, So how can we justify that it belongs to which year and of which month calender??
Thanks & Regards,
Mahesh.
You can add Month to vertical axis!
I have captured Month on vertical axis and hidden it. You can do multiple things like create input controls or place holders to display each Month. I just did a simple example.
When I try this the crosstab is not displaying a week in the same row. It created a separate row for each individual date. I have tried all sorts of things to fix, such as hiding different dimensions, the ‘Avoid duplicate row aggregation,’ etc.
Nothing solves the issue. Any ideas?
Well, I posted the previous message too soon. I have it working now. The trick was to not include a hidden dimension on the original date object. I removed that and it then displayed correctly.
Thanks for this post.
I was having problems trying to do this example. The problem was that I had results for more than one year. Once I included the year in the table it displayed correctly.
I don’t see any calendars that begin with Monday. Every calendar I have starts with Sunday. Therefore, in my example, November 6th would be on the second week of November, not the first, and not before Nov 1st.
In Italy we use calendars starting on monday… 🙂