Relational data items

[leahmarie]

I have a quick question about functionality.  My requirement for a report is to add particular data items together.  For example in the relational package I have, There is Name which if you put it into the report without any filters, puts out Smith, Bond, Bourne, etc.  For each row in the report, the end user wants different combinations of name.

ROW 1 (Smith and Bond)
ROW 2 (Bourne and Smith)
etc.etc..

I know how to do this within a cube, but for some reason I cannot seem to figure out how to make this work within my relational package.  Is it possible?? Any suggestions would be greatly appreciated!!:D

[bdbits]

If you want to concatenate two fields:  '(' + [somedataitem] + ' and ' + [otherdataitem] + ')'

Or am I missing something?

[AussiePete2011]

I think you need to do two things

1.  Create two queries
     a.  Query 1 will filter all names needed for the concatenation (name field)
     b.  Query 2 will filter all other names needed for the concatenation (name field)
2.  Create a Union between the two queries

Create a list report and drag in the name field

This is quick and dirty but it will do the job

If you have a large record set, this would be better done in a view which would then be materialised and brought in as a table in the FM Model.

Hope this helps
Cheers
Peter

[leahmarie]

I tried concatenating already and it didn't work. I mean technically it put the two items together, but I need it to add the measure for it together as well.  And for some reason if I try to have more than one concatenated item then it makes everything else in the list below the first row exactly the same.

Thanks for the response:)

Oh and the union query doesn't really work either because there are so many different names; and maybe I'm confused but it is only one data item that I have and I am trying to group certain attributes of that item and use that grouped row item to measure dollars and etc.

Hopefully that makes more sense?

[blom0344]

For each row in the report, the end user wants different combinations of name.

ROW 1 (Smith and Bond)
ROW 2 (Bourne and Smith)
etc.etc..

Why? Cannot fathom what your intend is.  Do you want all combinations from a list of names?  Say , you have 20 names and you want all combinations in pairs (20x20 - 20 = 380)

The easiest would be to use a cross-join and suppress the combinations where [name1] = [name2]

The easiest way to define a cross-join is to use 2 identical queries , each containing a dataitem with a fixed value, say 'x'. Join the queries over this dataitem and you'll get all the combinations.

If you want to omit 'Smith' 'Smith' then add a filter to the projected set..

[leahmarie]

No it is not about different combinations.  The intent would be to add two or three or four of the items together in order to measure and graphically present the groups. 

Basically I want this::

Crosstab:
                Amount
Travel          xxx
Cards             xx
Frames        xx

Where Travel, Cards, and Frames are calculated data items that contain:
Travel= 'Plane' + 'Hotel' + 'Meals'
Cards = 'January' + 'February'
Frames= 'Gifts' + 'Books'

Where 'Plane, Hotel, Meals, January, February, Gifts, and Books are all members of the query item: Information in the relational package. 

Obviously, these are made up and unrelated but the gist of what I am trying to accomplish is there.

I also know that using a crosstab is frowned upon with relational packages; but I will just be using the one measure and just these data items.

[blom0344]

My best bet is to use CASE statements:

Code Select Expand


TOTAL(CASE
WHEN
[NAME] IN ('Smith','Bond')
then [some_measure]
else 0
end)


which yields a seperate grouped value for Smith and Bond

[leahmarie]

Thanks for the reply. I didn't think to add the total part to the case statement.  Apparently our database is broken currently; so I'll have to wait to try it out.

[blom0344]

I also know that using a crosstab is frowned upon with relational packages; but I will just be using the one measure and just these data items.

Them dimensional types, I guess?  ;)

[leahmarie]

haha yeah that's what I learned on so relational is still new to me. Self taught so once the cube clicked, relational just seems silly to me to use because it doesn't do as much.

[blom0344]

But..
Is your case solved?

Eh, your not by the way very well endorsed in mdx?

[leahmarie]

Yeah for the most part it is solved, sorry.  That case function worked gave a measure for the items, just need a way to label it now; but just formatting the report the way that they want to see it is the trick now.  Not that big of a deal, haven't had a chance to get back to that in a while had too much other stuff.

As far as MDX, I can understand it and edit it, can't write it from scratch.