Sparse Cross-report

mike shared this question 3 months ago
Answered

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?

Comments (5)

photo
1

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

photo
1

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.

photo
1

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

photo
1

Hi Mike,

I just wanted to check in and see how things are going with this.

Regards,

Mike


Regards,

Mike

photo
1

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.

photo
1

Hi Mike,

Thanks for your response, and yep, I'm sure that's a bit of a pain. Hopefully there's a workaround given the provided limitation of the AR driver.

This considered all though, I'll go ahead and close this case out for now then, but please don't hesitate to reach out with any other questions or concerns.

Regards,

Mike


Regards,

Mike

photo