So I am by no means an expert in SQL so I'm sure there is a way in sql itself to do this. But anyways I have a query with 2 unions and I am trying to link a report, but I only want the column reference to effect the last row. There are only 3 rows total and there is always going to be only 3 rows so I only want the drill down to be clicked from the last row. Is there any way to do this or any advice on how to achieve this with a dbr.report command?
Linked Report DBR Command on specific row(5 posts) (2 voices)
Is there anyways to check the condition of a sql variable against the show_link reference? An example of what I mean below.
set var2="something"; select 'dbr.report', 1, 'drilldown_report', 'parameter=something', 'show_link=[v1]==var1'; select var1 as '[v1]',
var2 as '[v2]'
select d.dynamic_value1 as '[v1]',
d.dynamic_value2 as '[v2]'
from database.random_table d;
a colum is identified by the column name or by ColumnReference. For example the code:
select v_var1 as 'Visble colname 1[columnRef1]', v_var2 as 'Visble colname 2[columnRef2]'
select dynamic_value1, dynamic_value2';
would show two column with visble names of "Visble colname 1" and "Visble colname 2". The columnReference which you can use in your code would be 'columnRef1' and 'columnRef2'. The data to the rows/columns can from a table of from variable, it does not matter.
To determine which reports are attached to which rows you can use the show_link-option. For example the code:
declare v_var1 varchar(10);
declare v_var1 varchar(10); set var1="a";
set var2="b"; select 'dbr.hidecolumn', 'show_link_col';
select 'dbr.report', 'sp_DBR_linked_1', '[v1]', 'inA=v1', 'show_link=[show_link_col]=="yes"';
select 'dbr.report', 'sp_DBR_linked_2', '[v1]', 'inA=v1', 'show_link=[v1]==[v2]'; select var1 as 'V1[v1]', var2 as 'V3[v2]', 'no' as 'show_link_col'
select data1, data2, 'yes'
Would not attach either of the linked report to first row. The first linked report, [show_link_col]=="yes", would not be true as "no"!="yes" and the second linked report, [v1]==[v2], would not be true either as the variable values would be different ("a" != "b")
For the rest of the rows, coming from the query, the first linked report would be attached to every row as [show_link_col]=="yes" would always be true. The second linked report showing would be determined if the values from data1 and data2 would be the same for the row.
Note also the proper parameter order for the dbr.report-command: the second parameter is the linked report name, third being the optional column where the report is attached to.
Wow I cannot believe I did not think of that.....you are a lifesaver thank you!
You must log in to post.