Dynamic Query

I use IIF in the dynamic query to dynamically change the Select, Group By, and Order By statements.

In the table grouping properties, i also use IIF to change the grouping Field.

There are no errors, the report processes OK, but the report is not grouped or shows any Field values for which i have to use dynamic query. What could be the problem? Can anybody help please.

Thanks

Can you post the dynamic query you are using, as well as the group expression? Also if you enable tracing on the database side, is the query being executed correct?|||

Thank you for answering to my problem. I did get over it after much trying. The dynamic query looks like this:

="SELECT SUM(BASE_UNIT) AS BASE_UNIT "
& IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", ", Provider_Name")) &
" as grouping FROM TBL_EOM
WHERE (MONTH(ENTRY_DATETIME) =@.RepMonth) AND (YEAR(ENTRY_DATETIME) = @.RepYear)" &
IIF(Parameters!Pract.Value = "*** ALL ***", " ", " AND (NAME = @.Prac) ") &
" GROUP BY Provider_Name, NAME" & IIF(Parameters!Type.Value = "Location", ", Location", IIF(Parameters!Type.Value = "Admit_Source", ", Admit_Source", " "))

In layout view i have this strig for field, grouping and sorting:

IIF(Parameters!Type.Value <> "Provider", Fields!grouping.Value, " ")

Thank you

Dynamic Grouping.. Is it possible?

Hello All,
I have a table with 2 groups in my report. When I edit a group I can
specify the expression to "group on" for the groups.
I would like to be able to make the expression, for the top group, a value
from a report parameter so that the user can specify this when the report
is generated.
The Edit Group Window certainly allows me to pick a report parameter but I
am not sure if it would actually work.. nor what the value of the parameter
should be to make it work. Is this possible?
Example:
Top Group: User Selectable (Country, State, City)
Second Group: Product
Detail Row: Sales data for that product/place combo
So the user could say he would like to see sales grouped by
country/product, state/product, or city/product.
Is this doable without creating 3 different reports?
--
Message posted via http://www.sqlmonster.comTry the following dynamic group expression:
=Fields(Parameters!TopGroup.Value).Value
This requires that the values (or labels) of the TopGroup parameters have
matching field names in your dataset (i.e. Country, State, City).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brian W via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7c78eafd5f1d46329db04bd95646fb57@.SQLMonster.com...
> Hello All,
> I have a table with 2 groups in my report. When I edit a group I can
> specify the expression to "group on" for the groups.
> I would like to be able to make the expression, for the top group, a value
> from a report parameter so that the user can specify this when the report
> is generated.
> The Edit Group Window certainly allows me to pick a report parameter but I
> am not sure if it would actually work.. nor what the value of the
> parameter
> should be to make it work. Is this possible?
> Example:
> Top Group: User Selectable (Country, State, City)
> Second Group: Product
> Detail Row: Sales data for that product/place combo
> So the user could say he would like to see sales grouped by
> country/product, state/product, or city/product.
> Is this doable without creating 3 different reports?
> --
> Message posted via http://www.sqlmonster.com|||Worked like a charm. Thanks.
--
Message posted via http://www.sqlmonster.com

Dynamic Grouping with Report Designer

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

Helen:

I had a similar requirement and here's how I solved it. For the "None" entry at each grouping level you'll need to include a value for it in the combobox that's the following functions will recognize. Include the following in the "Code" and call the appropriate function. You're probably most interested in the "GetSubGrouping" function but I'm including "GetGrouping" so you can also use it at the main grouping level. Include the call to GetSubGrouping in the "Group on"; you'll also find that "GetField" is useful instead of coding a lot of iif's for controlling the visibility of the groupings.

Hope this helps

Glenn L

SharedFunction GetGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return GetField(Fields, Parameters("GroupBy").Value, "no_grouping")

EndFunction

SharedFunction GetSubGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return IIf(Parameters("GroupBy").Value = "no_grouping" _

Or Parameters("GroupBy").Value = Parameters("SubGroupBy").Value, Nothing, GetField(Fields, Parameters("SubGroupBy").Value, "no_sub_grouping"))

EndFunction

SharedFunction GetField(ByVal Fields AsObject, ByVal FieldName AsString, ByVal NoGroupingValue AsString) AsObject

If Trim$(FieldName) = Trim$(NoGroupingValue) Then

ReturnNothing

ElseIf IsDate(Fields(Trim$(FieldName)).Value) Then

