February 25, 2009 at 2:03 pm
I'll be the first to admit - I have no idea what I'm doing. I think what I'd like to do is fairly simple, but having no SQL experience (and little time to get what I need to do done), I'm about ready to pull my hair out. I've put this under BI because I'm using SSRS - I apologize if I've put this into the wrong area.
I'm trying to create some reports. In this report, I need to show counts. So far I know that this is right:
SELECT COUNT(*) AS P3Hardware
FROM MASTER6
WHERE (Type = 'hardware') AND (mrPRIORITY = '3')
This gives me a count of all of incidents that are Type=Hardware and Priority=3.
That's about the extent of my SQL/SSRS knowledge (sad, I know). I need to do the same sort of 'calculation' as above for our different Types and Priorities, and put them all into the same report. Can anyone give me an idea of what I need to do?
(If I haven't given you enough information, just let me know... thank you for any help you can offer!)
February 25, 2009 at 2:08 pm
Try this:
SELECT Type, mrPriority, COUNT(*) AS P3HardwareCount
FROM MASTER6
GROUP BY Type, mrPriority
See if that gets you closer to what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 2:26 pm
Ooooh, thanks! I can see all of my counts, so that's progress. *laughing*
Is there a way to separate each of the counts into their own variable? For example, right now when I execute it I can see:
Hardware, Priority 1 has a count of 16
Hardware, Priority 2 has a count of 28
Hardware, Priority 3 has a count of 338
Hardware, Priority 4 has a count of 65
but when I drag the P3HardwareCount into the layout and preview, it's giving me a total of 447. I would like to see separate counts for each priority...
By the end of this, I will also have to have similar counts for Software, Enterprise Application, etc...
(thank you, thank you!)
February 25, 2009 at 2:32 pm
I'm not a report designer, but I'm sure you can get the whole recordset to show on the report. I'm not sure why it would only show the total without showing all the rows.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 2:40 pm
Hey, you've gotten me this far - further than I've gotten on my own! I really appreciate the help. Maybe someone else will have an idea.
Thank you!
February 26, 2009 at 9:16 am
Hi,
I am not sure how you are getting the total for the count in the report, i guess you might be dragging and dropping the P3hardwareCount field in to a text box..
If you want to get all the P3hardwareCounts separately based on Priority and Type, you can insert a table in to the Body of the report and then group by "Priority" and then add a parent group with "Type"..then put the count field in the details /data box
finally i hope u ll get the report structure like this:
Type Priority Count
Hardware
1 16
2 28
3 338
4 65
Software
1 ##
2 ###
3 ##
Enterprise Applications
1 ?
2 ??
February 26, 2009 at 12:28 pm
Hi ARP,
Thanks! That worked.
Now, if I can only figure out why I can't get a date range to work... I'll probably be back with more questions. 🙂
February 26, 2009 at 12:34 pm
Glad we could help.
What trouble are you having with date ranges?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2009 at 12:47 pm
*laughing* I'm glad you all were able to help, too!!
I'm getting an error message when I try to preview the report in SSRS and enter in the StartDate and EndDate:
"An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the data set FootPrints.
Conversion failed when converting the nvarchar value '2/2/2009' to data type int."
I'm using the Filter that they show in the book I'm learning from (>= @StartDate AND < @EndDate +1), but no go.
What I find strange is 2/2/2009 is what I entered for the EndDate. At first I thought I might have entered the date in the wrong format, but then I should see an issue with the StartDate in the error message...
February 26, 2009 at 12:51 pm
Have you set the parameters to be DateTime? It sounds like SSRS is treating them as something other than dates.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2009 at 1:08 pm
Hrm... well, no, I haven't set the parameters to be DateTime, but I don't think I need to. I've been playing around in SSRS and I think this might be an SSRS 'thing' as opposed to a SQL 'thing'. I just figured out that I can execute the SQL expression in the Data screen, enter my dates, and it shows up with values in the Results pane - I only seem to get the error when I try to View the report in the Preview screen.
Weeeeird.
February 26, 2009 at 2:18 pm
That's what I'm talking about. In the SSRS Report Parameters area - set it to be dateTime parameters. The Endate+1 is trying to be evaluated before being passed into the SQL as a query, and it doesn't understand whether the result of that addition should be varchar, or Int (answer - neither, since it should be datetime).
SSRS is very strange, and it takes some time to figure out what piece is doing the processing at any given time.
I'd also steer clear of the filters unless absolutely need it there. put it into the data screen's query instead and it should work a LOT better.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2009 at 2:51 pm
...whooboy. Sorry, but you've kind of gone over my head, now! (I thought I was doing good when I managed to get the program open and connect to the right table!) I understand what you're saying about the parameters, but I'm not sure where to set it.
When I was trying to type the info in for the StartDate and EndDate in the query screen, I ended up getting errors. I'm going to go out on a limb here and say that it's because my SQL knowledge is very, very limited and I just don't know what I'm doing - it was probably some sort of formatting issue.
I definitely agree with SSRS being very strange... I took OUT the info for the date search since it wasn't working for me anyway, and now it doesn't prompt me for it in the dates when I execute the SQL expression, but it's still showing the StartDate and EndDate fields in the Preview screen. *sigh*
I don't know if this will help at all, but this is what is in the SQL pane now... I was trying to add the StartDate and EndDate information into the WHERE statement.
SELECT Type, mrPRIORITY, COUNT(*) AS TypeCount
FROM MASTER6
WHERE (mrPRIORITY <= '4') AND (Type = 'Desktop__bSoftware') OR
(mrPRIORITY <= '4') AND (Type = 'Enterprise__bApplication') OR
(mrPRIORITY <= '4') AND (Type = 'Hardware') OR
(mrPRIORITY <= '4') AND (Type = 'System__bAccess__bProblem') OR
(mrPRIORITY <= '4') AND (Type = 'Telephone') OR
(mrPRIORITY <= '4') AND (Type = 'User__bAccess__b__f__bSecurity')
GROUP BY Type, mrPRIORITY
ORDER BY Type, mrPRIORITY
Thanks for staying with me on this one... I wouldn't blame you if you just gave up on me. 🙂 I'm sure that this all seems pretty basic to someone who has an idea of what they're doing!
February 26, 2009 at 3:05 pm
Try this as your query.
SELECT Type, mrPRIORITY, COUNT(*) AS TypeCount
FROM MASTER6
WHERE (mrPRIORITY <= '4') AND
Type IN (
'Desktop__bSoftware',
'Enterprise__bApplication',
'Hardware',
'System__bAccess__bProblem',
'Telephone',
'User__bAccess__b__f__bSecurity'
) AND
(
mydatecolumn >=@startDate AND
mydatecolumn <@endate+1
)
GROUP BY Type, mrPRIORITY
ORDER BY Type, mrPRIORITY
You need to update the script with whatever your actual date column is called (sorry - didn't see that anywhere).
Also - make sure you update the parameters to be date values (in the Report Parameters screen - change the 2nd drop-down to be Date). When you switch to the preview screen - you should see the little calendar thingie to the right of each of the params if you got them set up the right way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 26, 2009 at 4:30 pm
...Excellent! 😀
That seems to have worked! I'm shuttin' it down for the day, but I'll take another look at this tomorrow. Thank you!
(...and I'm sure I'll be back...) 🙂
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply