March 2, 2009 at 3:19 am
I using SQL2005, vb.net as platform for reporting services.
In the report layout, how to hardcode a textfield if record is not exists in dataset?
For example, below is dataset records
monthmth_value
Jan152
Feb 142
Mar120
Jun100
How to hardcode a textfield to "No value" if there is record found where month = "April"?
March 2, 2009 at 7:30 am
You would need to re-write your query to return a row for April. This may mean creating a Months table or CTE and joining to it. Something like:
Select
M.month,
D.value
From
months M Left Outer Join
data D On
M.month = D.month
If you check out the links in my signature line and post accordingly someone will likely offer more specific advice.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 4, 2009 at 1:46 am
Thanks..
my problem is I am have no the CREATE right to the db.
March 9, 2009 at 5:29 pm
siewlian908 (3/4/2009)
Thanks..my problem is I am have no the CREATE right to the db.
You may have to request it from someone who does:)
March 9, 2009 at 5:43 pm
Can you post the SQL you are using? Then we can make suggestions.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2009 at 1:26 am
Thanks... I get the access to create and drop my own table.
But appreciate if you can answer below question,
** I still have no right to create stored procedure!
my current query is confusing.
SELECT cd.touchedWhen, ocmi.name, ocmi.Description, Param_Value =
case when valuetext is null then value
else valuetext end ,
displaysequence
FROM cv3ClientDocument cd
JOIN cv3ObservationDocument od ON cd.Guid = od.OwnerGuid
JOIN cv3ObsCatalogMasterItem ocmi ON od.ObsMasterItemGuid = ocmi.Guid
JOIN cv3Observation o ON od.ObservationGuid = o.Guid
left outer JOIN SCMObsFSListValues fslv
ON od.Observationdocumentguid = fslv.ParentGuid
WHERE cd.ClientGUID = 9000001035200200 AND
cd.GUID = (select top 1 guid from Cv3ClientDocument
where clientguid = 9000001035200200 AND
DocumentName = 'CDM CGH FA DBC' order by touchedWhen desc)
AND od.Active = 1
order by displaysequence
below are return records
touchedWhen name param_value
02/03/2009 15:26SHS_CDM_OI_DbcReferralSrc this is suggested msg of referral source
02/03/2009 15:26SHS_CDM_OI_DbcReferralSrc Medical Clinic
02/03/2009 15:26SHS_CDM_OI_PatHx Patient has been warded to XXXXX
02/03/2009 15:2651_DHLS_obs_Diabetes_c_started 2000
02/03/2009 15:2651_DHLS_obs_Diabetes_c_year 9
02/03/2009 15:2651_DHLS_obs_OM_c_started 2001
02/03/2009 15:2651_DHLS_obs_OM_c_years 8
02/03/2009 15:2651_DHLS_obs_Insulin_c_started 2000
Q1, For example, I would place a textbox. It support to show the param_value if the name = "SHS_CDM_OI_PatHx2", else hardcode the textbox value to "NA".
Can this be done at design window?
Q2, from the return records, i have 2 row records with same name "SHS_CDM_OI_DbcReferralSrc", how can i join their value together?
Q3, This is not related to above sql. Is it I have to done all the sql quey including create, insert, select and drop table in ONE dataset? I have NO right to create stored procedure.
March 10, 2009 at 6:25 pm
To answer Q1:
Your code:
Param_Value =
case when valuetext is null then value
else valuetext end
can be implemeted as:
Param_Value =
isnull(valuetext,value)
which makes the solution to Q1 simpler:
Param_Value =
case when ocmi.name = 'SHS_CDM_OI_PatHx2' then isnull(valuetext,value)
else 'NA' end
To answer Q2:
Search for articles on string concatenation on this site.
(I am not familiar enough with the pros and cons to suggest a particular option, but others might be)
To answer Q3:
Depending on how the application is designed, you'll need a query/stored proc for reading the information, and another for updating any changes made by the user. (You shouldn't need to create and delete tables.)
A SELECT will return the information to display in your application.
When the user changes fields that the application allows them to modify, an UPDATE will make the corresponding changes in the database (normally this is triggered by an 'Apply' or 'OK' button in an application).
If this doesn't answer your question, you may have to be more specific:
Does your application use stored procedures or SQL queries?
How are existing records read and updated?
Why do you think you need to create and drop tables?
March 10, 2009 at 8:07 pm
Thanks for your answer. Pls find my comments
For Q1:
Param_Value =
case when ocmi.name = 'SHS_CDM_OI_PatHx2' then isnull(valuetext,value)
else 'NA' end
This does not fulfill my req. because I have about 500 ocmi.name for user to enter in a page. And the query is only return those ocmi.name which is enter by user.
JOIN should be able to solve this but need to create temp table and there is performance issue.
For Q3:
Yes. This is purely reporting. I refer to vendor example, they use a lot of stored proc.
March 11, 2009 at 4:39 pm
Just to make sure I understand, the report works something like this:
1. User enters a large number of ocmi.name values that they want displayed
2. These are stored in a vb.net 'array' (or equivalent)
3. This array is placed in a temporary table
4. A query is run that joins on ocmi.name equals temptable.name
5. The results are displayed to the user
Edit:
Do the performance problems happen with the original query or only the query which is joined to the temporary table?
How often does the user select their ocmi.name values?
On setup?
Periodically?
Every time the report is run?
What are the table and index definitions for the tables in the query?
In particular, how are the temporary table and ocmi tables defined?
How is the join between ocmi.name and temptable.name defined?
How many rows are returned in a typical query?
Are they paginated or scrolled?
March 11, 2009 at 7:29 pm
the report is working as following.
1. User enters a large number of ocmi.name values in a form. It is about 300 ocmi.name is used to build the form.
2. These are stored in MS SQL table (NOT temporary table), not array format.
3. I have to design a query for user to generate the report. This 3 pages report (fixed format) and this included all the ocmi.name of the form.
This form is use when patient visit to hospital. When doctor is consulting the patient, they will key in this form. After the consultation, they will print this report to patient for their reference.
Conclusion, this is daily form and use very frequent.
What are the table and index definitions for the tables in the query?
In particular, how are the temporary table and ocmi tables defined?
How is the join between ocmi.name and temptable.name defined?
How many rows are returned in a typical query?
Are they paginated or scrolled?
Don't know how to answer this
March 11, 2009 at 8:28 pm
siewlian908 (3/11/2009)
the report is working as following.1. User enters a large number of ocmi.name values in a form. It is about 300 ocmi.name is used to build the form.
2. These are stored in MS SQL table (NOT temporary table), not array format.
3. I have to design a query for user to generate the report. This 3 pages report (fixed format) and this included all the ocmi.name of the form.
This form is use when patient visit to hospital. When doctor is consulting the patient, they will key in this form. After the consultation, they will print this report to patient for their reference.
Conclusion, this is daily form and use very frequent.
What are the table and index definitions for the tables in the query?
In particular, how are the temporary table and ocmi tables defined?
How is the join between ocmi.name and temptable.name defined?
How many rows are returned in a typical query?
Are they paginated or scrolled?
Don't know how to answer this
Can you post create table and create index scripts for the ocmi table and the temporary table?
Do the performance problems happen with the original query, or only the query which is joined to the temporary table?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply