Macro to add level filters

[Kaiaualad]

I have an Impromptu report that has two levels,Level and Grouping.
There are 5 levels in the business, and the prompt asks from 0 to 4 which level they want to choose.The Grouping is also chosen from this prompt,but works one back,so if I choose Level 4, then the Grouping would be level 3.

I need to produce PDFs of every category in every level.(I know -they should have Impromptu Web!)

I understand a series of Level category reports would provide the filter values, and a loop would need to run each level report in turn, moving down to the last,saving to PDF as it went.

I have a macro that goes a part of the way:

Dim ImpApp as Object 'Impromptu
Dim ImpRep1 as Object 'Report1
Dim ImpRep2 as Object 'Report2
Dim ImpRep3 as Object
Dim ImpRep4 as Object
Dim ImpRep5 as Object

Dim PdfRep as Object 'Acrobat publisher

'Variables for the catalog and report locations

Dim Catlocn as String
Dim Replocn as String
Dim PDFLoc as String

'Integers to hold the number of rows in report 1 and a running count for report 2

Dim Rcount as Integer
Dim Runnum as Integer







'Set the locations
Catlocn = "\\xserver\Vol2\Data\Cognos\Impromptu\Catalogues\"
ListRep = "\\xserver\Vol2\Data\Cognos\Impromptu\Development Reports\Test Reports\"
Replocn = "\\xserver\Vol2\Data\Cognos\Impromptu\Development Reports\"
PDFLoc = "c:\Acrobats\"


'Start Impromptu and make it visible
Set ImpApp = CreateObject("Impromptu.Application&quot
ImpApp.Visible True

'Open the named catalog
ImpApp.OpenCatalog Catlocn + "XLive.cat"

'Open the named report

Set ImpRep2 = ImpApp.OpenReport(ListRep+ "Level 2 List.imr", "(1st 1 Level Value)&quot

'Create the publishing object and send the output to the unique file name

' Set PdfRep = ImpRep2.Publishpdf
' PdfRep.Publish pdflocn + "Level 2 List for " + Dparm + ".pdf"
' Set PdfRep = Nothing

Streamcount = ImpRep2.GetDataValue(2,1)
Scount = 1

Do while Scount <> Streamcount + 1


Sparm = ImpRep2.GetDataValue(3,Scount)
allparm = &quot;5|2003|1|(1st 1 Level Value)|&quot; + SPARM
'msgbox allparm
Set ImpRep3 = ImpApp.OpenReport(pdflocn + &quot;XReport.imr&quot;,allparm)
Set PdfRep = ImpRep3.Publishpdf
PdfRep.Publish Pdfloc + &quot;XReport for &quot; + Sparm + &quot;.pdf&quot;

ImpRep3.CloseReport
Set ImpRep3 = Nothing


Scount = Scount + 1
Loop


ImpRep2.CloseReport

Set ImpRep2 = Nothing
Quit the Impromptu application and clear resources
ImpApp.Quit
Set ImpApp = Nothing
Set PdfRep = Nothing
End Sub


(I have copied and pasted the previous code twice more - there are 3 0 level values - A loop would be better?)

If anyone has a better idea for looping all the levels into a filter of a report, it would be much appreciated.

Thanks in advance




End Sub

 

[griffindm]

Kaiaualad,

Given your description, AND assuming your 5 business levels can be designated as 1 - 5 ,you need a loop to do the following combinations:

-- Lvl -- -- Grp --
4 5
3 4
2 3
1 2
0 1

You can do this very easily with a loop base on a single counter and without the GetData call and second report.

Rpt1 = pdflocn + &quot;XReport.imr&quot;

x = 1
For x = 1 to 5
sparm = CStr(x)+&quot; - &quot;+CStr(x-1)
allparm = &quot;5|2003|1|&quot;+CStr(x)+&quot;|&quot;+CStr(x-1)
Set ImpRep3 = ImpApp.OpenReport(Rpt1,allparm)
Set PdfRep = ImpRep3.Publishpdf
PdfRep.Publish Pdfloc + &quot;XReport for &quot; + Sparm + &quot;.pdf&quot;
ImpRep3.CloseReport
Set ImpRep3 = Nothing
Next x

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[Kaiaualad]

Thanks Dave,
I ran your script and it loops ok, but for some reason the level number is not being incremented on each pass, so the report is saving 5 pdfs at level 1.

The levels in the report are based on the lookup function in Impromptu:
Lookup(?Level?) in (&quot;1&quot;->Region,&quot;2&quot;->Country etc... 0 is the default and the highest level.

x = 1
For x = 1 to 5
sparm = CStr(x-1)+&quot; - &quot;+CStr(x)
allparm = &quot;1|1996|&quot;+CStr(x-1)+&quot;|&quot;+CStr(x)
Set ImpRep3 = ImpApp.OpenReport(Rpt1,allparm)

The allparm first parameter I set at 1,the second parameter is the year,but what are the third and fourth parameters?
Do I have one parameter too many?
The prompts on the report are Level,Year.

Thanks in advance

 

[griffindm]

Kaiaulad,

I was assuming that the fourth and fifth prompt parameters were the levels for the filter and grouping for the report respectively. I also assumed the second was the year, and that the first and third were related to the month and or week of the report filter.

I don't know why the loop is not stepping. If you run the test code below, the message box shows the values changing.

Sub Main
x = 1
For x = 1 to 5
msgbox &quot;Lvl1 Group is &quot;&CStr(x)&&quot;. Lvl2 Group is &quot;&CStr(x-1)
Next x
End Sub


Dave Griffin

The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com

 

[Kaiaualad]

Got it working now - thanks Dave,my prompts were in the wrong order.
In addition to looping through the levels,i.e one report at five levels,I also have to loop through each category of each level.
For example if I used a Northwind report that contained Country,Region,City, Postcode,revenue, then the loop would not just produce 4 reports, one for each level, but a report for each country at the first level,each region at level 2 etc..

Would I need to use the ImpRep.GetDataValue method in a loop inside the level loop?

Cheers

 

[griffindm]

I would use the GetDataValue call if the other dimensions you need to step through are not contiguously numeric. A simple For .. Next loop is great for incrementing a counter, but cannot help you when you need unique alpha-numeric data for filtering the report. You can step through the row-level data while the second report runs as long as both reports come from the same catalog. Another alternative is to use GetDataValue to load the data into an array and step through this without have the original report open. Either way should work fine.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ20-2863 first!

http://www.decisionsupportgroup.com