A flexible, dynamic graph 1

[ookete]

I am planning on creating a bar graph where grouping is selected at runtime by the user. For example, suppose I have the following table in a database:

Defect Table
ID   Area  Code     DefectCount
1    A     Red        12
2    A     Yellow      1
3    B     Red        15
4    B     Yellow      4
5    C     Red        13
6    C     Yellow      7
7    D     Red        10
8    D     Yellow      3

In the reporting services Dataset, I could have a query like:

SELECT Area, SUM(DefectCount)
FROM Defect
GROUP BY Area

Result:
A   13
B   19
C   20
D   13

The result would be displayed in a graph with a bar for each Area. But in my application, I want to allow the user to group by different fields such as Code. So my query could change to:

SELECT Code, SUM(DefectCount)
FROM Defect
GROUP BY Code

Result:
Red     50
Yellow  15

My question is... is there a way to allow Reporting Services to do this type of alteration on the fly with parameters? I know how to use parameters for filtering the WHERE condition, but this would require parameters for grouping. Not only the GROUP BY, but also the SELECT list. Is it possible with parameters, or do I need to change the Dataset query myself through code?

If I do need to alter the underlying Dataset through code, does that mess up the Report Definition stored in the RS database? Are there problems if a concurrent user is running the same report?

Any ideas are appreciated. Thanks!

 

[Catadmin]

I haven't tried using the method with graphs, but I have written my datasets/Stored Procedures with a parameter. You could simply place the @paramName where you would put the column in each Where/Group By/etc. statement. Then have @paramName set up in Report Parameters. See if you can pass it that way. I think you can call parameters in graphs and charts since the wizard allows you to enter an expression.

As far as multiple users running the reports simultaneously, part of that depends on how you have your locks set up. Make sure your queries use With (nolock) or Read Uncommitted (there's another one that's better than this, but I can't remember it off the top of my head) so that multiple users can access the data simultaneously.

As far as accessing the reports simulatneously, I haven't heard of any problems doing this.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

Catadmin,

Sprocs, of course! Thanks. I forgot we could use them, I was so used to creating SQL statements. That is perfect because then the report doesn't care what I'm doing, I just enter the parameters into the stored procedure and it spits out a dataset for the report to use.

Perfect.

 

[Catadmin]

You're welcome.

I prefer using sprocs anyway from a security standpoint. It keeps the whole "sql injection attack" possibilities to a minimum and keeps the hackers from seeing what tables I'm actually referencing.

Anyway, glad I could help. @=)


Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???