Calculations based on filter

Larry Beasley shared this question 3 years ago
Answered

So my topic title might not truly describe what I'm looking to do here but maybe my description will make sense. I want a user to be able to enter an "As of Date" and I want to calculate datediff based on that "As of Date" filter value. If I create the SQL in the report as opposed to a view I can do this by declaring a variable:

DECLARE @as_of_date DATE = {?}

Select

CASE WHEN datediff(m, service_date, @as_of_date) <=30 THEN balance else 0 end [0-30 Days],

CASE WHEN datediff(m, service_date, @as_of_date) between 31 and 60 THEN balance else 0 end [31-60 Days]

from tableA

where service_date <= @as_of_date


I have had a bad experience working with custom queries built inside the report. My report requestors often like to add and remove fields after the report is presented and that seems to always wipe away my charts and formats. Can the above be done without the custom SQL? Or is this something not supported in my version of yellowfinbi. Is there a variable I could use to create a similar calculated field? I would very much prefer to use a view. Thanks in advanced.

Comments (7)

photo
1

Hi Larry,


This seems like something that would be easiest to explain over a quick call. Would you be interested in scheduling a time to screen-share today? If so, please let me know what works best for you (I am on MST)


Regards,

Nathan

photo
1

how about some time tomorrow. I'm on EDT and I'm here from 8:30-5pm. Or you can call before 5pm today. Still have my number?

photo
1

Hi Larry,


Does 10:45 EST tomorrow work for you? Typically we will use ring central, so I can send an invitation to your email now. If this doesnt work let me know and I can switch the schedule time.


Regards,

Nathan

photo
1

Sounds good. Thank you!

From: Yellowfin Support [mailto:support@yellowfin.bi]

Sent: Thursday, May 18, 2017 3:42 PM

To: Larry Beasley

Subject: New Comment in "Calculations based on filter"

photo
1

Hi Larry,


No problem! I just realized the link I sent you was for 10:45 MST. If you just want to join the call at 10:45 EST the link should still be valid. (I will send another if not)


Regards,

Nathan

photo
2

Hi Larry,


Sorry for the delay here, I got sucked into another case. Here is the wiki for parameters:


http://wiki.yellowfin.com.au/display/USER72/


Regarding the hard coded date value. If you set the default to not be a dyanmic date, you are allowed to type values, and are provided a date picker:


446cb508426dd4c6bbc7313f6bad951f


I am going to close this off for now, but if you run into trouble, let me know!


Regards,

Nathan

photo
1

Nathan,


The parameter worked like a charm for a moment. I mean, everything was GREAT then it came time to introduce a drill through and I cannot pass the value from the parameter to the related report. SO CLOSE! I even was able to create some pretty interesting reports where the report user could group by whatever they wanted. I got the parameter to help calculate the 'As of date' and all. I'm handicapped being on 7.1 and not 7.3. Unfortunately, we have no control over how our vendor updates the BI tool. Can you twist some arms and get them to update??? :)