CODEWITHSUNDEEP
visual-studio-2012reporting-services
Jerry
Jerry

Reputation: 4547

How do I implement SSRS Y-Axis like a subquery?

I have a report that pulls from a stored procedure. The SP takes a good 3 minutes to run, so I don't want to hit the DB to pull this information more than once. I should already have it in the dataset, so how can I juggle it to get these percents (if at all)?

I have the data in a dataset, and I display a chart based on that data so I can't alter the dataset directly. I need all of the information as it stands. All is well so far. Now, At the bottom of the report, I want a line chart that shows the number of rows that went over a threshold as compared for the ones that did NOT go over that threshold for a given week number. WeekNumber is already a column I collect in my stored proc.

I already have category groups set as WeekNumber, so I believe that my X-Axis should already be set to that. Now, I need to set my Y axis to a percentage of the values that went outside the bounds for that specified week.

My Y-Axis values would then be something like:

OveragePercent=FormatPercent(
               (SELECT COUNT(0) FROM ThisWeek WHERE DataSet1.Threshold > 50) /
               (SELECT COUNT(0) FROM ThisWeek WHERE DataSet1.Threshold < 50), 2)

Any thoughts or suggestions would be appreciated.

Upvotes: 0

Views: 387

Answers (2)

Jerry
Jerry

Reputation: 4547

I found the solution.

In my SSRS table, I use the DataSet-level sum vs the group-level sum to derive a total percent. The table uses MyDataSet and has a grouping by date. I can tell how many loads were delivered on that day based on Loads.value. I can then determine which weekday had the highest percentage of the weekly loads by using the following.

=Sum(Fields!Loads.Value) / Sum(Fields!Loads.Value, "MyDataSet")

This gives me overall percentage for each day.

I then needed to filter down some of the data based on whether a flag was set (thus the reason for the main post.) Using the same approach, I discovered the IIF function works at a row level even inside a sum, so it became fairly "obvious" (although it wasn't obvious to me at the time).

=Sum(IIF(Fields!Option1.value=1,Fields!Loads.Value, 0)) / Sum(Fields!Loads.Value, "MyDataSet")

This tells me what percent of those same loads for the week had option1 selected.

I hope this solution helps someone else, too.

Upvotes: 0

djangojazz
djangojazz

Reputation: 13272

Set up a Shared Dataset instead of an attached one to your report. Publish the Dataset much the same as you do a DataSource or report. Once published, click on it in it's appropriate folder. Choose the drop down arrow and select Manage. Choose 'Caching' and set a duration.

Usually when I have things that are batch build related versus transactional I cache them. (Office locations, people, etc.) This way every morning I get what was input in the last day and then use that data instead of querying every time. As far as I know this works with procs and functions as well.

Upvotes: 1

Related Questions