September 29, 2008 at 9:16 am
I'm having trouble defining how I need to set up a report as follows....
Planning units have Objectives.
Objectives have Tasks
Objectives have Assessment Measures
Objectives have Results
Objectives have UseofResults
each of the tables below objectives has a 1 to many relationship....
I need to be able to look thru the objectives/objectiveid, take a couple pieces of data from the tasks, do the same with AssessmentMeasures, Results, and UseofResults tables, and display the corresponding information.
I have this done as en external assembly that will return a string with the proper information (very unhappy with this ugly approach though it's the only one I've been able to make work so far to get me the data), but I can't seem to figure out another way to do it...
ideas?:crazy:
September 30, 2008 at 8:06 am
What reporting tool are you using? I don't see anything here that can't be done through the source T-SQL query.
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
September 30, 2008 at 8:21 am
the query isn't an issue, getting the data is easy. what I don't see is how to get it into SSRS Reports.
here is the pattern:
for each objective
Display Objective Title
Objective Detail
Objective Tasks
Objective Assessment Measures
Objective Results | Objective Use of Results
next
running thru the data isn't hard...
I think I'm just missing something. This report is more like a word document type output in view, than like a tabular/matrix style report.
September 30, 2008 at 8:31 am
So you're looking for something like this?
Clever Title 1
Detail 1
Detail 2
Task 1
Task 2
Task 3
Measure 1
Measure 2
Results 1
Results 2
Clever Title 2
Detail 1
Detail 2
Task 1
Task 2
Task 3
Measure 1
Measure 2
Results 1
Results 2
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
September 30, 2008 at 9:06 am
correct where each break would contain a different objective and the details therin as well. and a dual column results | use of results.
I can almost get my mind around the pattern I need but.... then it slips away....
September 30, 2008 at 9:24 am
If getting the data is easy, then I would think you could add sub reports to your main report. Or you could use Data Grouping. Highlight an entire row of data in the GUI interface, go to properties, and add groups in the "Data Grouping" property. Or you could use some sweet T-SQL...
In any case, this is a bit tricky and more difficult than it should be. Are you using the "BIDS 2005" (Business Intelligence Dev Studio) for the reports?
This is one of the key reasons I still say to this day that MS Access is the best reporting tool ever created, in spite of its flaws and ability to be a chainsaw in the wrong hands...
Alternatively, have you checked out the new Report Builder? (http://msdn.microsoft.com/en-us/library/ms155933.aspx) It might be easier to do in there...
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
September 30, 2008 at 9:42 am
yes I'm using VS BIDS....
I'd have to agree with you about Access being a simple reporting tool.... and I also have see situations where it would put the "Texas chainsaw massacre" to shame...
If I could group subreports this would be easier I think.... but I haven't see the documentation that I could do that....
I'm wondering if I need to create a custom table, with the customdata set (external assembly) and just call functions from the assembly with report variables, (objective id) being the only real id that I need. And then group by that objective.
October 3, 2008 at 1:26 pm
I finally solved it by creating a series of subreports and creating a table to locate them in and grouping the table on the ObjectiveID.
worked really slick.
October 3, 2008 at 5:55 pm
Cool! I'm about ready to say SSRS with Sharepoint is a viable application platform...
_______________
bkDBA
0.175 seconds -- 10 year average margin of victory at the Daytona 500
212 Degrees
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply