How to implement below query through yellowfin report builder

Mukta shared this question 1 year ago
Answered

How to implement using report builder the below scenario:

SELECT T1.C1, SUM(T1.AMOUNT)

FROM(SELECT C1,C2, SUM(AMOUNT) AS AMOUNT

FROM AGGREGATE_TABLE

JOIN T2 ON T2.C3=AGGREGATE_TABLE.C3

GROUP BY

C1,C2

HAVING SUM(AMOUNT)<>0)

T1

GROUP BY T1.C1;


Please note that Aggregate_table is a huge table and I dont want to refer it twice.Also, I already have view containing this part:" AGGREGATE_TABLE JOIN T2 ON T2.C3=AGGREGATE_TABLE.C3"


Please advise.I need to implement it urgently.

Comments (7)

photo
1

Hi Mukta,


Unfortunately something of this complexity may only be possible through a free-hand SQL report, or on a report created on top of a stored procedure view. Are either of these possibilities here?

Regards,

Nathan

photo
1

Hi Nathan,

I am going to try it with freehand SQL.Will keep you posted.

Thanks!

photo
1

Hi Nathan,

I am able to create it using Stored Procedure.SO I have created a view to be based on this stored proc and report over it.But in my output all the filters(which are basically IN parameters of stored procedure) are marked as mandatory.Could you advise me on making some filters non-mandatory.

photo
1

Hi Mukta,

Any filter passed as a parameter to the stored procedure will be mandatory. If you remove all but essential where clauses from your report, you can create these as normal filters in Yellowfin which will not be mandatory. Let me know if this makes sense.

Regards,

Nathan

photo
1

Could you please elaborate it further?

photo
1

Hi MuktaWhen you create a stored procedure you can create filters. As an example, say this is your stored proc:

myproc(date my_date){
SELECT * FROM table
WHERE date_field > my_date
}


This procedure accepts a date field, which it then uses in the WHERE clause. In Yellowfin the "my_date" field will always be a mandatory filter, as it is a mandatory parameter to the stored procedure. The stored procedure will always be run before the report, with the filter value provided to the mandatory filter.

In addition to this filter, you can also add normal Yellowfin filters at the report level. These will not be mandatory. In the screen-shot below, the red date filter is mandatory because it is an argument to my stored procedure.

Let me know if this makes more sense!

Regards,

Nathan

photo
photo
1

Hi Mukta,

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan