How to destroy a column in PB 6x datawindow

rmbaker
rmbaker used Ask the Experts™
on
I'm trying to destroy unused columns in a datawindow and the printout of the datawindow contains blank spaces where the colums had been. Since these columns are on the right hand end of the datawindow I end up with a blank page.

Can anyone tell me how to destroy the columns so the datawindow does not hold space for them?

Here's the code I'm using. llcount is the total of columns to be used.

//      also loop through all the unused fields to make them invisible
      FOR i = llCount + 1 TO 25
            
            dw_report.Modify("destroy column mix_cd" + string(i))
            dw_report.Modify("destroy column mix_percent" + string(i))
            dw_report.Modify("destroy column mix_prem" + string(i))
            dw_report.Modify("destroy column mix_cost" + string(i))
            dw_report.Modify("destroy column mix_cost_chg" + string(i))
            dw_report.Modify("destroy column mix_protein" + string(i))
      NEXT


Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
There is an odd solution to this, what u need to do is save the dw as powersoft report ,then assign the psr file as
a dataobject to ur dw & retrieve the dw. (do this process before retrieve to avoid  calling retrieve twice)
i.e. ur code will look like
Also dont forget to destroy the col headings

dw_report.SetRedraw(False)
    FOR i = llCount + 1 TO 25
         
         dw_report.Modify("destroy column mix_cd" + string(i))
         dw_report.Modify("destroy column mix_percent" + string(i))
         dw_report.Modify("destroy column mix_prem" + string(i))
         dw_report.Modify("destroy column mix_cost" + string(i))
         dw_report.Modify("destroy column mix_cost_chg" + string(i))
         dw_report.Modify("destroy column mix_protein" + string(i))
        //add a code to destroy col headings also
         dw_report.Modify("destroy mix_cd" + string(i)+"_t")
         dw_report.Modify("destroy mix_percent" + string(i)+"_t")
         dw_report.Modify("destroy mix_prem" + string(i)+"_t")
         dw_report.Modify("destroy mix_cost" + string(i)+"_t")
         dw_report.Modify("destroy mix_cost_chg" + string(i)+"_t")
         dw_report.Modify("destroy mix_protein" + string(i) +"_t")

    NEXT
//save the dw as a psr file
         dw_report.SaveAs("c:\psr1.psr",PSReport!,True)
//assign the psr file as a dataobject
        dw_report.DataObject = "c:\psr1.psr"
//set the transaction object & retrieve the data
      dw_report.SetTransObject(Sqlca)
dw_report.Object.DataWindow.Print.Preview= 'yes'
//do the retrieve here & not initially
dw_report.Retrieve()

dw_report.SetRedraw(True)
Another Way on the same lines :

Have another DW or a datastore, to which attach the same dataobject. Do not retrieve this. Also destroy the same colums/controls for this datastore.
 The trick is to store data in some other placetemporarily, and re-set dw and copy it back.

The code will be somewhat like this:

Blob lblb_temp
DataStore lds_temp
lds_temp = Create dataStore
lds_temp.Dataobject = this.DataObject

//Destory
lds_temp.Modify("destroy .....")

//Get Data into this DS, can also use rowscopy()
lds_temp.ImportString(String(dw_report.Object.Datawindow.data))


dw_report.SetRedraw(false)
dw_report.Reset()
dw_report.GetFullState(lblb_temp)
dw_report.SetFullState(lblb_temp) //This will re-set it
//Instead of the above 3 statements, u can also try re-assigning the dataobject (and destroying the reqd cols), Or you can just Try printing the second DW/Datastore.

//Get Data Back in the report
dw_report.ImportString(String(lds_temp.Object.Datawindow.data))
dw_report.SetRedraw(true)

Destroy lds_temp

regards,
Vikas Dixit

Author

Commented:
When I attempt to use prashant_khatarnak 's solution I get an oracle error 1722. The .psr file seems to be creating fine. Is there a type conflict that's possible when doing this? I'm using numbers type in several fields. Since I need calculations to determine headers I'm retreiving twice (but I am using different objects). Is this a problem?

I'm thinking that I can not use Vikas_Dixit's solution of using a temporary data store since I am using calculations at runtime to help create the datawindow. IE the sql return results determine the headers...

Perhaps it would help if I included the entire window function.

Any ideas?

Thanks again!

string       lsDate, lsMix, lsLot
long             llCount, llRowCount
decimal       ldMixCost, ldMixCostChg, ldMixProtein, ldMixPrem, ldPercent
integer      i, i2

dw_report.Retrieve(report_parms.prft_ctr_cd, DATE(report_parms.begin_date))
llRowCount = dw_report.RowCount()

IF llRowCount = 0 THEN
      //no data for this report
      RETURN FALSE
END IF

lsDate = string(date(report_parms.begin_date))

SELECT COUNT(DISTINCT MIX_CD)
  INTO :llCount
  FROM TCOST_CARD
 WHERE PRFT_CTR_CD = :report_parms.prft_ctr_cd
   AND TRUNC(COST_CARD_DATE) = TO_CHAR(TO_DATE(:lsDate, 'MM/DD/YYYY'), 'DD-MON-YYYY');

IF llCount = 0 THEN  //there are no mixes on this cost card...
      dw_report.Object.mixprem_t.Visible = FALSE
      dw_report.Object.mixcost_t.Visible = FALSE
      dw_report.Object.mixcostchg_t.Visible = FALSE
      dw_report.Object.mixprotein_t.Visible = FALSE      

ELSE

      DECLARE MIX_CURSOR CURSOR FOR
      SELECT DISTINCT MIX_CD, MIX_COST, MIX_COST_CHG, MIX_PROTEIN, MIX_PREM        
        FROM TCOST_CARD
       WHERE PRFT_CTR_CD = :report_parms.prft_ctr_cd
            AND TRUNC(COST_CARD_DATE) = TO_CHAR(TO_DATE(:lsDate, 'MM/DD/YYYY'), 'DD-MON-YYYY')
      ORDER BY MIX_CD;
            
      IF SQLCA.SQLCODE < 0 THEN
            f_db_sql_display_msg(SQLCA,"Error declaring cursor in wf_report_sheet::wf_cost_values.")
            RETURN FALSE
      END IF
      
      OPEN MIX_CURSOR;
                  
      FOR i = 1 TO llCount  //set the mix cd headings on the cost card report
            IF i > 25 THEN
                  MessageBox("GACS Error", "Cost Card can currently only display up to 25 mixes.  Please contact I.S. support.")
                  RETURN TRUE
            END IF
      
            FETCH MIX_CURSOR INTO :lsMix, :ldMixCost, :ldMixCostChg, :ldMixProtein, :ldMixPrem;
      
            dw_report.SetItem(1, "mix_cd" + string(i), mid(lsMix, 0, 5))
            dw_report.SetItem(llRowCount, "mix_cd" + string(i), mid(lsMix, 0, 5))
            dw_report.SetItem(llRowCount, "mix_prem" + string(i), ldMixPrem)
            dw_report.SetItem(llRowCount, "mix_cost" + string(i), ldMixCost)
            dw_report.SetItem(llRowCount, "mix_cost_chg" + string(i), ldMixCostChg)                                    
            dw_report.SetItem(llRowCount, "mix_protein" + string(i), ldMixProtein)

            FOR i2 = 1 TO llRowCount
                  lsLot = dw_report.GetItemString(i2, "lot_cd")
                  ldPercent = 0
                  SELECT PERCENT
                    INTO :ldPercent
                    FROM TCOST_CARD
                   WHERE PRFT_CTR_CD = :report_parms.prft_ctr_cd
                     AND TRUNC(COST_CARD_DATE) = TO_CHAR(TO_DATE(:lsDate, 'MM/DD/YYYY'), 'DD-MON-YYYY')
                        AND LOT_CD = :lsLot
                        AND MIX_CD = :lsMix;
                  
                  IF ldPercent <> 0 THEN
                        dw_report.SetItem(i2, "mix_percent" + string(i), string(truncate(ldPercent*100, 0)) + "%")
                  END IF            
            NEXT

      NEXT
      
//      //now set the lines to be only as long as the number of mixes...
      dw_report.Object.line1.X2 = 1350 + (215 * llCount)
      dw_report.Object.line2.X2 = 1350 + (215 * llCount)
      dw_report.Object.line3.X2 = 1350 + (215 * llCount)
      dw_report.Object.line4.X2 = 1350 + (215 * llCount)
            
      dw_report.Object.sum_line1.X2 = 1370 + (215 * llCount)
      dw_report.Object.sum_line2.X2 = 1370 + (215 * llCount)
      dw_report.Object.sum_line3.X2 = 1370 + (215 * llCount)
      dw_report.Object.sum_line4.X2 = 1370 + (215 * llCount)
      dw_report.Object.sum_line5.X2 = 1370 + (215 * llCount)
      
//      also loop through all the unused fields to destroy them
      FOR i = llCount + 1 TO 25
            
            dw_report.Modify("destroy column mix_cd" + string(i))
            dw_report.Modify("destroy column mix_percent" + string(i))
            dw_report.Modify("destroy column mix_prem" + string(i))
            dw_report.Modify("destroy column mix_cost" + string(i))
            dw_report.Modify("destroy column mix_cost_chg" + string(i))
            dw_report.Modify("destroy column mix_protein" + string(i))
      NEXT
      
END IF
//save file as data object
dw_report.SaveAs("c:\gacs\psr1.psr", PSReport!, True)
//assign data object to datawindow
dw_report.DataObject = "c:\gacs\psr1.psr"
dw_report.SetTransObject(Sqlca)
dw_report.Object.DataWindow.Print.Preview = 'Yes'
//pull window again
dw_report.Retrieve()

RETURN TRUE

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Hi,

1. You can safely use the approach of using a local data store, because, event if u are setting data at runtime, you are setting it as values to the columns (and not as text of some static text controls etc). All you need is :

//     also loop through all the unused fields to destroy them
    Blob lblb_temp
    DataStore lds_temp
    lds_temp = Create dataStore
    lds_temp.Dataobject = dw_report.DataObject

    FOR i = llCount + 1 TO 25
         dw_report.Modify("destroy column mix_cd" + string(i))
         dw_report.Modify("destroy column mix_percent" + string(i))
         dw_report.Modify("destroy column mix_prem" + string(i))
         dw_report.Modify("destroy column mix_cost" + string(i))
         dw_report.Modify("destroy column mix_cost_chg" + string(i))
         dw_report.Modify("destroy column mix_protein" + string(i))
         lds_temp.Modify("destroy column mix_cd" + string(i))
         lds_temp.Modify("destroy column mix_percent" + string(i))
         lds_temp.Modify("destroy column mix_prem" + string(i))
         lds_temp.Modify("destroy column mix_cost" + string(i))
         lds_temp.Modify("destroy column mix_cost_chg" + string(i))
         lds_temp.Modify("destroy column mix_protein" + string(i))
    //You also need to destroy the col headers for the above columns
    NEXT
    //Get Data into this DS, can also use rowscopy().Check Error
    lds_temp.ImportString(String(dw_report.Object.Datawindow.data))
    dw_report.Reset()
    dw_report.GetFullState(lblb_temp)
    dw_report.SetFullState(lblb_temp) //This will re-set it
    //Instead of the above 3 statements, u can also try re-assigning the dataobject     (and destroying the reqd cols), Or you can just Try printing the second DW/Datastore.
    //Get Data Back in the report
    dw_report.ImportString(String(lds_temp.Object.Datawindow.data))
    Destroy lds_temp
    dw_report.Object.DataWindow.Print.Preview = 'Yes'



2. A better solution will be to destory the cols before u retrieve the dw_report. No need then of the set/get fullstate/psr thing :

lsDate = string(date(report_parms.begin_date))
SELECT COUNT(DISTINCT MIX_CD)
  INTO :llCount
  FROM TCOST_CARD
  WHERE PRFT_CTR_CD = :report_parms.prft_ctr_cd
  AND TRUNC(COST_CARD_DATE) = TO_CHAR(TO_DATE(:lsDate, 'MM/DD/YYYY'), 'DD-MON-YYYY');

//loop through all the unused fields to destroy them
IF > 0 Then
  FOR i = llCount + 1 TO 25
         dw_report.Modify("destroy column mix_cd" + string(i))
         dw_report.Modify("destroy column mix_percent" + string(i))
         dw_report.Modify("destroy column mix_prem" + string(i))
         dw_report.Modify("destroy column mix_cost" + string(i))
         dw_report.Modify("destroy column mix_cost_chg" + string(i))
         dw_report.Modify("destroy column mix_protein" + string(i))
      //Also Destory the corresponding col headings.
  NEXT
END IF

//Now retrieve the report. First do settransobject here
dw_report.SetTransObject(SQLCA)
llRowCount = dw_report.Retrieve(report_parms.prft_ctr_cd, DATE(report_parms.begin_date))

IF llRowCount = 0 THEN //Check for error also (-1)
    //no data for this report
    RETURN FALSE
END IF

//Futher ptocessing...........
................
.................


dw_report.Object.DataWindow.Print.Preview = 'Yes'


3. Can Crosstab report be used somehow??

Regards,
Vikas Dixit
Also,
In the second approach, you need to put :


 
//loop through all the unused fields to destroy them
IF > 0 Then
 FOR i = llCount + 1 TO 25
    --------------------
    ---------------------
   dw_report.Modify("destroy column mix_protein" + string(i))
   //Also Destory the corresponding col headings.
 NEXT
END IF
Blob lblb_temp
dw_report.GetFullState(lblb_temp)
dw_report.SetFullState(lblb_temp) //This will re-set it
//Now retrieve the report. First do settransobject here
dw_report.SetTransObject(SQLCA)
llRowCount = dw_report.Retrieve(report_parms.prft_ctr_cd, DATE(report_parms.begin_date))

Regards,
Vikas

Author

Commented:
Solution 2 worked great.

Could not use Crosstab due to issues with data I needed in trailer group

As a note: The oracle error was caused by the original SQL used to create the datawindow. I needed to modify that SQL dynamically as well. Using lds caused all my formatting to be lost when I tried that approach.

Thanks tons for the help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial