Showing posts with label based. Show all posts
Showing posts with label based. Show all posts

Friday, March 30, 2012

How to value parameters for sqldatasource

I set up a sqldatasource based on a stored procedure which takes one parameter. The sqldatasrouce wizard generates the following code for the parameter below. The question is how do I value the DeptID parameter on the load of the form. I tried the following code in the load of the page, but get a null reference error:

Me.SqlDataSource1.InsertParameters("DeptID").DefaultValue = Session("DeptID")

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:FDConn %>"
SelectCommand="GetTruckStatus" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="DeptID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<radG:RadGrid ID="RadGrid1" runat="server">
</radG:RadGrid>

You've set the value of an InsertParameter, but you are performing a Select, not an Insert. Anyway, the SqlDataSource offers a number of different parameter types. One of them is an <asp:SessionParameter>. Use that instead of the generic<asp:Parameter> that you are currently using. You can reconfigure the SqlDataSource to generate it automatically. Click the WHERE button when applying the Select Statement, and in the Source dropdown, choose Session.

|||

Mike,
Thanks for the information. I went back into the configure the sqldatasrouce, but if you chose to use a stored procedure, the button for selecting "Where" is grayed out. I assumed that since the code was generated from vb and from the stored procedure I selected, vb would know the type of parameter it had to generate.


Regards,
Tom

|||

Mike,

I went back into the code and used the "SelectParameter" type and all worked well. Thanks for pointing me in the right direction.

Tom

Monday, March 26, 2012

How to use the same stored procedure to display two different char

I need to display two charts on a report based on the same stored procedure
that accepts a single parameter. I do not wish to be promted for the
parameter. Instead, I would like to pass two different values to each chart
within the report.
Please advise.
Thanks,
KonstantinHow do you know what value to pass it?
Anyway, something to get you going in the right direction. First thing to
realize is that query parameters and report parameters are two different
things. RS automatically creates a report parameter for you for each stored
procedure query parameter it sees. But, you don't have to use it. You can
map it to an expression instead. Click on the ..., parameters tab. On the
right pick change it from mapping to a report parameter and map it to an
expression instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Konstantin Shaumyan" <KonstantinShaumyan@.discussions.microsoft.com> wrote
in message news:3A7E2C6B-FF43-4993-9B21-04EA79316410@.microsoft.com...
>I need to display two charts on a report based on the same stored procedure
> that accepts a single parameter. I do not wish to be promted for the
> parameter. Instead, I would like to pass two different values to each
> chart
> within the report.
> Please advise.
> Thanks,
> Konstantin|||Thanks for the tip.
I created two datasets based on the same stored procedure for each graph and
set my parameter for each dataset to literals.
Everything worked as expected.
Regards,
Konstantin
"Bruce L-C [MVP]" wrote:
> How do you know what value to pass it?
> Anyway, something to get you going in the right direction. First thing to
> realize is that query parameters and report parameters are two different
> things. RS automatically creates a report parameter for you for each stored
> procedure query parameter it sees. But, you don't have to use it. You can
> map it to an expression instead. Click on the ..., parameters tab. On the
> right pick change it from mapping to a report parameter and map it to an
> expression instead.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Konstantin Shaumyan" <KonstantinShaumyan@.discussions.microsoft.com> wrote
> in message news:3A7E2C6B-FF43-4993-9B21-04EA79316410@.microsoft.com...
> >I need to display two charts on a report based on the same stored procedure
> > that accepts a single parameter. I do not wish to be promted for the
> > parameter. Instead, I would like to pass two different values to each
> > chart
> > within the report.
> >
> > Please advise.
> >
> > Thanks,
> > Konstantin
>
>sql

Wednesday, March 21, 2012

How to use SQL query in reports

How can I use SQL commands in Reports?
and I want use JOIN based on LIKE keyThere is a menu option called Database, start there by connecting a database and adding either tables or stored procedures. Under the Database menu you can find the option Show SQL query, just add whatever query you like and there you go :)

this works fine in my CR 8, don't know about other version though..|||For crystal reports 9 and above you can go to Database Expert and under your connection click 'Add Command' and the resulting dialog box lets you add the exact SQL query you want and also lets you pass the parameters to the query...
You can then save this as a command object and also add it to repository.

:thumb:

Friday, March 9, 2012

how to use group by (group tasks based on projects)

Hi folks,

I have a Projects , each project have many tasks now i want to display tasks replated to each project:

for example:

Project1-->task1

task2

task3

task4

Project2-->task4

task5

task6

.............................................projectN.....................

how to write query for this

i have 2 tables:

Project .......>columns are projectid

Task->columns are projectid, taskid

|

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

In this case you would not have to use a group by.

Jens K. Suessmeyer.


http://www.sqlserver2005.de

|||its giving taskid 's null|||

Consider this example

Project

1

2

3

Task

1,A

1,B

2,C

I you want all tasks and their projects, you could write

SELECT ProjectID, TaskID

FROM Task

GROUP BY ProjectID, TaskID

ORDER BY ProjectID, TaskID

This would return

1,A

1,B

2,C

but would provide nothing about Project 3 because it has no tasks.

Given the structure of the table (no repeated tasks), you might even get away with

SELECT ProjectID, TaskID

FROM Task

ORDER BY ProjectID, TaskID

However, if you wanted all projects and to include their tasks, if available, then Jens is correct

Select p.projectid, t.Taskid
From Project p
Left Join Task t --For the case of no assigned tasks
ON p.projectid = t.taskid
order by p.projectid, t.taskid

This would return

1, A

1, B

2, C

3, NULL

The NULL on the last row exists because Project 3 is shown (from the Project table) but there is not equivalent task in the Task table.

The word LEFT from LEFT JOIN tells SQL to return all rows from the Project table and, if available, the row data from Tasks too. If there is no row in Task for a Project in Project, then NULL is placed in the task specific columns.

By dropping the word LEFT, it would limit the result to only those projects that had tasks (but the earlier examples are simpler).

If you want to replace NULL with something more useful, using ISNULL, you could write

SELECT p.projectid, ISNULL(t.Taskid, 'No associated tasks')
FROM Project p
LEFT JOIN Task t ON p.projectid = t.taskid
ORDER BY p.projectid, t.taskid

This would now return

1, A

1, B

2, C

3, No associated tasks

regards,

Niall.

Wednesday, March 7, 2012

How to use cubes of 2000 in 2005

We have to create reports based on cubes, which were designed using SQL 2000. We have migrated to SQL 2005. We have the cubes (.cub files) and a database of SQL 2000. We need to design few sample reports based on these cubes (specifically some charts). We are quite new to Analysis Services.

Can anyone guide us or point to the right direction, what are the steps to be followed here, like do we need to import these cubes into a 2005 database or can these be just copied in a folder and referred directly. basically where and how to start from.

Any suggestion or help is appreciated.

It is not possible to re-use local cube files (.cub) that were created with Analysis Services 2000 in Analysis Services 2005.

You will have to ether Migrate your AS2000 server cubes to AS2005 or to create new cubes in AS2005 from scratch.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

hi Edward,

i also had similar queries and got a reply as follows

"

This Project REAL paper should give you some guidance:

http://www.microsoft.com/technet/prodtechnol/sql/2005/asmigrtn.mspx

>>

Analysis Services 2005 Migration

Published: August 19, 2005

SQL Server Technical Article
Applies To: SQL Server 2005

Summary: This paper guides you through the process of migrating to Analysis Services in SQL Server 2005. The Unified Dimensional Model (UDM) provides new design opportunities for cube developers. The UDM has brought together the relational reporting and OLAP reporting worlds to provide a unified forum for both data request environments. Understanding the new design options and how they affect your organization will optimize migration. The Migration Wizard is a fast and effective tool for moving your existing cubes to Analysis Services 2005. This paper introduces the wizard and helps you determine whether you should use it. In some cases it is better to redesign from scratch to ensure that you are properly leveraging the new features of Analysis Services 2005.

>>

"

please let me know if this is correct as i haven't tried out and i would also like to know how to try it out.

thanks,

|||

I think you would benefit from the information in the sources you mention.

I dont think what I mention above comes in any contradiction with the information you get from the papers you mention. Is there specific point you are not certain about?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to use combo box for a parameter on reporting services?

I have created a reporting services report based on data elements from an Analysis Services cube. I have selected one of the data element as the parameter on the report. The default for the selection list of the parameter is a drop down box. My users didn't like to go through a long list of values in the drop down box to find a specific value. How can I convert the drop down box to a combo box so that my users can search for a specific value and therefore shorten the selection list.

Any help is greatly appreciated!!!

Mitch

Hello. Combo boxes are not a part of SSRS2000/2005.

Your best choice to simplify parameter search is to build cascading parameters.

Here is a link to an article of how to do this with SSAS-cubes

http://www.databasejournal.com/features/mssql/article.php/3653271

HTH

Thomas Ivarsson

Friday, February 24, 2012

How to use a Wildcard with the value for the parameter?

I have a report that selects certain records based on what the user enters for the parameter but I'm trying to allow the user to enter a wildcard and show the relevant records. I used like and had the user add an * or even a % but it still returns nothing. Records are returned only when the user enters the full value with no wildcards. Help.

SELECT DISTINCT
PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID, PDFBM.REQUIRED_QTY, ICFPM.PART_DESC, ICFPM.DWG_REV, ICFPM.PENDING_ECN,
dbo.ICFSS.STORES_CODE, dbo.ICFSS.STOCK_LOCATION
FROM dbo.ICFPM ICFPM INNER JOIN
dbo.PDFBM PDFBM ON ICFPM.PART_ID = PDFBM.ASSEMBLY_ID INNER JOIN
dbo.ICFSS ON ICFPM.PART_ID = dbo.ICFSS.PART_ID
WHERE (PDFBM.COMPONENT_ID LIKE @.componentid) AND (PDFBM.ASSEMBLY_ID LIKE '350%')
ORDER BY PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID

A

I'm not sure but try converting the query to a dynamic query:

="SELECT DISTINCT
PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID, PDFBM.REQUIRED_QTY, ICFPM.PART_DESC, ICFPM.DWG_REV, ICFPM.PENDING_ECN,
dbo.ICFSS.STORES_CODE, dbo.ICFSS.STOCK_LOCATION
FROM dbo.ICFPM ICFPM INNER JOIN
dbo.PDFBM PDFBM ON ICFPM.PART_ID = PDFBM.ASSEMBLY_ID INNER JOIN
dbo.ICFSS ON ICFPM.PART_ID = dbo.ICFSS.PART_ID
WHERE (PDFBM.COMPONENT_ID LIKE '" + Parameters!componentid.Value + "') AND (PDFBM.ASSEMBLY_ID LIKE '350%')
ORDER BY PDFBM.ASSEMBLY_ID, PDFBM.COMPONENT_ID"

Regards,

Telmo Moreira

|||

try:

LIKE @.parameter + '%')

drop the user adding the wildcard char

Sunday, February 19, 2012

How to use "delete" query for more than two tables?

hi all,
i need to perform a operation where in to delete tableA,tableB based on some
conditions, here is the query,
***********************QUERY************
**************
delete from tblSS_ShiftPeriodStamps,tblSS_ShiftStamp
s where
tblSS_ShiftPeriodStamps.StartTime <= @.PurgeTime and
tblSS_ShiftPeriodStamps.EndTime <= @.PurgeTime and
tblSS_ShiftPeriodStamps.ShiftStartTime <= @.PurgeTime and
tblSS_ShiftStamps.ShiftEndTime <= @.PurgeTime and
tblSS_ShiftPeriodStamps.ObjectID = tblSS_ShiftStamps.ObjectID
***********************QUERY************
**************
how will i perform the above operation so that the checks are performed, for
the deletion of first table, i need to check in the second table also.
i would be happy if my friends could help me out...
thanks in advance,
regards,
ChakkaradeepOne object per Delete statement only.
Jens Suessmeyer.