Some time back I posted a series of articles describing how to create “dynamic date” objects based off of the current system date. For examples I used the Oracle database. This post simply contains a collection of links that I have found to references for date/time functions for other databases. I hope it will be useful.
Related Links
External links are provided without endorsement and may become inactive at any time. These links were functioning properly at the time this blog post was published but dagira.com has no guarantee that they will continue to be available.
Last Time On This Subject…
This is the fourth in my series about dynamic date objects. The first three included objects for daily, weekly, and then monthly ranges. I am going to complete my collection of Oracle-based dynamic date objects by providing yearly objects in this post.
Continue reading “Dynamic Dates Part IV: Yearly Date Ranges”
Last Time On This Subject…
In the first post in this series I introduced the idea of dynamic date objects. The intent was to show how a universe designer can create dynamic date objects that can be used to schedule reports with a condition that changes over time. The first post covered the basic concepts and included “Today” and “Yesterday” dynamic date objects. The next post covered weekly ranges. In this post I will further extend the concept to monthly ranges. As with the other posts so far in this series I will be using Oracle functions.
Continue reading “Dynamic Dates Part III: Monthly Date Ranges”
Last Time On This Subject…
In the first post in this series I set up both regular objects and predefined condition objects that were based on the Oracle sysdate pseudo-column. These objects were designed to be used for scheduling reports with date conditions while allowing the date range to move forward in time for each new execution of the scheduled report. For that article I only created objects for Today and Yesterday. Today 😉 I will provide some weekly date ranges with the help of a few Oracle functions.
Continue reading “Dynamic Dates Part II: Weekly Date Ranges”
Hitting a Moving Target
Does anybody really know what time it is?
Does anybody really care?
If so I can’t imagine why…
With apologies to the rock group Chicago, but I could not help starting out this post with a quote from one of their most famous hit records. Yeah, I have records. Deal with it. 😛
I am going to start out this post with a very obvious statement. There are two ways to get reports: Interactive and Scheduled. Interactive reports can have prompts that allow me to specify which values I want to see, and many times those prompts will include dates or date ranges. Since they are interactive I can change the dates each time I run the report.
But what about scheduled reports? I would hardly want to schedule a report that always ran for August 22, 2007, right? I want that date parameter value to change. Maybe I want the report to always run for “yesterday” or “last week” or “the second Tuesday of the month” or anything along those lines. The challenge is, of course, that the value for “yesterday” changes each day I run the report. How do I hit that moving target?
Continue reading “Dynamic Dates Part I: Yesterday and Today”
I think it’s probably a safe bet to suggest that just about every data warehouse (or even transactional system) has some sort of calendar table. In many cases, the unique key for this table might be the natural key of the date itself, or perhaps it’s a system-generated surrogate key. That doesn’t really matter for this post. What I want to do is show one idea of how I used an OLAP aggregate function called row_number() to extend my calendar functionality, and make it really easy to schedule reports for the “last three months” given an input date. Continue reading “Using OLAP Functions to Extend Calendar Capabilities”
One of the frequent requests that I see goes something like this:
I have a hierarchy set up. When I drill to the bottom of the hierarchy, I want extra detail objects to show up.
This seems like it should be the default behavior, right? If I take the time as a universe designer to properly classify objects as dimensions or details, and also take the time to set up hierarchies, then it would seem that reports would recognize and utilize that information.
Unfortunately they don’t. That means I have to use some report functions and set up some variables and do some creative formatting to make it work the way I think it should. I first showed how to do this with Desktop Intelligence way back in 2000 at the Business Objects conference in Washington D.C. Today I will update the technique and show how it can be done in Web Intelligence. Continue reading “Drilling to Details”
One of the possible enhancements we have been requesting for years is the ability to validate prompts. (We’ve also been looking for the ever-so-popular ability to use a formula such as “Today()” as a default for a prompt but this is different.) If we had true cascading prompts in Web Intelligence that would eliminate one use case for validated prompts but not all. I had someone comment on my blog recently asking about how to validate one prompt selection against another and that started me thinking… what would something like this look like if we did get it? Continue reading “Why Can’t I Validate Prompts?”
In the first post in this series I defined what time-sliced measures are and why they can be useful in a universe. I also shared the design requirements for a particular project I was on. The requirements included:
- Each report is expected to have multiple time-sliced measures
- Users must be able to select the type of calendar during the refresh process; calendar types include Monthly and Fiscal
- Users can provide any possible date as the “to date” for the time slices
- The process of splitting each time-slice time period into its own SQL statement should be completely transparent
- The resulting SQL should be as efficient as possible
In this post I am going to cover the design of a special table that we built in order to support our solution for these requirements. After I talk about the table design I will cover how I use it in the universe, as well as provide a few pros and cons about this solution as I have outlined it so far. Just to prepare you, this post is a bit longer than most that I write, and gets fairly detailed. This post will cover slides 23 through 25 from my 2008 GBN Conference presentation. There is a link to download the file at the end of this post. Continue reading “Time-Sliced Measures Part II: Time Slice Calendar Table”
In the first post on designing universe prompts I talked about the idea of having a default date value present in a prompt. In case you don’t want to read the entire post, here is the executive summary:
You can’t do it, sorry.
At the end of that post I mentioned something called a “magic date” that I frequently use in my universes to get past this limitation. That’s my subject for today.
Problem Definition
I have a date prompt in my universe. I would like to be able to provide a default value of today’s date. This would allow my users to click-n-go on the report without having to update the date value each time. It is also essential for scheduled reports, which is something I didn’t really touch on last time. When I schedule a report, I have to provide values for every prompt. I have a long series of posts on how to create some dynamic date objects that can be used for scheduling, but those were not prompts.
In a nutshell: I need a way to accept a prompt value and convert it to today’s date for user convenience and for scheduling. Can I make that happen?
Continue reading “Using a “Magic Date” Value in Prompts”