List field and paths from an RDL file

For a customer, I wrote this particular script. On a report based on a Model, it generates an Excel file listing all the fields and paths that have been used in the RDL file. All you have to do is store the rdl files in a directory somewhere on your c-drive, put the below script in a textfile, alter the path and save the file with a VBS extention :

==========================================================================

targetfolder = "C:\foldername\"
set fso = createobject("scripting.filesystemobject")
set objfolder = fso.getfolder(targetfolder)
set fc = objfolder.files
set objexcel = createobject("Excel.Application")
objExcel.visible = true
objExcel.WorkBooks.Add
ObjExcel.Cells(1,1).Value = "Fieldname"
ObjExcel.Cells(1,2).Value = "Path"
ObjExcel.Cells(1,3).Value = "Table"
ObjExcel.Cells(1,4).Value = "Report"
z = 2
for each x in fc
set file = fso.opentextfile(targetfolder & cstr(x.name))
dim strLijn
do until instr(strLijn, "/Hierarchies") > 1
strLijn = file.readline
if instr(strLijn, "<Grouping Name=") > 1 then

objExcel.Cells(z,1).Value = mid(strLijn, 28, len(strLijn) - 32)

do until instr(strLijn , "/Path") > 1
strLijn = file.readline
if instr(strLijn, "!--") > 1 then
strpad = strpad & "\" & mid(strLijn, instr(strLijn, "&")+7, len(strLijn) - (instr(strLijn, "&")+7) - 5 )
strlocatie = mid(strLijn, instr(strLijn, "&")+7, len(strLijn) - (instr(strLijn, "&")+7) - 5 )

end if
loop
objExcel.Cells(z,2).Value = strpad
objExcel.Cells(z,3).Value = strlocatie
objExcel.Cells(z,4).Value = x.name
z = z +1
strpad = ""
end if
loop
strLijn = ""
next


==========================================================================

use with caution :)

Binabik

Reporting services Picklists based on models

To force a picklist to have a fixed length :

1) In the picklist-query, add the field a second time (you'll use this one as label on your parameter) -- change the code for that field by right-clicking it and changing the formula (on the query panel)

for the example, I'll use a string-field that should be 7 characters long and should get leading zeroes -- a specific case I used this for.

2) change the formula to :
left("0000000"; 7 - length([nameofthefield])) & [nameofthefield]
if the field is numeric :

left("0000000"; 7 - length(text([nameofthefield]))) & text([nameofthefield])

That also sorts the picklist, but if you simply want to sort, just add the same field a second time.


Binabik