Showing posts with label field. Show all posts
Showing posts with label field. Show all posts

Wednesday, March 28, 2012

Report designer - how you refer to an existing field in calculation

Hi,

This seems simple but can't find. Say in my report I have 3 fields(A,B and C).

In field C I want to give a calcultion based on fields A and B. Say field C value = A+B

How do you do this? In expressions I couldn't see existing report fields, it only shows dataset columns.

Sonny

Hi Sonny,

You have several options to do something like this.

You can create a calculated field in your dataset:

In your dataset window, right click on the dataset and select Add Type in the name (FieldC) Select Calculated Field and type in =Fields!FieldA.Value + Fields!FieldB.Value In the textbox where you want FieldC to be, type in =Fields!FieldC.Value|||

Hi Jessica,

Thanks for the answer.

Sonny

Report designer - how you refer to an existing field in calculation

Hi,

This seems simple but can't find. Say in my report I have 3 fields(A,B and C).

In field C I want to give a calcultion based on fields A and B. Say field C value = A+B

How do you do this? In expressions I couldn't see existing report fields, it only shows dataset columns.

Sonny

Hi Sonny,

You have several options to do something like this.

You can create a calculated field in your dataset:

In your dataset window, right click on the dataset and select Add Type in the name (FieldC) Select Calculated Field and type in =Fields!FieldA.Value + Fields!FieldB.Value In the textbox where you want FieldC to be, type in =Fields!FieldC.Value|||

Hi Jessica,

Thanks for the answer.

Sonny

Report designer

In a RD, i have 2 dataset, one of them consist of two field (id and name) by which the name will be shown as a parameter. When a user select from the combo box, how do I enable the id of the this dataset to be link up as a 'parameter' to another dataset which is a query to retrieve the result?

In another words instead of using the parameter 'name' i would like to implicitly link up the id instead of the name which is shown to the user.

Thanks.

Regards

Alu

In Report Parameters, you set the

Value Field to be ID

and the

Label Field to be Name

A bit like this

<Prompt>Select Hierarchy Group</Prompt>

<ValidValues>

<DataSetReference>

<DataSetName>H_Groups</DataSetName>

<ValueField>id</ValueField>

<LabelField>List</LabelField>

</DataSetReference>

</ValidValues>

Where id is the parameter passed to the query & List is the values displayed for the user to choose from in the Listbox

|||

thanks!

Regards

Alu

sql

Monday, March 26, 2012

Report Delivery Options To field is not editable

In the Report Delivery Options page of a new subscription the To: field is
read only. I followed the step: "Configuration Options for Setting the To:
Field in a Message" in the document "Configuring a Report Server for E-Mail
Delivery" http://technet.microsoft.com/en-us/library/ms159155.aspx. However
the To: field still can not be changed.
What am I over looking?
Thanks, MikeI donâ't know if it has to do with the way we are configured or a step that
was omitted for the procedure outline in my post below. But I logged off of
the network then back on and now the To: field can be change when subscribing.
"Mike Mullane" wrote:
> In the Report Delivery Options page of a new subscription the To: field is
> read only. I followed the step: "Configuration Options for Setting the To:
> Field in a Message" in the document "Configuring a Report Server for E-Mail
> Delivery" http://technet.microsoft.com/en-us/library/ms159155.aspx. However
> the To: field still can not be changed.
> What am I over looking?
> Thanks, Mike
>

Tuesday, March 20, 2012

Report Builder Filter drop down list - when the list is more than 500 values

Hi All,

I build a model (either from Cube or from tables) and open it in report builder. When I select a FIELTER on field which has few distinct values like state code, I get a drop down list. But when I select a filter on 'Dept Name', the drop down list on filter disapears. I have to manually key the filter value.

Can any one help me in getting a drop down list on filter, when distinct values in that column are more than few hundred.

Regards

Bobba

This is controlled by a property InstanceSelection, that can be set on each attribute of a dimension (within the dimension Editor).

The cube/dimension wizard initially sets this property, but then it can be changed as required.

Report Builder field calculate problem

Hi,
I have a problems with the field calculations. NOT Work.
When i tried calculate total sales for a specific store, this duplicate or
3, 4, 5 times the total sales.Hello Guillermo,
I would look closely at you data source view and check that there is
nothing funny with the table joins you are basing your model on. This
is the main place where something goes wrong and causes the issue.
I would also review the report you are creating and check there are no
many to many possibilities with entries you have selected.
If you are still not having any luck have a look at the blog entry
describing how to trap the SQL generated by Report Builder.
http://blogs.msdn.com/bobmeyers/archive/2006/07/05/657125.aspx
Hope this helps
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Monday, March 12, 2012

