hello,
i have a table with different columns
my table has 1 group (group per hour)
my last column of my table is a calculation of different values from that line.
in my footer of my column i want to display the max value of that column.
somehow i can't manage to achieve this.
this i an example of my table (how i want i to look like)
hour | ColA | ColB | ColC | ColD
00 15 14 16 16
01
02
03 20 21 23 23
....
23 55 16 75 75
90 51 114 75
ColD is the max value of ColA, ColB and ColC.
The footer contains the sumed values for A, B and C
In the footer for ColD i would like to display the max value of ColD (in this case 75).
This may be a stupid question, but i'm stuck on it ..
Vinnie
Here the query,
Code Snippet
Create Table #data (
[hour] Varchar(100) ,
[ColA] int ,
[ColB] int ,
[ColC] int ,
[ColD] int
);
Insert Into #data Values('00','15','14','16',NULL);
Insert Into #data Values('03','20','21','23',NULL);
Insert Into #data Values('23','55','16','75',NULL);
Select
hour,
max(case when colid=1 Then case when hour is not null then data1 else data2 end end) as [cola],
max(case when colid=2 Then case when hour is not null then data1 else data2 end end) as [colb],
max(case when colid=3 Then case when hour is not null then data1 else data2 end end) as [colc],
max(case when colid=4 Then data1 end) as [cold]
from
(
select
hour,
Isnull(id,4) colid,
Max(Case When id=1 Then cola
When id=2 Then colb
When id=3 Then colc End) data1,
Sum(Case When id=1 Then cola
When id=2 Then colb
When id=3 Then colc End) data2
from
#data
cross join
(select 1 id Union Select 2 Union Select 3) as d
Group By
hour,id
With Cube
) as Data
Group by
hour
Order By
isnull(hour,99999)
|||
Hi,
my problem is with a table in reporting services.
my data already comes from a dataset.
so my question is how to retreive the max value of a table column.
also, i work with reporting models and not with query based datasets.
but that has nothing to do with my question.
Greetings
Vinnie
|||
How much of this table have you implemented successfully? Everything but the last value at the bottom right?
Are you using report builder or Business Intelligence Studio?
|||
Hello Greg,
Thanks for the answer.
I am using visual studio to build my reports.
The table itself works fine. The only thing i cannot accomplish is to
have the max value of that specific table column in my footer.
Is what i'm asking impossible to do or am i that stupid that it's right under
my nose and cannot figure it out ?
any help is welcome.
Greetings
Vinnie
|||
VSempoux wrote:
Hello Greg,
Is what i'm asking impossible to do or am i that stupid that it's right under
my nose and cannot figure it out ?
Neither.
VSempoux wrote:
also, i work with reporting models and not with query based datasets
I don't understand this. So there is no query to generate your report? You said you weren't using report builder, so how could you be using reporting models?
We have to understand how you are going about this before we can help you. The only way I know of how to get that max value is to write it into a query....
|||Okay Greg,
Let me clarify my situation.
I am writing reports for an application that is in dev for the moment.
We want to give our customers the opportunity to also create their own report, based on the data that
we use in our standard reports that are supplied with the app.
So, in order to don't have to do the work twice, i am making report models that i use in my reports.
With VS or BIS you can choose your original datasource, so in my case it is a report model.
My orignal report models are based on queries that i have put into views.
So in my report model data source view i select all the data from the sql view and from that i generate
my model. Afterwards i add some calculated fields in the model itself, if i have the need for them.
Then in the report itself i define datasets.
But for my datasets i can only do drag and drop the fields (enteties) i need in my report.
Of course i can define filters for the datasource.
Does this give you a better insight into my situation ?
If it would make your life easier, i could sent you some screenshots ...
Greetings
Vinnie
|||
VSempoux wrote:
My orignal report models are based on queries that i have put into views.
Then I suppose the answer is you need to create a field in the model with a query that selects the max of the dataset that produces ColA, ColB and ColC.
When I say dataset here, I am not talking about the SQL dataset. I am talking about the collection of data. It really makes no difference whether it comes from a dataset, a view, or a donkey for that matter.
hour | ColA | ColB | ColC | ColD
00 15 14 16 16
01
02
03 20 21 23 23
....
23 55 16 75 75
90 51 114 75
|||Hi,
I already have such a field and it's doing what it's suposed to do.
But this field is set into the table group (remember the group that groups per hour)
So, when my table is displayed after generation, i have 24 lines with in the last column the max values
of several columns.
example :
this is how my table layout looks like
table group= hour(Fields!TimeValue.value) -> this groups all my data per hour
table group header contains 7 colums
column 1 : hour(Fields!TimeValue.value) sorted ascending
column 2-3-4-5-6 contains a calculation like this : (sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value))*100
this returns a ratio value
column 7 contains an expression that gives me max value of column 2-3-4-5. this is the expression i use :
=CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100))))))
This expression returns the highest ratio value for a specif hour.
At the end i have 24 values in column 7, each time the highest value per hour.
In my table footer i cannot use the expression above, because this will calculate the highest value for the total 24 hours (which is of course different from the highest single value per hour).
Do you see now what i want to do ?
Since i do various other calculations inside my report with fields like Fields!CountTest1.value, .... i cannot do this (i think) in my sql view.
Fields!CountTest1 is also a calculated field within my dataset (=iif(Fields!TestState.Value=101 and Fields!ContactTotalTest.Value=1,1,0))
Soooooo, the only simple thing i want ... the max value, of the generated max values per hour, in my table footer !
I don't know how i can make this more clear.
Greetings
Vinnie
|||See this post:http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1&mode=1
Ian
No comments:
Post a Comment