Showing posts with label certain. Show all posts
Showing posts with label certain. Show all posts

Friday, March 23, 2012

Report Name in Subscription

Hi!
Is there a way to make the name of the report dynamic? instead of just
appending a number if a certain already exists, the name could append
the name of the current month or a selected parameter.
Thanks! :)Hi Sigh,
I think you need a data driven subscription which will require the
enterprise edition of reporting services..
Cheers
Matt
"Sigh" wrote:
> Hi!
> Is there a way to make the name of the report dynamic? instead of just
> appending a number if a certain already exists, the name could append
> the name of the current month or a selected parameter.
> Thanks! :)
>|||Hi Matt.
Unfortunately, we're running the standard edition. Thanks for the
idea. :)
Sigh.

Report Model missing some fields

I hope someone can clarify what I observe below.

When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.

This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).

Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?

Perhaps I'm doing something wrong, or there is a workaround?

If anyone can shed any light in the issue, I'd greatly appreciate their comment.

Thanks in advance and kindest regards

Craig

What is the datatype of the column that is not added? Text fields are not supported. Also, are you saying that the field IS added as a role? This is not clear.

-Carolyn [MSFT]

|||

Carolyn,

Thanks for your reply.

I hope will make what I'm trying to say slightly clearer.


CREATE TABLE [dbo].[MBB010](
[PRE_B01] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARTNO] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT15] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMMCODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HAZARD1] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
<...SNIP... (total of 77 fields) >
[ITSTACODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MBB010__ITSTACOD__03681F15] DEFAULT (' '),
CONSTRAINT [MBB010_1] UNIQUE CLUSTERED
(
[PRE_B01] ASC,
[PARTNO] ASC,
[ACCOUNT15] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Indexes as follows

index_name index_description index_keys
-- --
MBB010_1 clustered, unique, unique key located on PRIMARY PRE_B01, PARTNO, ACCOUNT15
MBB010_2 nonclustered located on PRIMARY PRODGROUP, PRE_B01, PARTNO, ACCOUNT15
MBB010_3 nonclustered located on PRIMARY ACCOUNT15, HSYSCODE_ITEM

So PARTNO column points off to other tables within the DSV.

PRE_B01 and ACCOUNT15 get added by the wizard when I add this table to the report model, but PARTNO gets skipped. Only thing I can see different about this one field is that its included as a role.

I'm keen to understand and avoid having to manually edit the model to fix this as I have 500+ tables :-(

Thanks in advance

Craig

|||

Sorry to reply to me own question but I think I made a mistake. ACCOUNT15 does NOT get added either. So can someone clarify the rules that the SQL wizard (when adding a new table to a report model) follows?

Do all fields that become roles not get included (to end users) when they select this table in Report Builder?

|||

I too am struggling with the same problem. There are about 277 tables in my model and each table one or more such fields that a part of primary key go missing and appear as roles. So when I am trying to build a report I cannot find it under the parent table I have to go the related child table and pick it from there. This is not necessarily obvious to the end users of the model who are building reports.

It will be great to hear if anyone know how to work around this problem. It is not feasible to add all of the manually again.

|||

Sorry for not updating people.

I'm since got this working as you would expect (for new test fields I added into the model), by which I mean the "field" remains as an Attribute but is also created as a Role. I am giving SSRS the benefit of the doubt that I had corrupted the report model as I created the entire thing programmatically by reverse engineering the XML from other examples. Mind you, many times during this excersise Visual Studio would report the model as being unloadable or corrupt in some way (so I'm saying its validation is usually very good), but my current model definately loads without complaint.

The only related thing that I find annoying is that it renames the fields. So as I have many tables that link on PARTNO, the roles gets renamed PARTNO2, 3, 4, 5, etc. I guess this is because the underlying format is XML which is case-sensitive and so SSRS can't allow to "items" to have the same name.

Report Model missing some fields

I hope someone can clarify what I observe below.

When I add a certain Table into my report model, one of the fields is not automatically converted into an attribute, but I'm not sure what the exact pattern is.

This table has 3 fields as its key, two of them get included and one does not. The one that does not, is also added as a Role as it is used in a relationship within the DSV (Data Source View).

Does anyone know what rules BIS (Business Intelligence Studio) uses in deciding which fields to automatically convert using the wizard and which to skip?

Perhaps I'm doing something wrong, or there is a workaround?

If anyone can shed any light in the issue, I'd greatly appreciate their comment.

Thanks in advance and kindest regards

Craig

What is the datatype of the column that is not added? Text fields are not supported. Also, are you saying that the field IS added as a role? This is not clear.

-Carolyn [MSFT]

|||

Carolyn,

Thanks for your reply.

I hope will make what I'm trying to say slightly clearer.


CREATE TABLE [dbo].[MBB010](
[PRE_B01] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PARTNO] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ACCOUNT15] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[COMMCODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HAZARD1] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
<...SNIP... (total of 77 fields) >
[ITSTACODE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__MBB010__ITSTACOD__03681F15] DEFAULT (' '),
CONSTRAINT [MBB010_1] UNIQUE CLUSTERED
(
[PRE_B01] ASC,
[PARTNO] ASC,
[ACCOUNT15] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Indexes as follows

index_name index_description index_keys
-- --
MBB010_1 clustered, unique, unique key located on PRIMARY PRE_B01, PARTNO, ACCOUNT15
MBB010_2 nonclustered located on PRIMARY PRODGROUP, PRE_B01, PARTNO, ACCOUNT15
MBB010_3 nonclustered located on PRIMARY ACCOUNT15, HSYSCODE_ITEM

So PARTNO column points off to other tables within the DSV.

PRE_B01 and ACCOUNT15 get added by the wizard when I add this table to the report model, but PARTNO gets skipped. Only thing I can see different about this one field is that its included as a role.

I'm keen to understand and avoid having to manually edit the model to fix this as I have 500+ tables :-(

Thanks in advance

Craig

|||

Sorry to reply to me own question but I think I made a mistake. ACCOUNT15 does NOT get added either. So can someone clarify the rules that the SQL wizard (when adding a new table to a report model) follows?

Do all fields that become roles not get included (to end users) when they select this table in Report Builder?

|||

I too am struggling with the same problem. There are about 277 tables in my model and each table one or more such fields that a part of primary key go missing and appear as roles. So when I am trying to build a report I cannot find it under the parent table I have to go the related child table and pick it from there. This is not necessarily obvious to the end users of the model who are building reports.

It will be great to hear if anyone know how to work around this problem. It is not feasible to add all of the manually again.

|||

Sorry for not updating people.

I'm since got this working as you would expect (for new test fields I added into the model), by which I mean the "field" remains as an Attribute but is also created as a Role. I am giving SSRS the benefit of the doubt that I had corrupted the report model as I created the entire thing programmatically by reverse engineering the XML from other examples. Mind you, many times during this excersise Visual Studio would report the model as being unloadable or corrupt in some way (so I'm saying its validation is usually very good), but my current model definately loads without complaint.

The only related thing that I find annoying is that it renames the fields. So as I have many tables that link on PARTNO, the roles gets renamed PARTNO2, 3, 4, 5, etc. I guess this is because the underlying format is XML which is case-sensitive and so SSRS can't allow to "items" to have the same name.

Wednesday, March 21, 2012

Report Model Data Security

Hello
I have designed a report model, but now have a requirement to limit data
access to certain people. I have tried researching about Role based
security, but nothing seems to make sense. I have to design a suite of
reports 16 in total. However,I have 8 users, all looking at data from
difference business departments. It has been requested that each department
cannot look at another department's data. The thought of cloning these 16
reports, 8 times over, does not seem like a viable option.
Is there an alternative?
Kind regards
Ricky
(SS2005)If you are saying that each report needs to be viewed by each dept and that
the data should be different on the report depending on who is viewing it
then:
On your reports you can have a hidden extra parameter that defaults to the
user id.
Then pass this id into your stored procedures that generate the data.
The stored procedures will do the filtering based on your own internal logic.
"RickyP" wrote:
> Hello
> I have designed a report model, but now have a requirement to limit data
> access to certain people. I have tried researching about Role based
> security, but nothing seems to make sense. I have to design a suite of
> reports 16 in total. However,I have 8 users, all looking at data from
> difference business departments. It has been requested that each department
> cannot look at another department's data. The thought of cloning these 16
> reports, 8 times over, does not seem like a viable option.
> Is there an alternative?
> Kind regards
> Ricky
> (SS2005)
>|||Hi Jimbo
Thanks for your post, but the model cannot not based on an SP, it is created
from a View.
Kind regards
Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> If you are saying that each report needs to be viewed by each dept and
> that
> the data should be different on the report depending on who is viewing it
> then:
> On your reports you can have a hidden extra parameter that defaults to the
> user id.
> Then pass this id into your stored procedures that generate the data.
> The stored procedures will do the filtering based on your own internal
> logic.
>
>
>
> "RickyP" wrote:
>> Hello
>> I have designed a report model, but now have a requirement to limit data
>> access to certain people. I have tried researching about Role based
>> security, but nothing seems to make sense. I have to design a suite of
>> reports 16 in total. However,I have 8 users, all looking at data from
>> difference business departments. It has been requested that each
>> department
>> cannot look at another department's data. The thought of cloning these
>> 16
>> reports, 8 times over, does not seem like a viable option.
>> Is there an alternative?
>> Kind regards
>> Ricky
>> (SS2005)|||Your sp can read from the view :)
"RickyP" wrote:
> Hi Jimbo
> Thanks for your post, but the model cannot not based on an SP, it is created
> from a View.
> Kind regards
> Ricky
> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
> > If you are saying that each report needs to be viewed by each dept and
> > that
> > the data should be different on the report depending on who is viewing it
> > then:
> >
> > On your reports you can have a hidden extra parameter that defaults to the
> > user id.
> >
> > Then pass this id into your stored procedures that generate the data.
> >
> > The stored procedures will do the filtering based on your own internal
> > logic.
> >
> >
> >
> >
> >
> >
> >
> > "RickyP" wrote:
> >
> >> Hello
> >>
> >> I have designed a report model, but now have a requirement to limit data
> >> access to certain people. I have tried researching about Role based
> >> security, but nothing seems to make sense. I have to design a suite of
> >> reports 16 in total. However,I have 8 users, all looking at data from
> >> difference business departments. It has been requested that each
> >> department
> >> cannot look at another department's data. The thought of cloning these
> >> 16
> >> reports, 8 times over, does not seem like a viable option.
> >>
> >> Is there an alternative?
> >>
> >> Kind regards
> >>
> >> Ricky
> >> (SS2005)
> >>
>|||Hi Jimbo
Thanks, but I think you must be confused, or maybe I have not stated my
scenario clearly. I have built a Report Model, to be used in Report
Builder, there is no facility to use SP's, maybe I have misunderstood what
you mean?
Kind regards
~Ricky
"Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
news:2CB0DA49-ED42-4E2F-8326-93322C0F429A@.microsoft.com...
> Your sp can read from the view :)
>
>
> "RickyP" wrote:
>> Hi Jimbo
>> Thanks for your post, but the model cannot not based on an SP, it is
>> created
>> from a View.
>> Kind regards
>> Ricky
>> "Jimbo" <Jimbo@.discussions.microsoft.com> wrote in message
>> news:B3DF6CA7-175E-42E7-A432-C023612E582D@.microsoft.com...
>> > If you are saying that each report needs to be viewed by each dept and
>> > that
>> > the data should be different on the report depending on who is viewing
>> > it
>> > then:
>> >
>> > On your reports you can have a hidden extra parameter that defaults to
>> > the
>> > user id.
>> >
>> > Then pass this id into your stored procedures that generate the data.
>> >
>> > The stored procedures will do the filtering based on your own internal
>> > logic.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > "RickyP" wrote:
>> >
>> >> Hello
>> >>
>> >> I have designed a report model, but now have a requirement to limit
>> >> data
>> >> access to certain people. I have tried researching about Role based
>> >> security, but nothing seems to make sense. I have to design a suite
>> >> of
>> >> reports 16 in total. However,I have 8 users, all looking at data from
>> >> difference business departments. It has been requested that each
>> >> department
>> >> cannot look at another department's data. The thought of cloning
>> >> these
>> >> 16
>> >> reports, 8 times over, does not seem like a viable option.
>> >>
>> >> Is there an alternative?
>> >>
>> >> Kind regards
>> >>
>> >> Ricky
>> >> (SS2005)
>> >>
>>|||Hello!
Im in the same situation as you. What we have done is that we have used the
feature "Default Security Filter" which allows
you to filter data using an entity which you base upon your view containing
DepartmentID/userID. The entity should also include
an attribute which contains the calculated "GETUSERID()".
You then specify a reliation between your entities to be filtered and this
filter-entity and you also set the "Default Security Attribute"
to be this filter-entity.
Thing is that this causes RS to produce even worse-looking SQL and makes it
hard to debug so Im currently trying to
figure out some other way, because it is almost impossible to debug 300
lines of SQL (most of it rs-schmuck).
Anyway - you can try it out, but try and read up on it on the web somewhere
first...
David|||Hi David
Thanks for your post, it seems like there isn't much in the way of security
on the net at the moment, I can't believe we're the only ones trying to
achieve this? I look in to this further - thanks Daivd.
Kind regards
Ricky
"David" <david_sundstrom_sandegard.nospam@.hotmail.com> wrote in message
news:2C9C703C-BA30-4EAA-ABEA-79F0DE457287@.microsoft.com...
> Hello!
> Im in the same situation as you. What we have done is that we have used
> the feature "Default Security Filter" which allows
> you to filter data using an entity which you base upon your view
> containing DepartmentID/userID. The entity should also include
> an attribute which contains the calculated "GETUSERID()".
> You then specify a reliation between your entities to be filtered and this
> filter-entity and you also set the "Default Security Attribute"
> to be this filter-entity.
> Thing is that this causes RS to produce even worse-looking SQL and makes
> it hard to debug so Im currently trying to
> figure out some other way, because it is almost impossible to debug 300
> lines of SQL (most of it rs-schmuck).
> Anyway - you can try it out, but try and read up on it on the web
> somewhere first...
> David