Create a string from report parameters, return checksum

Hi,
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>

Create a Sequential Line Count for Group...

I have a report that groups by item number but then is filtered by a
parameter to only show the top # the user has entered. Does anyone
know if it is possible to number the results I return so that the user
can see how that particular record ranks with other records? Basically
I want to be able to have the user see 1 for the top item sold, 2 for
the second most items sold, etc...Try using
=Rownumber(Groupname)
--
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
"Brent" <Brent.Raymond@.gmail.com> wrote in message
news:1126532242.793502.287600@.o13g2000cwo.googlegroups.com...
>I have a report that groups by item number but then is filtered by a
> parameter to only show the top # the user has entered. Does anyone
> know if it is possible to number the results I return so that the user
> can see how that particular record ranks with other records? Basically
> I want to be able to have the user see 1 for the top item sold, 2 for
> the second most items sold, etc...
>

Crearting a new table via SPROCS

I supply a parameter @.TEMPTABLE for the table name. When I execute my SPROC with the parameter, execution goes thru by I always end up getting "@.TEMPTABLE" as the name of the table instead of the value of the parameter.

Any ideas would be appreciated.

Thank you.

CREATE PROCEDURE sp_CREATE_TEMP_TABLE
(@.TEMPTABLE varchar(30))
AS
CREATE TABLE @.TEMPTABLE (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY]Hi,

your tablename is "@.temptable" ... the server will not use the var, instead it will name the table...

you need to build an cmd-string and execute it. something like this:

declare @.S nvarchar(1000)
select @.S=
'CREATE TABLE'+@.TEMPTABLE+' (
[WOTempID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderScheduleID] [int] NULL ,
[OrderID] [int] NULL ,
[FormCode] [varchar] (4) NULL ,
[AcctName] [varchar] (50) NULL ,
[AcctRetailer] [varchar] (50) NULL ,
[StoreCode] [varchar] (12) NULL ,
[RankCode] [varchar] (3) NULL ,
[RankID] [tinyint] NULL ,
[DeptCode] [int] NULL ,
[WeekNo] [tinyint] NULL ,
[StartDate] [smalldatetime] NULL ,
[EndDate] [smalldatetime] NULL
) ON [PRIMARY] '
exec sp_executesql @.S

markus

CR11 - How do I choose a NULL value (or ALL RECORDS) in a dynamic parameter?

We are using CR11 and our own OLE DB provider that, in turn, uses our SQL Server database.

We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.

In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.

Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.

CR Parameter field Question

Ok, so here's the thing:

I have a VB(6.0) project and a CR report in it. The report draws it's data from a stored procedure. Now, one of the fileds appearing on the report is called Code1, and in this particular case it has values 010, 100, 110 and 230. These numbers represent the item codes my company uses for something not important to us right now ... On the report, Code1 is located in the details section.

What I can't seem 2 figure out is this:

I made a parameter field called Selection. The value type I want to work with is a String. Code1 field also has the same value type. On the " Set default values" tab I entered earlier mentioned values ( 010, 100, 110, 230 ) and assigned description to them.

Now, when I use my parameter field " Selection " with the Select Expert, the formula looks like this: {spProSastavnicePrintQuery.Code1} = {?@.Selection}

The thing that troubles me is when I call upon my report, the entire Details section is EMPTY. BUT! When I use Select Expert and manualy enter

{spProSastavnicePrintQuery.Code1} = 100 for example, the report comes up the

way it's supposed to .. in the Details Section it generates only the data with the
Code1 containing the value of 100. What is the problem ?

Hope this post isn't too confusing .. Thnx in advance ..is it like this
{spProSastavnicePrintQuery.Code1} = {?@.Selection}

or

{spProSastavnicePrintQuery.Code1} = {?Selection}

?Parameter represent parameter and @.Parameter represent formula