• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 437
  • Last Modified:

Crosstab Report Works, but I need to add to it

Hi All,

I have worked hard and finally gotten my dynamic crosstab report working.  Now I would like to add data from a different record source.  

The crosstab report has dppm by model & category and month.  I need to add a input qty as another row, but it I can't put that in my crosstab query or it will create too many categories and mess up the look of the report.  If I made another crosstab query with the same months and used the input qty instead, how could I add that information to my report?
How it is now.How I want it to be...
0
Elena Quinn
Asked:
Elena Quinn
  • 5
  • 3
1 Solution
 
IrogSintaCommented:
You question is similar to another question I just worked on.  In that question, the Asker was trying to get another row called BUSK added to his crosstab under the ModKit heading.  You can find the details on this link.

Basically, you would need to create a query similar to the query or table used as a source for your Crosstab query.  This new query should have the same number of columns but should have the Input Qty details you mentioned.  Also, the heading that normally would be the Category should be assigned the text "Usage."    

You would then create a Union Query using the original and new query and this would be used as the source for the crosstab instead.  

Hopefully you would be able to follow my explanation using the link I provided.  If not, it would be easiest if you upload a copy of your database.
0
 
Elena QuinnAuthor Commented:
Your link seems to be broken.  It sends me to a list of questions and answers, but Q_28240080 is not one of them.  I will look into Union Queries, though and see if I can get there from here.  I'll post back either way.
0
 
Elena QuinnAuthor Commented:
Hi Again,

Love the Union idea.  It worked to get the usage data into the query in the same format.  So far so good.  However, in my report, I have a total row as well, only I don't want to add the usage row to that.  Any ideas how I might go about that?

Also, how can I make sure my categories are in a certain order?  I need the usage value at the very bottom, and the category 1 to be at the top, just below the totals.  The other categories don't really matter.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Elena QuinnAuthor Commented:
So, nevermind on the total thing.  I created yet another query for that and added it to the same union that feeds the report.  So now all the data is correct.  But it's still in the wrong order.  Still need help on that.
0
 
Elena QuinnAuthor Commented:
OK, I got it.  I went into the Sorting and Grouping in the report and was able to sort based on an expression like the below:

IIf([Category]="Gross DPPM",1,IIf([Category]="A",2,IIf([Category]="B",3,IIf([Category]="D",4,IIf([Category]="C",5,IIf([Category]="Usage",6,7))))))

Open in new window

Thought I would post this here in case others are looking for similar information.
0
 
Elena QuinnAuthor Commented:
Love the Union idea and it did what I wanted.
0
 
IrogSintaCommented:
Sorry about that broken link but great job in figuring the rest of this out!  
I also fixed the link in case anyone else happens upon this question.

Ron
0
 
IrogSintaCommented:
By the way, you can also use the Switch function in place of nested IIF statements.
Switch([Category]="Gross DPPM",1,[Category]="A",2,[Category]="B",3,[Category]="D",4,[Category]="C",5,[Category]="Usage",6)

Open in new window


Ron
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now