IE 10 breaks SSRS

Well, not completely, but enough that it caused pain and frustration.

I upgraded to Windows 8 this week.  That brought along IE 10.  Once I got back around to troubleshooting security issues in SSRS, I had a problem.

I had lost the ability to click the drop down arrow next to folders and reports:

Did some googling and found the quick fix is to enable IE 10 Compatability View.

In IE 10, hit F12 to open the Developer Tools at the bottom of the window.
In the middle, you can change the Browser Mode.

Our beloved dropdown menu is back!

 

Now it’s back to solving security issues.  Why does it have to be so hard to get folks to view reports.  What simple steps am I missing… <sigh>

 

Publish SSRS report in ConnectWise

I just went through the motions to figure out how to take a published SSRS report and make it available in ConnectWise Reports.

I figured it out, but I’m not sure it’s best for us… yet.  I’m still trying to understand several things before settling on the “best” one.

Thankfully, Brad Brenner of NexNow.net saved me some time and has already written a post about the process.  Wish I had seen it earlier!

Check it out here -> Adding a custom-built SSRS report into ConnectWise

First Report – The Output

We’ve done a lot up to this point.  Let’s Run our report and see the results.

Click Run

You’ll now see this screen:

See the Last Update box?  That’s the Parameter we set up a while back.  Click the Calendar to select the date you want to use in the report.  Remember it should return everything that hasn’t been updated since that date (or was updated before that date).

Once the date has been set, click the “View Report” button way over to the right (frustratingly far away!).  Wait a couple of seconds and enjoy the results.

Congratulations for building and running your first report!.

BONUS: For those that have never understood a SQL statement, you can see what we’ve built over the past few posts.

In the Design view of Report Builder,

• Expand Datasets
• Right-click on the Dataset you defined
• Select Dataset Properties

First Report – Grabbing data

So far we’ve defined the basic data we want to bring back.  Now we get to tell it how to behave.

Click Next to get to the Arrange Fields step of the Wizard.

Another section to practice on.  Here’s what I understand (don’t forget… first report ever!):

Clear, right?  I did this:

Another gotcha I need to follow up on… numerical fields will default to the Sum function (can’t explain it).  We want to change them to Max so we can see the actual value, not a total of the values.  That’s what I was told, so I’m doing it for now.

Clicking Next takes us to the Choose the Layout step.

More stuff that I’m not clear on.  I don’t need totals on this report, so I’ve unchecked the “Show subtotals and grand totals box”.  I’ve left the “Expand/Collapse groups” checked, because I’ve already done this a few times and know I need to have that so I can expand the ticket number and see its detail.  This can be remedied… somehow.

Click Next to Choose a Style.

Pick whatever you think is prettiest.

Click Finish and you’re done!

We can come back to pretty it up a bit.  Next we’ll run the report and see what happens.

Reports from CW Marketplace

I’ve learned enough that it’s time to see what others are doing.  I know there are SQL queries in the forum, but I want to see a finished product from Report Builder.  Best place to start is the CW Marketplace.

• Fire up CW, expand the Other menu, and you’ll see Marketplace at the very bottom.
• Refine your results to just “Custom Reports”
• Start browsing and find a report you wish to work with.
• Click the Import button (which really should say Download in this case)
• Save the .zip file.

Actually, stop right there.  Push your chair back from the desk.  Let me save you a bunch of time.

I downloaded EVERY SINGLE REPORT listed on those 7 pages (well, not those named asdf or test) and ended up with 4 usable items.  Two saved Report Builder .rdl files and 2 files with defined queries in them.  Everything else was useless to me:

• Partner specific output from the report.  Yes, the actual client information in a PDF.
• Some Crystal Report .exe files that didn’t do anything but crashed when I ran them.
• Lots of test.docx files

I was also shocked to see a couple of reports actually linking back to their publicly accessible Report Server!  I’ll be sending e-mails about those.

