Tracing #Plandata

When executing a form or report and the result doesn't match the expected you may trace the SQL that was executed. Follow the steps below:

1. Run the form/report with Trace.
2. Execute the SQL in SSMS.
3. Evaluate the result.

Run the Form/Report with Trace

Make the order and hold down CTRL+SHIFT when clicking on the Start button. When the result displays, choose Trace in the File menu. From version 8.8 you can also view SQL trace in HTML.

By default and for end-users, running a report/form with SQL trace can only be done if pressing Ctrl+Shift when clicking the Refresh button in the ribbon. However, for users that are member of the BV30 role, a new menu item will now be visible when clicking the Refresh button - Refresh with SQL Trace.

Choose the correct data source in the upper part of the dialog, select all SQL in the lower section (CTRL + A) and copy the SQL.

Execute the SQL in SSMS

Open the Sql Server Management Studio and connect to the database. Open a new query window and paste the sql. Execute the sql with F5 or click on the Execute button in the ribbon.

The sql will return two resultset. Note! If someone has done some selects in the procedure the result can be more than two set. In that case its always the two last one that you want to focus on.

The first set is the result from #plandata with certain where clauses that are from PARAM.

The second set is the result with cases on object that you have in column A in the template. Usually TIMEUNIT, TIMEVAL and some more objects.

Evaluate the Result

When you have executed the result there are some alternatives where to find the problem. Read about the options you have below:

The first set is correct but the second is not

If you can see the values you're supposed to find in the report in the first set, but they are missing in the second, then you have the problem in column A in the template. Localize similar SQL in the end of the SQL.

select*

into#BIZVIEW_#SECTION1_#plandata_#SECTION1

from  (selectsheetrow,

              VKAT1,

              REGION,

              TILTAK,

              STRNR6,

              sum(casewhenTIMEUNIT='ISOMONTH'

                        andTIMEVALbetween'201701'and'201712'

                        andVERSION ='BU201706'

                        andVALT='VOLUM'

                        andLINJENR='11'then[sum_CVAL]

                  elsenull

                  end)ascol_40,

              sum(casewhenTIMEUNIT='ISOMONTH'

                        andTIMEVALbetween'201701'and'201712'

                        andVERSION='BU201706'

                        andVALT='AMOUNT'

                        andLINJENR='11'then[sum_CVAL]

                  elsenull

                  end)ascol_42,

              sum(casewhenTIMEUNIT='ISOMONTH'

                        andTIMEVALbetween'201701'and'201712'

                        andVERSION='BU201706'

                        andVALT='AMOUNT'

                        andLINJENR='20'then[sum_CVAL]

                  elsenull

                  end)ascol_43,

...

As you can see here every column that should return data has a case. In Column 40 in the template following criteria is used:

sum(casewhenTIMEUNIT = 'ISOMONTH'

                        and VERSION = 'BU201706'

                        and VALT = 'VOLUM'

                        and LINJENR = '11'then[sum_CVAL]

                  elsenull

                  end)ascol_40,

This is done for every column and you should understand by looking the criteria why the values doesn't show up.

The first set is missing values 

When the first set doesn't have the values you expected, check if #plandata contains the values. Scroll up in the SQL and localize similar script, as below: 

selectsr.sheetrow,

      s.REGION,

      s.TILTAK,

      s.STRNR6,

      s.VKAT1,

      s.TIMEUNIT,

      s.TIMEVAL,

      s.VERSION,

      s.VALT,

      s.LINJENR,

      sum(s.CVAL)as[sum_cval]

into#BIZVIEW_#SECTION1_#plandata_#SECTION1_temp

select *  

from#plandata_#SECTION1swith (nolock)

      innerjoin[#__VKAT1_sheetrow]sr

              onsr.VKAT1=s.VKAT1

where 1 = 1 

      ands.CLIENT='FK01'

      ands.STRNR6='87KM'

      ands.DIVISJON='02L'

      ands.CURR='NOK'

      and((s.VERSION='BU201706'))

      and((s.LINJENR='11')

              or(s.LINJENR='20')

              or(s.LINJENR='40')

              or(s.LINJENR='31'))

groupbysr.sheetrow,

          s.VKAT1,

          s.REGION,

          s.TILTAK,

          s.STRNR6,

          s.TIMEUNIT,

          s.TIMEVAL,

          s.VERSION,

          s.VALT,

          s.LINJENR

Enter select * before#BIZVIEW_#SECTION1_#plandata_#SECTION1_temp s (in this example): 

select *  

#BIZVIEW_#SECTION1_#plandata_#SECTION1_temp s

Select the two rows and press F5 or select Execute in the ribbon. 

If the query results in no records, move to the next chapter. However, if you find the values in #plandata, you may add some where clauses. Remove the inner join below the from statement and select the query. 

select *  

from#plandata_#SECTION1swith (nolock)

where 1 = 1 

      ands.CLIENT='FK01'

      ands.STRNR6='87KM'

      ands.DIVISJON='02L'

      ands.CURR='NOK'

      and((s.VERSION='BU201706'))

      and((s.LINJENR='11')

              or(s.LINJENR='20')

              or(s.LINJENR='40')

              or(s.LINJENR='31'))

Probably you don't get the values now. Either you can understand why by looking at the where clause but you can also select a few where and execute the query and continue to elaborate with the criteria until you find the criteria that causes the query to fail. 

#plandata is empty

If #plandata is empty you must look in the output from the procedure that populates #plandata. If you followed the steps above, you need to paste a new fresh SQL from trace again in a query window and execute it.

When the SQL has finished, select the Message tab in the output pane. Here, you can find the SQL that the procedure has produced. First, in the SQL you have a lot of inserts but you can scroll down to a similar SQL like this:

INSERT #dim_sel_clusters 

SELECT DISTINCT dim_cluster_id 

FROM   dat.data_cells dc  

inner join #timevalues tv on tv.timeunit_id = dc.timeunit_id and tv.timeval_id = dc.timeval_id  

where client_id  = 'FK01' and row_status = 0  and ((version_id = 'LP2018TEST' or version_id like 'BU%') OR (version_id = 'ACT') OR (version_id = 'BU201706')) and currcode_id = 'NOK' and (valtype_id = 'AMOUNT' OR valtype_id = 'MARGIN' OR valtype_id = 'SATS' OR valtype_id = 'VOLUM') 

*** Execution time: 500 (Elapsed: 1466) 

*** Records affected: 274874 

This is the first select that the procedure does to get the planning data. This should result in some records (274874 in this case). If this query results in zero records, you can copy this select (without the initial INSERT) and paste it to a new query window. As you see, the select does an inner join on #timevalues so this you must change. For example, you can choose one timevalue: 
 

SELECT DISTINCT dim_cluster_id 

FROM   dat.data_cells dc  

Where dc.timeunit_id ='ISOMONTH' and dc.timeval_id = '201701' and client_id  = 'FK01' and row_status = 0  and ((version_id = 'LP2018TEST' or version_id like 'BU%') OR (version_id = 'ACT') OR (version_id = 'BU201706')) and currcode_id = 'NOK' and (valtype_id = 'AMOUNT' OR valtype_id = 'MARGIN' OR valtype_id = 'SATS' OR valtype_id = 'VOLUM') 

If you run this query and result shows up, you have problem with TIMEUNIT and TIMEVAL in column A in the template. If it results in zero records, you can elaborate with the where clause to find out which criteria that causes the problem. 

If the query resulted in some records you need to scroll down and look for some joins on objects. 

Maybe you will find a similar insert below: 

__________________________________________________________________________________________ 

insert #childvalues select child_sqlshort from dat.object_trees 

where client_id = 'FK01' and  parent_obj_id = 'STRNR6' and child_obj_id = 'ANSVARSSTED' and parent_objval_id = '87KM' 

*** Execution time: 13 

*** Records affected: 231 

___________________________________________________________________________________________ 

INSERT #dimjoin1 

SELECT DISTINCT dim_cluster_id 

FROM   dat.data_dimensions 

inner join #childvalues on child_value = objval_id 

WHERE  client_id = 'FK01' 

AND obj_id = 'ANSVARSSTED' 

*** Execution time: 160 (Elapsed: 4313) 

*** Records affected: 42820 

___________________________________________________________________________________________ 

There are two possibilities here, either you don't have the first block (insert #childvalues) or you have a similar SQL as above. If it looks similar to above, Bizview has found that the column ANVSARSSTED is used in the template and some criteria in PARAM is set with STRNR6 = '87KM'. What happened here is that Bizview gets all ANSVARSSTED (that is child to STRNR6) where STRNR6 is '87KM'. When the ANSVARSSTED has been found, it searches for stored planning data that ANSVARTSSTED has been stored on.  

If you don't have the insert #childvalues statement it looks similar like this: 

INSERT #dimjoin1 

SELECT DISTINCT dim_cluster_id 

FROM   dat.data_dimensions 

WHERE  client_id = 'FK01' 

AND obj_id = 'STRNR6' and objval_id like '87KM' 

In this case, STRNR6 is considered a root object that has been stored on. 

You may find more inserts that joins data. If you find an insert that result in 0 records (*** Records affected: 0) Bizview will abort the procedure and return an empty. 

Finally, the procedure will intersect all dim_cluster_id from the tables it has created which means that a dim_cluster_id must exist in every insert to be returned. 

insert #dim_clusters select dim_cluster_id from #dim_sel_clusters  

INTERSECT select dim_cluster_id from #dimjoin1 

INTERSECT select dim_cluster_id from #dimjoin2 

*** Execution time: 360 (Elapsed: 5140) 

*** Records affected: 15458