March 12, 2012 at 11:02 am
Hi All,
I am creating a line graph that charts MTD, Prior month, and Prior Prior Month new orders by date. Ideally, the X-Axis would show business days and the Y-Axis show the # of orders on that date. The 3 months we are looking at would each have their own line. This is my query so far:
SELECTFM.FileNumber,
DAY(FM.OpenedDate) AS OpenedDate,
'MTD' AS Type
FROMFileMain FM
WHEREFM.ClientID !=3
AND FM.FileNumber NOT LIKE 'PA-%'
AND FM.OpenedDate >= @MTD
AND FM.OfficeID = 1
UNION ALL
SELECTFM.FileNumber,
DAY(FM.OpenedDate) AS OpenedDate,
'PM' AS Type
FROMFileMain FM
WHEREFM.ClientID !=3
AND FM.FileNumber NOT LIKE 'PA-%'
AND FM.OpenedDate >= @PMonth
AND FM.OpenedDate < @PMonthEnd
AND FM.OfficeID = 1
UNION ALL
SELECTFM.FileNumber,
DAY(FM.OpenedDate) AS OpenedDate,
'PPM' AS Type
FROMFileMain FM
WHEREFM.ClientID !=3
AND FM.FileNumber NOT LIKE 'PA-%'
AND FM.OpenedDate >= @PPMonth
AND FM.OpenedDate < @PPMonthEnd
AND FM.OfficeID = 1
This Query shows all orders within the date ranges. Each "Type" would be a different line on the chart. I have little experience with charts in SSRS so any help would be appreciated. Thank you.
March 13, 2012 at 2:54 am
Are you wanting the number of orders per day for a month and then passing that back into the report.
I would say you would want a type of count in there which groups by the date for each type something like the below
SELECT COUNT(FM.FileNumber) AS NumOrders,
DAY(FM.OpenedDate) AS OpenedDate,
'MTD' AS Type
FROM FileMain FM
WHERE FM.ClientID !=3
AND FM.FileNumber NOT LIKE 'PA-%'
AND FM.OpenedDate >= @MTD
AND FM.OfficeID = 1
GROUP BY DAY(FM.OpenedDate)
Then you would want to pass this in the the chart giving NumOrders as the value, OpenedDate as the Category and Type as the group
March 13, 2012 at 7:19 am
Thank you. I was able to figure it out late last night. The key was using runningvalue() in BI Studio to plot the total orders for the month and group them by the type (mtd, pm, etc). Thanks for your help.
March 13, 2012 at 7:22 am
yep that would work, one thing I would try though is doing the count in the data set in one report and doing the runningtotal in another and see if there is any time difference.
seen occasions where its slower to do it in BIDS than it is do to it in SQL.
not used runningtotal so cant say the performance aspects on it.
March 13, 2012 at 7:24 am
I'll try that out. thanks for the heads up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply