Hi,
I’m working as test lead and we use MFALM/HPQC as test management tool to track the project testing activities.
MFALM has excel add-in called Business excel view and with the help of this excel add-in we can connect to MFALM database to refresh and download the data of test artifacts like test step status, defects status etc.
For each entity we have one business object is available to download the data and likewise we have ‘Test step data’, ‘Test script’ and ‘defects’.
So, when I refresh business objects for all 3 entities at once and data refresh completes within 30 to 60 seconds time.
However, when I add summary sheet, it consists of Tables (Data grid) and now refresh data is taking around 10 mins to complete.
- Summary sheet will consists of Tables (Data grid), they have around 10 columns, first one is team names, second is total steps, third onwards it starts status of the test steps (i.e. Pass, failed, In progress etc.), in the last total pending, % pass and % pending
I’m using excel formula =Countifs to get the counts of each status and the formulas are referring to source data sheet (where we refresh the data).
Can you pls suggest why data refresh is fast without adding summary sheet and taking time when I have added summary sheet (Tables -Data grid) and
Your inputs will be much appreciated.
Note: I’m using MS 365 pro plus.
Thanks,
Sai.
Hi Sai,
Welcome to our forum!
I wonder if the formulas in the summary sheet are recalculating repeatedly during refresh. Why don't you use a PivotTable for the counts? This would be far more efficient than lots of COUNTIF formulas.
Mynda
Thanks you Mynda for your replay...
I thought so and have created macro as given below to copy the data to another sheet called "Extended Data".
The summary sheet will refer the "Extended Data" sheet and once the data refresh is completed, I will run macro to copy the date from source to "Extended Data" sheet.
However, I still see refresh is taking the same i have mentioned earlier. Does the summary sheet will still re-calculate during the refresh?
I can use the Pivot tables but the project team's requirement is to see all the status columns. In Pivot table if the data for any status is zero they those columns will not appear in the pivot table.
-------------------------------------------------------------------------------------------
Public Sub CopyRows()
Sheets("Step Level Data").Select
Range("A3:AI30000").Select
Selection.Copy
Sheets("Extended Data").Select
Cells(1, 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ThisWorkbook.Save
------------------------------------------------------------------------------------
Is there way we can still show all the status columns in the pivot table even count is zero. i.e.Deferred
Much appreciated your help !!
Thanks,
Sai.
Hi Sai,
I would strongly advise against copying the data again. This will only result in a bigger, slower file.
In the PivotTable you can set the field to 'show items with no data' so that even items with zero values will be included. You can do this in the field settings.
Mynda
Thanks Mynda and i have implemented pivot table.
Thanks,
Sai