Return FormatDateTime(Fields(Trim$(FieldName)).Value, 2)

Else

Return Fields(Trim$(FieldName)).Value

EndIf

EndFunction

|||

Thank you Glenn! That did the trick!

Helen

Dynamic Grouping with Report Designer

I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.

I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?

Thx

Helen

Helen:

I had a similar requirement and here's how I solved it. For the "None" entry at each grouping level you'll need to include a value for it in the combobox that's the following functions will recognize. Include the following in the "Code" and call the appropriate function. You're probably most interested in the "GetSubGrouping" function but I'm including "GetGrouping" so you can also use it at the main grouping level. Include the call to GetSubGrouping in the "Group on"; you'll also find that "GetField" is useful instead of coding a lot of iif's for controlling the visibility of the groupings.

Hope this helps

Glenn L

SharedFunction GetGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return GetField(Fields, Parameters("GroupBy").Value, "no_grouping")

EndFunction

SharedFunction GetSubGrouping(ByVal Parameters AsObject, ByVal Fields AsObject) AsObject

Return IIf(Parameters("GroupBy").Value = "no_grouping" _

Or Parameters("GroupBy").Value = Parameters("SubGroupBy").Value, Nothing, GetField(Fields, Parameters("SubGroupBy").Value, "no_sub_grouping"))

EndFunction

SharedFunction GetField(ByVal Fields AsObject, ByVal FieldName AsString, ByVal NoGroupingValue AsString) AsObject

If Trim$(FieldName) = Trim$(NoGroupingValue) Then

ReturnNothing

ElseIf IsDate(Fields(Trim$(FieldName)).Value) Then

Return FormatDateTime(Fields(Trim$(FieldName)).Value, 2)

Else

Return Fields(Trim$(FieldName)).Value

EndIf

EndFunction

|||

Thank you Glenn! That did the trick!

Helen

Dynamic grouping problem

