You have the right to remain still!!

All the credits go for Guerrila BI. Nice macro he did. Check the original post at http://guerrillabi.com/node/26

Before publishing a Report it is good to make all the objects static. This is specially true with the version 9 that the user is allowed to create his own objects and move the original ones.

I was making my little precious this morning when I bumped into the post above. The first thing I though was F#$. I had spent a couple of ours in the making to discover that the work was already done.

Anyway, below goes the code with a little adaptation:

What

Create a set of button that will enable/disable the move operation for all objects. Perfect for report design then publish.

How

At the edit module (CTRL+M) paste the code below. It will basically traverse all sheets and all objects to enable/disable move. The commented code is the original code that I did not use. Maybe you will.



' Enable the move resize for all objects on the report.
sub EnableMove

' do the magic
setMove TRUE,TRUE
end sub


' Disable the move resize for all objects on the report.
sub DisableMove

' do the magic
setMove FALSE,FALSE
end sub




' Set the move properties to all objects
'msVal - Move/Size setting for non-chart objects
'chVal - Copy/Clone setting for non-chart objects
'amVal - Auto Minimize setting for chart objects
'ccVal - Move/Size setting for chart objects
function setMove (msVal, ccVal)
set doc = ActiveDocument


' for all the sheets set the property chosen 
for j = 0 to ActiveDocument.NoOfSheets - 1
set s=ActiveDocument.GetSheet(j)
objs=s.GetSheetObjects

' for all objects
for i=lbound(objs) to ubound(objs)

' get the object's name and property
set objInt = objs(i)
objID = replace(objInt.GetObjectId,"Document\","")
'msgbox("obj ID " & objID &" has the objType " & objs(i).GetObjectType)
set obj = doc.GetSheetObject(objID)
objProp = obj.GetProperties

' as each object has diferent places for the frame object, treat them differently
select Case objs(i).GetObjectType
Case 1,2,3,4,6,7,8,9,17,18,19,34,35 'LB,MB,SB,TB,TX,CS,IB,LA
objProp.Layout.Frame.AllowMoveSize = msVal
'objProp.Layout.Frame.AllowCopyClone = ccVal 
Case 5,31 'BU,SO
objProp.Frame.AllowMoveSize = msVal
Case 10,11,12,13,14,15,16,20,21,22,27,28 'CH's
objProp.GraphLayout.Frame.AllowMoveSize = msVal
'objProp.GraphLayout.Frame.AllowCopyClone = ccVal
'objProp.GraphLayout.Frame.AutoMinimize = amVal
Case Else
msgbox("ObjectID: " &objID & " with objectType: " & objs(i).GetObjectType &" couldn't be found, trying LB settings")
objProp.Layout.Frame.AllowMoveSize = msVal
'objProp.Layout.Frame.AllowCopyClone = ccVal
end select
obj.SetProperties objProp
next
next
end function


Now create the buttons and call the subs EnableMove and DisableMove. Good thing to let them hidden before publishing.

And VIVA LA REVOLUCIÓN!!!

Finding differences with QlikView.

After various months checking synchronization data I have done a report that will tell me which information is present in one side and not in the other. After creating that, I needed to check why the hell the info didn't went to the other system. After all the analysis I had a list with the items that had problems.

Good. Now what? Export to excel and create the query. These days are gone for me!!

What:
Create the search query based on a list box selection of the report.

How:

• I have made an inline load with all the queries I use to check (all the tables as well) info separated by System and subsystem.

LOAD * INLINE [
    SYSTEM,  SUBSYSTEM, QUERY
    SENDER,   INVOICES, "select *   from INVOICES a,  INVOICES_BODY b where a.invoice_num = b.a.invoice_num and a.invoice_num in ( "
    RECEIVER, INVOICES, "select *   from usr_inv.inv_data a,  usr_data.invoices b  where a.code = b.code    and b.code in ( "
 ];

• Loaded the invoice nr for both system in the same table, having a column called system with the system name RECEIVER and SENDER;
• Created two tables, one only with RECEIVER and other with SENDER data using set analisys on expression  ( count ( {<SYSTEM= {'SENDER'}>} distinct INV_NR ) ) and the SUBSYTEM as dimension;
• Created one calculated list (Nr of Systems) with the number of system that an Invoice is present ( =aggr(count(distinct SUBSYSTEM) , INV_NR )  ). As we have 2 systems here, it should show 1 for invoices only on one system and 2 for both systems;
• Created a filter for the SUBSYSTEM;
• One textbox that will convert the list to string and merge with the query ( =Only(QUERY) & Concat(CODREALIZACAO,',') &' )'   ). This will only be shown if I have selected a Subsystem and Nr of Systems.

When clicking the list of number of system, it will show me the amount I have in one side and not on another. Clicking on System, will show my List Box and will show me the query I need to executed over a PL/SQL Program.

That´s it. The versatility of Qlikview, it´s clicking options and the possibility of loading from multiple sources.