Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

Need Help with a Custom Expression in Report Builder

Questions
Tags
Users
Badges
Marcus Whitehead
(13●4) Nov 29 '22, 10:29 a.m.

 I need help with creating a custom expression to get the first table to look like the second table.  Any suggestions will be appreciated


 156 <o:p> </o:p>

Red <o:p> </o:p>

Charlie <o:p> </o:p>

<o:p>  156 </o:p>

Blue <o:p> </o:p>

Alpha <o:p> </o:p>

<o:p>  156 </o:p>

Green <o:p> </o:p>

Delta <o:p> </o:p>


156 <o:p> </o:p>

Red <o:p> </o:p>

Charlie <o:p> </o:p>

Blue <o:p> </o:p>

Alpha <o:p> </o:p>

Green <o:p> </o:p>

Delta <o:p> </o:p>


0 votes

Comments
David Honey
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER Nov 29 '22, 1:20 p.m.

What's the meaning of <o:p>?
What does that first column represent?
Why is the first column sometimes showing the <o:p> after the 156 and other times after it?
You'll need to give details of what this data represents.

Marcus Whitehead
Nov 30 '22, 7:12 a.m.

Those are empty spaces this forum added after copying the chart from MS office...

so it should be.....
  ID     Color     Attribute
156 |   Red   |  Charlie
156 |   Blue   |  Alpha
156 |  Green |  Delta

  ID     Color     Attribute
156 |  Red   |  Charlie
       |  Blue  |   Alpha
       | Green |  Delta
What's happening is when i run the report, I get the second example print out on Report Builder (which I want), but once I download it as an excel sheet or word document, it reverts back to the first example.  I need it stay as the the second example to create my PUB template easier.  Also, I can not show my example with Real Data as I work in the Classified environment.



1 answer
1,319 views
0 votes

One answer

Permanent link
David Honey
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER (1.8k●1●7) Nov 30 '22, 8:17 a.m.

I think this is working as designed. The actual results are 3 rows each with 3 populated columns.
For report results displayed in the web UI, when you have sorting, the UI presentation performs grouping presentation. When you export to CSV, you're exporting the actual results of 3 rows with 3 populated columns.If you want the CSV to be different, you might post-process it or use Excel macros. I don't think there is a way to do what you are asking natively in Report Builder.

0 votes

Comments
Marcus Whitehead
Nov 30 '22, 8:35 a.m.

I understand it is working as design, however, arent there ways you can do a custom expression within Report Builder?  I've Concat multiple rows, to print out as i wanted with a comma separating the attributes before doing an custom expression IE the example above, 

I had it print (156 | Red, Blue, Green | Charlie)
 
however, this is concatenating an attribute with the same ID, into one so like a merge....I'm sure there is some sparql code that will allow me to do this....and thats what I am asking for....

Help with a custom expression to have report builder print out as i need it.    Any Ideas?

Marcus Whitehead
Nov 30 '22, 8:38 a.m.

 Also, doing it post process will not allow me to use Report Builder to do my PUB template with the correct information...as the template uses the dta from Report Builder hence why i need it done in Report Builder, not Post Report Builder

David Honey
FORUM ADMINISTRATOR / FORUM MODERATOR / JAZZ DEVELOPER Nov 30 '22, 9:20 a.m.
You need to watch out for group concat is how to deal with cross-products. In your example:
156 | Red | Charlie
156 | Blue | Alpha
156 | Green Delta
Expressing this as:
156 | Red, Green, Blue | Charlie
is incorrect because that is effectively saying there are pairs {Red,Charlie}, {Green,Charlie}, and {Blue, Charlie} but that's not the case. If you also did a group contact on the 3rd column and got:
156 | Red, Green, Blue | Charlie, Alpha, Delta
that too would be incorrect because it is effectively saying you got the cross product {Red,Charlie}, {Red,Alpha}, {Red,Delta}, {Green,Charlie}, {Green,Alpha}, {Green,Delta}, {Blue,Charlie}, {Blue,Alpha}, {Blue,Delta}.

In order to express the data correctly, you need to group concat the concat of column 2 and column 3. Something like:
156 | Red:Charlie, Green:Alpha, Blue:Delta
That would be a correct compact representation of the data.


Marcus Whitehead
Nov 30 '22, 10:22 a.m.

True, I was just giving you a vague example on what I have done before not specific to just this example if that makes sense.   Just implied I concatenated other values separated with a comma...but I have not been able to merge a cell together that has the same value as 156 does to just have one number not the 3 156's.   


So I'm looking for help to get the ID attribute that has the same ID merged into one cell, and all the other information stays the same. If this is possible with a custom expression.  

Marcus Whitehead
Nov 30 '22, 10:30 a.m.

 I need to customize the report in Report Builder so that when I use the .DTA for Pub I get the correct Template in order to create my Document from Report Builder.  


Are you familiar with PUB/RPE?  

Your answer

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Ask a question
Guidelines
FAQ
Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details

Question asked: Nov 29 '22, 10:29 a.m.

Question was seen: 1,319 times

Last updated: Nov 30 '22, 10:30 a.m.

Confirmation Cancel Confirm