5 Replies Latest reply: May 20, 2012 10:08 AM by Purna Cheekati RSS

    DB Storedproc or function

      I have a database stored function and I want to implement the same function on qlikview side for dynamic calculation when ever the user selection changes. so i would like to know the best option to handle this sceneario ie. write a vb script or setAnalysis.



        • DB Storedproc or function
          Kaushik Solanki



              I would suggest you to use set analysis if you are able to achieve what you want with that.


              The reason is set analysis is very fast as compared to vb script code.



          Kaushik Solanki

            • DB Storedproc or function

              Let me try SA first then.. Thanks for your quick responce...

                • DB Storedproc or function
                  Kaushik Solanki



                     Yes try and let us know if you have any more problem.



                  Kaushik Solanki

                    • DB Storedproc or function

                      I am trying to convert this fun to SetAnalysis and i was confused on how to convert WHERE conditions and input variable in this function. your suggestions are welcome...


                      CREATE FUNCTION dbo.fnGetABCAAmount


                            @ABC_ZZZD INT ,

                            @ZZZZcode VARCHAR(5) ,

                            @IsTransisitonedFacility BIT ,

                            @DateOfService DATE


                      RETURNS MONEY




                              IF @DateOfService IS NULL

                                  SET @DateOfService = GETDATE();


                              DECLARE @amount MONEY


                              SELECT  @amount = ( ( ( WorkABC * WorkZZZ )

                                                  * ( CASE WHEN YEAR(@DateOfService) < 2006 THEN 1

                                                           WHEN YEAR(@DateOfService) = 2007

                                                           THEN 0.8994

                                                           WHEN YEAR(@DateOfService) = 2008

                                                           THEN 0.8806

                                                           ELSE 1

                                                      END  -- Budget Adjustor Value

                                                            ) )  + ( PEZZZ

                                                                 * ( CASE WHEN @IsTransisitonedFacility = 1

                                                                          THEN TransitionedFacilityPExpenseABC

                                                                          ELSE TransitionedNon_FacilityPExpenseABC

                                                                     END ) ) + ( MalpracticeABC

                                                                                 * MPZZZ ) )

                                      * ConversionFactor

                              FROM    dbo.ABC_PprABC ABC JOIN

                                      dbo.ABC_GPC gpc ON ABC.ABC_HeaderID = gpc.ABC_HeaderID

                                      JOIN dbo.ABC_Header h ON gpc.ABC_HeaderID = h.ABC_HeaderID

                                      AND @DateOfService BETWEEN StartDate AND EndDate

                              WHERE   ZZZZcode = @ZZZZcode

                                      AND ABC_ZZZD = @ABC_ZZZD



                              RETURN CAST( @amount AS DECIMAL(10,2))