Grouping by defined number of days.

This is a question about custom grouping by a defined number of days. I
would like to have a start date, then group the data in 3 day blocks. Is
this possible ' Ideally, I would like to have a start date, end date, and
all the little intervals in-between, even if no data is in those intervals.
CREATE TABLE [dbo].[YourTable] (
[dt] [datetime] NOT NULL ,
[ev] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
I was screwing around with the datepart() function, but that did not work.
This does a grouping by hour.
Select ev,DATEPART(hh,dt),count(*)
From YourTable
Group by ev,DATEPART(hh,dt)
In this example, say the start date was 2/10/2004 and the interval was 3
days, Ithink the output would be something like
interval # ev count
1 2 2
2 1 1
3 null 0
For the third result record listed above, if this cannot be done easily,
that is . I can work around it in code. I am really concerned with
getting the interval number and the count of EV's
Thanks for your time.SELECT DATEDIFF(dd, '20040210', dt)/3 + 1 AS Interval, ev, COUNT(*)
FROM YourTable
GROUP BY DATEDIFF(dd, '20040210', dt)/3 + 1, ev
ORDER BY Interval
You can get the missing intervals with a numbers table:
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
SELECT n.Number, yt.ev, COUNT(*)
FROM Numbers n
LEFT OUTER JOIN YourTable yt
ON n.Number = DATEDIFF(dd, '20040210', yt.dt)/3 +1
GROUP BY n.Number, yt.ev
HAVING n.Number <= (SELECT MAX( DATEDIFF(dd, '20040210', dt)/3 + 1) FROM
YourTable)
ORDER BY n.Number
Jacco Schalkwijk
SQL Server MVP
"Jack" <jack@.jack.net> wrote in message
news:g21qe.7805$R21.1536@.lakeread06...
> This is a question about custom grouping by a defined number of days. I
> would like to have a start date, then group the data in 3 day blocks. Is
> this possible ' Ideally, I would like to have a start date, end date,
> and all the little intervals in-between, even if no data is in those
> intervals.
> CREATE TABLE [dbo].[YourTable] (
> [dt] [datetime] NOT NULL ,
> [ev] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO YourTable VALUES ('2004-02-12T09:00:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-14T10:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-12T09:30:00.000', 2)
> INSERT INTO YourTable VALUES ('2004-02-22T11:00:00.000', 1)
> INSERT INTO YourTable VALUES ('2004-02-27T11:05:00.000', 1)
>
> I was screwing around with the datepart() function, but that did not work.
> This does a grouping by hour.
> Select ev,DATEPART(hh,dt),count(*)
> From YourTable
> Group by ev,DATEPART(hh,dt)
> In this example, say the start date was 2/10/2004 and the interval was 3
> days, Ithink the output would be something like
> interval # ev count
> 1 2 2
> 2 1 1
> 3 null 0
> For the third result record listed above, if this cannot be done easily,
> that is . I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
>|||On Thu, 9 Jun 2005 15:05:12 -0400, Jack wrote:

> For the third result record listed above, if this cannot be done easily,
> that is . I can work around it in code. I am really concerned with
> getting the interval number and the count of EV's
> Thanks for your time.
I'm not sure I understand where you get your interval numbers... Here's
what I got from your sample data:
declare @.startdate datetime
set @.startdate='2004-02-10'
Select Floor(Datediff(d,@.startdate,dt)/3) "Interval #", ev,count(*) "Count"
From YourTable
GROUP BY ev, Floor(Datediff(d,@.startdate,dt)/3)
ORDER BY ev, Floor(Datediff(d,@.startdate,dt)/3)
Interval # ev Count
-- -- --
1 1 1
4 1 1
5 1 1
0 2 2

Grouping and Filters

Hi everyone

I am using SSRS2005 with an SSAS cube building in BI

I need to create a custom grouping. Here's what i mean:

I give my period parameter some default Values. Like :
Period = 200501,200502,200601,200602
Now when building the report, I filter 2 Tables on the 2 years respectively.
Grouped by Period

So one table list all the Measures for 2005 and the other for 2006.
Now I want to use a Chart to Display the two totals. I can only get the Chart to
display the by monthley periods. IE:

30 o
|--|-||
20 o o
|--|-||
10 o
|--|-||
200501 200502 200601 200602 (instead of 2005 and 2006 as I need)

I need to create a grouping by which i can tell the chart what data to use.
I can't group by period.year because the Period field is an Integer

Any help is greatly appreciated
If I am unclear about anything please point it out to me

Thanks in advance
Gerhard Davids

Ok

So I sorted this out and it seems I was being really retarded.

I used the following statments in the grouping of the Chart.

Series group : =iif(Left(CStr(Fields!Period.Value),4) = "2004", 2004, iif(Left(CStr(Fields!Period.Value),4) = "2005",2005,iif(Left(CStr(Fields!Period.Value),4) = "2006",2006,Nothing)))

Category group : =iif(Right(Cstr(Fields!Period.Value),2) = "01" ,01,iif(Right(Cstr(Fields!Period.Value),2) = "02",02,iif(Right(Cstr(Fields!Period.Value),2) = "03",03,iif(Right(Cstr(Fields!Period.Value),2) = "04",04,iif(Right(Cstr(Fields!Period.Value),2) = "05",05,iif(Right(Cstr(Fields!Period.Value),2) = "06",06,iif(Right(Cstr(Fields!Period.Value),2) = "07",07,iif(Right(Cstr(Fields!Period.Value),2) = "08",08,iif(Right(Cstr(Fields!Period.Value),2) = "09",09,iif(Right(Cstr(Fields!Period.Value),2) = "10",10,iif(Right(Cstr(Fields!Period.Value),2) = "11",11,iif(Right(Cstr(Fields!Period.Value),2) = "12",12,iif(Right(Cstr(Fields!Period.Value),2) = "13",13,Nothing)))))))))))))

This allowed it to group the periods together but seperate for each year
and in the series explanation it gave me the total for
each year respectiveley.

In the data section I then simply sumed my measure

G

Grouping and Custom Code

Hello everyone,

I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.

TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal

I have the following in my Code window.

Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function

This gives me incorrect results and I can't figure out why. Here's how it shows on my report:

Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00

The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.

As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?

Jarret

Hi Jarret,

The reason for seeing 0 (I think) Is that after a group ends, Reporting Services basically creates a new instance of your custom code and therefore any saved values get cleared.

I am not sure how your GF1 shows a value though... I could be wrong, but this is the experience I have had...

Regards,
Neil

|||The way I approached it...

I ordered my duplicate values... or some way of identifying that the value was not needed, and if the previous item = that item then do not add it to the total... Then for each footer call the same code and passing in the same values.

So each footer will be identical ... passing in the value and some other way of identifying if the value is unique...

Hope this helps...

Regards,
Neil

GroupExpressions and Aggregate()

I'm using Aggregate() with a custom data extension implementing the IDataReaderExtension interface, to make RS use my
own calculation of subtotals for matrices and lists, and this works just fine as long as there is only one
<GroupExpression> tag within each <GroupExpressions> tag, e.g.:
<MatrixRows>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping>
<GroupExpressions>
<GroupExpression>=Fields!Year.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems><Textbox><Value>=Fields!Year.Value</Value></Textbox></ReportItems>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping>
<GroupExpressions><GroupExpression>=Fields!Quarter.Value</GroupExpression></GroupExpressions>
</Grouping>
<ReportItems><Textbox><Value>=Fields!Quarter.Value</Value></Textbox></ReportItems>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping>
<GroupExpressions><GroupExpression>=Fields!Month.Value</GroupExpression></GroupExpressions>
</Grouping>
<ReportItems><Textbox><Value>=Fields!Month.Value</Value></Textbox></ReportItems>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<MatrixRows>
But when I don't want to show an intermediate grouping, like Quarter in the above, but still want to group by it, the
matrix cells are blank:
<MatrixRows>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping>
<GroupExpressions>
<GroupExpression>=Fields!Year.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems><Textbox><Value>=Fields!Year.Value</Value></Textbox></ReportItems>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<DynamicRows>
<Grouping>
<GroupExpressions>
<GroupExpression>=Fields!Quarter.Value</GroupExpression>
<GroupExpression>=Fields!Month.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems><Textbox><Value>=Fields!Month.Value</Value></Textbox></ReportItems>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<MatrixRows>
The dataset is identical, and returns the same results for IsAggregateRow etc.
Can someone please tell me what I'm doing wrong or misunderstanding?
Thanks!
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Currently this is by design. Aggregate function only works when there is
only one group expression and it must be simple field reference.
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Olaf Ollgaard" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:O7PuZUCYEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I'm using Aggregate() with a custom data extension implementing the
IDataReaderExtension interface, to make RS use my
> own calculation of subtotals for matrices and lists, and this works just
fine as long as there is only one
> <GroupExpression> tag within each <GroupExpressions> tag, e.g.:
> <MatrixRows>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping>
> <GroupExpressions>
> <GroupExpression>=Fields!Year.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
>
<ReportItems><Textbox><Value>=Fields!Year.Value</Value></Textbox></ReportIte
ms>
> </DynamicRows>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping>
>
<GroupExpressions><GroupExpression>=Fields!Quarter.Value</GroupExpression></
GroupExpressions>
> </Grouping>
>
<ReportItems><Textbox><Value>=Fields!Quarter.Value</Value></Textbox></Report
Items>
> </DynamicRows>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping>
>
<GroupExpressions><GroupExpression>=Fields!Month.Value</GroupExpression></Gr
oupExpressions>
> </Grouping>
>
<ReportItems><Textbox><Value>=Fields!Month.Value</Value></Textbox></ReportIt
ems>
> </DynamicRows>
> </RowGrouping>
> </RowGroupings>
> <MatrixRows>
> But when I don't want to show an intermediate grouping, like Quarter in
the above, but still want to group by it, the
> matrix cells are blank:
> <MatrixRows>
> <RowGroupings>
> <RowGrouping>
> <DynamicRows>
> <Grouping>
> <GroupExpressions>
> <GroupExpression>=Fields!Year.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
>
<ReportItems><Textbox><Value>=Fields!Year.Value</Value></Textbox></ReportIte
ms>
> </DynamicRows>
> </RowGrouping>
> <RowGrouping>
> <DynamicRows>
> <Grouping>
> <GroupExpressions>
> <GroupExpression>=Fields!Quarter.Value</GroupExpression>
> <GroupExpression>=Fields!Month.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
>
<ReportItems><Textbox><Value>=Fields!Month.Value</Value></Textbox></ReportIt
ems>
> </DynamicRows>
> </RowGrouping>
> </RowGroupings>
> <MatrixRows>
> The dataset is identical, and returns the same results for IsAggregateRow
etc.
> Can someone please tell me what I'm doing wrong or misunderstanding?
> Thanks!
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Group Total..

Thanks Brian.
Can you give me an example of using a custom field and using the Recusrive
keyword? If there is a sample in BOL, then pls provide any reference/links
(I wasn't able to locate any help on this topic)
appreciate it.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> While I appreciate your eagerness to help, I think that most people would
> prefer that you refrain from advertising other products in a forum
dedicated
> to SQL Server Reporting Services. If you start a SIMX newsgroup, I promise
> not to post there. :)
>
> That being said, you should be able to define a custom field that does the
> calculation and then referce the custom field in a sum in the group
footer.
> Presumably, you only need to add values from the inner group as the outer
> group is just summary. If you want it to do parent / child hierarcy
> aggregates, you need to use the recursive keyword.
>
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >I recently came across a new software, that I think you might want to
look
> >into.
> >
> > www.simx.com/simx/home_report%20manager.htm
> >
> > Works with SQL Server, and I was able to do reporting much like what you
> > are describing.
> >
> > "newmem" <"" wrote:
> >
> >> I'm working on a Financial Report which contains a column "XYZ" , its
> >> value
> >> is calculated from a formula by passing the row's record id and
> >> commission
> >> rate. (the formula is inside a custom dll). The values are correctly
> >> computed. Now, the footer should display the total of all the rows in
the
> >> group.
> >> for instance:
> >>
> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> Sodas
> >> Pepsi $361,000 gfyeefyefffee
> >> Coca Cola $475,250 djfdfjdfddddd
> >> RCola $28,757 re8reruejreerr
> >>
> >> fdfsfnfsfssf
> >> _________________________________________
> >> Total: $ 865,007
> >>
> >> Each of the "XYZ Total" in the above example, uses an expression as = > >> FindTotal(recID!value, comm_rate!value)
> >> In this case, how do I get the total in the footer? How to recursively
> >> add
> >> the FindTotal expression when it contains the row's unique record id?
> >>
> >> Thanks
> >>
> >> P.S. The above data is a sample data. The actual report contains 3
> >> different
> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> Transaction Title
> >>
> >>
> >>
>
>Custom field is available when you right click on the fields window in
Report Designer and click add. Type whatever expression you want. For
recursive functions, you add the keyword recursive as the last parameter of
the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message
news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Thanks Brian.
> Can you give me an example of using a custom field and using the Recusrive
> keyword? If there is a sample in BOL, then pls provide any reference/links
> (I wasn't able to locate any help on this topic)
> appreciate it.
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
>> While I appreciate your eagerness to help, I think that most people would
>> prefer that you refrain from advertising other products in a forum
> dedicated
>> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
>> promise
>> not to post there. :)
>> That being said, you should be able to define a custom field that does
>> the
>> calculation and then referce the custom field in a sum in the group
> footer.
>> Presumably, you only need to add values from the inner group as the outer
>> group is just summary. If you want it to do parent / child hierarcy
>> aggregates, you need to use the recursive keyword.
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Brian" <Brian@.discussions.microsoft.com> wrote in message
>> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>> >I recently came across a new software, that I think you might want to
> look
>> >into.
>> >
>> > www.simx.com/simx/home_report%20manager.htm
>> >
>> > Works with SQL Server, and I was able to do reporting much like what
>> > you
>> > are describing.
>> >
>> > "newmem" <"" wrote:
>> >
>> >> I'm working on a Financial Report which contains a column "XYZ" , its
>> >> value
>> >> is calculated from a formula by passing the row's record id and
>> >> commission
>> >> rate. (the formula is inside a custom dll). The values are correctly
>> >> computed. Now, the footer should display the total of all the rows in
> the
>> >> group.
>> >> for instance:
>> >>
>> >> "Unit" "BrandName" "XYZ Total" "Comments"
>> >> Sodas
>> >> Pepsi $361,000 gfyeefyefffee
>> >> Coca Cola $475,250 djfdfjdfddddd
>> >> RCola $28,757 re8reruejreerr
>> >>
>> >> fdfsfnfsfssf
>> >> _________________________________________
>> >> Total: $ 865,007
>> >>
>> >> Each of the "XYZ Total" in the above example, uses an expression as =>> >> FindTotal(recID!value, comm_rate!value)
>> >> In this case, how do I get the total in the footer? How to recursively
>> >> add
>> >> the FindTotal expression when it contains the row's unique record id?
>> >>
>> >> Thanks
>> >>
>> >> P.S. The above data is a sample data. The actual report contains 3
>> >> different
>> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> >> Transaction Title
>> >>
>> >>
>> >>
>>
>|||I was able to add the custom field and assign the expression for the
calculation with the inner group as scope.
Hwever, I need to sum unique dollar amount for each of the first row in
the inner group. Right now , its adding all the dollar amounts in the
group1.
The sample data below would give a better overview.
The database contains different rec IDs for the "Regular" and the "Diet"
Category. But the report should display the amounts only once, if the
category exitss for a soda then only the name of the category is displayed
in the second level.
"Unit" "BrandName" "XYZ Total" "Comments"
Sodas
=> Group1
Pepsi Regular $361,000 gfyeefyefffee
=> Group2
Diet
ffrfrefegfegegeg => Group 3
_____________
Pepsi Totals: $361,000
Coca Cola Regular $475,250 djfdfjdfddddd
Diet
tefdnfdfdjgdgdgd
Vanila $20,000
dewrwrwrwrwrwr
__________
Coca Cola Totals: $495,250
RCola $28,757
re8reruejreerr
==============================================Soda Totals: $885,007
When I use the custom expression to get the group total, for example in case
of "Pepsi", i get the total as $ 722,000. whch is incorrect.
Can you suggest a way to handle this case?
Thanks. Appreciate all the help.
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:uyOAmreXEHA.736@.TK2MSFTNGP10.phx.gbl...
> Custom field is available when you right click on the fields window in
> Report Designer and click add. Type whatever expression you want. For
> recursive functions, you add the keyword recursive as the last parameter
of
> the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "newmem" <""> wrote in message
> news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> > Thanks Brian.
> > Can you give me an example of using a custom field and using the
Recusrive
> > keyword? If there is a sample in BOL, then pls provide any
reference/links
> > (I wasn't able to locate any help on this topic)
> >
> > appreciate it.
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
> >> While I appreciate your eagerness to help, I think that most people
would
> >> prefer that you refrain from advertising other products in a forum
> > dedicated
> >> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
> >> promise
> >> not to post there. :)
> >>
> >> That being said, you should be able to define a custom field that does
> >> the
> >> calculation and then referce the custom field in a sum in the group
> > footer.
> >> Presumably, you only need to add values from the inner group as the
outer
> >> group is just summary. If you want it to do parent / child hierarcy
> >> aggregates, you need to use the recursive keyword.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> >>
> >> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> >> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
> >> >I recently came across a new software, that I think you might want to
> > look
> >> >into.
> >> >
> >> > www.simx.com/simx/home_report%20manager.htm
> >> >
> >> > Works with SQL Server, and I was able to do reporting much like what
> >> > you
> >> > are describing.
> >> >
> >> > "newmem" <"" wrote:
> >> >
> >> >> I'm working on a Financial Report which contains a column "XYZ" ,
its
> >> >> value
> >> >> is calculated from a formula by passing the row's record id and
> >> >> commission
> >> >> rate. (the formula is inside a custom dll). The values are correctly
> >> >> computed. Now, the footer should display the total of all the rows
in
> > the
> >> >> group.
> >> >> for instance:
> >> >>
> >> >> "Unit" "BrandName" "XYZ Total" "Comments"
> >> >> Sodas
> >> >> Pepsi $361,000 gfyeefyefffee
> >> >> Coca Cola $475,250 djfdfjdfddddd
> >> >> RCola $28,757 re8reruejreerr
> >> >>
> >> >> fdfsfnfsfssf
> >> >> _________________________________________
> >> >> Total: $ 865,007
> >> >>
> >> >> Each of the "XYZ Total" in the above example, uses an expression as
=> >> >> FindTotal(recID!value, comm_rate!value)
> >> >> In this case, how do I get the total in the footer? How to
recursively
> >> >> add
> >> >> the FindTotal expression when it contains the row's unique record
id?
> >> >>
> >> >> Thanks
> >> >>
> >> >> P.S. The above data is a sample data. The actual report contains 3
> >> >> different
> >> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
> >> >> Transaction Title
> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>|||Something got mangled in your posting so I don't quite understand what you
are looking for. It seems like you just need to hide the group header row
and drop the group label down to the inner group and set 'hide repeating' on
the group name. And just make sure you are not computing sums in your query.
You can let the report engine do them for you.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"newmem" <""> wrote in message news:uqcyNIhXEHA.712@.TK2MSFTNGP11.phx.gbl...
>I was able to add the custom field and assign the expression for the
> calculation with the inner group as scope.
> Hwever, I need to sum unique dollar amount for each of the first row in
> the inner group. Right now , its adding all the dollar amounts in the
> group1.
> The sample data below would give a better overview.
> The database contains different rec IDs for the "Regular" and the "Diet"
> Category. But the report should display the amounts only once, if the
> category exitss for a soda then only the name of the category is displayed
> in the second level.
> "Unit" "BrandName" "XYZ Total" "Comments"
> Sodas
> => Group1
> Pepsi Regular $361,000 gfyeefyefffee
> => Group2
> Diet
> ffrfrefegfegegeg => Group 3
> _____________
> Pepsi Totals: $361,000
> Coca Cola Regular $475,250 djfdfjdfddddd
> Diet
> tefdnfdfdjgdgdgd
> Vanila $20,000
> dewrwrwrwrwrwr
> __________
> Coca Cola Totals: $495,250
> RCola $28,757
> re8reruejreerr
> ==============================================> Soda Totals: $885,007
> When I use the custom expression to get the group total, for example in
> case
> of "Pepsi", i get the total as $ 722,000. whch is incorrect.
> Can you suggest a way to handle this case?
> Thanks. Appreciate all the help.
>
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:uyOAmreXEHA.736@.TK2MSFTNGP10.phx.gbl...
>> Custom field is available when you right click on the fields window in
>> Report Designer and click add. Type whatever expression you want. For
>> recursive functions, you add the keyword recursive as the last parameter
> of
>> the aggregate, i.e. Sum(Fields!Sales.Value, "Scope", recursive).
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "newmem" <""> wrote in message
>> news:%23FAJuQRXEHA.1656@.TK2MSFTNGP09.phx.gbl...
>> > Thanks Brian.
>> > Can you give me an example of using a custom field and using the
> Recusrive
>> > keyword? If there is a sample in BOL, then pls provide any
> reference/links
>> > (I wasn't able to locate any help on this topic)
>> >
>> > appreciate it.
>> >
>> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
>> > news:OxzDQD1WEHA.3972@.TK2MSFTNGP12.phx.gbl...
>> >> While I appreciate your eagerness to help, I think that most people
> would
>> >> prefer that you refrain from advertising other products in a forum
>> > dedicated
>> >> to SQL Server Reporting Services. If you start a SIMX newsgroup, I
>> >> promise
>> >> not to post there. :)
>> >>
>> >> That being said, you should be able to define a custom field that does
>> >> the
>> >> calculation and then referce the custom field in a sum in the group
>> > footer.
>> >> Presumably, you only need to add values from the inner group as the
> outer
>> >> group is just summary. If you want it to do parent / child hierarcy
>> >> aggregates, you need to use the recursive keyword.
>> >>
>> >> --
>> >> Brian Welcker
>> >> Group Program Manager
>> >> SQL Server Reporting Services
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> > rights.
>> >>
>> >> "Brian" <Brian@.discussions.microsoft.com> wrote in message
>> >> news:C6115A01-132A-429D-9AFC-99E46F75A2FC@.microsoft.com...
>> >> >I recently came across a new software, that I think you might want to
>> > look
>> >> >into.
>> >> >
>> >> > www.simx.com/simx/home_report%20manager.htm
>> >> >
>> >> > Works with SQL Server, and I was able to do reporting much like what
>> >> > you
>> >> > are describing.
>> >> >
>> >> > "newmem" <"" wrote:
>> >> >
>> >> >> I'm working on a Financial Report which contains a column "XYZ" ,
> its
>> >> >> value
>> >> >> is calculated from a formula by passing the row's record id and
>> >> >> commission
>> >> >> rate. (the formula is inside a custom dll). The values are
>> >> >> correctly
>> >> >> computed. Now, the footer should display the total of all the rows
> in
>> > the
>> >> >> group.
>> >> >> for instance:
>> >> >>
>> >> >> "Unit" "BrandName" "XYZ Total" "Comments"
>> >> >> Sodas
>> >> >> Pepsi $361,000 gfyeefyefffee
>> >> >> Coca Cola $475,250 djfdfjdfddddd
>> >> >> RCola $28,757 re8reruejreerr
>> >> >>
>> >> >> fdfsfnfsfssf
>> >> >> _________________________________________
>> >> >> Total: $ 865,007
>> >> >>
>> >> >> Each of the "XYZ Total" in the above example, uses an expression as
> =>> >> >> FindTotal(recID!value, comm_rate!value)
>> >> >> In this case, how do I get the total in the footer? How to
> recursively
>> >> >> add
>> >> >> the FindTotal expression when it contains the row's unique record
> id?
>> >> >>
>> >> >> Thanks
>> >> >>
>> >> >> P.S. The above data is a sample data. The actual report contains 3
>> >> >> different
>> >> >> levels of grouping - Grouping1: Unit, Grouping2: Brand, Grouping 3:
>> >> >> Transaction Title
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>
>

Group Header Repeating issue

Hi,

I have a group header and I have a invisible textbox which initializes some variable in the custom code (written using C#). The Group Header has been set to repeat on each page but whenever it repeats, the variable is not being initialized. It is initialized only when a new section of the same group starts. Please let me know if this is a drawback with Microsoft reporting services and if there is any workaround.

Thanks,

Shyam

Hi Shyam

I'm don't know if the hidden textbox is in the group header,
in the case that is is not:

Try putting the Initialization code into a hidden column in the groupHeader,
it should solve your problem.

Gerhard Davids

|||

The group header has just one rectangle and many textboxes inside it. One of the textboxes is hidden which is used to initialize the variable in custom code. I tried to put the same in another new column which is hidden but it did not work. But in crystal reports, the initialization code is invoked everytime a group header is displayed either in a new page as a new group.

Thanks,

Shyam

|||

I've Tried this out and couldnt get it working.

This is a sticky Problem, maby see if it is not possible to get it into the Page header instead.
It may be that the header isn't redone on every page simply re-displayd,
Where as the page header i think is different.

Gerhard