Forum

This content is now out of date.

Visit Our Community

Creating report from freehand SQL view

Hi,

I am beginner to YF. I am trying to create a report using view written in freehand SQL.
(The view is freehand SQL and the report is drag and drop from it.)
i'm struggle with some issues with fulfilling the report:

1. I need to display for each row in the report total columns. meaning, column which display for each row the same - sum of all the column's values.
for example:

ID Price Total
1 2.5 6.0
2 1.0 6.0
3 2.0 6.0
4 0.5 6.0
The column 'Total' display for each row the sum of 'Price' column.

I received this info from the view while using:
'SUM(Price) OVER() AS Total' and received the total for all the view results.
But when trying to filter the report, the column value remained the same. the filter built in the report does not sum the column according the new sum after filtering the report. (the sum need to calculate for less rows)
How do I solve it?

2. I build date filter in the report which filter between two dates. When a user choose date as 20/04/2014 and 24/04/2014 I need to displayed those values in two columns.
for this example 'Period from' column need to display 20/04/2014 and 'Period to' column - 24/04/2014
I could not find any option to affect the column valued from the filter. Does this is possible?

I am using YF 7.0 and Microsoft SQL server

Thanks
and sorry for my bad english....
I will try to make the example more clear:
ID Price Total
1 2.5 6.0
2 1.0 6.0
3 2.0 6.0
4 0.5 6.0
One more try... :)


Forum image
Hi,

Thanks for the screenshots and no need to apologize. I think I understand what you're trying to achieve.

Firstly, do you really need to use a freehand sql view?
This really does cause some limitations, especially with 2).

Can I suggest moving from freehand SQL view to using a virtual table?

Please see comments below on your queries;

1)
Is there a reason why you're simply not using the column > Totals = SUM ?
More info on this can be found in the following wiki page : Summaries

E.g.

Forum image


2)
Because you're using freehand sql view, I don't really see a way to do this.
However, the filter value used can be shown on the report output page.
So you will still see the value passed.
E.g.


Forum image




Forum image


-Can you please consider the virtual table option, as this will allow us to use a parameter ;)


Hope this helps, and please let me know how you go with this.

Regards,
David


Hi,

First, thanks for your answers it really gave some new directions to check.

Here my problems: I need to create report using really strict design.
I have tried alot of options from YF, in any option I crossed with issues:

1. Some of the fields in the report are not taken from any table, they supposed to display only default value. for example in SQL I Write those as " 'test' as teat1' " - displayed 'test' for all rows.

2. As I wrote you before - I need to have one column which displayed the same value : the sum of other column values. (look at the example above.) I must have this column in the report because of the strict design!. (question 1 from above)

3. And again, I must have 2 columns which displayed the period to and the period from taken from the filters. (question 2 from above) I must have this columns in the report because of the strict design!.

4. I need to have default values to the date filters : Report period from - Display the first day of the last full month.
Report period to - Display the last day of the last full month.

Do you have any advice how to include all of it in the report?

Thanks!!!!
Hi,

No problems at all.


1-
That is fine, you can still create a calc field in your report to return a value for all rows.
Simply creating a calc field with the formula 'text' will return the data 'text' for all rows returned.

2-
In your example, the 'total' column is displaying data that doesn't really give us any insight as to where this value is coming from.
If it's a calculation, then you can add this as a calc field at the report level , or even view level.
E.g. Sum(price)xSum(quanity)

3-
This is a little bit tricky, as you cannot manually add data to a report, unless you're using some form of calculated field.
Filter values are not displayed in the report, as they are not returned from the Report SQL itself.

4-
Is there a reason why you cannot use 'Previous month' ?
This will return results between the 1st and last day of last month which should give you the same results.

If you're still having issues with this , can you please;

-Send across the raw data you're trying to report against.
-Provide the SQL used to build your view
-Try this with a virtual table, and let me know the results.
-Create a drag and drop view builder, to match as closely as possible to your freehand sql view and let me know the results
-Create your report as best as possible, including the date filter and let me know the results
-An export of the Info.jsp page.
To get this, just add /info.jsp to your Yellowfin URL.
E.g http://localhost:8080/info.jsp

Once we have all this, we should have a better picture on the data you're dealing with as well as the results you're currently getting on your specific YF version.

Regards,
David