To hurt me more, the 2 Report Builder files weren’t really helpful.  One was even more basic than what I’ve thrown together.  The other had the wrong Data Type configured for the Parameters, so I couldn’t even enter valid data.  Easy enough to correct, but…

In the end, it was a surprisingly depressing experience.  It would be nice for CW to clean this up.  (Let me help – Delete them all!)  Hopefully things will pick up from the community (Brad?  Dan?  CW?  Anyone?).

Looks like y’all might have to rely on me a bit longer. 🙂

 

Uninstalling and Reinstalling Reporting Services

I finally got frustrated trying to figure out permissions in SSRS.  I appear to have users in all the right places, but still couldn’t get network accounts to run reports via the web interface.

My last ditch effort was to uninstall and reinstall SSRS.  We had problems with reporting a few months back and CW Support “did stuff” to fix the problem.  I was sure my current challenges stemmed from that.

I dug around to make sure the process was as simple as Add/Remove from Control Panel.  It is.  I found this article that’s pretty straightforward about any gotchas (and there is one!).  http://blogs.msdn.com/b/mariae/archive/2009/07/10/recommendations-when-uninstalling-installing-reporting-services.aspx

Following that, 3 things happened:

1) I still have permissions problems.  At least I’m to a position now where I can set up subscriptions and send e-mails, but can’t allow assigned network accounts to run on-demand reports.  I can set up a SQL User to sign in with and generate reports, but I want Windows Authentication so that step isn’t necessary for my Exec staff.  If you have a solution, please help!

2) I found this awesome write up from ConnectWise that fully documents the uninstall/reinstall process.  Saved me tons of time from having to create the document for this site.  You’ll need a CW account to sign in to get the document.  https://university.connectwise.com/University/Content/Training/SQL-Server-Reporting-Services-2008-Configuration-Standards.pdf

3) I broke CW’s internal reporting.  Oops!  Turns out I should have thought a bit more about the warning mentioned in the first link above.  CW keeps their reports in the ReportServer database.  It wasn’t just for mine.  Manually deleting that database to get a fresh start wasn’t a brilliant move.  Fortunately, I had a super-quick response from CW Support correcting my blunder.

I’m a half step further than when I started, but am still frustrated trying to figure out permissions.  It’s not critical to me now as I can generate and send reports, but will need to come back to this soon.

First report – The Filter!

Meet your new friend, The Filter:

A quick gotcha…. When you click the Add Filter icon, it defaults to the first Select Field in the list above.  Click on the Field Name in the Applied Filters box to select the field you want to filter on.  Unfortunately it returns all fields in the view, not just the fields you selected.  (I assume this means that you don’t have to select the fields for the report.  I could have probably selected fewer fields.)

After selecting the field, you set the operator.  These vary based on the type of field.  More trial and error.  (One thing I discovered is that you may want to set the opposite of what you want returned for testing.  It was easier for me to count all tickets updated since date X and compare that to the report that was generated here.  Tough to explain.)

Next, set the value(s) you want to filter on.  You’ll need the Data Dictionary or experience to know what to enter here.

Honestly, I wish I could remember everything I did to get to my final report.  Trust me; the trial and error was a lesson unto itself.  Some of the fields listed above were added after my report brought back too much information.  That’s your homework for this weekend!

Here are the filters I applied and why:

Closed_Flag is False – Remember?  Only bring back open tickets.
  If true, the ticket is Closed.  If false, the ticket is Open

Last_Update is less than or equal to (none)  PARAMETER (checked) –
The Parameter check box allows me to select a data (for this field type).  The report will show everything that is less than or equal to (that is older than or equal to) the date I select.

We’ll see this in action when we run the report.  For now remember that a Parameter allows you to select the values to filter on at some point in the future, not now.

(Something else for me to learn is how to set this so that the query can do its own math to determine everything that’s older than {today – 10 days}.  Soon…)

Board_Name is any of Systems, Data / Voice, Implementations – This returns just tickets on the service boards that I’m concerned with.  Adjust to match your  Service Boards.

This was one of those trial and error sections trying to figure out how to get the values entered correctly.  In the end I needed to enter them one at a time.

(This was made much more frustrating by the Report Builder crashing every time I went back to modify something! )

Parent is (blank) – Again, I only want to know about Parent tickets.  In our environment, we use Child Tickets to hide things.  If you put time on your Child Tickets, you don’t want to add this filter.

For reference, if it is a Child Ticket, the value of this field is the ticket number of the Parent.

(Since taking the screenshot above, I’ve added another field to filter on…)

Status_Description is none of Completed, Completed – Silent – Filters out tickets that are still Open, but marked with our Completed statuses.  I know there’s a “like” option available so I don’t have to list both Completed status.  Again, these statuses are unique to my company.  Enter your own.

Moving on… let’s start arranging the data on a report.

First report – Design the query

Moving on to the fun part…
Welcome to the “Design a query” window

The basics

Here’s what I’ve done for my report (Remember?  All tickets not updated in the last 10 days.)

The v_rpt_Service report tells me a bunch about open tickets. (Remember the Data Dictionary from earlier?)

The fields I selected are (probably more than I need, but I can come back and clean up the query or remove from the report in Design Mode… just a few clicks away!)

TicketNbr – The ticket number.  May not be necessary.
BusGroup – Not sure.  I’m thinking it may give me something to sort by.
Board_Name – I don’t want reports from all of our boards, just our support boards.  Having this available gives me something to filter and sort.
Status_Description – Current status of the ticket.
Closed_Flag – I learned long ago that you want to make sure you’re only returning Open tickets, not all 401448 tickets currently in the system.  We can filter on this field to narrow that down.
Service_Type – Not necessary, but I’ve added it to see if it’s useful to those that will be viewing the report
Company_Name – Again, not necessary, but might show where we’re dropping the ball.
Contact_Name – Ditto.
Summary – I want to display this so the assigned member has usable information to defend himself.
Team_Name – Probably not necessary.
Date_Entered – I want to display so we can see the age of the ticket.
Last_Update – The field that stores the information we want to view.
Updated_By – Who applied the last update.  I’m throwing this one in because I the Last_Update field can be modified by several things.  One of those is our Dispatcher pinging the engineer about the ticket.  If the report shows someone other than the engineer updating the ticket, I’m going to think that it has probably even been longer since the last update.

(This opens up something to think about…. If the ticket hasn’t been updated for 10 days and my dispatcher updates it yesterday for a status check, that ticket won’t show up on this report.  Not sure how to deal with that…)

Hours_Actual, Hours_Billable, Hours_NonBillable – Just because I want to know how much time is on the ticket.
Parent – I don’t want to return Child Tickets.
Resource_List – Shows all engineers assigned to a ticket

OK, now that I have the fields I want to display I need to make sure that I’m not returning half a million tickets.  Next we’ll use filters to narrow things down.

 

Not encouraging

I know I’ll be gleaning all of my knowledge from the Internet and fine readers like YOU, but thought I would jump over to Amazon to see what resources were available for SQL Report Builder.  Scrolling down, I hit this…. not sure what that’s telling me.

First report – Start the wizard and define the data source

1)      Create a New Report using the Table or Matrix Wizard

2)      Create your dataset (I’m trying to figure out how to save a dataset so I don’t have to create it for every new report…. Doing this slowly, I think I just saw the option for this on the previous screen…. A future post)

3)      Create the Data Source Connection

4)      Give the Data Source a name and click the Build… button

(I’m sure there’s a method to this.  Probably should name it appropriately so that it means something next time you select it.  As I learn more, I’m sure this will come together.  If you have guidance, please guide.)

5)      Enter Connection Properties for your ConnectWise database

You’ve successfully defined a Connection String

and Data Source Connection

Up next:  Build the query