Skip to main content

Month-to-date, Year-to-date metric selection and Date filters

One of my clients came up with a request - We need to create a report that prompts the user for a date and a second prompt that prompts whether the user would like to see MTD or YTD metric based on the answer to the date prompt.

Really simple solution! If you haven't gotten in to this mode of thinking then do so now - Divide and Conquer.

There are two separate requests here:

  1. Date Prompt
  2. Object Prompt

Create a report (MicroStrategy Tutorial) with Year, Month and Day in the template. Define a report filter - Day. Easy Peasy!

This step assumes you have already created n metrics that you would like to offer in the prompt selection - in this example Revenue, Revenue - MTD and Revenue - YTD. Next, create a new prompt using Prompt Wizard. Make it an Object Prompt and offer the above mentioned three metrics as objects available for selection. Save and close the prompt wizard.

Simply drag and drop the prompt to report template area (not report filter), save and execute.

An easy solution to complex sounding request.

Labels:

Comments

Unknown said…
This is very nice article thank you for share this information i like it very much keep sharing this information.if you want more information to visit here it will give us the more than one useful information.
July 18, 2016 at 1:19 AM
Anonymous said…
Thank you very much for sharing. It does sound easy peasy. Writing service findwritingservice.com is very professional. If you need help, you may apply for it online and you will receive it in a blink of an eye.
February 17, 2017 at 4:26 AM
Anonymous said…
I have not been in this mode of thinking but from now on I certainly will. Thank a lot for sharing. Custom writing service star-writers.com is what you need in case you require help with your essay or any other type of work. There is no longer any need to be afraid of home assignments. They will not bite.
February 20, 2017 at 2:23 AM
Unknown said…
This comment has been removed by the author.
December 5, 2017 at 4:52 AM
Unknown said…
Very useful information. Easy peasy... just like you said. If you need any help in the future you should try elite custom writers. They really know how to do their job!
December 5, 2017 at 5:00 AM
Amanda Snow said…
Briefly and understandably) It is visible when a professional is working in his case and this article is an example of that! Custom writing service will teach to distinguish high-quality.
December 7, 2017 at 2:12 AM
Deborah Queen said…
Thanks for explaining date filters, it helped me to write reviews on Top essay writing as I was struggling previously with this topic.
January 3, 2018 at 2:53 AM
Post a Comment

The Popular Ones

Using SQL To Calculate XIRR (Internal Rate of Return)

Thanks to binaryworld.net , I was finally able to get a sql way to calculate XIRR. After 2 long hours of search I found this site and the logic as well as the code works perfectly well! XIRR is a function in excel that calculates Internal Rate of Return based on payments/income over a period of time. Without further ado, here is the code (a slightly modified version from BinaryWorld.net. Happy XIRRing! -- First, CREATE XIRR Table to store values CREATE TABLE XIRRTempData( amt float, dt datetime, guid varchar(128) ) go create function dbo.XIRR( @d datetime, @GUID varchar(128) ) returns decimal(18,10) as begin /* USAGE: select @IRR = dbo.xirr(null, guid) select @IRR IRR, @IRR * 100 'IRR %' Note: Leave the first parameter (date) null if you wish to see the XIRR calculated as of the maximum date in the dataset provided else provide a specific date to see the XIRR calculated as the given date. Created By: Ankeet Shah Created On: 7/16/2008 */ IF @d is null SELECT @d = max(d) from Inc...
25 comments
Read more

Alternating Row Background Color For SSRS Matrix (Pivot Table)

I had a tough time to apply alternate row colors to a SSRS Matrix and finally figured out! Without further ado, here it is... Assume you have a matrix with more than 1, lets say 2 row groupings; RG1 and RG2. 1. Right-click on RG2 (innermost row group), and select "Insert Group"; for simplicity call it "RowColorGroup" 2. In the "Group On" section, add some constant value - for example ="" or ="ankeet" etc... you get the idea! 3. Select the newly created group "RowColorGroup" and enter the following in its "Value" property: =iif(RunningValue(Fields!RG1.Value & Fields!RG2.Value,CountDistinct,Nothing) Mod 2, "LightSteelBlue", "White") 4. Select the "BackgroundColor" property of "RowColorGroup" and enter "=Value" 5. Set the width of "RowColorGroup" to 0pt and "CanGrow" to false 6. Select the data cell(s) and set their "BackgroundColor" pro...
5 comments
Read more

cannot create a column accessor for OLE DB provider "ibmdasql" for linked server

I have a linked server from Microsoft SQL 2008 to a DB2 server. Today when I tried to run a SELECT statement based on the linked server, I hit this error, "cannot create a column accessor for OLE DB provider "ibmdasql" for linked server". Earlier in the day, we had restarted the SQL Server Service. Running the following script on the 'affected' sql server should fix the issue. USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[usp_enum_oledb_providers] AS exec sp_enum_oledb_providers GO sp_procoption 'usp_enum_oledb_providers', 'startup', 1 Restart the sql server service after running above script.
Post a Comment
Read more