Between Prompts for Date Columns

Say you need a report containing Start Date, Sales and Number of Days where Number of Days is the number of days between the values chosen in the dashboard prompt, to achieve this using Presentation Variables. This is a variation of the approach that we saw earlier here. Lets start with creating a dashboard prompt. This dashboard prompt shall contain 2 prompts on any column. For both the columns, in the formula tab enter the below formulas




CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test’ END



CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test1′ END



We have used the above formula in order to protect the columns from getting the filters. This is very important for setting presentation variables and at the same time not affecting any columns

Now, for both the prompts convert the Results to SQL Results and enter the below SQL



SELECT DISTINCT TIMES.CALENDAR_MONTH_START_DATE FROM SH2



In the above case, TIMES.CALENDAR.MONTH_START_DATE is my date column. Make both the above prompts to set 2 presentation variables Date1 and Date2.

 
Save this prompt. Now go to Answers and start creating a report containing 2 columns, TIMES.CALENDAR_MONTH_START_DATE and SALES1.SALES. Now create another column with the following formula




TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMP ‘@{Date2}{1900-01-01 12:00:00}’, TIMESTAMP ‘@{Date1}{1900-01-01 12:00:00}’)



So, what this does is, it calculates the time difference between the 2 dates which would be selected in the dashboard prompt.



 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Add 2 filters to TIMES.CALENDAR.MONTH_START_DATE (one with less than or equal to and the other with greater than or equal to). In both the filters refer the Date1 and Date2 respectively.




CALENDAR_MONTH_START_DATE is less than or equal to @{Date1}{1/1/1900 12:00:00 AM}



CALENDAR_MONTH_START_DATE is greater than or equal to @{Date2}{1/1/1900 12:00:00 AM}



Now, include both the Dashboard Prompt and the Answers request in the dashboard page. Now, you should see that as you change the 2 dates, the Number of Days in the report would change and also the filter would be applied accordingly.
 

Popular posts from this blog

Image
If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod environments. But the major problem with UDML is that one cannot update the passwords of Connection Pools, Users etc since the UDML expects them to be encoded. In such a case, for updating passwords alone we can use an undocumented command line switch option for the AdminTool. For example, look at the screenshot below. If you had gone through my blog entry here, i would have talked about using UDML as a way for automating migration from dev to test/prod environments. But the major problem with UDML is that one cannot update the passwords of Connection Pools, Users etc since the UDML expects them to be encoded. In such a case, for updating passwords alone we can use an undocumented command line switch option for the AdminTool. For example, look at the screenshot below. Open Dev_Machine.rpd Administrator Administrator SetProperty "Connecti...
Read more

How To Unlock Bootloaders on your Xperia Arc S

UPDATE : Before you proceed, please visit  this link  to check if your phone’s boot loader can be unlocked or not? NOTE:  If the light above the USB port on your phone doesn’t turn blue when you connect your phone using the USB cable to your PC while holding the “MENU” button (on Xperia Arc. For Xperia Play – “SEARCH” button), then it means that you cannot connect to fastboot, and hence you cannot unlock the boot loader. Here’s a Step by Step guide (w/ video) on how to unlock bootloaders on your Xperia Arc, Play, Neo & Pro. [info]Although this guide is made using Xperia arc, I can assure that it would work perfectly with Xperia Play, Neo & Pro too. NOTE : Xperia Play users should press the “ Search ” button instead of “ Menu ” button in Step 8th of this tutorial.[/info] [warning]The methods and procedures discussed here are considered risky and you should not attempt if you don’t know completely what it is. If any damage occurs to you or your device, w...
Read more

Inherited Colour Vision Deficiency

Image
Red/green colour blindness is a common hereditary condition which means it is usually passed down from your parents. Colour blindness is usually passed from mother to son on the 23rd chromosome, which is the sex chromosome. Chromosomes are structures which contain genes – these contain the instructions for the development of cells, tissues and organs. If you are colour blind it means the instructions for the development of your cone cells are wrong and the cone cells might be missing, or less sensitive to light or it may be that the pathway from your cone cells to your brain has not developed correctly. The X chromosome is the sex chromosome: males have an X chromosome and a Y chromosome and females have two X chromosomes. For a male to be colour blind the faulty colour blindness gene only has to appear on his X chromosome. For a female to be colour blind it must be present on both of her X chromosomes. This is why red/green colour blindness is far more common in men than women. Bl...
Read more