Hi all,
I need a certain functionality in my Reporting service report.
I need a report with 4 levels of grouping above the detail level. The
initial visibility of all the lower levels should be hidden expect the top
most level. For all the levels toggle item is set in the group properties.
There should also be a boolean parameter called "expand all" which will
affect the initial visibility of the report.
This report should also have dynamic grouping facility with the help of 4
parameters one for each level. Thus if the value passed to one of these
groupby parameters is the word "none" then that group header row should not
be shown. In all cases the detail row should be visible either by drilling
down to that level or as a top level if all other levels are "none".
I am partially successful in the above scenario by making the top level a
mandatory one. i.e the top level can never be "none". However my problem is
that if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up.
Thus there are 3 boolean values to be controlled along with toggle in each
group and detail section. How to achieve this?
Please help me solve this issue.
Thanks.Hi,
Welcome to MSDN Managed NewsGroup. This is Justin from Microsoft.
I do not quite understand the following:
"if in the database a particular column supplied to report thorough one
of the groupby parameters has all nulls then also the group header row
should
not show up. But when I try to put expression to control this using
"isnothing" function, the detail row never shows up. "
Could you elaborate your issue?
Please aslo let me know the expression you set for the visiblity of the
group so that I could better understand your issue.
If you have any question, please feel free to let me know.
Thanks & Regards,
Justin Shen
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Dynamic grouping problem
| thread-index: AcYmdoUqBM3uTevuTUye6qEq3EKGnQ==| X-WBNR-Posting-Host: 38.113.18.195
| From: "=?Utf-8?B?bXNkbnVzZXI=?=" <ringt@.nospam.nospam>
| Subject: Dynamic grouping problem
| Date: Tue, 31 Jan 2006 06:56:30 -0800
| Lines: 29
| Message-ID: <AE4B3F3C-C7DF-431E-9F62-5FCE1D4B82CB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:67863
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi all,
|
| I need a certain functionality in my Reporting service report.
|
| I need a report with 4 levels of grouping above the detail level. The
| initial visibility of all the lower levels should be hidden expect the
top
| most level. For all the levels toggle item is set in the group
properties.
| There should also be a boolean parameter called "expand all" which will
| affect the initial visibility of the report.
|
| This report should also have dynamic grouping facility with the help of 4
| parameters one for each level. Thus if the value passed to one of these
| groupby parameters is the word "none" then that group header row should
not
| be shown. In all cases the detail row should be visible either by
drilling
| down to that level or as a top level if all other levels are "none".
|
| I am partially successful in the above scenario by making the top level a
| mandatory one. i.e the top level can never be "none". However my problem
is
| that if in the database a particular column supplied to report thorough
one
| of the groupby parameters has all nulls then also the group header row
should
| not show up. But when I try to put expression to control this using
| "isnothing" function, the detail row never shows up.
|
| Thus there are 3 boolean values to be controlled along with toggle in
each
| group and detail section. How to achieve this?
|
| Please help me solve this issue.
|
| Thanks.
||||Hi,
I have 3 parameters by name groupby2,groupby3,groupby4 which helps to create
3 dynamic groups. Also I have the top most non-dynamic group. Thus 4 group
headers above my details row and no group footers. I pass the word "NONE"
through one or many of the above parameters,if I dont want to see any or all
of the group header. I use a parameter called expand_all to set the initial
toggle state of all the groups to be expanded or collapsed.
Here are the values I set for each group:
1st group header:
(I reach visibility tab as follows: right click group header--edit
group-visibility tab)
There I set the initial visibility to be visible and no toggle item. close
dialog boxes.
Then I select the first group header row and in the properties window, I set
the visibility(hidden) to be visible.
2nd Group Header:
I reach visibility tab. Set initial visibility to be an expression ="(not
parameters!expand_all.value)" and toggle item to be the name of first textbox
in the 1st group header.
Then I select the 2nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby2.value="none",true,false)
3rd Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby2.value="none",false,true) and toggle item to be name
of the first text box in the 2nd group header row.
Then I select the 3nd group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby3.value="none",true,false)
4th Group Header:
I reach the visibility tab. Set initial visibility to be ="(not
parameters!expand_all.value) and
iif(parameters!groupby3.value="none",false,true) and the toggle item to be
name of the first textbox in the 3rd group header row.
Then I select the 4th group header row and in the properties window, I set
the visibility(hidden) to be
="IIF(parameters!groupby4.value="none",true,false)
Details row:
initial visibility: =(NOT Parameters!EXPAND_ALL.Value) AND
IIF(PARAMETERS!GROUPBY4.VALUE="NONE",FALSE,TRUE) and the toggle item to be
the name of the first text box in the 4th group header row.
Then I select the details row and in the properties window, I set the
visibility(hidden) to be =false.
With the above set up, the report works good. But my problem is say we pass
a particular database field name to groupby2 parameter. (These 4 groupby
parameters are attached in the select list of the dataset of report). Now in
the table if the sent column has no data just nulls, then the group header
first column has just the + toggle sign or - toggle sign based on the state
of toggle and no value. So I tried to suppress the group header when the
field passed for that group header has null values in the database using the
a condition like this in the group header row properties.
=iif(parameters!groupby2.value=none or
fields!groupby2databasefieldname.value is nothing,true,false)
now this trick does work good when the initial visibillity is expanded. But
when the initial visibility is collapsed, then the detials row always remains
hidden. Never shows up.
To put the problem precisely, I want to suppress the group header rows
conditionally; but details row should show up under all circumstances, even
if the initial state of details row is hidden.
Thanks|||Hi,
Is it possible for you to generate a sample rdl file using AdventureWorks
database to describe the issue more?
I understand the information may be sensitive to you, my direct email
address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
ONLINE before you click SEND), you may send the file to me directly and I
will keep secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Quick question Michael
One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
(interested in one of the features demonstrated).
How does one go about getting these (other than the chunking sprocs in the
default Report Server database)?
rob
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||all set (helped my developer...)
just 'edit' from the http interface and copy the .rdl into a VB.NET project.
thanks anyways
rob
"tutor" wrote:
> Quick question Michael
> One of my developers wanted the .rdls for AdventureWorks from SSRS 2000 SP1
> (interested in one of the features demonstrated).
> How does one go about getting these (other than the chunking sprocs in the
> default Report Server database)?
> rob
> "Michael Cheng [MSFT]" wrote:
> > Hi,
> >
> > Is it possible for you to generate a sample rdl file using AdventureWorks
> > database to describe the issue more?
> >
> > I understand the information may be sensitive to you, my direct email
> > address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> > ONLINE before you click SEND), you may send the file to me directly and I
> > will keep secure.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> > Microsoft Online Partner Support
> > ======================================================> > When responding to posts, please "Reply to Group" via your newsreader so
> > that others may learn and benefit from your issue.
> > =====================================================> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >|||Michael,
Do you have the rdl based on AdventureWorks for this reporting issue? I have
a similar need.
Thanks
John
"Michael Cheng [MSFT]" wrote:
> Hi,
> Is it possible for you to generate a sample rdl file using AdventureWorks
> database to describe the issue more?
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.ONLINEmicrosoft.com (Please make sure you have removed
> ONLINE before you click SEND), you may send the file to me directly and I
> will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Did you resolve this problem? I have the exact same issue and would
appreciate any help.
"msdnuser" wrote:
> Hi all,
> I need a certain functionality in my Reporting service report.
> I need a report with 4 levels of grouping above the detail level. The
> initial visibility of all the lower levels should be hidden expect the top
> most level. For all the levels toggle item is set in the group properties.
> There should also be a boolean parameter called "expand all" which will
> affect the initial visibility of the report.
> This report should also have dynamic grouping facility with the help of 4
> parameters one for each level. Thus if the value passed to one of these
> groupby parameters is the word "none" then that group header row should not
> be shown. In all cases the detail row should be visible either by drilling
> down to that level or as a top level if all other levels are "none".
> I am partially successful in the above scenario by making the top level a
> mandatory one. i.e the top level can never be "none". However my problem is
> that if in the database a particular column supplied to report thorough one
> of the groupby parameters has all nulls then also the group header row should
> not show up. But when I try to put expression to control this using
> "isnothing" function, the detail row never shows up.
> Thus there are 3 boolean values to be controlled along with toggle in each
> group and detail section. How to achieve this?
> Please help me solve this issue.
> Thanks.

