Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
14 replies Latest Post - ‏2013-01-29T13:17:25Z by DanielWagemann
Gary-William
Gary-William
56 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: Data Item in a crosstable instead of measure

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

    Re: Data Item in a crosstable instead of measure

    ‏2013-01-18T20:59:51Z  in response to Gary-William
    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
      ACCEPTED ANSWER

      Re: Data Item in a crosstable instead of measure

      ‏2013-01-21T14:59:41Z  in response to Gary-William
      try this create dataitem with vale 1 and drag into measure..
      • Gary-William
        Gary-William
        56 Posts
        ACCEPTED ANSWER

        Re: Data Item in a crosstable instead of measure

        ‏2013-01-22T14:54:05Z  in response to pkiran
        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
          ACCEPTED ANSWER

          Re: Data Item in a crosstable instead of measure

          ‏2013-01-22T15:09:57Z  in response to Gary-William
          Not sure if this is right... is the attached PDF what you are looking for?
          • Gary-William
            Gary-William
            56 Posts
            ACCEPTED ANSWER

            Re: Data Item in a crosstable instead of measure

            ‏2013-01-22T15:38:46Z  in response to DanielWagemann
            That is exactly it. How did you work it out?
            • DanielWagemann
              DanielWagemann
              411 Posts
              ACCEPTED ANSWER

              Re: Data Item in a crosstable instead of measure

              ‏2013-01-22T15:46:58Z  in response to Gary-William
              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
                ACCEPTED ANSWER

                Re: Data Item in a crosstable instead of measure

                ‏2013-01-22T17:43:15Z  in response to DanielWagemann
                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
                ACCEPTED ANSWER

                Re: Data Item in a crosstable instead of measure

                ‏2013-01-28T14:03:58Z  in response to DanielWagemann
                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
                  ACCEPTED ANSWER

                  Re: Data Item in a crosstable instead of measure

                  ‏2013-01-28T16:49:38Z  in response to Gary-William
                  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
                    ACCEPTED ANSWER

                    Re: Data Item in a crosstable instead of measure

                    ‏2013-01-28T19:06:28Z  in response to DanielWagemann
                    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
                      ACCEPTED ANSWER

                      Re: Data Item in a crosstable instead of measure

                      ‏2013-01-28T20:50:05Z  in response to Gary-William
                      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
                        ACCEPTED ANSWER

                        Re: Data Item in a crosstable instead of measure

                        ‏2013-01-29T13:17:25Z  in response to DanielWagemann
                        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.