Calculate ratios

Hi Dani,

well, I hadn't previously tried the ratio advanced function, so I gave it a go and I must say that I agree with you, it didn't really do what I thought it would!

However, I'm sure we can come up with something for you. Today is running out so I'll have a crack at it tomorrow and let you know what happens.

By the way, could you please confirm that your requirement is that the ratio should be expressed as two natural numbers and not a decimal. and that the two natural numbers must be divided by their lowest common denominator.

In other words, using your example figures of 15 to 100, an answer of 0.15 is not what you want, and neither is 15:100, it has to be written as 3:20

regards,

David

Thanks David. Yes, the final figure being 3:20 is the calculation I am after!

I look forward to hearing from you soon.

Cheers,

Dani

Hi Dani,

I've found a way, but it involves creating a function in your data source. Are you allowed to do that?

regards,

David

Hello again Dani,

just in case you're allowed to create a function in your data source, I'll show you the steps now:

(by the way I'm assuming your data source is MySQL, because in previous cases Eagle BI has always used that db)

1. Run the following code in your data source

CREATE FUNCTION gcd(x int, y int) RETURNS int DETERMINISTIC

BEGIN

DECLARE dividend int;

DECLARE divisor int;

DECLARE remainder int;

SET dividend := GREATEST(x, y);

SET remainder := LEAST(x, y);

WHILE remainder != 0 DO

SET divisor = remainder;

SET remainder = MOD(dividend, divisor);

SET dividend = divisor;

END WHILE;

RETURN divisor;

END

2. In your report (or view) create a Freehand SQL calculated field called "new_col_1" with the following code:

CAST(((column1/GCD(column1, column2))DIV 1) as char(10))

and another one called "new_col_2" with this code

CAST(((column2/GCD(column1, column2))DIV 1) as char(10))

and a simple calculated field containing just one colon, I guess a good name for it might be "Colon"

3. Apply the Advanced Function called "Concatenation" to Colon, choose "new_col_2" to append to it. Rename it "colon_plus_col_2"

4. Apply the Advanced Function called "Concatenation" to "new_col_1", choose "colon_plus_col_2" to append to it. Rename it "Ratio"

And then your report should look like this!

If you run into any difficulties just let me know and I'll be glad to help out via a screenshare session.

Good luck!

regards,

David

Hi Dani,

silly me....a much easier way is to ignore those previous steps 2, 3, 4 and just do the following new step 2:

create a Freehand SQL calculated field called Ratio by using the following SQL code:

CONCAT( CAST(((c3/GCD(c2, c3))DIV 1) as char(10)), ' : ', CAST(((c2/GCD(c2, c3))DIV 1) as char(10)) )

regards,

David

Thanks David; would this need to be created in each client organisations database?

Cheers,

Dani

Hi Dani,

it depends, if all your client orgs are using the same view created in the default org then no, you could create the Freehand calculated field just once in that view to be used in the different client orgs' reports.

Please let me know how it goes.

regards,

David

Hi David,

We have tried running this on the database which is hooked up to default but we are getting this error:

Any ideas?

Cheers,

Dani

Hi Dani,

is it a MySQL database?

regards,

David

It is SQL Server

Hi Dani,

that's why it's not working! It's written using the MySQL syntax for functions (the rules are different for different databases)

I'll try and find a version for SQL Server, or I'll rewrite the MySQL one for SQL Server.

regards,

David

Ah sorry!

Thank you, David! :)

here you go Dani, this will work in SQL Server:

CREATE FUNCTION dbo.GCD
(
            @a INT,
            @b INT
)
RETURNS INT
AS
BEGIN
            DECLARE     @c INT
 
            IF @a IS NULL OR @b IS NULL OR (@a = 0 AND @b = 0)
                         RETURN NULL
 
            IF @a = 0 OR @b = 0
                         RETURN ABS(@a) + ABS(@b)
 
            IF ABS(@a) < ABS(@b)
                         SELECT      @c = ABS(@a),
                                     @a = ABS(@b),
                                     @b = @c
            ELSE
                         SELECT      @a = ABS(@a),
                                     @b = ABS(@b)
 
            SET         @c = @a % @b
 
            WHILE @c > 0
                         SELECT      @a = @b,
                                     @b = @c,
                                     @c = @a % @b
 
            RETURN      @b
END

and then in Yellowfin create the following Freehand SQL Calculated Field in either a view or a report:

CONCAT( CAST(((column1/dbo.GCD(column1, column2))) as varchar(10)), ' : ', CAST(((column2/dbo.GCD(column1, column2))) as varchar(10)) )

be sure to substitute the names of the columns in your ratio for the above ones called "column1" and "column2".

And then Bob's your uncle!

If you run into any problems be sure to let me know.

Good luck!

David

Hi David,

Thanks for your help so far! We've gotten closer but can't quite get it to work.

Here is the error we get when we try to add the freehand SQL calculated field:

However, when I run the statement directly on the database with the freehand SQL part added to the statement it works correctly:

And we are given the following result:

Here is how the report looks so far if it's any help:

Thanks again for your help!

Cheers

Mike

Hi Mike,

the database can't find the column called "IP_ID" in the table called "InjuryInfoVT".

Seeing as your original query runs without error, I'm guessing that maybe that "InjuryInfoVT" is not the actual table name, it is just a table alias.

So, please check if that table is actually called "InjuryInfoVT" and let me know.

And if that's not the issue, then please send across the Yellowfin View SQL for the view that your report is based on.

regards,

David

Hi Dave,

Thanks for the response - InjuryInfoVT is a virtual table created in the view. Below is a screenshot showing that part of the SQL.

I have attached the view :)

Cheers

Mike

Hi Mike,

that explains the error - the table doesn't exist in the database.

Can you use INJURY_PROBLEMS.IP_ID?

Or if that's not possible, can you create the Yellowfin Virtual Table "InjuryInfoVT" as a database view?

regards,

David

Ok, if that's the problem we'll be able to work around it!

Thanks for your help. We'll be all good from here I think. We'll let you know if we have any trouble. Feel free to complete the ticket.

Cheers

Mike

no problems Mike, that's OK I'll keep the ticket open until it's all working - let me know how it goes!

regards,

David

Sorry Dave - I was wondering if I could ask you an unrelated question just so I can get a quick answer - which database table are the content categories stored in? Like if a category was deleted where would I find it to undelete it? Apologies for going off topic!

no worries.....ContentManagement.

Thanks Dave. Is there another table with the actual category name in it? The reason I'm asking is that we have a client who is trying to tidy up and delete some categories but the system complains that there are still reports in there even though there aren't. From memory, this was a hangover from an old issue with sub-queries or something like that. The user has added the word "Delete" to the end of every category she wants removed so I was going to do it in the database for her... Just trying to locate them so I can do this.

Again, I apologise for going off topic but I'm on leave after tomorrow for a month so I'm scrambling to get some things finished off.

I've attached what I'm seeing for their org in the ContentManagement table.

as far as I know there's no other table, and I asked my colleague and he agress. Try restarting Yellowfin, it sounds like maybe the application cache is out of synch with the database.

did restarting do the trick?

Sorry, Dave - I will probably try this later on in the day outside of business hours.

It's odd though, because the entire table doesn't look to have the actual folder names which is confusing me - everything is capitalised with no spaces, not just for their org, too, it's for everything. Are you sure I'm looking in the right place?

Here is a screenshot of their folders/categories, if you compare it to the Excel file I sent through before it doesn't look much like it!

Hi Mike,

You're absolutely right...the Sub Folder name is stored in the OrgReferenceCodeDesc table. You can find it with

select ShortDescription from OrgReferenceCodeDesc where RefCode = <insert old name> and RefTypeCode = 'RPTSUBCATEGORY'

Hopefully that will help you out!

regards,

David

Hi Mike,

I'm just checking up how you got on with this and whether you have any further questions on the matter or whether it is OK to mark this Question as "Answered"?

regards,

David

Hi Dave,

I apologise for the delay in coming back to you - it has been a busy first week back after being on holiday.

I'll try to check this out again as soon as I can and let you know.

Cheers

Mike

don't stress Mike! I was only asking because.....well you know how it is....support guys are always to trying to clean up their queue!

Just whenever you're ready.

Hope you had a good holiday!

regards,

David

Completely understand. And I have good news for you, it works when using something that isn't a virtual table so you can clear this one from your queue!

The holiday was good, thanks - just difficult coming back to cold Christchurch!

Thanks again for your help - it is greatly appreciated.

Cheers,

Mike

that certainly is great news, thanks for letting me know!

Well, the way this planet is heating up I reckon that places like cold Christchurch are the way to go!

Always glad to be of some help, let us know anytime you come across any issues or questions.

David