display Report data – displaying values in the table programatically

9 December, 2013

In a table TEST, there are columns called A, B. Column A has repeating values 100, 100,100 AS ID of the table and column B should vary according to repeating values in column A. at the first occurrence of value in Column A value in Column B should be 1 but after that it should be 0. IS it possible at the report level rather than changing the values in the physical table. which query can make this possible programmatically at report level? Please let me know if there is any solution at view level as well.

TABLE

A B

100 1
100 1
100 1
200 1
200 1
300 1
400 1

EXPECTED OUTPUT

A B

100 1
100 0
100 0
200 1
200 0
300 1
400 1

Hi Renuka,

I'm adding SQL query you emailed to this post for the sake of completeness:

[code]
select A,
case when (row_number() over (partition by A order by A)) = 1
then 1
else 0
end as B
from TEST
[/code]

In this situation when you need to use a DB function that isn't available in a report-level calculated field you should be able to add the function as a Custom Function and then you'll be able to select it in a report-level Calculated Field.

Or you could add a Virtual Table to your view in which you could paste the above code to create the Column B that you need.

Or you could use a
view-level Freehand SQL calculated field

I hope that helps your report, if there are any further questions then please don't hesitate to contact us.

regards,
Dave

Hi Renuka,

I�m sorry the Freehand SQL Calculated Field didn�t accept your query, I have tested a Custom Function (as indicated in the forum post) with your query and it seems to work fine, here is the code you need to pasted into your CustomFunction.xml file:
[code]

row order

1
column
text

case when (row_number() over (partition by $1 order by $1)) = 1
then 1
else 0
end

SQLServer
numeric

[/code]

Make sure you back up the original file for easy roll-back if required.

You will have to restart Yellowfin to pick up the new function. I hope that helps your report, please let us know how it goes.

regards,
Dave

Hi,

This is with regard to using custom functions.

The custom function has been created.

I need to assign the predefined formula to the main query ( which is not the case when I use the predefined function).

I am attaching the query generated in Yellowfin and what is actually required.

Please let me know if this could be done.
I have highlighted the function in both the documents.

Hi Vicky,

Thanks for the report SQL. Did you get both the SQL results from the same report?
Or did you simply modify the 2nd query in word?

Sorry but I'm a little confused as to the results you are trying to achieve.

Are you able to show us a screenshot of the results you are currently getting, and what you want to see.

Thanks,
David

Hi David,
Yes the second query is the modified one.
Below is an example:

Employee Table
empid emptype posnid
1 SUB 2
2 SUB 1
3 NONSUB 2
4 SUB 1

Position Table
posnid posn_title
1 A
2 B
3 C

Required Output

Position_ID Position_Title sub Nonsub FTE
1 A 2 NULL 1
1 A 4 NULL 0
2 B 1 3 1
3 C NULL NULL 1

I need FTE to be 1 only for the first occurence of positionid. Then it should be 0.
I have created the custom function and used it in the report.
I have attached the screenshot .

Thanks,
Vicky

Hi Vicky,

Sorry for the delay.
Thanks for that.

Are you able to send across some screenshots on how you have setup your sub-queries?

I can confirm this is working fine with a single query.
E.g.

Raw Data: