Saved Reports overview in 1 report

This issue has been solved. See solution.
Tom Muyldermans's picture
Tom Muyldermans
March 5th, 2009

Hello,

As you know you can easily save reports on a scheduled time.

Each time a report is saved and when you look at the report you get a list of all the saved reports.

A created a report that gives me each day the number of something.

So, for each day I have a "saved report" and when I look at it, I can see what number it was on a specific time.

BUT, I want to have this is 1 report.

So I have create a report that goes to the database and look for the saved reports and I tried to get the data out of the database. I can do it, but it's not that easy. The report is saved as a string, with more than 2000 characters.

so I have to find 2 characters out of the 2000 :-/ I can find them, but it is with the substring function, which gives me not much playingspace....

So, what I really want is a report saying :

Date         A number

03/03/09     15

04/03/09      20

05/03/09      17

each row, is from a auto generated saved report.

Anybody who has a neat trick todo this?

 

kind regards,

 

Tom

 

Filed under: , , ,
0 votes
ludovic_ferre's picture
ludovic_ferre
37 weeks 5 days ago

How to list saved reports name?

You need to join the SavedReport table to the Item table in order to map the SavedReport with the orignial report data (name, description etc).

The following SQL will do just that:

select tm.name, tm.description, sr.CreatedDate
  from savedreport sr
  join item tm
    on sr.ReportGuid = tm.Guid

 

Then if you want to see the count of reports per day, or the count of saved reports per report you just need to use an aggregate clause: group by.

Here's an illustration (count of saved reports by report name):

select tm.name, count (tm.name) as 'Saved instances', max (sr.ModifiedDate) as 'Last saved report'
  from savedreport sr
  join item tm
    on sr.ReportGuid = tm.Guid
 group by tm.name
 order by tm.name

 

Ludovic FERRE
Princ. Remote Product Specialist
Symantec

0 votes
Tom Muyldermans's picture
Tom Muyldermans
36 weeks 6 days ago

answer

Solution

sorry, maybe I did not write my questions that well.

I needed an overview of all the RESULTS of the saved reports of a specific report.

but I found the answer.

first I needed the correct reportguid. which I found by running the report and saved them.

than I created a report that shows me the latest saved reports.

USE altiris               
SELECT  TOP 5 createddate, ReportGuid 
FROM SavedReport                
WHERE Description like '%' 
order by createddate DESC

so now I know the correct ReportGuid.

than I lookup in the database the reports with that reportguid

In each saved report there is a field called 'State.'

In that field the saved report is saved as a XML file.

then I used a coldfusion page to get the correct elements out of the xml file, and used that to create a graph with the results.

example : ( 'er' = the value of the state field) and results/dataset/Saved/Table/NCAD_IT is the value that I need.

<cfscript>
myxmldoc = XmlParse(er);
   selectedElements = XmlSearch(myxmldoc, "results/dataset/Saved/Table/NCAD_IT");
    writeoutput(selectedElements[1].XmlText);
Myvar = selectedElements[1].XmlText;
</cfscript>

So now I run this script for every Saved Report with the same ReportGuid, 2 field

1 field is time the saved report was saved, and the Elements value.

Now I have a list with all the data I need, so an historical overview of that data.

0 votes
SOLUTION