14 Replies Latest reply: Mar 3, 2011 5:19 AM by 755755 RSS

    Number format and decimal places

    755755
      Hello evereyone!

      I have a few number columns on my request which must be displayed with 2 or 4 decimal places depending on clients selection in dashboard prompt (presentation variable). If I set Column Properties - Decimal Places - up to 4 then some numbers will not be displayes correctly, eg. number eight will be displayed as 8, instead of 8.00 or 8.0000.

      How to achieve this requirement?

      Best regards!
        • 1. Re: Number format and decimal places
          Aravind Addala
          Hi,

          Try this..

          Change column datatype to double in rpd
          • 2. Re: Number format and decimal places
            755755
            OK, but which format mask to apply that will satisfy client's requirement.

            If he picks "Two decimal places" in dashboard prompt, he wants to see all the numbers on request looking like eg. 8.99
            If he picks "Four decimal places" in dashboard prompt, he wants to see all the numbers on request looking like eg. 8.9999

            I hope I was clear...

            Best regards!
            • 3. Re: Number format and decimal places
              Srikanth Mandadi
              Hi,
              I dont think its possible straight away.Try using guided naviagation creating same reports one with two decimal and other with four decimal.
              Now create intermediate report and show reports based on selection done in prompt.

              Regards,
              Srikanth
              • 4. Re: Number format and decimal places
                755755
                Thanks for your reply!

                I have already tried with Guided Navigation and it works fine, but for some reason both reports (for 2 and 4 decimal places) are executed (i can see it in database session monitor) which is not acceptible since these reports are time consuming. It seems that OBIEE executes both reports and depending on Guided Navigation it only shows one...

                Is this normal behaviour?

                Best regards!

                Edited by: user10203008 on Mar 1, 2011 6:41 AM
                • 5. Re: Number format and decimal places
                  David_T
                  Try doing this:

                  1) Instead of using dashboard prompts, build a small report with 3 columns. In the first column, dummy out the column using the CASE statement: CASE WHEN 1=0 THEN tablename.columnname ELSE '2 DECIMALS' END

                  2) In the second column, also dummy it out using the CASE statement: CASE WHEN 1=0 THEN tablename.columnname ELSE '4 DECIMALS' END

                  3) The 3rd column, simply hide. You need this column because OBIEE requires at least one column in a report.

                  4) For the first column, use Value Interaction Navigation to the report you built that is already formatted for two decimal places. For the second column, use Value Interaction Navigation to go to the second report.

                  You don't need to have "Is Prompted" anywhere, because you are not passing the value from the main report. The Value Interaction is just to take you to the second report. Here's the cool part: because you have Navigation set on different columns, OBIEE will only run the report the user desires, not both like in Guided Navigation.
                  • 6. Re: Number format and decimal places
                    755755
                    Well this is a really good idea. I appreciate it! Now i only have to convince my clients to accept a slightly changed functionality, which is the hardest part :)

                    Thanks a lot!

                    Best regards!
                    • 7. Re: Number format and decimal places
                      Paul
                      I have an alternative approach for you in case you don't use David T's method.

                      Create your report as normal but add 2 copies of each metric. Set the precision on half of the metrics to 2 decimal places, the other half to 4 decimal places.

                      Create 2 compound layouts. In one build your tables/charts using the 2 decimal place metrics, in the other layout use the 4 decimal place metrics.

                      Create a prompt which lets the user pick the precision (2,4) and set a presentation variable.

                      Add 2 sections to your dashboard, assign a different compound layout to each one.

                      Finally, use this technique to hide/show the dashboard sections based on your presentation variable:
                      http://total-bi.com/2011/01/obiee-hide-show-sections/

                      The example in the link is for 11g but you should be able to do the same thing in 10.x with a bit of effort.

                      Paul
                      • 8. Re: Number format and decimal places
                        David_T
                        Paul, with your method, you don't even need prompts or PVs. Once you have the two compound layouts built, (one for the 2 decimals and one for the 4 decimals), then you just need another Compound Layout. This Compound Layout will only have a View Selector.

                        The View Selector will have as its contents the two compounds layouts renamed to "Two Decimal places" and "Four Decimal Places."

                        Now the user just picks the view of choice and voila, two reports for the price of one!
                        • 9. Re: Number format and decimal places
                          Paul
                          Hi David, Yes I thought of the view selector too - on the bus 10 minutes after writing that post! Great minds think alike as they say!
                          • 10. Re: Number format and decimal places
                            755755
                            Guys, thanks for your ideas.

                            I've already tried creating two compound layouts but I don't know if i'm doing something wrong or what... I I have two table views and if I change column property (decimal places) on first table view, the second one is affected also!?

                            It works for you?
                            • 11. Re: Number format and decimal places
                              Paul
                              As I say above. You need to pull two copies of each metric into the request on the crieteria tab. Set the data format on half of the metrics to 2 decimal places, the other half to 4 decimal places. Then build 2 tables (views) in the results tab, one where you use the 2dp versions of the metrics, one where you use the 4dp versions.

                              Paul
                              • 12. Re: Number format and decimal places
                                755755
                                Yes, I followed your steps... But as I said, if I add one metric in table view 1, it is added also on table view 2. Same thing happens if I try to hide some metric - it affects all other table views...

                                Maybe we're working on different versions? My OBIEE version is 10.1.3.4.1.
                                • 13. Re: Number format and decimal places
                                  Paul
                                  Yes I only tried on 11g and I don't have a 10.3 environment here to test at the moment. Can you try dragging the unrequired columns to the 'excluded' area in a pivot table view builder?
                                  • 14. Re: Number format and decimal places
                                    755755
                                    If I use pivot view instead of table view then I can use your idea with multiple Compound Layouts...

                                    Paul, David, tnx a lot guys...