Send Populated Date with airSlate SignNow

Send populated date

hi this is Alan Williams of Sunset Hill solutions in this video I'll be walking you through a demonstration of how to create dynamic default date prompts for report scheduling and Business Objects for so here's the scenario we have a web intelligence report this report is a production report that should be scheduled to run in the first date every month and when it runs on the first day of the month we want it to be populated with your month prompt from the last month so for example if we run this report on January 1st 2013 we want that prompt to be have the data - oh one two one two so the Year month of the last day of the previous month we also want to have the ability for users to run this report on an ad-hoc basis and select the Year month that they want to run the report for so in order to illustrate how this works I've created a database on a sequel server 2008 instance and I've call it dynamic prompts it only has three tables it has a product dimension table a date dimension table and the sales fact table I've added a few rows for December 2012 and a few rows for January 2013 to illustrate how the functionality works for this demo before we go any further I should mention that I've created a backup file of this database and there will be a link provided underneath the video here on YouTube where you can download this database if you want to try this demo for yourself now in order for this dynamic default a prompt to work I have to use some of the date functions available in sequel server I'm sure that Oracle and db2 and other database platforms have similar type of date functions what what this sequel does here is it uses the date part function the date add and they get date function within sequel server to return the month and year for the previous months of execute the sequel you'll see right now the date is January 12 2013 but it's returning 2012 12 so that's what I want and we will be using this sequel as part of a filter that we build in information design tool so that information design tool opened and we're going to start building the universe it's going to be the basis for this demo so I'm going to go ahead and add a new project and calling this project dynamic prompt demo finish and I'm going to add a connection I've already created a connection to the sequel server database called dynamic prompts so I want to right click on the connection down here and create relational connection shortcut and add it to the dynamic prompts demo project okay now we have the connection there so now we'll go ahead and add the data foundation and new data foundation and we're going to call this d/f dynamic prompt demo I use the DF prefix because the data foundation file click on next this will be a single source data foundation and I'll be using the connection to the data then M prompts database finish so now we can add the three tables that are part of this database so we have the date dimension product dimension and the sales fact table finish I'll just rearranged them here put the sales table in the middle and create the relationships here detect the cardinality and I will add the relationship for the product ID and tech cardinality okay so now we've got three tables from our database in our data foundation I'm going to save the data foundation now I will create a new business layer and it's a relational data foundation now I'll call this BL dynamic prompt demo click on next select the data foundation in this project ok and we will have it automatically create the folders and objects and I'll just do a little bit housekeeping after it's done that alright so now we have our three classes which are the three tables in database we don't need the primary key and keys in these tables I'm just going to remove those and the product table the same thing we don't need the Product ID it's going to change the product name description there we go and for the fact sales table we don't need these IDs in the universe so I will delete them now I want to turn the sales amount into a measure so I'm going to turn them into measure with aggregate function and some okay okay I'm just going to do a little bit more housekeeping here I'm going to just going to rename some of these classes so I'm just going to call this class date and this class product so now we want to add a filter to this date class so I go up here this plus sign click the down arrow and select filter I'm going to call this dynamic default date prompt and I click on the sequel assistant and I'm just going to paste in the code then I'm going to explain what it does let me just validate it everything's good alright the expression is valid so let me explain how this filter works it says that if the year month filled in a dim date table if the prompt that we've defined here is filled in with the text yyy mmm then we're going to run that sequel that I displayed earlier that calculates for the previous month the month and the year if that prompt value is different than yyy mm then whatever is in that prompt is what's going to be used for the report when it's run so this will allow for ad hoc reports to be run or the report to be run at any time with values that the user select so as mentioned earlier I'm going to be supplying a backup of the database and also I'm going to be including this business objects for project in a zip file that you can download and I'll have a link at the bottom of just underneath the video here on YouTube where you can download this information from so at this point I want to save the project I want to right click on the business layer and before I publish it I just want to change the name of this universe to dynamic prompt demo with some spaces in there save it again I'm going to publish this to a repository click on next and finish alright the universe was published publish successfully so now I've opened up the eye launchpad I'm going to go into applications web intelligence application so now I have web intelligence open in a new tab I want to click on new and I must select the universe I'm going to select that universe that we just published which was called dynamic prompt demo select so let's select the objects that we want for our report we want to have the product name the year month and the sales amount I'm going to run this first without using our dynamic default they prompt just to show the data that's in that those tables going to run the query so just take a minute and there we go is expand these columns little bit okay there are only four rows in that sales fact table we have two records for widget one and two records for widget two and for each of those two products we have one record for December 2012 and one for January 2013 so this is the raw data before we add that dynamic default date prompt okay so let's edit this report now click on the data access tab and we'll click on edit for the edit to the data provider I'm going to add this filter we created and I'm going to run the query again so what I'm going to do now is I'm going to select January 2013 so I expect because I've selected a value and filled in this prompt I expect to see results from ghin January 2013 and that is the case we only have two rows returned now if i refresh this query again click the Refresh icon here I'm going to leave the default value for the prompt because I'm leaving this as why why why why mmm I expect to see results for December 2012 let's see if that's the case indeed we only have results for December 2012 because the prompt was not changed okay I'm going to save this report to a report scheduling folder that I've created for scheduled reports I'm gonna call it dynamic prompt sample unsaved I'm just going to close the report here open it up in the scheduled reports folder or have a look at it here and now I'm going to do is I'm going to schedule the report schedule an instance of this report so I click on the report select schedule so in this case I'll leave the defaults for the instance title and everything else and in the prompts I'm going to leave the default which is YY YY mmm all right I don't want to change this I'm going to schedule the report to run now and we can see that the report is now we're going to be running right away and we see that the report has run successfully I'm going to close a history tab and just have a look here to view the latest instance of the report so this is a report that we schedule with the default prompt and we are getting results for December 2012 because we were on this report in January 2013 so the way you'd put this scheduled report into production so it runs every month and right click on it click on schedule and in the recurrence area we would select monthly want to run every one month and we said we want to have it run at 6 a.m. on February 1st as an ex run date and the end time only the default because it's 10 years in advance from now so this is good and I click on schedule all right so now we've set up a recurring instance of this report to run on the first at every month it's always going to return data as of the previous month so in my next video I'm going to expand a bit more on scheduling and show how you can use events in order to run reports under certain circumstances for example if you have an ETL process that populates a database and it usually finishes around 4:00 or 5:00 in the morning you can create an event that will only trigger the report to run after the ETL process has been completed so I'll be covered in the next video so this is al Williams from sunset health solutions I hope you found this video interesting and informative and I hope it can help you out