May 11, 2009 at 1:18 pm
Ola!
My name is telly. I've good SQL Server skills and i'm a bloody newbie with Reporting Services.
Thats why I have a Design- ans Implementation-Question about Reporting Services 2005.
I want to create a Report:
Januar Februar März
-------------------------------------------------------------------------------------------------
Fact A select count (*) select count (*) select count (*)
From Table a From Table a From Table a
Where month = 1 Where month = 2 Where month = 3
------------------------------------------------------------------------------------------------
Fact B select count (*) select count (*) select count (*)
From Table b From Table b From Table b
Where month = 1 Where month = 2 Where month = 3
---------------------------------------------------------------------------------------------------
Fact C select count (*) select count (*) select count (*)
From Table c From Table c From Table c
Where month = 1 Where month = 2 Where month = 3
--------------------------------------------------------------------------------------------------
How can i implement a report like these with Microsoft Visual Studio Report Designer? When i choose a table from the toolbox i can connect only to one Datasource and all columns of this datasource will be displayed! Then i can for one cell define something like this "=Count(Fields!Col1.Value, "MyDataSource")" but i how can i filter this column for Values with month 1,2 or 3?
Many Tnx for your Help!
Greetz, teLLy
May 12, 2009 at 12:40 pm
May 13, 2009 at 2:38 pm
Ola!
Tnx for yout Links. I read them and tried may way to get a report as i want. I found a solution but i think it is a worse workaround:
1) I create a DataSet
SELECT
(Select count(*)
FROM table1
WHERE Month(date_logged) = Month(dateadd(mm, 0, @AktuelleDatum)) and year(date_logged) = year(dateadd(mm, 0, @AktuelleDatum)) ) as month1,
(Select count(*)
FROM table1
WHERE Month(date_logged) = Month(dateadd(mm, -1, @AktuelleDatum)) and year(date_logged) = year(dateadd(mm, -1, @AktuelleDatum)) ) as month2,
(Select count(*)
FROM table1
WHERE Month(date_logged) = Month(dateadd(mm, -2, @AktuelleDatum)) and year(date_logged) = year(dateadd(mm, -2, @AktuelleDatum)) ) as month3
2) I linked in my table to the Values
=Sum(Fields!month1.Value, "ActualMonth")
=Sum(Fields!month2.Value, "ActualMonth")
=Sum(Fields!month3.Value, "ActualMonth")
Now it worked well but it lookslike not correct.
First Question: Someone know a better solution??
Second Question: Is it possible to create ONE SQL Statement to get records of 3 month and then get the "month-count" with a filter or somethiung else within the Reporting Service table?
Tnx for your Support!
teLLy
May 13, 2009 at 3:21 pm
Use this and then create a cross tab report in ssrs's reporting wizard:
Select count(*), Month(date_logged)
FROM table1
WHERE
Month(date_logged) between Month(dateadd(mm, 0, @AktuelleDatum)) and Month(dateadd(mm, -2, @AktuelleDatum))
and year(date_logged) between year(dateadd(mm, 0, @AktuelleDatum)) and year(dateadd(mm, -2, @AktuelleDatum))
group by date_logged
May 14, 2009 at 2:05 am
Ola!
I modify your SQL Statement
Select count(date_logged) as M_Count, Month(date_logged) as M_Month
FROM incident
WHERE
date_logged > '20090101' AND date_logged < dateadd(mm, +2, '20090101')
group by month(date_logged)
But now i get a table like this:
M_Count | M_Month
14 1
17 2
How can i access to the field with the "17"? I Need that, because my report lookslike:
Type | Jan | Feb
TYP_1 14 17
Furthermore i need another SQL Statement (or modify the Statement obove), because my report need more the one row!
Type | Jan | Feb
TYP_1 14 17
TYP_2 1 33
How can i handle these Problems? I think i need one Dataset per "Type" ?
Greetz and TnX
teLLy
EDIT:
I see for the first Problem i can use a cross tab. But for the second Problem?
May 15, 2009 at 12:39 pm
Reporting Services isn't so friendly to complex SQL. I usually create a database view or some kind of a temp table, so the reporting side doesn't need to move data around that much.
Even if you get the SQL in the dataset to work correctly, it would be faster to do the SQL in a view, rather than in the dataset.
If you're really looking to pivot across different fields, you might want to consider using Analysis Services to build a cube and then reference the cube from Reporting Services. Analysis services is much more flexible than Reporting Services.
Good luck
May 18, 2009 at 8:47 am
Select count(date_logged) as M_Count, datename(month,date_logged) as M_Month
FROM incident
WHERE
date_logged > '20090101' AND date_logged < dateadd(mm, +2, '20090101')
group by datename(month,date_logged)
just add the select for your "type" and you are good to go...you shouldn't need another data set.
May 20, 2009 at 1:48 am
Ola!
Tnx 4 your help but we dint find a solution. Thats why i try to specify my Problem. With
Select count(date_logged) as Inci_count, datename(month,date_logged) as Inci_Month
FROM incident
WHERE
date_logged > '20090101' AND date_logged '20090101' AND date_logged < dateadd(mm, +2, '20090101')
group by datename(month,date_logged)
i get
Prob_count | Prob_Month
---------------------------
5 Februar
19 Januar
Now i need a report
Categorie | Januar | Februar
-----------------------------------------
Incident 10 17
Problem 19 5
And than i need a lot of other row which based on onter Table (Change, Service Requests, etc)!
GreetzU
teLLy
May 20, 2009 at 7:53 am
You'll have to update the post with more information about the tables we are selecting from. You've given only a small amount of information about the 'Problem' table. I'll need it's table definition, minimally the column names and I'll need whatever table contains the 'Categorie' field. I want to help, but I can't do it with partial information. 😀
see this best practices article on how to post http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 20, 2009 at 11:01 am
Ok, i try to give you as much informition as you need. The Tables are much greater, i post only a part:
Table Incident:
-ID
-date_logged
-Text
Table Problem:
-ID
-date_logged
-Text
But Incident and Problem are Different tables.
The Category ist the name of the Table.
In my Scenario i have 10 Tables . Now i need for every Table a summary, how many new entries in each Table for every of the last 6 month! This Report shout display all Table activity in the last 6 month!
Category | Month-5 | Month-4 | Month-3 | Month-2 | Month-1 | Month |
Table 1 45 5 34 5 34 0
Table 2 4 3 4 5 12 2
Table 3 6 34 2 3 54 8
Table 4 422 43 12 1 1 19
Hope you will understand the scenario. If not, i will post more information tomorrow (because i'm now ouf of office)!
Tnx!
teLLy
May 27, 2009 at 5:16 pm
Create a query on the following lines:
Select * from (
select
ID,
date_logged,
Text,
'Incident' as TableName
Union
select
ID,
date_logged,
Text,
'Problem' as TableName)
Then Group by TableName on the Rows. This should work perfectly.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply