Sparse Cross-report
Hello,
lets imagine I have two dictionaries - table A with column Name and table B with column Name.
Nad we have a transactional table C, which uses data from dictionaries A and B by value (not by reference) and have columns ID, A_Name, B_Name, Created. We create records in table C in reglar basis but in specific period it can hold records with subset of A and B dictionaries.
In other words, here is sample dataset:
A = ['123', '234', '345', '456']
B = ['aaa', 'bbb', 'ccc']
C = [{1, '123', 'bbb', 2019-01-01}, {2, '456', 'ccc', 2019-01-02}, {3, '456', 'ccc', 2019-01-02}] I'd can create cross-report from C with A values as colunms, B values as rows, counts in intersection for the period from 2019-01-01 to 2019-01-02:
'123' '456' 'bbb' 1 0 'ccc' 0 1
BUT! I'd like to have a cross-report with ALL elements from A and B on the same period:
'123' '234' '345' '456' 'aaa' 0 0 0 0 'bbb' 1 0 0 0 'ccc' 0 0 0 1
Please notice fully zeroed rows and colunms
Is it possible using only view builder, without free-hand SQL?
The same question
Hi Mike,
Thanks for reaching out. I'm a little confused by these examples are formatted. Are A, B, and C three different fields, with the values contained in each being the ones you've exemplified? It would be easier to understand what the desired result is if I can see how the fields are currently displayed in the report builder itself. Based on what I've understood thus far, I'm thinking that either an Advanced Function or Freehand SQL would be required here, but I can confirm once more details are obtained.
I should also point out that as a BMC client, I'd also recommend you reach out to their support staff as well http://www.bmc.com/support/support-central.html.
I'm happy to provide some pointers here however, if we can receive some more info/clarity on this.
Regards,
Mike
Hi Mike,
Thanks for reaching out. I'm a little confused by these examples are formatted. Are A, B, and C three different fields, with the values contained in each being the ones you've exemplified? It would be easier to understand what the desired result is if I can see how the fields are currently displayed in the report builder itself. Based on what I've understood thus far, I'm thinking that either an Advanced Function or Freehand SQL would be required here, but I can confirm once more details are obtained.
I should also point out that as a BMC client, I'd also recommend you reach out to their support staff as well http://www.bmc.com/support/support-central.html.
I'm happy to provide some pointers here however, if we can receive some more info/clarity on this.
Regards,
Mike
Hi Mike,
no, each A, B and C are tables, A and B with one column - Name, table C with four colums - ID, A_Name, B_Name and Created.
I was able to create necessary query with "full join" statement on MSSQL, which allow to specify join condition. Yellowfin has "cross-join". which does not allow to specify join condition.
Hi Mike,
no, each A, B and C are tables, A and B with one column - Name, table C with four colums - ID, A_Name, B_Name and Created.
I was able to create necessary query with "full join" statement on MSSQL, which allow to specify join condition. Yellowfin has "cross-join". which does not allow to specify join condition.
Hi Mike,
Can I see the SQL query output in SQL Server? This will help my determine whether this would be possible in Yellowfin without creating a Freehand SQL report. I should also note that it would likely be possible to then take said query and use it in a Freehand SQL Report, however, BMC disallows its users from creating Freehand SQL reports I believe, so this may wind up not being an option for you.
Regards, Mike
Hi Mike,
Can I see the SQL query output in SQL Server? This will help my determine whether this would be possible in Yellowfin without creating a Freehand SQL report. I should also note that it would likely be possible to then take said query and use it in a Freehand SQL Report, however, BMC disallows its users from creating Freehand SQL reports I believe, so this may wind up not being an option for you.
Regards, Mike
Hi Mike,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Regards,
Mike
Hi Mike,
I just wanted to check in and see how things are going with this.
Regards,
Mike
Regards,
Mike
Hi Mike. I think we can close this topic. It's a bit complicated when your db drived is not fully SQL compliant. In other case you can just write your own query.
Hi Mike. I think we can close this topic. It's a bit complicated when your db drived is not fully SQL compliant. In other case you can just write your own query.
Replies have been locked on this page!