Skip to Content
Author's profile photo Former Member

Webi Tricks: Displaying table data in Calendar format

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

/wp-content/uploads/2013/12/calendar_352899.png

Assigned Tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      In Step#1 you point out:

      Day in text: FormatDate([Future date];"Dddd")

      ..but you don't actually specify what this [Future Date] object or function is?


      NMG

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Oops, I'm sorry..its a typo. replace that [Future Date] object with [date] field object on which you're reporting.

      Thanks,

      Abhi

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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.

      Author's profile photo Stephen Pert
      Stephen Pert

      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? 

      calendar crosstab.jpg

      Author's profile photo Stephen Pert
      Stephen Pert

      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. 

      Calendar 1.jpg

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Marco Marsico
      Marco Marsico

      In Italy we use calendars starting on monday… 🙂

      Author's profile photo Emilio Perez de Tena Merino
      Emilio Perez de Tena Merino

      Hi,

      I had some problems with this report. Can you attach a biar file with the sample

      Thanks