Report Builder Error

I have created a view with one table (CASEMASTER). When I look at the Visual
Studioâ's property of the Case Number field within table CASEMASTER, it is set
to AllowNull = False, which correctly corresponds with the property of this
field on the SQL Server.
However, I need this table to be replaced with a new named query because I
need to limit the data to only one county, plus I need to link other tables.
When I set it as a query, it changes the property of the Case Number field to
AllowNull = True. Consequently, when I try to deploy it, I get a "Nullable
Property Error".
Is there any way to change the AllowNull property OR prevent this from
happening so that I can deploy the view/model without errors?
Any suggestions are appreciated.
--
K. ThomasI removed the minOccurs="0" property in the code to get this to work properly.
--
K. Thomas
"K. Thomas" wrote:
> I have created a view with one table (CASEMASTER). When I look at the Visual
> Studioâ's property of the Case Number field within table CASEMASTER, it is set
> to AllowNull = False, which correctly corresponds with the property of this
> field on the SQL Server.
> However, I need this table to be replaced with a new named query because I
> need to limit the data to only one county, plus I need to link other tables.
> When I set it as a query, it changes the property of the Case Number field to
> AllowNull = True. Consequently, when I try to deploy it, I get a "Nullable
> Property Error".
> Is there any way to change the AllowNull property OR prevent this from
> happening so that I can deploy the view/model without errors?
> Any suggestions are appreciated.
> --
> K. Thomas

report builder cant join views?

Hi friends
i have quick question with report builder utility .am using VS2005 and Sql 2005.
When creating report model solution ,when adding field names from a table it loads fields names as it is ,right ? so to make end users life easier i created
Views with lengthy field names.

the issue is ,i'll start with an example ,i've 2 views (ParentView and ChildView) and the tables used by these views have foreign key relation ship .when i use these 2 entities in report builder as soon as i add a column from one view (say ParentView) the other view disappears from list !! I assume Report builder looks FKey to link 2 entities and since am using views which will not have any fkeys on them it removes other entities from list.
is there any way to link these 2 views so that users can add few columns from ParentView and few more from ChildView.
Yes, If i use tables directly everything works fine .
Thanks for your help.
i finally figured out .if any one interested
when we double click on data source view (in report model project you created) it gives graphical representation of objects where we can drag & drop to create relationships just like diagrams in Enterprise Managers

report builder cant join views?

Hi friends
i have quick question with report builder utility .am using VS2005 and Sql 2005.
When creating report model solution ,when adding field names from a table it loads fields names as it is ,right ? so to make end users life easier i created
Views with lengthy field names.

the issue is ,i'll start with an example ,i've 2 views (ParentView and ChildView) and the tables used by these views have foreign key relation ship .when i use these 2 entities in report builder as soon as i add a column from one view (say ParentView) the other view disappears from list !! I assume Report builder looks FKey to link 2 entities and since am using views which will not have any fkeys on them it removes other entities from list.
is there any way to link these 2 views so that users can add few columns from ParentView and few more from ChildView.
Yes, If i use tables directly everything works fine .
Thanks for your help.
i finally figured out .if any one interested
when we double click on data source view (in report model project you created) it gives graphical representation of objects where we can drag & drop to create relationships just like diagrams in Enterprise Managers

Friday, March 9, 2012

Report Builder / sorting

It is not possible to sort by value which is not selected as report
field.
In my scenario, I've selected monthname as field and I can not sort it
chronologically :(
KamelI don't know if this will help, but in the UDM for each attribute you can
specify the data that will be displayed and you can specify differnt data to
sort on.
So you could display the month name, and sort it on the year, month values
of the date.
Look for the sort attributes of the attributes properties.
hth, Mike
"kamel" <kwiciak@.gmail.com> wrote in message
news:1146557598.692677.326160@.u72g2000cwu.googlegroups.com...
> It is not possible to sort by value which is not selected as report
> field.
> In my scenario, I've selected monthname as field and I can not sort it
> chronologically :(
> Kamel
>

Report Builder & Drillthrough

Has anyone succeeded in specifying a report that should be drilled-to from a
specific field? I have followed all the instructions I could find, but always
get the error:
The 'DrillthroughSourceQuery' parameter is missing a value
Is it necessary to learn the gory details of .rdl and semantic queries to
get this to work?
cheers,
Helen
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.comI'm doing this like this:
* create a report using report builder
* set the option in the report to authorized the drill to this report from
other report
* save it as RDL (if you want to edit it in VS) or publish it directly
* using the management studio, edit the report builder model and setup the
drill for the target entity to your new report.
A "drill-to" report required some metadata and specific parameters, using
this method the system create all of these parameters for you.
good luck!
Jerome.
"Helen Warn" <HelenWarn@.discussions.microsoft.com> wrote in message
news:63A3CBC0-6C39-4777-B70A-11F2B57BEB2A@.microsoft.com...
> Has anyone succeeded in specifying a report that should be drilled-to from
> a
> specific field? I have followed all the instructions I could find, but
> always
> get the error:
> The 'DrillthroughSourceQuery' parameter is missing a value
> Is it necessary to learn the gory details of .rdl and semantic queries to
> get this to work?
> cheers,
> Helen
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com|||Yes it works perfectly fine... the drill through depends on how you design
the Report model and the relation it has with all the tables for drill
through. Why tables ? because it has data, so it relates the data and drill
through the data from your report, depending on the relation and the columns
selected on the report.
Ofcourse you have an option on report builder to enable and disable. Go to
Report Properties from the menu (From report Builder) and enable it.
Amarnath
"Helen Warn" wrote:
> Has anyone succeeded in specifying a report that should be drilled-to from a
> specific field? I have followed all the instructions I could find, but always
> get the error:
> The 'DrillthroughSourceQuery' parameter is missing a value
> Is it necessary to learn the gory details of .rdl and semantic queries to
> get this to work?
> cheers,
> Helen
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com|||Well, maybe it isn't designed to do what I want it to do. I have a contact
report that displays several fields related to a person. What I would like to
do is drill through to an address report (each contact can have several
addresses) that lists all the contact's addresses using drill-through on one
or more fields in the contact report. Is this possible?
Thx
Helen
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com
"Amarnath" wrote:
> Yes it works perfectly fine... the drill through depends on how you design
> the Report model and the relation it has with all the tables for drill
> through. Why tables ? because it has data, so it relates the data and drill
> through the data from your report, depending on the relation and the columns
> selected on the report.
> Ofcourse you have an option on report builder to enable and disable. Go to
> Report Properties from the menu (From report Builder) and enable it.
> Amarnath|||I agree, there is no much of flexibility in RB, but still it satisfies quite
a bit what the so called "C" users want to see. Answering the second part of
your post.
It is very much possible. After creating report model with proper keys, and
enabling drill through through Report Properties from RB menu. one more thing
you need to do to make this work. You need to associate this report with an
entity in the data source, to allow users to drill to this report using
Management Studio, then it should work fine.
Amarnath
"Helen Warn" wrote:
> Well, maybe it isn't designed to do what I want it to do. I have a contact
> report that displays several fields related to a person. What I would like to
> do is drill through to an address report (each contact can have several
> addresses) that lists all the contact's addresses using drill-through on one
> or more fields in the contact report. Is this possible?
> Thx
> Helen
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com
>
> "Amarnath" wrote:
> > Yes it works perfectly fine... the drill through depends on how you design
> > the Report model and the relation it has with all the tables for drill
> > through. Why tables ? because it has data, so it relates the data and drill
> > through the data from your report, depending on the relation and the columns
> > selected on the report.
> >
> > Ofcourse you have an option on report builder to enable and disable. Go to
> > Report Properties from the menu (From report Builder) and enable it.
> >
> > Amarnath
>|||Thank you for your reply. I have followed all the steps you outline, and it
still does not work. Yesterday I installed SP2, which did solve another
intermittent problem I was having (saving reports from the Report Builder),
but I still cannot get my drill-through report to work properly.
I am very pleased to know that the RB should allow what I want to do. There
seems to be a problem in generating the semantic query.
Perhaps there is something wrong with my Report Model, but I am not seeing
it. I plan to open another support case with MS this morning.
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com
"Amarnath" wrote:
> I agree, there is no much of flexibility in RB, but still it satisfies quite
> a bit what the so called "C" users want to see. Answering the second part of
> your post.
> It is very much possible. After creating report model with proper keys, and
> enabling drill through through Report Properties from RB menu. one more thing
> you need to do to make this work. You need to associate this report with an
> entity in the data source, to allow users to drill to this report using
> Management Studio, then it should work fine.
> Amarnath
>
> "Helen Warn" wrote:
> > Well, maybe it isn't designed to do what I want it to do. I have a contact
> > report that displays several fields related to a person. What I would like to
> > do is drill through to an address report (each contact can have several
> > addresses) that lists all the contact's addresses using drill-through on one
> > or more fields in the contact report. Is this possible?
> >
> > Thx
> >
> > Helen
> > --
> > Helen Warn, PhD
> > Agile Software Inc.
> > www.agile-soft.com
> >
> >
> > "Amarnath" wrote:
> >
> > > Yes it works perfectly fine... the drill through depends on how you design
> > > the Report model and the relation it has with all the tables for drill
> > > through. Why tables ? because it has data, so it relates the data and drill
> > > through the data from your report, depending on the relation and the columns
> > > selected on the report.
> > >
> > > Ofcourse you have an option on report builder to enable and disable. Go to
> > > Report Properties from the menu (From report Builder) and enable it.
> > >
> > > Amarnath
> >

Wednesday, March 7, 2012

Report Builder - filtering data

We have a web application that supports muliple companies (around 500). All
tables in our SQL Server database have a CompanyId field. We want to use
SSRS Report Builder to let end users create Ad Hoc reports. We need to
build a Model/Data View that allows an end user to veiw data from their
company only. How would we filter the data so that our end users do not see
other companies data in the Model that we design?
We can not allow the end user to create a filter for CompanyID at run time.I am trying to do the exact same thing (as I'm sure are many others), so any
assistance from Microsoft on this topic would be greatly appreciated...
Michael Carr
"Eva M." <Eva M.@.discussions.microsoft.com> wrote in message
news:7C18DD4A-8D07-46CC-A837-CAA2D0D5B156@.microsoft.com...
> We have a web application that supports muliple companies (around 500).
> All
> tables in our SQL Server database have a CompanyId field. We want to use
> SSRS Report Builder to let end users create Ad Hoc reports. We need to
> build a Model/Data View that allows an end user to veiw data from their
> company only. How would we filter the data so that our end users do not
> see
> other companies data in the Model that we design?
> We can not allow the end user to create a filter for CompanyID at run
> time.
>

report based on day

I have a report that runs daily.
I need to set the criteria for a date field to be today.
if i use the now() command then i get the value to the minute, whereas i wan
to cover the entire day.
today() doen't work in reporting services with visul studio.
what other date command can i use?This will give you the date with no time portion.
Now.ToShortDateString
Note that for a date/time field this really turns out to be today at 12 AM.
This would convert it back to a date instead of text.
CDate(Now.ToShortDateString)
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anon" <anon@.anon.com> wrote in message
news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>I have a report that runs daily.
> I need to set the criteria for a date field to be today.
> if i use the now() command then i get the value to the minute, whereas i
> wan to cover the entire day.
> today() doen't work in reporting services with visul studio.
> what other date command can i use?
>
>|||How would i enter this into the criteria part of the query?
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23bxuGQD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
> This will give you the date with no time portion.
> Now.ToShortDateString
> Note that for a date/time field this really turns out to be today at 12
> AM.
> This would convert it back to a date instead of text.
> CDate(Now.ToShortDateString)
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "anon" <anon@.anon.com> wrote in message
> news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>>I have a report that runs daily.
>> I need to set the criteria for a date field to be today.
>> if i use the now() command then i get the value to the minute, whereas i
>> wan to cover the entire day.
>> today() doen't work in reporting services with visul studio.
>> what other date command can i use?
>>
>|||If this is the default for the report parameter then just use this for the
default.
If you haven't yet done any queries that are parameter based then backup and
do some simple reports that use query parameters like this:
select * from sometable where somevalue = @.MyParam
Note that if this is hard coded, the report should only do the current day
then use SQL syntax. You don't even need a query parameter. I am at home and
don't have my SQL reference but I am sure there is some normal SQL that can
be used (like DatePart, DateAdd etc).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"anon" <anon@.anon.com> wrote in message
news:%23Y0u5KP8FHA.3636@.TK2MSFTNGP09.phx.gbl...
> How would i enter this into the criteria part of the query?
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23bxuGQD8FHA.1020@.TK2MSFTNGP15.phx.gbl...
>> This will give you the date with no time portion.
>> Now.ToShortDateString
>> Note that for a date/time field this really turns out to be today at 12
>> AM.
>> This would convert it back to a date instead of text.
>> CDate(Now.ToShortDateString)
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "anon" <anon@.anon.com> wrote in message
>> news:%23Bz8zTC8FHA.2816@.tk2msftngp13.phx.gbl...
>>I have a report that runs daily.
>> I need to set the criteria for a date field to be today.
>> if i use the now() command then i get the value to the minute, whereas i
>> wan to cover the entire day.
>> today() doen't work in reporting services with visul studio.
>> what other date command can i use?
>>
>>
>