Topic
  • 14 replies
  • Latest Post - ‏2013-01-29T13:17:25Z by DanielWagemann
Gary-William
Gary-William
56 Posts

Pinned topic Data Item in a crosstable instead of measure

‏2013-01-18T18:52:55Z |
Objective : Show what product is in what location for the following X months.

What I'd like : To create a crosstable in report studio with row->location, colon->Month and measure->Product.

Note : The closest to a result I've got was by doing the above crosstable, unlocking it, selecting the Text Item in the measure section of the crosstable and changing the source type property from cell value to data item value*.*

Problem : The Product loses it relationship with month and location and return the first product found and repeats it in every fact cells.

??? Is there a way to do this or is it just not possible?
Updated on 2013-01-29T13:17:25Z at 2013-01-29T13:17:25Z by DanielWagemann
  • JVJ
    JVJ
    10 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-18T19:44:33Z  
    If possible send a screenshot of the issue.
  • Gary-William
    Gary-William
    56 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-18T19:50:01Z  
    • JVJ
    • ‏2013-01-18T19:44:33Z
    If possible send a screenshot of the issue.
    I kept it as simple as possible but this screenshot should illustrat quit well what I mean.
  • Gary-William
    Gary-William
    56 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-18T20:59:51Z  
    Here's the xml of and exemple base on GO Sales (query) package. In that exemple I have the same issues. What I try to show in that exemple is what country sold what product line each year. Hope it will help you help me.
  • pkiran
    pkiran
    9 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-21T14:59:41Z  
    Here's the xml of and exemple base on GO Sales (query) package. In that exemple I have the same issues. What I try to show in that exemple is what country sold what product line each year. Hope it will help you help me.
    try this create dataitem with vale 1 and drag into measure..
  • Gary-William
    Gary-William
    56 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-22T14:54:05Z  
    • pkiran
    • ‏2013-01-21T14:59:41Z
    try this create dataitem with vale 1 and drag into measure..
    I'm not sure I understand? If I put in 1 in measure then I'll lose my product. Could you give me more details?
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-22T15:09:57Z  
    I'm not sure I understand? If I put in 1 in measure then I'll lose my product. Could you give me more details?
    Not sure if this is right... is the attached PDF what you are looking for?
  • Gary-William
    Gary-William
    56 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-22T15:38:46Z  
    Not sure if this is right... is the attached PDF what you are looking for?
    That is exactly it. How did you work it out?
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-22T15:46:58Z  
    That is exactly it. How did you work it out?
    I will post the 10.1.1 samples specification. Sorry I am too busy today to back port it, but you maybe just able to change the schema version in the actual spec to have a look at it.

    The spec uses a crosstab which has Country and Product Line as the nested rows and years as the column. I use quantity as the measure to determine if something was sold in that year for that country.

    I then use a report expression which displays the product line if the quantity is not 0 or missing.
    With that in place, I am able to cut the quantity measure out of the crosstab and change its lable to the Product line text.

    I also cut out the product line nesting lable, and changed the padding to 0 to collapse the cell.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-22T17:43:15Z  
    I will post the 10.1.1 samples specification. Sorry I am too busy today to back port it, but you maybe just able to change the schema version in the actual spec to have a look at it.

    The spec uses a crosstab which has Country and Product Line as the nested rows and years as the column. I use quantity as the measure to determine if something was sold in that year for that country.

    I then use a report expression which displays the product line if the quantity is not 0 or missing.
    With that in place, I am able to cut the quantity measure out of the crosstab and change its lable to the Product line text.

    I also cut out the product line nesting lable, and changed the padding to 0 to collapse the cell.
    Here is the similar example post it on IBM site but I try to reproduce the same thing never worked.

    http://www-01.ibm.com/support/docview.wss?uid=swg21454433

    I will try what Mr.Daniel post it.

    Thanks Daniel for helping us to solve this issue. We are really grateful to you.

    Winson.
  • Gary-William
    Gary-William
    56 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-28T14:03:58Z  
    I will post the 10.1.1 samples specification. Sorry I am too busy today to back port it, but you maybe just able to change the schema version in the actual spec to have a look at it.

    The spec uses a crosstab which has Country and Product Line as the nested rows and years as the column. I use quantity as the measure to determine if something was sold in that year for that country.

    I then use a report expression which displays the product line if the quantity is not 0 or missing.
    With that in place, I am able to cut the quantity measure out of the crosstab and change its lable to the Product line text.

    I also cut out the product line nesting lable, and changed the padding to 0 to collapse the cell.
    Thanks Daniel. I got it to work. You don't happen to have any idea on how to get ride of white spaces by any chance do you?

    Ex:
    Camping equipment
    <-- White scpae -->
    Cooking gear

    I'd like it together:
    Camping equipment
    Cooking gear
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-28T16:49:38Z  
    Thanks Daniel. I got it to work. You don't happen to have any idea on how to get ride of white spaces by any chance do you?

    Ex:
    Camping equipment
    <-- White scpae -->
    Cooking gear

    I'd like it together:
    Camping equipment
    Cooking gear
    Not sure I follow... is it a Null member or is the space elswehere? Would you mind posting a picture?

    Please of course.
  • Gary-William
    Gary-William
    56 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-28T19:06:28Z  
    Not sure I follow... is it a Null member or is the space elswehere? Would you mind posting a picture?

    Please of course.
    Here, I've commented the pdf you gave me few post before.

    The reason of that need is that I got hundreds of potential products but only about 5 of them will show up in a given year.

    Hoping that my answer is not too confusing. Being confine to the samples giving with Cognos makes it hard to build a representative exemple.
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-28T20:50:05Z  
    Here, I've commented the pdf you gave me few post before.

    The reason of that need is that I got hundreds of potential products but only about 5 of them will show up in a given year.

    Hoping that my answer is not too confusing. Being confine to the samples giving with Cognos makes it hard to build a representative exemple.
    This maybe tough to do as the items being sorted are report expressions not really in the crosstab except for the hidden edge. I am not sure I can link the two but I will ponder it a bit.
  • DanielWagemann
    DanielWagemann
    411 Posts

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-29T13:17:25Z  
    This maybe tough to do as the items being sorted are report expressions not really in the crosstab except for the hidden edge. I am not sure I can link the two but I will ponder it a bit.
    At this point I think you have stumped me with the re-ordering of the blank cells. If I think of anything else I will post it.