Dynamic Grouping please?

Hi, I want to have an optional group for a table.
I place this in the group expression:
=iif(Code.m_RR.GetControlParameter(Parameters!ENVIRONMENT.Value,"PA_Split_Internal_External")="true",Fields!MAKE_BUY.Value,"")
But it doesn't group at all.
If I just put =Fields!MAKE_BUY.Value it does. I thought the grouping
allowed dynamic groups? Does anyone know how to do this?
Thanks heaps,
CraigSilly me, you can do this and it works great. It helps if you group on the
correct field though!!!
"Craig" <craigm_richardson@.hotmail.com> wrote in message
news:O2i3zs4MGHA.3908@.TK2MSFTNGP10.phx.gbl...
> Hi, I want to have an optional group for a table.
> I place this in the group expression:
> =iif(Code.m_RR.GetControlParameter(Parameters!ENVIRONMENT.Value,"PA_Split_Internal_External")="true",Fields!MAKE_BUY.Value,"")
> But it doesn't group at all.
> If I just put =Fields!MAKE_BUY.Value it does. I thought the grouping
> allowed dynamic groups? Does anyone know how to do this?
> Thanks heaps,
>
> Craig
>

Dynamic Grouping Group Header Problem

Hi, I am currently trying to create a report the dynamicaly groups from parameters. The grouping part works fine but I need to show the parameter label name or the field referenced by a parameter.

=iif(Parameters!Param1.Value="P",Parameters!Param2.Label,Fields(Parameters!Param1.Value).Value)

This is the expression I have written to do this. The problem seems to be is with the true part of the iif statement. The false displays fine in the group header. The Parameters!Param2.Label also displays if used on its own.

This is the error that is fires back when I run the report.

The Value expression for the textbox ‘textbox4’ contains an error: The expression referenced a non-existing field in the fields collection.

I am quite new to SSRS and I am using VS2005 pro.

Thanks

D

D,

Try

iif(Parameters!Param1.Value="P",Fields(Parameters!Param2.Label).Value,Fields(Parameters!Param1.Value).Value)

I believe this should work for you.

Ham

Dynamic Grouping

Hi all
I have a table which has employee information like
empno, empname, empsalary, empmgr. The empmgr column points to empno.
I want to create a report which will dynamically recognise the employees
under a employee and show his salary. The other requirement is that i want to
enable drill down, ie. at first one employee and his salary will appear who
is the head. On drilling down all the employee directly reporting to him
should appear and so on and so forth. Is this possible in reporting services.RS has drilldown and drillthrough. With drill down you have all the info
returned in a single dataset. You set up your grouping and then hide the
detail rows based on the previous field. The downside to this can be the
amount of information you are bringing back. You do not want to be bringing
back more than a thousand or so records (definitely not 50,000+ records). So
it depends on your situation. Drillthrough allows the user to click on a
field and automatically jump to another report, filling in the report
parameters and pulling up the report. I find that this works very well and
is intuitive for the user. They are very used to this from using the web. I
make the text in the field they should click on blue and underlined. In
books online search on drilldown and drillthrough.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> Hi all
> I have a table which has employee information like
> empno, empname, empsalary, empmgr. The empmgr column points to empno.
> I want to create a report which will dynamically recognise the employees
> under a employee and show his salary. The other requirement is that i want
to
> enable drill down, ie. at first one employee and his salary will appear
who
> is the head. On drilling down all the employee directly reporting to him
> should appear and so on and so forth. Is this possible in reporting
services.|||Hi Bruce
I am able to acheive what i was looking for. But i am also facing a problem.
The first time i run the report it showed like this
empid empsalary
(+)1 2000
on drilling down on the 1 i see like this.
empid empsalary
(-)1 2000
(+)2 1000
(+)3 3000
4 4000
on drill down on 2 i get some thing like this.
empid empsalary
(-)1 2000
(-)2 1000
5 500
6 350
(+)3 3000
4 4000
Now at this point when i want to drill up to the top most item by clicking
on 1, i expect everything to be collapsed. But i am getting something like
this
empid empsalary
(+)1 2000
5 500
6 350
Which is wrong it should show only the first record. Can anybody help in
resolving this. I hope i have given you the correct picture of my problem.
Please help me with this.
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>|||You can set the visibility of a row or of a field. So you set the visibility
of the row based on the field that you will have the +/- with. I have two
levels and it works as you expect it to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> Hi Bruce
> I am able to acheive what i was looking for. But i am also facing a
problem.
> The first time i run the report it showed like this
> empid empsalary
> (+)1 2000
> on drilling down on the 1 i see like this.
> empid empsalary
> (-)1 2000
> (+)2 1000
> (+)3 3000
> 4 4000
> on drill down on 2 i get some thing like this.
> empid empsalary
> (-)1 2000
> (-)2 1000
> 5 500
> 6 350
> (+)3 3000
> 4 4000
> Now at this point when i want to drill up to the top most item by clicking
> on 1, i expect everything to be collapsed. But i am getting something like
> this
> empid empsalary
> (+)1 2000
> 5 500
> 6 350
> Which is wrong it should show only the first record. Can anybody help in
> resolving this. I hope i have given you the correct picture of my problem.
> Please help me with this.
> "Bruce L-C [MVP]" wrote:
> > RS has drilldown and drillthrough. With drill down you have all the info
> > returned in a single dataset. You set up your grouping and then hide the
> > detail rows based on the previous field. The downside to this can be the
> > amount of information you are bringing back. You do not want to be
bringing
> > back more than a thousand or so records (definitely not 50,000+
records). So
> > it depends on your situation. Drillthrough allows the user to click on a
> > field and automatically jump to another report, filling in the report
> > parameters and pulling up the report. I find that this works very well
and
> > is intuitive for the user. They are very used to this from using the
web. I
> > make the text in the field they should click on blue and underlined. In
> > books online search on drilldown and drillthrough.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > Hi all
> > >
> > > I have a table which has employee information like
> > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > >
> > > I want to create a report which will dynamically recognise the
employees
> > > under a employee and show his salary. The other requirement is that i
want
> > to
> > > enable drill down, ie. at first one employee and his salary will
appear
> > who
> > > is the head. On drilling down all the employee directly reporting to
him
> > > should appear and so on and so forth. Is this possible in reporting
> > services.
> >
> >
> >|||I have set the visibility to hidden and visibility can be toggeled by the
field that has +/- with , i.e. the employe id field. But its still not
working
"Bruce L-C [MVP]" wrote:
> You can set the visibility of a row or of a field. So you set the visibility
> of the row based on the field that you will have the +/- with. I have two
> levels and it works as you expect it to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
> > Hi Bruce
> >
> > I am able to acheive what i was looking for. But i am also facing a
> problem.
> >
> > The first time i run the report it showed like this
> >
> > empid empsalary
> > (+)1 2000
> >
> > on drilling down on the 1 i see like this.
> >
> > empid empsalary
> > (-)1 2000
> > (+)2 1000
> > (+)3 3000
> > 4 4000
> >
> > on drill down on 2 i get some thing like this.
> >
> > empid empsalary
> > (-)1 2000
> > (-)2 1000
> > 5 500
> > 6 350
> > (+)3 3000
> > 4 4000
> >
> > Now at this point when i want to drill up to the top most item by clicking
> > on 1, i expect everything to be collapsed. But i am getting something like
> > this
> >
> > empid empsalary
> > (+)1 2000
> > 5 500
> > 6 350
> > Which is wrong it should show only the first record. Can anybody help in
> > resolving this. I hope i have given you the correct picture of my problem.
> > Please help me with this.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > RS has drilldown and drillthrough. With drill down you have all the info
> > > returned in a single dataset. You set up your grouping and then hide the
> > > detail rows based on the previous field. The downside to this can be the
> > > amount of information you are bringing back. You do not want to be
> bringing
> > > back more than a thousand or so records (definitely not 50,000+
> records). So
> > > it depends on your situation. Drillthrough allows the user to click on a
> > > field and automatically jump to another report, filling in the report
> > > parameters and pulling up the report. I find that this works very well
> and
> > > is intuitive for the user. They are very used to this from using the
> web. I
> > > make the text in the field they should click on blue and underlined. In
> > > books online search on drilldown and drillthrough.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > > > Hi all
> > > >
> > > > I have a table which has employee information like
> > > > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> > > >
> > > > I want to create a report which will dynamically recognise the
> employees
> > > > under a employee and show his salary. The other requirement is that i
> want
> > > to
> > > > enable drill down, ie. at first one employee and his salary will
> appear
> > > who
> > > > is the head. On drilling down all the employee directly reporting to
> him
> > > > should appear and so on and so forth. Is this possible in reporting
> > > services.
> > >
> > >
> > >
>
>|||I don't know what to tell you. It works for me (I only go two levels deep
however, although that was the same as the example you gave). Do you have
groups too. This works in tandem with grouping.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:CB32D4AF-F7AE-4795-8E9C-CCA429565B63@.microsoft.com...
>I have set the visibility to hidden and visibility can be toggeled by the
> field that has +/- with , i.e. the employe id field. But its still not
> working
> "Bruce L-C [MVP]" wrote:
>> You can set the visibility of a row or of a field. So you set the
>> visibility
>> of the row based on the field that you will have the +/- with. I have
>> two
>> levels and it works as you expect it to.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> news:EA77BD4A-F9E3-423F-B2FA-5FBCD32C7913@.microsoft.com...
>> > Hi Bruce
>> >
>> > I am able to acheive what i was looking for. But i am also facing a
>> problem.
>> >
>> > The first time i run the report it showed like this
>> >
>> > empid empsalary
>> > (+)1 2000
>> >
>> > on drilling down on the 1 i see like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (+)2 1000
>> > (+)3 3000
>> > 4 4000
>> >
>> > on drill down on 2 i get some thing like this.
>> >
>> > empid empsalary
>> > (-)1 2000
>> > (-)2 1000
>> > 5 500
>> > 6 350
>> > (+)3 3000
>> > 4 4000
>> >
>> > Now at this point when i want to drill up to the top most item by
>> > clicking
>> > on 1, i expect everything to be collapsed. But i am getting something
>> > like
>> > this
>> >
>> > empid empsalary
>> > (+)1 2000
>> > 5 500
>> > 6 350
>> > Which is wrong it should show only the first record. Can anybody help
>> > in
>> > resolving this. I hope i have given you the correct picture of my
>> > problem.
>> > Please help me with this.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > RS has drilldown and drillthrough. With drill down you have all the
>> > > info
>> > > returned in a single dataset. You set up your grouping and then hide
>> > > the
>> > > detail rows based on the previous field. The downside to this can be
>> > > the
>> > > amount of information you are bringing back. You do not want to be
>> bringing
>> > > back more than a thousand or so records (definitely not 50,000+
>> records). So
>> > > it depends on your situation. Drillthrough allows the user to click
>> > > on a
>> > > field and automatically jump to another report, filling in the report
>> > > parameters and pulling up the report. I find that this works very
>> > > well
>> and
>> > > is intuitive for the user. They are very used to this from using the
>> web. I
>> > > make the text in the field they should click on blue and underlined.
>> > > In
>> > > books online search on drilldown and drillthrough.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > > "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> > > news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
>> > > > Hi all
>> > > >
>> > > > I have a table which has employee information like
>> > > > empno, empname, empsalary, empmgr. The empmgr column points to
>> > > > empno.
>> > > >
>> > > > I want to create a report which will dynamically recognise the
>> employees
>> > > > under a employee and show his salary. The other requirement is that
>> > > > i
>> want
>> > > to
>> > > > enable drill down, ie. at first one employee and his salary will
>> appear
>> > > who
>> > > > is the head. On drilling down all the employee directly reporting
>> > > > to
>> him
>> > > > should appear and so on and so forth. Is this possible in reporting
>> > > services.
>> > >
>> > >
>> > >
>>|||Bruce,
I use the drill thru however we have discovered an issue when the report
is deployed to a portal. User has a portal screen with content for selection
on the left and display area on the right. User selects Report A for display
and Report A comes up and renders in the right hand side of the page (content
area). Then user selects item on Report A for drill thru to Report B.
Report B is replacing the entire browser window Content Selection on left and
Display area on right where Report A was. When the user hits the BACK button
on the browser they are not returned to the previous report display - they
are returned to the screen as it appeared before they selected Report A for
display. I want to be able to return to the report they drilled from (that
report takes LOTS of parameters and I dont want to have to carry them all
forward and have to code another "jump to report" just to return back again
... that seems rather lame). What am I missing here' thanks!
"Bruce L-C [MVP]" wrote:
> RS has drilldown and drillthrough. With drill down you have all the info
> returned in a single dataset. You set up your grouping and then hide the
> detail rows based on the previous field. The downside to this can be the
> amount of information you are bringing back. You do not want to be bringing
> back more than a thousand or so records (definitely not 50,000+ records). So
> it depends on your situation. Drillthrough allows the user to click on a
> field and automatically jump to another report, filling in the report
> parameters and pulling up the report. I find that this works very well and
> is intuitive for the user. They are very used to this from using the web. I
> make the text in the field they should click on blue and underlined. In
> books online search on drilldown and drillthrough.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:0A6360E7-1FF7-41A8-B94E-E5536AA20A01@.microsoft.com...
> > Hi all
> >
> > I have a table which has employee information like
> > empno, empname, empsalary, empmgr. The empmgr column points to empno.
> >
> > I want to create a report which will dynamically recognise the employees
> > under a employee and show his salary. The other requirement is that i want
> to
> > enable drill down, ie. at first one employee and his salary will appear
> who
> > is the head. On drilling down all the employee directly reporting to him
> > should appear and so on and so forth. Is this possible in reporting
> services.
>
>

dynamic grouping

Hi,
I have a report with grouping. I am passing a parameter via asp.net and if
that parameter @.paramrep=7 then i want to group by type and by region
otherwise i want to group only by region.
I have looked at Chris Hay's example but I am still not sure i do get it to
work as I keep on getting an error: the expression referenced a non-existing
field in the fields collection.
This is my expression:
=iif(Parameters!Paramreport.Value
=7,1,Fields(iif(Parameters!Paramreport.Value =7,
"region",Parameters!Paramreport.Value)).Value)
I am not sure what I am doing wrong as basically I didn't really understand
the article.
I would appreciate any help offered.
ThanksWhile I haven't seen the example you mention, I often use functions for
dynamic grouping ie...( my syntax here might be bad...)
Public function GetGroups(Byref Groupid as integer, Byref Paramval as
Integer) as String
Switch Paramval
Case 7 If Groupid = 1 Then
Return("Price")
Else Return("Productname")
End IF
Case Else If Groupid = 1 Then
Return("Othercol1")
Else Return("Othercol2")
End IF
End
End
THen in the Grouping dialog
=Fields(GetGroups(1,Parameters!Parametername.Value)).Value
=Fields(GetGroups(2,Parameters!Parametername.Value)).Value
Hope this helps... By the way, sometimes I have to dynamically change the
sort to match the grouping at the highest level, otherwise I have gotten an
error...But the sort is handled the same way..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"collie" <collie@.discussions.microsoft.com> wrote in message
news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> Hi,
> I have a report with grouping. I am passing a parameter via asp.net and if
> that parameter @.paramrep=7 then i want to group by type and by region
> otherwise i want to group only by region.
> I have looked at Chris Hay's example but I am still not sure i do get it
to
> work as I keep on getting an error: the expression referenced a
non-existing
> field in the fields collection.
> This is my expression:
> =iif(Parameters!Paramreport.Value
> =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> "region",Parameters!Paramreport.Value)).Value)
> I am not sure what I am doing wrong as basically I didn't really
understand
> the article.
> I would appreciate any help offered.
> Thanks|||Hi,
Thanks so much for your response.
I have a few questions about your code if you could please clarify (I feel
stupid for asking but...)
Ok here goes :-)
What do you mean by groupid such as groupid=1?
What is price? A field name to group by if groupid=1?
Parmetername in my case would be the parameter that i send from asp.net
@.paramrep=7 correct?
Thanks
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>|||Wayne your code was a great help.
Thanks :-)
"Wayne Snyder" wrote:
> While I haven't seen the example you mention, I often use functions for
> dynamic grouping ie...( my syntax here might be bad...)
> Public function GetGroups(Byref Groupid as integer, Byref Paramval as
> Integer) as String
> Switch Paramval
> Case 7 If Groupid = 1 Then
> Return("Price")
> Else Return("Productname")
> End IF
> Case Else If Groupid = 1 Then
> Return("Othercol1")
> Else Return("Othercol2")
> End IF
> End
> End
>
> THen in the Grouping dialog
> =Fields(GetGroups(1,Parameters!Parametername.Value)).Value
> =Fields(GetGroups(2,Parameters!Parametername.Value)).Value
> Hope this helps... By the way, sometimes I have to dynamically change the
> sort to match the grouping at the highest level, otherwise I have gotten an
> error...But the sort is handled the same way..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "collie" <collie@.discussions.microsoft.com> wrote in message
> news:416E56D8-9856-412A-8381-A7BBD1DCBAC5@.microsoft.com...
> > Hi,
> >
> > I have a report with grouping. I am passing a parameter via asp.net and if
> > that parameter @.paramrep=7 then i want to group by type and by region
> > otherwise i want to group only by region.
> > I have looked at Chris Hay's example but I am still not sure i do get it
> to
> > work as I keep on getting an error: the expression referenced a
> non-existing
> > field in the fields collection.
> > This is my expression:
> > =iif(Parameters!Paramreport.Value
> > =7,1,Fields(iif(Parameters!Paramreport.Value =7,
> > "region",Parameters!Paramreport.Value)).Value)
> > I am not sure what I am doing wrong as basically I didn't really
> understand
> > the article.
> >
> > I would appreciate any help offered.
> >
> > Thanks
>
>

Dynamic Columns & Dynamic Grouping ?

Hi,

We need to design more than 500 Reports for our ongoing project and we are dealing with MS SQL Server Reporting Services first time. We are currently confused in 2 things

1) How to manage Reports? I mean we should store entire report in database and load at runtime or simply store as a report file

2) Most Important is Dynamic Columns & Dynamic Grouping.

We need something like Microsoft Office Accounting 2007 Reports. Header part of form has some parameters for filter and right part has some parameters for showing or hiding and reordering columns and groups.

We are not getting clue for this dynamic stuff. Can any one post working sample or at least necessary basic code with some idea?

Reply on Urgent Basis

Thanks lot

Someone Please Help

If I have posted in wrong forum then suggest the correct one.

Dynamic and Group Parameters

I am using Parameter to determine the grouping of my table. Is it possible to
use a dynamic parameter based upon my grouping value. i.e. if the user
selects to group on students the dynamic parameter will be populated with all
student names, or if the user selects to group on subjects the dynamic
parameter will be populated with all subject names.http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:8E5BEA8E-C744-4FA2-9FAE-5D268BAEE170@.microsoft.com...
> I am using Parameter to determine the grouping of my table. Is it possible
to
> use a dynamic parameter based upon my grouping value. i.e. if the user
> selects to group on students the dynamic parameter will be populated with
all
> student names, or if the user selects to group on subjects the dynamic
> parameter will be populated with all subject names.|||Hi Chris:
I will try explaining my issue again. I have created a report which has
dynamic grouping based on parameter selection. e.g. the user selectes student
or subject from the parameter drop don box and the report is then grouped
accordingly. What I want to do now is have another parameter which is
populated based on the first parameters value. So if students are selected in
parameter 1 , parameter 2 will allow me to select which student to report on.
"Chris Hays [MSFT]" wrote:
> http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:8E5BEA8E-C744-4FA2-9FAE-5D268BAEE170@.microsoft.com...
> > I am using Parameter to determine the grouping of my table. Is it possible
> to
> > use a dynamic parameter based upon my grouping value. i.e. if the user
> > selects to group on students the dynamic parameter will be populated with
> all
> > student names, or if the user selects to group on subjects the dynamic
> > parameter will be populated with all subject names.
>
>