October 24, 2011 at 8:24 pm
First post and ultimage noob. I have a report from a medical database. One of the columns is a the type of equipment that is used for the appointment (xray, ultrasound, mri, etc). I needed to get the sum of each equipment to show horizontally being grouped by the doctor's that had referred the patients. I was able to get the totals of the equipment by doing a sum(case when.... for each equipment. Because of that function, I'm forced to then Group by the two other columns, which are Doctors and the Appointment Dates. I want to use the dates as start and end date parameters. The prooblem that I'm running into when I run the report with multiple dates is that if a doctor had referred multiple days within the selected date range, the report shows up with blank spaces between each doctor. I'm trying to find a way to do the report without having to do a group by the date column. Like I said I'm new to SSRS and SQL for that matter. I attached a screen shot of a report with single dates and one with multiple dates.
October 25, 2011 at 6:02 am
I use the dates for the begindate and enddate parameters. I tried taking it out of that dataset and creating a seperate dataset, but the report doesn't recognize the parameter inputs unless the dates are on the main dataset. Is there a way to have a second dataset just for the parameters and have it linked to the first dataset?
October 25, 2011 at 6:06 am
Hi all,
Can any body tell
What is the main differences between File System deployment and SQL Server deployment
needs all major advantages and disadvantages...
Thanks,
pcsb
October 25, 2011 at 6:06 am
Hi all,
Can any body tell
What is the main differences between File System deployment and SQL Server deployment
needs all major advantages and disadvantages...
Thanks,
pcsb
October 25, 2011 at 6:06 am
Hi all,
Can any body tell
What is the main differences between File System deployment and SQL Server deployment
needs all major advantages and disadvantages...
Thanks,
pcsb
October 25, 2011 at 6:07 am
Hi all,
Can any body tell
What is the main differences between File System deployment and SQL Server deployment
needs all major advantages and disadvantages...
Thanks,
pcsb
October 25, 2011 at 6:14 am
wilmer 15225 (10/25/2011)
I use the dates for the begindate and enddate parameters. I tried taking it out of that dataset and creating a seperate dataset, but the report doesn't recognize the parameter inputs unless the dates are on the main dataset. Is there a way to have a second dataset just for the parameters and have it linked to the first dataset?
What about using a where clause in your query instead? That would solve your issue. If this is not desirable, you'll have to do the aggregation in reporting services instead (or too).
October 25, 2011 at 6:19 am
For SQL Server vs File System: http://pragmaticworks.com/help/dtsxchange/index.htm#page=FAQ%20-%20What%20are%20the%20advantages_disadvantages%20of%20storing%20SSIS%20packages%20to%20MSDB%20vs%20File%20System.htm.
What I think is missing here, is scalability. If you want to run the same package on/against several servers, I would definitely prefer a highly available file share.
October 25, 2011 at 6:35 am
okbangas (10/25/2011)
wilmer 15225 (10/25/2011)
I use the dates for the begindate and enddate parameters. I tried taking it out of that dataset and creating a seperate dataset, but the report doesn't recognize the parameter inputs unless the dates are on the main dataset. Is there a way to have a second dataset just for the parameters and have it linked to the first dataset?What about using a where clause in your query instead? That would solve your issue. If this is not desirable, you'll have to do the aggregation in reporting services instead (or too).
My query that I have on the dataset is below.... Are you saying that if I do the aggregation in the reporting services, that the rules between aggregation and group by can be broken? thanks for your help.
SELECT MWAPPTS.ADATE, CLREFER.NAME2,
SUM(CASE WHEN mwappts.book = 'XR_1_871' THEN 1 ELSE 0 END) AS XRAY,
SUM(CASE WHEN mwappts.book = 'DX_1_871' THEN 1 ELSE 0 END) AS DEXA,
SUM(CASE WHEN mwappts.book = 'MG_1_871' THEN 1 ELSE 0 END) AS MAMMO,
SUM(CASE WHEN mwappts.book = 'US_1_871' THEN 1 ELSE 0 END) AS US,
SUM(CASE WHEN mwappts.book = 'CT_1_871' THEN 1 ELSE 0 END) AS CT,
SUM(CASE WHEN mwappts.book = 'STAND_1_871' THEN 1 ELSE 0 END) AS UPRIGHT,
SUM(CASE WHEN mwappts.book = 'MRI_1_871' THEN 1 ELSE 0 END) AS MRI,
SUM(CASE WHEN mwappts.book = 'CT_1_871' OR
mwappts.book = 'MRI_1_871' OR
mwappts.book = 'XR_1_871' OR
mwappts.book = 'STAND_1_871' OR
mwappts.book = 'MG_1_871' OR
mwappts.book = 'US_1_871' OR
mwappts.book = 'DX_1_871' THEN 1 ELSE 0 END) AS Total
FROM MWAPPTS INNER JOIN
CLREFER ON MWAPPTS.REFERRAL = CLREFER.CODE
WHERE (MWAPPTS.ADATE BETWEEN @FIRSTDATE AND @LASTDATE)
GROUP BY CLREFER.NAME2, MWAPPTS.ADATE
October 25, 2011 at 7:46 am
Okbangas,
I took the date column out of the select statement and left the where statement in the query. that did it. Just learned something new. thanks for your help.
October 26, 2011 at 1:13 pm
The attachments have the names of doctors in Florida, a date range, and the equipment used in appointments. Depending on your source for this information and the accuracy of the data, this could be violating privacy laws. Be careful with healthcare data.
October 26, 2011 at 1:28 pm
That could've been a very costly oversight on my part. I appreciate the "look out".
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply